PDA

Click to See Complete Forum and Search --> : ASP & VBScript Problem

kwilliams
06-09-2004, 03:27 PM
Hello,

I'm somewhat of a newbie to VBScript, so please keep that in mind. I've created a form that is going to be an "In/Out" board for a few employees. It simplay has 3 employees called "Tech's", and 2 radiobuttons by each name to that allows for them to log in and out. When they click on either radiobutton, the form automatically gets submitted, and the data gets inserted into a small DB table in SQL Server 2k.

My problem is in the actual form's radiobuttons that are located in a loop. If I name the radiobuttons "rbInOut", it does actually submit the "In" value to the DB table depending on the tech. But since the loop makes all of the radiobuttons keep the one name, I can't "check" the radiobutton as a display because they are all named the same.

So I tried having a number on the end of the RB's name (rbInOut1, rbInOut2, etc.), but then they wouldn't update the DB table. So I guess it's kind of the chicken & the egg problem.

Here's the code for my page, and maybe you see something that I'm missing in my code. Any help would greatly be appreciated. Thanks.

<%
Response.Buffer = True
Response.Write "</head><body>"
%>
<%
DIM strLogin, strUsername
strLogin = Request.ServerVariables("LOGON_USER")
'Capture everyting from position 10
strUsername = MID(strLogin, 10)
If strUsername = "" Then
Response.Redirect "http://SERVERNAME/HelpDesk/noaccess.asp"
End If

'The Form Selector:
Response.Write "<form name='techs' action='http://SERVERNAME/FOLDER/PAGENAME.asp' method='post'>"
Response.Write "<table width='150' border='0' bgcolor='#FFFFCC'><tr>"
sqlTech = "SELECT * FROM Tech_info ORDER BY Tech"
'------------------Database Query-----------------------
strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DATABASENAME; User ID=USERID; Password=PW"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectString

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open sqlTech, objConn
Response.Write "<td valign='top' width='150' colspan='3'>"
Response.Write "<strong>Help Desk - In/Out Board</strong><br></td></tr><tr>"
Response.Write "<td valign='top' width='100'>"
Response.Write "<strong>Technicians</strong></td>"
Response.Write "<td valign='top' width='25' align='center'>"
Response.Write "<strong>IN</strong></td>"
Response.Write "<td valign='top' width='25' align='center'>"
Response.Write "<strong>OUT</strong></td>"
While NOT objRS.EOF
'Looped radiobuttons
Dim strInOut
strInOut = objRS("InOut")
If strInOut = "In" Then
strStatus_In = " Checked"
strStatus_Out = ""
Else If strInOut = "Out" Then
strStatus_In = ""
strStatus_Out = " Checked"
End If
End If

'Dim strDBTech, strCount
'strDBTech = objRS("Tech")
'If strDBTech = "Tech1" Then
'strCount = "1"
'Else If strDBTech = "Tech2" Then
'strCount = "2"
'Else If strDBTech = "Tech3" Then
'strCount = "3"
'End If
'End If
'End If

Response.Write "<tr><td>" & objRS("Tech") & "</td>"
Response.Write "<td><input type='radio' name='rbInOut' value='" & objRS("Tech") & "_In' onClick='submit()'" & strStatus_In & "></td>"
Response.Write "<td><input type='radio' name='rbInOut' value='" & objRS("Tech") & "_Out' onClick='submit()'" & strStatus_Out & ">" & strInOut & "</td></tr>"
strInOut = Request.Form("rbInOut")
'Pass looped variables
strInOut = Request.Form("rbInOut")
If strInOut = "Tech1_In" Then
strTech = "Tech1"
strStatus = "In"
Else If strInOut = "Tech1_Out" Then
strTech = "Tech1"
strStatus = "Out"
End If
End If
If strInOut = "Tech2_In" Then
strTech = "Tech2"
strStatus = "In"
Else If strInOut = "Tech2_Out" Then
strTech = "Tech2"
strStatus = "Out"
End If
End If
If strInOut = "Tech3_In" Then
strTech = "Tech3"
strStatus = "In"
Else If strInOut = "Tech3_Out" Then
strTech = "Tech3"
strStatus = "Out"
End If
End If
objRS.MoveNext
Wend
Response.Write "<input type='hidden' name='hfUpdate' value='true'>"
objRS.Close
Set objRS = nothing

