Shoot me now! Trying to learn ASP/ADO...

[tedleonard]

Hi all,
Once again, I am turning to the crew at sitepoint for help. I have always used PHP/MYSQL for database driven websites, until I was given my current project of working on my employer's intranet. Now, I have to make a simple easy database driven site using ASP/ADO and an access database. So far, things have been going ok, but I've hit a roadblock. This is what I'm trying to do...

I have a list of people that work in the water department and sewer department, their home and cell phone numbers, and a boolean entry for whether or not they are the on-call person for that department over the weekend. It will be updated via a simple form that is divided up into the two departments' personnel with a radio button selecting one person from each department as the 'on call' person, with an additional text field at the bottom to enter any misc notes. It's on the intranet so there's no way to ask someone to take a look at the code except to copy and paste it, so please don't mind the mess...

<!--#INCLUDE FILE="include/header.inc.txt"-->
 
<center><table border="0" width="100%">
 
<%
 
dim formresponse
 
dim check
 
if Request.form("formresponse")="" then
 
set rs = server.createobject("ADODB.recordset")
 
rs.open "select name, home, cell, pager, standby from waterguys where type='water'", conn
 
response.write("<tr><th colspan=5>Wastewater Standby Personnel</th></tr>" & vbCrLf)
 
response.write("<tr><th></th><th>Name</th><th>Home</th><th>Cell</th></tr>" & vbCrLf)
 
response.write("<form method=""post"" action=""edit_water.asp"">" & vbCrLf)
 
do until rs.EOF
 
if rs("standby")=True then 
 
check="<input type=""radio"" name=""waterstandby"" checked>"
 
else 
 
check="<input type=""radio"" name=""waterstandby"">"
 
end if
 
response.write("<tr><td>")
 
response.write(check)
 
