SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: updating table

  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    updating table

    Hey guys, i got the following form, sCB is used to get the ID fields of the selected pets that their adoptionstatus field needs to be changed from Available to Not Available.
    i got 2 types of pets, "Month dogs" and "dogs", i wanna check if one of the IDs sCB gets has petType "month dog", if so, the SQL statement will change also its adoptionstatus to "not available" and also its type to "dog", else it'll just change the adoptionStatus to "not Available", how do i do that ?

    Atm the form i got has just the sql to change the status from available to not available:
    Code ASP:
    <%
    ADB= "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/db.mdb")
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open ADB
    sCB = Request.Form ("cb")
    sql = "UPDATE Animals SET adoptionstatus='Not available' WHERE fileID IN (" & sCB & ")"
    conn.execute (sql)
    conn.Close
    Set conn= Nothing
    response.redirect "edit_Pets.asp"
    %>

    Thanks in advance,
    Ulthane

  2. #2
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you give an example of what information your sCB variable is pulling in from your form?
    Follow me: @josh_max

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    comma-delimited list of IDs
    example: 1, 5, 7, 11

    So if the pet with ID 7 for example is a "month dog", it'll have to get changed to "Dog"

  4. #4
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am by no means an expert or even an novice, really, but I have had to do a lot of this type of thing lately.

    This is what I've come up with, give it a try and see if it works how you want.
    (Comments explain what's happening)
    Code ASP:
    <%
     
    	sCB = Request.Form ("cb")
     
    	ADB= "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/db.mdb")
    	Set conn = Server.CreateObject("ADODB.Connection")
    	conn.Open ADB
    	set rs=Server.CreateObject("ADODB.Recordset")
     
     
    ' Select ONLY the records that were grabbed from the form
    	rs.open "SELECT * FROM Animals WHERE fileID IN (" & sCB & ")",conn
     
     
    ' Loop through each one to grab their id, adoption status and pet type
    	' Depending on what you're doing, you may only need the petType if statement,
    	' I just like to be catious and get all that I think I'll need.
     
    	do until rs.EOF
    		for each x in rs.Fields
    			if x.name = "fileID" then
    				id	= x.value
    			end if
    			if x.name = "adoptionstatus" then
    				adoptStatus = x.value
    			end if
    			if x.name = "pettype" then
    				petType = x.value
    			end if
    		next
     
    	' Decide which pet type the record has and set the sql variable to the proper UPDATE statement
    		if petType = "month dog" then
    			sql = "UPDATE Animals SET adoptionstatus='Not available', pettype = 'dog' WHERE fileID IN (" & sCB & ")"
    		end if
     
    		if petType = "dog" then
    			sql = "UPDATE Animals SET adoptionstatus='Not available' WHERE fileID IN (" & sCB & ")"
    		end if
     
    ' Execute your sql statement
    	conn.execute (sql)
     
     
    	rs.MoveNext
    	Loop
     
    	conn.Close
    	Set conn= Nothing
    	response.redirect "edit_Pets.asp"
     
    %>

    Let me know how if it works ok or not.
    Follow me: @josh_max

  5. #5
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wait, don't use that yet! I just realized if you use the update statement as is, it's still going to update all the id's. You need to set your sCB into an array and loop through them individually to determine if they have a pettype of "month dog" or "dog".

    I think so at least :S
    Follow me: @josh_max

  6. #6
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Needed to set the fileID to the individual record's ID so it would loop properly. I think this is all that needs to be fixed:

    Code ASP:
    	' Decide which pet type the current record has and set the sql variable to the proper UPDATE statement
    		if petType = "month dog" then
    			sql = "UPDATE Animals SET adoptionstatus='Not available', pettype = 'dog' WHERE fileID =(" & id & ")"
    		end if
     
    		if petType = "dog" then
    			sql = "UPDATE Animals SET adoptionstatus='Not available' WHERE fileID = (" & id & ")"
    		end if
     
    ' Execute your sql statement
    	conn.execute (sql)
     
    ' Loop through each record you grabbed originally
    	rs.MoveNext
    	Loop
    Follow me: @josh_max

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hey, thanks for the help
    It gives an error:
    Syntax error (missing operator) in query expression 'fileID = ()'.

    on the conn.execute line

    Code:
                sql = "UPDATE Animals SET adoptionstatus='Not available', pettype = 'dog' WHERE fileID =(" & id & ")"
            end if
     
            if petType = "dog" then
                sql = "UPDATE Animals SET adoptionstatus='Not available' WHERE fileID = (" & id & ")"
            end if
     
    ' Execute your sql statement
        conn.execute (sql)
    id seems not to get the FileID field

  8. #8
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoops! Take the parentheses out of the sql statement:

    Code ASP:
    	' Decide which pet type the record has and set the sql variable to the proper UPDATE statement
                sql = "UPDATE Animals SET adoptionstatus='Not available', pettype = 'dog' WHERE fileID = " & id
            end if
     
            if petType = "dog" then
                sql = "UPDATE Animals SET adoptionstatus='Not available' WHERE fileID = " & id
            end if
    If that doesn't work, we may need to set the sCB comma-delimited id's into an array to get looped through.
    Follow me: @josh_max

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yah still doesnt work :P ID is blank

  10. #10
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    oh, ok solved it thanks for all the help

    Its simply that all those lines weren't needed
    Code:
            for each x in rs.Fields
                if x.name = "fileID" then
                    id  = x.value
                end if
                if x.name = "adoptionstatus" then
                    adoptStatus = x.value
                end if
                if x.name = "pettype" then
                    petType = x.value
                end if
            next
    Need just to
    Code:
        do until rs.EOF
    
    id = rs.fileid
    pettype = rs.petype
    
    ' The SQL lines
    loop

  11. #11
    SitePoint Zealot ok_hornet's Avatar
    Join Date
    May 2009
    Location
    Oklahoma
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, well great then! I wasn't for sure if they were necessary or not, but I'm glad you got it to work

    Glad to have helped!
    Follow me: @josh_max


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
  •