objConn.Close
Set objConn = nothing
'------------------End Database Query-------------------

'Update tech status
strForm = Request.Form("hfUpdate")
If strForm = "true" Then
'Update statement
sqlInOut = "UPDATE dbo.Tech_info SET InOut = '" & strStatus & "' WHERE Tech = '" & strTech & "'"

'------------------Database Query-----------------------
strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DATABASENAME; User ID=USERID; Password=PW"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectString

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open sqlInOut, objConn
Set objRS = nothing
Set objConn = nothing
Response.Write "<strong>Form Value: </strong>" & strStatus & "<br>"
Response.Write "<strong>Tech: </strong>" & strTech
End If
'------------------End Database Query-------------------

Response.Write "</td></tr></table></form>"
Response.Write "</table>"
Response.Write "</body></html>"
%>
buntine
06-09-2004, 11:15 PM
If you have more than one control with the same name, ASP will send the values in a comma-delimited string. So you can use some string handling functions to create an array.

Dim arrInOut
arrInOut = Split(Request.Form("rbInOut"), ",")

For i = 0 To UBound(arrInOut) - 1
Response.Write (arrInOut(i)) & "<br />")
Next

I hope i understood you correctly.

Regards,
Andrew Buntine.
kwilliams
06-10-2004, 09:19 AM
Hi buntine,

I understand what you're saying, and I will give it a try. I'll let you know what happens.

P.S. I wanted to let you know that I've greatly appreciated your wonderful help & quick responses in the past & present. Keep up the good work:)

KWilliams
buntine
06-10-2004, 10:48 AM
Thank you.

Its good to know im helping out. ;)
kwilliams
06-10-2004, 03:05 PM
Hi buntine,

Maybe I'm doing this wrong, but I was able to get your array to work at actually updating the DB "In/Out" column with the correct data, but I'm still having a problem when attempting to display the value in the radiobuttons. It's only displaying the last records value. Here's the part of the code that I revised with your array:
'------------------Database Query-----------------------
strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DATABASENAME; User ID=USERID; Password=PW"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectString

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open sqlTech, objConn
Response.Write "<td valign='top' width='150' colspan='3'>"
Response.Write "<strong>Help Desk - In/Out Board</strong><br></td></tr><tr>"
Response.Write "<td valign='top' width='100'>"
Response.Write "<strong>Technicians</strong></td>"
Response.Write "<td valign='top' width='25' align='center'>"
Response.Write "<strong>IN</strong></td>"
Response.Write "<td valign='top' width='25' align='center'>"
Response.Write "<strong>OUT</strong></td>"
While NOT objRS.EOF
'Looped radiobuttons
Dim strInOut
strInOut = objRS("InOut")
If strInOut = "In" Then
strStatus_In = " Checked"
strStatus_Out = ""
Else If strInOut = "Out" Then
strStatus_In = ""
strStatus_Out = " Checked"
End If
End If

'Dim strDBTech, strCount
'strDBTech = objRS("Tech")
'If strDBTech = "Tech1" Then
'strCount = "1"
'Else If strDBTech = "Tech2" Then
'strCount = "2"
'Else If strDBTech = "Tech3" Then
'strCount = "3"
'End If
'End If
'End If

Response.Write "<tr><td>" & objRS("Tech") & "</td>"
Response.Write "<td><input type='radio' name='rbInOut' value='" & objRS("Tech") & "_In' onClick='submit()'" & strStatus_In & "></td>"
Response.Write "<td><input type='radio' name='rbInOut' value='" & objRS("Tech") & "_Out' onClick='submit()'" & strStatus_Out & ">" & strInOut & "</td></tr>"
strInOut = Request.Form("rbInOut")
'Pass looped variables
strInOut = Request.Form("rbInOut")
If strInOut = "Tech1_In" Then
strTech = "Tech1"
strStatus = "In"
Else If strInOut = "Tech1_Out" Then
strTech = "Tech1"
strStatus = "Out"
End If
End If
If strInOut = "Tech2_In" Then
strTech = "Tech2"
strStatus = "In"
Else If strInOut = "Tech2_Out" Then
strTech = "Tech2"
strStatus = "Out"
End If
End If
If strInOut = "Tech3_In" Then
strTech = "Tech3"
strStatus = "In"
Else If strInOut = "Tech3_Out" Then
strTech = "Tech3"
strStatus = "Out"
End If
End If
objRS.MoveNext
Wend
Response.Write "<input type='hidden' name='hfUpdate' value='true'>"
objRS.Close
Set objRS = nothing

