Update records

[colinod]

I am trying to update multiple records in 1 hit, i have a page that sends the info to an update page with ids for the records generated with a count, these are mp31, mp32 etc and also an id for the text i want updated, this is just a number to represent the order the mp3s are listed in on the site this is textfield1, textfield2 etc

my code on the update page is

Dim iCount
iCount = Request.Form("mp3count")
Dim strLink, strID

Set mp3Connection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
mp3Connection.Open DatabaseDetails

Dim iLoop
For iLoop = 0 to iCount
strLink = Request(iLoop & ".orderno")
strID = Request(iLoop & ".idmp3")
response.write(strID)
response.write(strLink)

strSQL = "UPDATE mp3 SET orderno = ' " & strLink & " ' " & " WHERE idnumbermp3 = " & strID
Response.write(strSQL)
set mp3Recordset=mp3Connection.Execute(strSQL)
Next

mp3connection.Close
Set mp3connection = Nothing

i am getting the following error with the response write of the sql update

UPDATE mp3 SET orderno = ' ' WHERE idnumbermp3 =
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'idnumbermp3 ='.

/admin/updateok.asp, line 32
can anyone point me in the right direction

line 32 is

set mp3Recordset=mp3Connection.Execute(strSQL)

[jhardman]

Problem is there is nothing in the inputs some of the times thru the loop. At the beginning of the loop check to see if the inputs are populated. Let me know if this helps.

Jared

[colinod]

i think i have the fields populated now, but now i have a new problem the reponse.write of the sql query is this

UPDATE celebs SET bioginfo='Very versatile fabulous young actor - from middle class Indian to street cool or Northern. Since his screen debut in 2005 Riz has starred in award-winning film and TV including Michael Winterbottom's ‘Road to Guantanamo' (Berlin Silver Bear), Peter Kosminsky's ‘Britz' (BAFTA + RTS winner, Emmy nominated), ‘Dead Set' (BAFTA nominated), and 'Shifty' - for which he was nominated for Best Actor at the 2009 BIFA. Forthcoming screen appearances include Sally Potter's next feature alongside Jude Law, Steve Buscemi and Judy Dench. Riz is also an independent music artist under the name Riz MC. He has topped underground music charts and held an artistic residency at the Southbank Centre, been banned from - as well as championed on - national radio; supported Mos Def, Dizzee Rascal, and Massive Attack; played clubs and concerts from Fabric to the Royal Festival Hall, and been invited to play prominent slots at Glastonbury, BBC Electric Proms, and on several US tours.' and idnumber=317 and selectnum=317 and voicea='All' and voiceb='Indian' and voicec='' and voiced='' and voicee='' and voicef='' and voiceg='' and voiceh='' and voicei='' and voicej='' and voicek='' and voicel='' and voicem='' and voicen='' and voiceo='' and alternatives='A merjit Deu
Kayvan Novak' and voicedesc='' and celeb='n' and marketingtext=' Road to Guantanamo
Britz
Shifty
Dead Set' and marketingorder= '30' and mailmp3='AHMED - ID FRAUD (VARIOUS INDIAN NORTHERN STREET).mp3' and scottishmailout ='' and irishmailout='' and usamailout='' and geordiemailout= '' where idnumber = 301

which is right, the text contains some html for ease of formatting individual words but i get this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Very versatile fabulous young actor - from middle class Indian to street cool or Northern. Since his screen debut in 2005 Riz has starred in award-winning film and TV including Michael Winterbottom's <B>‘Road to Guantanamo'</B> (Berlin Silver Bear), Pete'.

/admin/updateok.asp, line 41

is this still the same problem as the error code is the same?

[colinod]

Figured out its all to do with the straign inverted commas in the text, does anyone know how to replace straign inverted commas with code

[jhardman]

You are correct, the "'" symbol is reserved in SQL. I prefer to use a dynamic recordset, you can set the value of each field and then call the recordset's update() method, that way you don't have to build really hairy update statements.

however, the easiest way to change characters in ASP is with a replace() statement

updateStr = replace(updateStr, "'", "'")

Jared

PS the code I typed in was changed by the forum software to show an inverted comma. The second inverted comma in the above replace statement should be the following (which I am splitting up so that the Bytes forum software doesn't change to an inverted comma)

&
#
39
;