Update Multiple Posts At same time

[alcstudio]

Hello there i wonder how the update string should look if i want to update multiple posts at same time.

Iam looping out all post from a table like this:

    set recsettraktamente = objconn.execute("select * from lander_belopp")
 
    if recsettraktamente.eof then
    response.write "Finns inga traktament belopp angivna"
 
    else
 
    response.write "<form action=""?do=spara_traktamente"" method=""post"">"
 
    do until recsettraktamente.eof
    response.write ""&recsettraktamente("land")&" <input type=""text"" size=""5"" name=""belopp"" value="""&recsettraktamente("belopp")&""">"
 
    bytrad = bytrad+1
    if bytrad = 1 then
    response.write "<br/>"
    bytrad = 0
    end if
 
    recsettraktamente.movenext
    loop
    response.write "<div style=""float:right""><input type=""submit"" value=""Spara Ändringar""></div>"
    response.write "</form>"
    recsettraktamente.close : Set recsettraktamente = Nothing
end if
 

Havent written the Update string because i dont know how i should look like. i tried whit something like this before, but get "index out of range"

dim i
if request.querystring("do") = "spara_traktamente" then
  for i = 0 to request.form("belopp").count
  objconn.execute("update lander_belopp set belopp='" & request.form("belopp")(i) & "'")
  next
end if
 

[alcstudio]

Anyone that can help me whit this. maybe a simple awnser but whould be nice whit help =)

[markrawlingson]

What are you trying to do? Are you trying to insert multiple records into a single table - or are you trying to insert 1 record into a single table, but are updating numerous columns within that table?

Judging by the code below it looks like you're trying to insert multiple records and are simply updating 1 column..

dim i
if request.querystring("do") = "spara_traktamente" then
for i = 0 to request.form("belopp").count
objconn.execute("update lander_belopp set belopp='" & request.form("belopp")(i) & "'")
next
end if

If that's the case, you could try this..

If Request.QueryString("do") = "spara_traktamente" Then
  For Each oItem In Request.Form
     If oItem = "belopp" Then
        objconn.execute("update lander_belopp set belopp='" & request.form(oItem) & "')"
     End If
  Next
End If
 

This will basically Loop through the entire Request.Form object, and if the information is coming from a field called "belopp" it will insert a new record into your table.

[alcstudio]

The code you postet is what i want to do but dont work. i changed it a bit but get this

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

Here is the modifyed:

If Request.QueryString("do") = "spara_traktamente" Then
For Each oItem In Request.Form
If oItem = "belopp" Then
 
objconn.execute("update lander_belopp set belopp='" & request.form(oitem) & "'")&""
End If
Next
End If
 

[alcstudio]

Dont get it to work, i get Syntax error in UPDATE statement.

[alcstudio]

Now i have tried to get this to work but i wont. Whats the problem exactly, can someone give me a hint. I know what the error says but how i turn and twist this it wont work. so should be great whit some more help.

[markrawlingson]

Well,

Any information coming back from the request.form object is automatically a string type. So what data type is your belopp field? It's not a string, and you're trying to throw a strong type variable into it.. it'll throw an error, so you'll have to treat the request.form(oItem) object first to change it from a string value to the context which you need it to be.

The other thing I noticed is at the end of your execute statment you have a couple of double quotes handing around...

objconn.execute("update lander_belopp set belopp='" & request.form(oitem) & "'")&""
 

You don't need the &"" there after the ) ending the execute statement, not sure why you put them there for?

Sincerely,
Mark

[alcstudio]

The ending on the code did get wrong, i saw it when i had posted it so the extra dose not exist.

The datatype for the colum belopp is Number. and the value its suppose to put in is a number. =).

The code looks like this now.

      If Request.QueryString("do") = "spara_traktamente" Then
      For Each oItem In Request.Form
      If oItem = "belopp" Then
 
      objconn.execute("update lander_belopp set belopp=" &    request.form("oItem") & "")
        End If
        Next
       End If
 

[markrawlingson]

Yeah I figured as much, that's why you're getting the error. Anything that comes back from the Request.Form object will be returned as string data type. I guess I should have mentioned that before - my bad!

You could use CInt wrapped around the data to convert it to a number, but my collegue and I wrote a more sophisticated version of string to numerical conversion as shared with you below. The VAL() function will convert a string to a numerical value, keeps decimal values, will return 0 if the string it's searching does not contain a numerical value, or will entirely rip out any text within the string, returning just the numerical value if one exists. I would use this.. very straight forward and no further work involved.

  Function Val( sValuePrivate )
    Set oRegExp = New RegExp
    oRegExp.Pattern = "[^0123456789\.]"
    oRegExp.IgnoreCase = True
    oRegExp.Global = True
    Val = Abs( oRegExp.Replace( "0" & sValuePrivate, "" ) )
    Set oRegExp = Nothing
  End Function
 

Example:

Val("Hello my name is 123") - returns 123
Val("Hello my name is Mark") - returns 0
Val("12345") - returns 12345
Val("") - returns 0 (good way to check null/empty values)

Hope this helps
Sincerely,
Mark

[alcstudio]

Really good man. Thumbs up, but how do i use the function whit the code?

[alcstudio]

Should i just replace "oitem" whit "oRegExp" ? The thing is the only thing that you type in in the textbox is a number so dont follow why i should have a function thats just take the numbers in the textbox, thats the only thing you will be writing.