objConn.Close
Set objConn = nothing
'------------------End Database Query-------------------
'Update tech status
strForm = Request.Form("hfUpdate")
If strForm = "true" Then

Dim arrInOut
arrInOut = Split(Request.Form("rbInOut"), ",")

For i = 0 To UBound(arrInOut) - 1
Response.Write arrInOut(i) & "<br>"
Next

'Pass looped variables
strInOut = arrInOut(i)
If strInOut = "Brad Miller_In" Then
strTech = "Brad Miller"
strStatus = "In"
Else If strInOut = "Brad Miller_Out" Then
strTech = "Brad Miller"
strStatus = "Out"
End If
End If
If strInOut = "Jason Leib_In" Then
strTech = "Jason Leib"
strStatus = "In"
Else If strInOut = "Jason Leib_Out" Then
strTech = "Jason Leib"
strStatus = "Out"
End If
End If
If strInOut = "Ollie Wenger_In" Then
strTech = "Ollie Wenger"
strStatus = "In"
Else If strInOut = "Ollie Wenger_Out" Then
strTech = "Ollie Wenger"
strStatus = "Out"
End If
End If
Do you see something that I'm obviously not getting? Thanks again for any help.
buntine
06-10-2004, 08:31 PM
This is because you are only storing the last member of the array in the strInOut variable.

Tbe For-Next loop i wrote was simply to demonstrate how you would go about printing each value to the page.

You may need to put a portion of your code into a loop so you can access each member of the array.

'Update tech status
strForm = Request.Form("hfUpdate")

For i = 0 To UBound(arrInOut) - 1

If strForm = "true" Then

Dim arrInOut
arrInOut = Split(Request.Form("rbInOut"), ",")

strInOut = arrInOut(i)

If strInOut = "Brad Miller_In" Then
strTech = "Brad Miller"
strStatus = "In"
Else If strInOut = "Brad Miller_Out" Then
strTech = "Brad Miller"
strStatus = "Out"
End If

End If

If strInOut = "Jason Leib_In" Then
strTech = "Jason Leib"
strStatus = "In"
Else If strInOut = "Jason Leib_Out" Then
strTech = "Jason Leib"
strStatus = "Out"
End If
End If

If strInOut = "Ollie Wenger_In" Then
strTech = "Ollie Wenger"
strStatus = "In"
Else If strInOut = "Ollie Wenger_Out" Then
strTech = "Ollie Wenger"
strStatus = "Out"
End If

Next

Im not exactly sure what your trying to do, though.

But the reason you are not getting all of the values is because you were only passing the last member to the variable strInOut.

Regards,
Andrew Buntine.
kwilliams
06-11-2004, 12:04 PM
Hello buntine,

I placed your suggested code on my page, but I received the following error message:
Microsoft VBScript compilation error '800a041f'

Unexpected 'Next'

/helpdesk/tech_inout.asp, line 101

Next
^
...so then I moved the "Next" under the Update statement, but then I received this error message:
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'UBound'

/helpdesk/tech_inout.asp, line 66

So I tested out my most recent setup, and it appears to be working with the Update statement just fine. It's the looped display of the radio buttons that I'm having the problem with. This code is meant to add a "checked" into the radiobutton's properties if that RB matches the DB value:
'Looped radiobuttons
Dim strRBLoop
strRBLoop = objRS("InOut")
If strRBLoop = "In" Then
strStatus_In = " Checked"
Else If strRBLoop = "Out" Then
strStatus_Out = " Checked"
End If
End If

I think that the problem with the looped RB display relates to this bit of code, because it's only selecting the last pulled value from the DB with strRBLoop = objRS("InOut") above. I have this code placed in the loop itself, so I think that's the problem. I'll use your suggested code to attempt to create an array with the objRS("InOut") value, and place those values in the loop. If you have anymore suggestions on how I can accomplish this, please feel free to forward them to me. Thanks so much buntine.
buntine
06-11-2004, 12:10 PM
Yer, i would implement the code i gave you earlier. It should solve the problem. *fingers crossed*
kwilliams
06-11-2004, 03:55 PM
Hello again buntine,

