SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict tedleonard's Avatar
    Join Date
    Jun 2000
    Location
    Dark side of the moon
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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...

    Code:
    
    <!--#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!!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict tedleonard's Avatar
    Join Date
    Jun 2000
    Location
    Dark side of the moon
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I removed the comma, but still get the same result.

  4. #4
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    check="<input type=""radio"" name=""waterstandby"" value=""" & rs("name") & """"
    
    if rs("standby")=True then 
      check = check & " CHECKED"
    End If
    
    check= check & ">"

  5. #5
    SitePoint Addict tedleonard's Avatar
    Join Date
    Jun 2000
    Location
    Dark side of the moon
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your response. This is where I'm at now...
    Code:
    
    <!--#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!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your UPDATE is wrong, you can have only one WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict tedleonard's Avatar
    Join Date
    Jun 2000
    Location
    Dark side of the moon
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it possible to have more than one update?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    is this what you mean?
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict tedleonard's Avatar
    Join Date
    Jun 2000
    Location
    Dark side of the moon
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, thank you! I have never worked with such a complex update statement. Your assistance is much appreciated!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •