Hi

trying to do a simple multiple update on a page but it keeps coming back with the error

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


My code is below and i cant seem to see what is wrong - can anyone shed some light on this

If Request.Form("Process"="True" Then

intRecIDs = (Rs_Customers.Fields.Item("ID".Value)
arrRecIDs = Split(intRecIDs, ", "
For i = 0 to Ubound(arrRecIDs) ' Loop trough the array
intText = Request("txtValue"
intNum = Request("PlanType"

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = MM_login_STRING
commUpdate.CommandText = "UPDATE [all_customer _apollo] SET Total_QTR = " & intText & ", Rec_Term = " & intNum & " WHERE ID = " & arrRecIDs(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next

End If
%>

many thanks in advance

Simon

DWMX 2004 | ASP | VBScript

this is commonly caused when using field name that is a reserved word, by either ADO, OLEDB or by Access for use as commands or system objects.

these are the list of reserved words for your reference.

support.microsoft.com/?kbid=248738

Yes, Micah is correct. Your culprit is "ID" you could use intID or ID_ but ID alone will cause problems.

May thanks for all response - seems clearly obvious now!

I will make the change and update.

Cheers

Simon

DWMX 2004 | ASP | VBScript

good luck then!

hi

have change it to OrderRef now - (which i understand is not a reserved word)

and it is still giving me the same error -

<%
If Request.Form("Process"="True" Then

intRecIDs = request("TypeID"
arrRecIDs = Split(intRecIDs, ", "
For i = 0 to Ubound(arrRecIDs) ' Loop trough the array
strText = request("txtValue"
intNum = request("PlanType"

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = MM_login_STRING
commUpdate.CommandText = "UPDATE all_customer_apollo SET Total_QTR = '" & strText & "', Rec_Term = " & intNum & " WHERE APPID = " & intRecIDs
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next

End If
%>

any ideas

Simon

DWMX 2004 | ASP | VBScript

UPDATE!

If i run the code with only one record its works fine and updates the database - but when it shows all records and runs the update it doesnt work

Simon

DWMX 2004 | ASP | VBScript

are you trying to update multiple records?

hi


yes i am.

Simon

DWMX 2004 | ASP | VBScript

commUpdate.CommandText = "UPDATE all_customer_apollo SET Total_QTR = '" & strText & "', Rec_Term = " & intNum & " WHERE APPID IN(intRecIDs)

is this where you get this coding?

www.drdev.net/article11.asp

please refrain from it. or you could try other way to update multiple records using SQL.

hi

sorry, im not quite sure what you mean? refrain from using the code that im using at the minute? Can you see what is wrong?

thanks in advance

Simon

DWMX 2004 | ASP | VBScript

sorry, that's not what i mean! lol...

anyways, i've checked this www.drdev.net/article11.asp link at the bottom page.

and it's the same with yours, isn't it? however, i've noticed one thing with your variables...

// your coding
strText = request("txtValue"
intNum = request("PlanType"

if you consistently followed the codes, it should be like the original, because, these two variables applies to the looping that has been used. check it out!

// original coding
strText = Replace(Request("txtText" & arrRecIDs(i)), "'", "''"
intNum = Replace(Request("txtNum" & arrRecIDs(i)), "'", "''"

Hi

I had already tried that with this code:

<%


intRecIDs = request("TypeID"
arrRecIDs = Split(intRecIDs, ", "
For i = 0 to Ubound(arrRecIDs) ' Loop trough the array
strText = Replace(Request("txtValue" & arrRecIDs(i)), "'", "''"
strNum = Replace(Request("PlanType" & arrRecIDs(i)), "'", "''"

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = MM_login_STRING
commUpdate.CommandText = "UPDATE all_customer_apollo SET CsTotalQTR = " & strText & ", CsRecTerm = " & strNum & " WHERE CsOrderRef = '" & intRecIDs
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
%>

and i keep getting the same error of:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


Any light you can cast in these shadowy times would be greatly appreciated!

Simon

DWMX 2004 | ASP | VBScript

i can't see anything wrong with the codes. i've tried to compare it to the original one. perhaps, you should check again your table fieldnames if one them named as a reserved word.

i've done that and it still gives me the error - i just cant see what is wrong!


Many thanks for your time on this though


Simon

DWMX 2004 | ASP | VBScript

i don't give up on that easily... anyway, if you were telling me if you had already checked your tables and variables, let's focus on your coding:

// This is the main source code:
commUpdate.CommandText = "UPDATE TestTable SET RecText = '"_
& strText & "', RecNum = " & intNum & " WHERE RecID = " & arrRecIDs(i)

// Notice the arrRecIDs(i), it has looping in it.

// This is your codes. If you really copy and paste your exact coding as shown:
commUpdate.CommandText = "UPDATE all_customer_apollo SET CsTotalQTR = " & strText & ", CsRecTerm = " & strNum & " WHERE CsOrderRef = '" & intRecIDs

// Notice that you haven't used arrRecIDs w/ looping as your query

// Now, let's reconstruct your coding and imitate the main source code:
commUpdate.Commandtext = "UPDATE All_Customer_Apollo SET CsTotalQtr = '"_
& strText & "', CsRecTerm = " & strNum & " WHERE CsOrderRef = '" & arrRecIDs(i)

Hi

I like your atitude!

I'll have to try this tomorrow im afraid as i dont have access at home at the minute - will update then

regards

Simon

DWMX 2004 | ASP | VBScript

alright then!

good luck!


-----------------------------------------
Micah Santos
n.1asphost.com/micahsantos

hey micah,

Still no resolve. I come to thinking that it is just not meant to be - i have crawled the internet and cannot find anything to help.

I keep going towards the thinking that it is a problem with the DB. I made sure there is no reserved words and have even run a repair but still not working.

Arrggghhh!

Simon

DWMX 2004 | ASP | VBScript

try to use OLEDB instead of OBDC... and then, RESPONSE.WRITE your queries to figure out their values.

// if these two fields Total_QTR and intNum are set to integer, then, try this one:

strText = cInt(request("txtValue")
intNum = cInt(request("PlanType")

hi micah,

cheers for your continued help and patience on this.

i have tried all that you have suggested already but to no avail - when i see the data that is being called in the SQL statement it all seems fine showing all the correct records in the right fields but it just doesnt update the database.

I may be going about it all the wrong way - what i have

I have a calculation in a different RS which gives a value with its corresponding name.

What i am then trying to do is write the new value back to the database matching by record ID - and of course there is nearly 100 records so i want them to all update at once as you can imagine. Do you have ny other ideas ow to acheive this.

Or have your quite rightly had enough like me and need a stiff drink and a change of career!

Simon

DWMX 2004 | ASP | VBScript

I came up with the idea to make another script on how to update multiple records in a single operation. Of course, with the help of this link [ www.drdev.net/article11.asp ] so I can really figure it out what's absolutely wrong about your codes.

Here's a working demo of what I've made:

n.1asphost.com/micahsantos/freescripts/asp/multiple/form.asp


Anyway, there are couple of things I want to make sure about your variables.

First thing:

Are these variables (strText & strNum) are NUMBERS?

If they are numbers, then, you should be having this code like this:
Note: CsOrderRef must be also a number

commUpdate.CommandText = "UPDATE all_customer_apollo SET CsTotalQTR =" & strText & ", CsRecTerm =" & strNum & " WHERE CsOrderRef=" & intRecIDs

Second thing:

If they're not numbers, then, you should change it like this:
*Note: Take a look at the ' I've used indicating that the variable is a character
When removing the ' means that the variable is a number

commUpdate.CommandText = "UPDATE all_customer_apollo SET CsTotalQTR ='" & strText & "', CsRecTerm ='" & strNum & "' WHERE CsOrderRef=" & intRecIDs

Again, if CsOrderRef is also character, then, change it to like this:

commUpdate.CommandText = "UPDATE all_customer_apollo SET CsTotalQTR ='" & strText & "', CsRecTerm ='" & strNum & "' WHERE CsOrderRef='" & intRecIDs & "'"


With regards to these matters, try to look for these in your UPDATE FORM
As you're pulling out the records from the table...

<td><input type="text" readonly name="id" value="<%=objRs("ID"%>"</td>
<td><input type="text" value="<%=objRs("name"%>" name="glName_<%=objRs("ID"%>"></td>
<td><input type="text" value="<%=objRs("pet"%>" name="glPet_<%=objRs("ID"%>"></td>

or it really depends on how you represent your variables...

however, when you run this code, it should end up like this in HTML

<td><input type="text" readonly name="id" value="1"></td>
<td><input type="text" value="Micah" name="glName_1"></td>
<td><input type="text" value="Aruwana" name="glPet_1"></td>

it must be the same thing with your coding, so, watch out for that!

Okay! Just follow my instructions and you'll absolutely gonna make your scripts work instantly.

Here's a working demo of what I've made:

n.1asphost.com/micahsantos/freescripts/asp/multiple/form.asp

the scripts are below
=================================


FORM.ASP
-------------------------
<html>
<head>
<title>Update Form</title>
</head>

<body>
<form method="post" action="update.asp">
<font face="verdana" size="2">
<table align=center border=1 height="22%" wdith="55%" cellpadding=5 cellspacing5>
<tr>
<td>Record ID</td><td>Owner</td><td>Pet</td>
</tr>
<%
// Set Database Connection

Dim objConn,SQL,objRs

Set objConn = Server.CreateObject("ADODB.Connection"
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("records.mdb" & ";"
objConn.Open

SQL = "SELECT * FROM USERS ORDER BY ID"
Set objRS = Server.CreateObject("ADODB.Recordset"
objRS.Open SQL, objConn


// Transfer values to variables

Do until objRs.EOF

%>
<tr>
<td><input type="text" readonly name="id" value="<%=objRs("ID"%>"</td>
<td><input type="text" value="<%=objRs("name"%>" name="glName_<%=objRs("ID"%>"></td>
<td><input type="text" value="<%=objRs("pet"%>" name="glPet_<%=objRs("ID"%>"></td>
</tr>


<%
objRs.MoveNext
Loop

objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>

<tr>
<td></td><td></td><td><input type="submit" value="Update"></td>
</tr>
</table>
</font>
</form>
</body>
</html>

UPDATE.ASP
-------------------------
<%

Dim strName,strPet

intRecIDs = Replace(Request.Form("ID", "*", ""
arrRecIDs = Split(intRecIDs, ", "

For i = 0 to Ubound(arrRecIDs)

strName = Replace(Request.Form("glName_" & arrRecIDs(i)), "'", "''"
strPet = Replace(Request.Form("glPet_" & arrRecIDs(i)), "'", "''"

response.write strName & "<br>"
response.write strPet

set commUpdate = Server.CreateObject("ADODB.Command"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("records.mdb" & ";"

commUpdate.ActiveConnection = strConnect

commUpdate.CommandText = "UPDATE USERS SET name='" & strName & "', pet='" & strPet & "' WHERE ID =" & arrRecIDs(i)

commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()

Next

response.redirect "form.asp"
%>

Hope this sample will help you to figure it out.

This looks great and i'll give it a go tomorrow - many thanks for your time on this and i'll update asap

cheers

Simon

DWMX 2004 | ASP | VBScript