Well unfortunately I was unable to get the code to work for me like I had hoped. However I was able to get the actual Update statement to work with the displayed form data. I tried in implement your suggested array code with my code for the display of the radiobuttons, but it never worked. I only received those error messages that I posted previously.

So I guess for now I'm stuck with a page that actually works by inserting the data into the database table, but doesn't actually display the correct data from the database dynamically. If you see anything in my most current code (see below) that could make the display part work, that would be great. Hope to hear from you soon. Have a great weekend.

This is the code for the page:

<%
'The Form Selector:
Response.Write "<form name='techs' action='http://SERVERNAME/FOLDER/tech_inout.asp' method='post'>"
Response.Write "<table width='150' border='0' bgcolor='#FFFFCC'><tr>"
sqlTech = "SELECT * FROM Tech_info ORDER BY Tech"
'------------------Database Query-----------------------
strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DBNAME; User ID=UID; Password=PW"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectString

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open sqlTech, objConn

Response.Write "<td valign='top' width='150' colspan='3'>"
Response.Write "<strong>Help Desk - In/Out Board</strong><br></td></tr><tr>"
Response.Write "<td valign='top' width='100'>"
Response.Write "<strong>Technicians</strong></td>"
Response.Write "<td valign='top' width='25' align='center'>"
Response.Write "<strong>IN</strong></td>"
Response.Write "<td valign='top' width='25' align='center'>"
Response.Write "<strong>OUT</strong></td>"

While NOT objRS.EOF

'Looped radiobuttons
strDBTech = objRS("Tech")
strDBInOut = objRS("InOut")
If strDBInOut = "In" Then
strStatus_In = " checked"
strStatus_Out = ""
Else If strDBInOut = "Out" Then
strStatus_Out = " checked"
strStatus_In = ""
End If
End If

Response.Write "<tr><td>" & objRS("Tech") & "</td>"
Response.Write "<td><input type='radio' name='rbInOut' value='" & strDBTech & "_In' onClick='submit()'" & strStatus_In & "></td>"
Response.Write "<td><input type='radio' name='rbInOut' value='" & strDBTech & "_Out' onClick='submit()'" & strStatus_Out & "></td></tr>"
Response.Write "<input type='hidden' name='hfTech' value='" & objRS("Tech") & "'>"
objRS.MoveNext
Wend
Response.Write "<input type='hidden' name='hfUpdate' value='true'>"
objRS.Close
Set objRS = nothing

objConn.Close
Set objConn = nothing
'------------------End Database Query-------------------

'Update tech status
strForm = Request.Form("hfUpdate")
strFormTech = Request.Form("hfTech")
strFormInOut = Request.Form("rbInOut")
If strForm = "true" Then
If strFormInOut = "Tech1_In" Then
strTech = "Tech1"
strStatus = "In"
Else If strFormInOut = "Tech1_Out" Then
strTech = "Tech1"
strStatus = "Out"
End If
End If

If strFormInOut = "Tech2_In" Then
strTech = "Tech2"
strStatus = "In"
Else If strFormInOut = "Tech2_Out" Then
strTech = "Tech2"
strStatus = "Out"
End If
End If

If strFormInOut = "Tech3_In" Then
strTech = "Tech3"
strStatus = "In"
Else If strFormInOut = "Tech3_Out" Then
strTech = "Tech3"
strStatus = "Out"
End If
End If

'Update statement
sqlInOut = "UPDATE dbo.Tech_info SET InOut = '" & strStatus & "' WHERE Tech = '" & strTech & "'"

'------------------Database Query-----------------------
strConnectString = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DBNAME; User ID=UID; Password=PW"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnectString

Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open sqlInOut, objConn

Response.Write "<strong>Status: </strong>" & strStatus & "<br>"
Response.Write "<strong>Form In/Out: </strong>" & strInOut & "<br>"
Response.Write "<strong>DB In/Out: </strong>" & strFormTech & "<br>"
Response.Write "<strong>Tech: </strong>" & strTech
Set objRS = nothing
Set objConn = nothing
End If
'------------------End Database Query-------------------

Response.Write "</td></tr></table></form>"
Response.Write "</table>"
Response.Write "</body></html>"
%>