response.write("<input type=""hidden"" name=""formresponse"" value=""" & formresponse & """></td>" & "<td><b>" & rs("name") & "</b></td><td>" & rs("home") & "</td><td>" & rs("cell") & "</td></tr>" & vbCrLf)
 
rs.movenext
 
loop
 
set rs = server.createobject("ADODB.recordset")
 
rs.open "select name, home, cell, pager, standby from waterguys where type='sewer'", conn
 
response.write("<tr><th colspan=""4"">Water Dept. Standby Personnel</th></tr>" & vbCrLf)
 
response.write("<tr><th></th><th>Name</th><th>Home</th><th>Cell</th></tr>" & vbCrLf)
 
do until rs.EOF
 
if rs("standby")=True then 
 
check="<input type=""radio"" name=""sewerstandby"" checked>" 
 
else 
 
check="<input type=""radio"" name=""sewerstandby"">"
 
end if
 
response.write("<tr><td>")
 
response.write(check)
 
response.write("</td>" & "<td><b>" & rs("name") & "</b></td><td>" & rs("home") & "</td><td>" & rs("cell") & "</td></tr>" & vbCrLf)
 
rs.movenext
 
loop
 
set rs = server.createobject("ADODB.recordset")
 
rs.open "select notetext from notes where noteid=1", conn
 
response.write("<tr><th colspan=""4"">Additional Notes</th></tr>" & vbCrLf)
 
response.write("<tr><td colspan=""4""><input type=""text"" size=""65"" name=""notetext"" value=""" & rs("notetext") & """></td></tr>" & vbCrLf) 
 
response.write("<tr><td colspan=""4""><center><input type=""submit"" value=""Submit Changes""></form></center></td></tr>" & vbCrLf) 
 
else
 
formresponse="y"
 
sql="UPDATE waterguys SET "
 
sql=sql & "standby='" & Request.form("waterstandby") & "',"
 
sql=sql & " WHERE type='water'"
 
on error resume next
 
conn.Execute sql
 
if err<>0 then
 
response.write("Cannot update")
 
else
 
response.write("Update was successful")
 
end if
 
end if
 
conn.close
 
%>
 
</table></center>
 
<!--#INCLUDE FILE="include/footer.inc.txt"-->

Note that I haven't attempted to get anything else except the water department people working.
No matter what I try to change, i always get the "cannot update" error messgae. Any help at all would be appreciated!!! Thanks!!!

[r937]

you have a comma in front of the WHERE keyword in your UPDATE statement

you should probably revise your code so that it will display the actual database syntax error that this produces (except i can't tell you how, because i don't do asp or ado)

[tedleonard]

I removed the comma, but still get the same result.

[jimfraser]

Your waterstandby checkboxes have no value, so your update query is putting an empty string into whatever type of variable that is.

I suspect you want to put the name or id of the person as the value for each checkbox:

check="<input type=""radio"" name=""waterstandby"" value=""" & rs("name") & """"

if rs("standby")=True then 
  check = check & " CHECKED"
End If

check= check & ">"

[tedleonard]

Thanks for your response. This is where I'm at now...

<!--#INCLUDE FILE="include/header.inc.txt"-->

<center><table border="0" width="100%">

<%

dim formresponse

dim check

if Request.form("formresponse")="" then

set rsWater = server.createobject("ADODB.recordset")

rsWater.open "select name, home, cell, pager, standby from waterguys where type='water'", conn

response.write("<tr><th colspan=5>Wastewater Standby Personnel</th></tr>" & vbCrLf)

response.write("<tr><th></th><th>Name</th><th>Home</th><th>Cell</th></tr>" & vbCrLf)

response.write("<form method=""post"" action=""edit_water.asp"">" & vbCrLf)

do until rsWater.EOF

check="<input type=""radio"" name=""waterstandby"" value=""" & rsWater("name") & """"

if rsWater("standby")="True" then 

check=check & " CHECKED" 

end if

check=check & ">"

response.write("<tr><td>")

response.write(check)

response.write("<input type=""hidden"" name=""formresponse"" value=""" & formresponse & """></td>" & "<td><b>" & rsWater("name") & "</b></td><td>" & rsWater("home") & "</td><td>" & rsWater("cell") & "</td></tr>" & vbCrLf)

rsWater.movenext

loop

set rsSewer = server.createobject("ADODB.recordset")

rsSewer.open "select name, home, cell, pager, standby from waterguys where type='sewer'", conn

response.write("<tr><th colspan=""4"">Water Dept. Standby Personnel</th></tr>" & vbCrLf)

response.write("<tr><th></th><th>Name</th><th>Home</th><th>Cell</th></tr>" & vbCrLf)

do until rsSewer.EOF

check="<input type=""radio"" name=""sewerstandby"" value=""" & rsSewer("name") & """" 

if rsSewer("standby")="True" then 

check=check & " CHECKED" 

end if

check=check & ">"

response.write("<tr><td>")

response.write(check)

response.write("</td><td><b>" & rsSewer("name") & "</b></td><td>" & rsSewer("home") & "</td><td>" & rsSewer("cell") & "</td></tr>" & vbCrLf)

rsSewer.movenext

loop

set rsNote = server.createobject("ADODB.recordset")

rsNote.open "select notetext from notes where noteid=1", conn

response.write("<tr><th colspan=""4"">Additional Notes</th></tr>" & vbCrLf)

response.write("<tr><td colspan=""4""><input type=""text"" size=""65"" name=""notetext"" value=""" & rsNote("notetext") & """></td></tr>" & vbCrLf) 

response.write("<tr><td colspan=""4""><center><input type=""submit"" value=""Submit Changes""></form></center></td></tr>" & vbCrLf) 

else

formresponse="y"

sql="UPDATE waterguys SET "

sql=sql & "standby=True"

sql=sql & " WHERE name='" & Request.form("waterstandby") & "' and type='water'; "

sql=sql & " standby=False"

sql=sql & " WHERE name!='" & Request.form("waterstandby") & "' and type='water'; "

sql=sql & "standby=True"

sql=sql & " WHERE name='" & Request.form("sewerstandby") & "' and type='sewer'; "

sql=sql & " standby=False"

sql=sql & " WHERE name!='" & Request.form("sewerstandby") & "' and type='sewer'; "

on error resume next

conn.Execute sql

if err<>0 then

response.write("Cannot update waterstandby is " & Request.form("waterstandby") & " and " & Request.form("sewerstandby") & vbcrlf)

else

response.write("Update was successful")

end if

end if

rsWater.close

rsSewer.close

rsNote.close

conn.close

%>

</table></center>

<!--#INCLUDE FILE="include/footer.inc.txt"-->

The problem is that I'm still getting an error. I've modified the error code a bit to show the values of Request.form("waterstandby") and Request.form("sewerstandby") which are working, showing the name of the person I select, so I'm guessing that it's my SQL code structure that needs the help, but it's been so long since Ive worked with this stuff that I can't remember exactly how it goes... can someone give me a hand? Thank you so much!

[r937]

your UPDATE is wrong, you can have only one WHERE clause

[tedleonard]

Is it possible to have more than one update?

[r937]

is this what you mean?

update waterguys 
   set standby = True
 where (
       name = '" & Request.form("waterstandby") & "' 
   and type = 'water'
       )
    or (
       name = '" & Request.form("sewerstandby") & "' 
   and type = 'sewer'
       )
    
update waterguys 
   set standby = False
 where (
       name <> '" & Request.form("waterstandby") & "' 
   and type = 'water'
       )
    or (
       name <> '" & Request.form("sewerstandby") & "' 
   and type = 'sewer'
       )

you could also combine those two, but it would be even more complex, and not as efficient

[tedleonard]

Yes, thank you! I have never worked with such a complex update statement. Your assistance is much appreciated!