Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    -27° 28' 22" , 153° 1' 22"
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Update sql statement returning incorrect results

    I am reading in a group of results from a user form when the form is submitted. The purpose of the code is to split the group of results into single records and then update the records in a SQL server database. Most of the code appears to work but the when looking at the sql statement produced it is only updating one record from the group of results.

    This is the code that I am using in the web page:

    Variable 'school' is the group of results from the user form.

    Code:
    Dim schoolEntry, schoolvalues, singlevalues, basic, sql, i, numupdated
    schoolEntry=split(school,"*") 
    
    'for error checking only
    response.write (school&"<br>")
    
    for each schoolvalues in schoolEntry
      schoolvalues=replace(schoolvalues,", ",",")
    	basic=split(schoolvalues,",")
    	'for error checking only
            response.write schoolvalues
    next
    
    for each singlevalues in basic
      sql="UPDATE sss_results SET "
      i=1
        do while i >= 0
          select case i
            case 0
              sql=sql & "WHERE school_name="  & SQLFormat(basic(i)) & ""
            case 1
              sql=sql & "save_priority=" & basic(i) & " "
          end select        
          i = i - 1
        loop
        'for error checking only
        response.write ("<br>"&sql)
        conn.Execute sql
    next
    This is what is displayed with response.write (school&"<br>"):

    *Biggenden State School, 1, *Mungar State School, 2

    This is what is displayed with response.write schoolvalues:

    Biggenden State School,1,Mungar State School,2

    This is the result of the sql statement, but it is only 'reading' one record of the array:

    Code:
    UPDATE sss_results SET save_priority=2 WHERE school_name=Mungar State School
    UPDATE sss_results SET save_priority=2 WHERE school_name=Mungar State School
    I thought the result of the sql statement should be something like this:

    Code:
    UPDATE sss_results SET save_priority=2 WHERE school_name=Mungar State School
    UPDATE sss_results SET save_priority=1 WHERE school_name=Biggenden State School
    I have looked at this from every side and cannot see what part of the code is causing the problem.

    Any help is greatly appreciated.
    "Computers are considered female - As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories for it."

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    That is because you are using a single variable named basic. Everytime the for-each loop iterates, it overwrites the previous value of basic, thus the final value of it is the last set in the schoolvalues string which is the Mungar State School set in this case.

    Why not execute the SQL query inside the first for-each loop and get rid of the 2nd for-each loop? And you don't need the do-while loop and select case either.
    Code:
    for each schoolvalues in schoolEntry
      schoolvalues=replace(schoolvalues,", ",",")
      basic=split(schoolvalues,",")
      'for error checking only
      response.write schoolvalues
    
      'execute SQL
      sql="UPDATE sss_results SET save_priority=" & basic(1) & " WHERE school_name="  & SQLFormat(basic(0)) & ""
      'for error checking only
      response.write ("<br>"&sql)
      conn.Execute sql
    next
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    -27° 28' 22" , 153° 1' 22"
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks glenngv,

    I was doing more than needed.

    I tried the code and got this error:

    Code:
    *Biggenden State School, 1, *Mungar State School, 2
    
    Microsoft VBScript runtime error '800a0009' 
    
    Subscript out of range: '[number: 1]' 
    
    /bq/sss_insertpriorityrecord.asp, line 80
    I had to add the trim() function to remove the ',' after the last set in the schoolvalues string and it fixed the error.

    Code:
    for each schoolvalues in schoolEntry
      schoolvalues=replace(schoolvalues,", ",",")
      if trim(schoolvalues) <> "" then
         basic=split(schoolvalues,",")
         sql="UPDATE sss_results SET save_priority=" & basic(1) & " WHERE school_name='" & SQLFormat(basic(0)) & "'"
         conn.Execute sql
      end if
    next
    Last edited by JustAsking; 08-27-2004 at 06:16 AM.
    "Computers are considered female - As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories for it."

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Dear,

    This might be the error of the total of the array. ( eg:basic(1) )
    Try to check the total value of i.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++


  •  

    Posting Permissions

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