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

Alright! Good luck!

Hi

really at a loss now - followed your advice and have the below code:

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

Dim StrTerm, StrTotal

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

For i = 0 to Ubound(arrRecIDs)

strTerm = Replace(Request.Form("CsTerm" & arrRecIDs(i)), "'", "''"
strTotal = Replace(Request.Form("CsTotal" & arrRecIDs(i)), "'", "''"

response.write strTerm & "<br>"
response.write strTotal

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = MM_login_STRING
commUpdate.Commandtext = "UPDATE customer_apollo SET CsTotalQTR=" & strTotal & ", CsRecTerm=" & strTerm & " WHERE CsOrderRef =" & arrRecIDs(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
End If
%>

but surprise surprise i keep getting the :

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


I now wandering if it is anything to do with the database, i have made sure of all permissions, but its anoying that if it is only 1 record it works fine its just on multiples.

Simon

DWMX 2004 | ASP | VBScript

I got the same error like yours when I first run the scripts, luckily, I was able to determine what's making all the stuff not working properly. I'm pretty much sure that you're mixing up different types of variables such as numeric and character with this:

commUpdate.Commandtext = "UPDATE customer_apollo SET CsTotalQTR=" & strTotal & ", CsRecTerm=" & strTerm & " WHERE CsOrderRef =" & arrRecIDs(i)

Your codes seems fine to me, my suggestion is examine your table CUSTOMER_APOLLO. I guess, it is where you're getting an error message.

By the way, remove these two scripts. I'm just trying to reponse.write my query with there.

//===========================
response.write strTerm & "<br>"
response.write strTotal
//===========================

Back on your case, I need to know if these following variables are numeric:

// Numeric Variables
strTerm
strTotal

// And, is your table set like this?

FieldName Data Type
==================================
CsTotalQTR Number
CsRecTerm Number
CsOrderRef AutoNumber

If you running your scripts using IIS 4 and up, to determine your scripts is working you should get this error message in case permission to write is set to off:

Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.

Again, examine your database table and data type. I don't see any instance anymore to conclude your scripts are not right. Perhaps, this time, it must be on your database.

i have checked my database and they are both set to numeric and the ID is set to Autonumber.

Im wondering if there is just a problem ith the DB and in which case i am going to build it again and see if that works.

Cheers

Simon

DWMX 2004 | ASP | VBScript

any updates?

afraid not - im now going to be trying it on a windows 2000 platform cos for the life of me i cannot get it to work.

I must thank you for all your time spent on this cos for that i am most grateful

regards

Simon

DWMX 2004 | ASP | VBScript

Hi,
I've also used that DrDev example and couldn't get it to work either (well it works if you only update one field, not two)
I emailed you micah santos

when i tried to run the scripts for the very first time it did actually worked for me. now, i'm wondering how come both of you and the author of this topic just don't make it work right.

the error you sent me indicates that the variable that supposed to be as your query string id was left with a null value so it can't update the records according to its unique identifying key.

here's the online demo:
n.1asphost.com/micahsantos/freescripts/asp/multiple/form.asp

for the actual coding, you need two files:

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)), "'", "''"

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.write "All records has been updated!<br><br>"
response.write "<a target='_parent' href='form.asp'>Go back to the form</a>"
%>

I meant I got DrDev's example (MultiUpdateDemo.asp-without touching it) working as it was, but as soon as I modified it I couldn't get it working.

(pointsEarned and PointsPossible are numeric fields, and comments is a memo (text) field)

i get this error now (and i am filling in all the fields- of which there are 3 to modify):

UPDATE Goals SET pointsEarned = 2, pointsPossible = 2, strComments = 'test' WHERE GoalID = 4804
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

now lets just say i fill in only the points possible field, then i get this error:

UPDATE Goals SET pointsEarned = , pointsPossible = 2, strComments = '' WHERE GoalID = 4804
Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.



<pre id=code><font face=courier size=2 id=code>
&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;%
If Request("Submit" &lt;&gt; "" Then
intGoalIDs = Replace(Request("hidGoalIDs", "*", "" ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
arrGoalIDs = Split(intGoalIDs, ", " ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(arrGoalIDs) ' Loop trough the array
intpointsEarned = Replace(Request("txtpointsEarned" & arrGoalIDs(i)), "'", "''"
intpointsPossible = Replace(Request("txtpointsPossible" & arrGoalIDs(i)), "'", "''"
strComments = Replace(Request("txtcomments" & arrGoalIDs(i)), "'", "''"

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("goals.mdb" & ";Persist Security Info=False"
commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & ", strComments = '" & strComments & "' WHERE GoalID = " & arrGoalIDs(i)
blah = commUpdate.CommandText
response.write(blah)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
strMessage = i & " Records Updated"
Response.Redirect("secure3.asp?Message=" & strMessage)
End If
%&gt;
&lt;%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("goals.mdb" & ";Persist Security Info=False"
Recordset1.Source = "SELECT * FROM Employees, Goals WHERE Employees.EmployeeID = Goals.EmployeeID AND Goals.quarter = 'Q406' AND employees.employeeID = 10"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%&gt;
&lt;%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Update Demo&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;script language="JavaScript"&gt;
&lt;!--
// When the value in a textfield is changed, notice the onChange="RecUpdate('&lt;%= intGoalID %&gt;')"
// on each of the textfields, the value of the Record ID associated with that field
// is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
// This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(GoalID){
var ThisID = "*" + (GoalID) + "*"
if (document.form1.hidGoalIDs.value == ""{ // If the hidden field is empty
document.form1.hidGoalIDs.value = (ThisID) // Store the value in the hidden field (hidGoalIDs) as it is.
}
if (document.form1.hidGoalIDs.value != ""{ // If the hidden field isn't empty
var str = document.form1.hidGoalIDs.value; // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID); // Search str to see if this GoalID is allready in it.
if (pos == -1) { // If the position returned is -1 it isn't allredy in there,
document.form1.hidGoalIDs.value = document.form1.hidGoalIDs.value + ", " + (ThisID)
} // so add ", " and this ID to what is already in hidGoalIDs
} // to create a list like this *2*, *5*, *8* etc.
}
//--&gt;
&lt;/script&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;&lt;%= Request.QueryString("Message" %&gt;&lt;/font&gt;
&nbsp;&lt;br&gt;
&lt;form name="form1" method="post" action="secure3.asp"&gt;
&lt;table width="500" border="0" cellpadding="0" cellspacing="1" bgcolor="#666699"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;table width="500" border="0" cellpadding="0" cellspacing="6" bgcolor="#EEEFF2"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;GoalID&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;Goal&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;PointsEarned&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;PointsPossible&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;Comments&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
While ((Repeat1__numRows &lt;&gt; 0) AND (NOT Recordset1.EOF))
%&gt;
&lt;% intGoalID =(Recordset1.Fields.Item("GoalID".Value) ' Store the current RecordID in a variable %&gt;
&lt;tr&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;%= intGoalID %&gt;&lt;input name="hidGoalID&lt;%= intGoalID %&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;%=(Recordset1.Fields.Item("goal".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;input name="txtpointsEarned&lt;%= intGoalID %&gt;" type="text" onChange="RecUpdate('&lt;%= intGoalID %&gt;')" value="&lt;%=(Recordset1.Fields.Item("PointsEarned".Value)%&gt;" size="1"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;input name="txtpointsPossible&lt;%= intGoalID %&gt;" type="text" onChange="RecUpdate('&lt;%= intGoalID %&gt;')" value="&lt;%=(Recordset1.Fields.Item("PointsPossible".Value)%&gt;" size="1"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;input name="txtcomments&lt;%= intGoalID %&gt;" type="text" onChange="RecUpdate('&lt;%= intGoalID %&gt;')" value="&lt;%=(Recordset1.Fields.Item("comments".Value)%&gt;" size="35"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%&gt;
&lt;/table&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;br&gt;
&lt;input name="hidGoalIDs" type="text" size="40"&gt;
&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;&lt;=== This would be hidden&lt;/font&gt;&lt;br&gt;
&lt;br&gt;
&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;
&lt;input type="submit" name="Submit" value="Update"&gt;
&lt;/font&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close()
Set Recordset1 = Nothing
%&gt;

</font id=code></pre id=code>

my codes are based on that article too. so, it's a little bit different like yours. however, i noticed some variables you used that are not related to the FOR NEXT looping.

here's the first one,

&lt;input name="hidGoalIDs" type="text" size="40"&gt;

i don't understand why do you need to use a text input...??? because in the very first place, it's all about updating multiple records in one single operation, am i right?
i don't know how it works actually unless when i see it virtually. sorry for my judgement. lol

and now, you use this variable in your ASP scripts which I suspect it doesn't contain any values if a user didn't enter any ID number. on the contrary, it should have automatic values.

&lt;%
intGoalIDs = Replace(Request("hidGoalIDs", "*", ""
%&gt;

secondly, this particular hidden input tag:

&lt;input name="hidGoalID&lt;%= intGoalID %&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;

i've tried to look for this variable in your asp script, but i can't find it or am i just blind? lol!
this tag should serve as your querystring instead of the "hidGoalIDs"

now, here's what i got. starting from displaying of records under the update form:

&lt;%

While ((Repeat1__numRows &lt;&gt; 0) AND (NOT Recordset1.EOF))

%&gt;

&lt;!--

you're original input tag is this:

&lt;input name="hidGoalID&lt;%= intGoalID %&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;

now, change it to like this:

--&gt;

&lt;%

&lt;input name="ID" type="hidden" value="&lt;%=intGoalID %&gt;" size="5"&gt;

&lt;%

Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend

%&gt;

once you've change that specified input tag, go back to your FOR NEXT looping script and change the value of intGoalIDs and the rest are the same.


&lt;%

intGoalIDs = Replace(Request("ID", "*", ""
arrGoalIDs = Split(intGoalIDs, ", "

%&gt;

and that's all! as far as i'm concern, those are the things i noticed that are not connecting to each other.

if you got any questions, please always let me know.

did you run the DrDev demo? at the bottom it has a input field that shows what IDs are going to be updated
so thats why i have &lt;input name="hidGoalIDs" type="text" size="40"&gt;
i dont really need it, but for now it doesn't hurt cuz it shows wuts gonna be updated.

then you're saying remove this?:
&lt;%
intGoalIDs = Replace(Request("hidGoalIDs", "*", ""
%&gt;
===================================
&lt;input name="hidGoalID&lt;%= intGoalID %&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;
&gt;&gt;i've tried to look for this variable in your asp script, but i can't find it or am i just blind? lol!
&gt;&gt;this tag should serve as your querystring instead of the "hidGoalIDs"

yea i didn't think i need this either, but remove it and replace with what?
replace with this? &lt;input name="ID" type="hidden" value="&lt;%=intGoalID %&gt;" size="5"&gt;
==================================
&gt;&gt;once you've change that specified input tag, go back to your FOR NEXT looping script and change the value of intGoalIDs and the rest &gt;&gt;are the same
which next for loop? i thot that was my last one
==================================
and then you're saying
replace

intGoalIDs = Replace(Request("hidGoalIDs", "*", ""
arrGoalIDs = Split(intGoalIDs, ", "

with:

intGoalIDs = Replace(Request("ID", "*", ""
arrGoalIDs = Split(intGoalIDs, ", "

or basically just change hidGoalIDs to ID

thanks so far

i didn't actually use the entire codes except for the FOR.. NEXT looping.

and yes, just change hidGoalIDs to ID...lol

change this tag:

&lt;input name="hidGoalID&lt;%= intGoalID %&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;

to this:

&lt;input name="ID"&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;

and change "hidGoalIDs" to "ID"

i was refering to the update form page looping.

but thats just a variable name, lets just leave it as hidGoalIDs
so the only thing i changed was
&lt;input name="hidGoalID&lt;%= intGoalID %&gt;" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;
to &lt;input name="hidGoalID" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;

also i commented out the "comments" field, so basically now its just updating two number fields, and now it works..
using this line:
<pre id=code><font face=courier size=2 id=code> commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & " WHERE GoalID = " & arrGoalIDs(i)
</font id=code></pre id=code>
but now lets say i wanna update both numeric fields and the comments field
so i change that line to this:
<pre id=code><font face=courier size=2 id=code> commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & ", strComments = '" & strComments & "' WHERE GoalID = " & arrGoalIDs(i)
</font id=code></pre id=code>
now i enter 1,1,test
i get this error:
UPDATE Goals SET pointsEarned = 1, pointsPossible = 1, strComments = 'test' WHERE GoalID = 4609
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

secure3.asp, line 20

line20 is this:
commUpdate.Execute()

so let me paste my whole code again, but i think the problem is somewhere in reading a text field..:

<pre id=code><font face=courier size=2 id=code>
&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;%
If Request("Submit" &lt;&gt; "" Then
intGoalIDs = Replace(Request("hidGoalIDs", "*", "" ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
arrGoalIDs = Split(intGoalIDs, ", " ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(arrGoalIDs) ' Loop trough the array
intpointsEarned = Replace(Request("txtpointsEarned" & arrGoalIDs(i)), "'", "''"
intpointsPossible = Replace(Request("txtpointsPossible" & arrGoalIDs(i)), "'", "''"
strComments = Replace(Request("txtcomments" & arrGoalIDs(i)), "'", "''"

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("goals.mdb" & ";Persist Security Info=False"
'commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & " WHERE GoalID = " & arrGoalIDs(i)
commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & ", strComments = '" & strComments & "' WHERE GoalID = " & arrGoalIDs(i)
blah = commUpdate.CommandText
response.write(blah)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
Next
strMessage = i & " Records Updated"
Response.Redirect("secure3.asp?Message=" & strMessage)
End If
%&gt;
&lt;%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("goals.mdb" & ";Persist Security Info=False"
Recordset1.Source = "SELECT * FROM Employees, Goals WHERE Employees.EmployeeID = Goals.EmployeeID AND Goals.quarter = 'Q406' AND employees.employeeID = 10"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%&gt;
&lt;%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Update Demo&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;script language="JavaScript"&gt;
&lt;!--
// When the value in a textfield is changed, notice the onChange="RecUpdate('&lt;%= intGoalID %&gt;')"
// on each of the textfields, the value of the Record ID associated with that field
// is passed to the RecUpdate function. First the value is surounded with 2 asterisks e.g. *6*
// This is so that *1* can be distinguished from *10*, *11* etc.
function RecUpdate(GoalID){
var ThisID = "*" + (GoalID) + "*"
if (document.form1.hidGoalIDs.value == ""{ // If the hidden field is empty
document.form1.hidGoalIDs.value = (ThisID) // Store the value in the hidden field (hidGoalIDs) as it is.
}
if (document.form1.hidGoalIDs.value != ""{ // If the hidden field isn't empty
var str = document.form1.hidGoalIDs.value; // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID); // Search str to see if this GoalID is allready in it.
if (pos == -1) { // If the position returned is -1 it isn't allredy in there,
document.form1.hidGoalIDs.value = document.form1.hidGoalIDs.value + ", " + (ThisID)
} // so add ", " and this ID to what is already in hidGoalIDs
} // to create a list like this *2*, *5*, *8* etc.
}
//--&gt;
&lt;/script&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;&lt;%= Request.QueryString("Message" %&gt;&lt;/font&gt;
&nbsp;&lt;br&gt;
&lt;form name="form1" method="post" action="secure3.asp"&gt;
&lt;table width="500" border="0" cellpadding="0" cellspacing="1" bgcolor="#666699"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;table width="500" border="0" cellpadding="0" cellspacing="6" bgcolor="#EEEFF2"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;GoalID&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;Goal&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;PointsEarned&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;PointsPossible&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;&lt;font color="#333333" size="1" face="Arial, Helvetica, sans-serif"&gt;Comments&lt;/font&gt;&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
While ((Repeat1__numRows &lt;&gt; 0) AND (NOT Recordset1.EOF))
%&gt;
&lt;% intGoalID =(Recordset1.Fields.Item("GoalID".Value) ' Store the current RecordID in a variable %&gt;
&lt;tr&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;%= intGoalID %&gt;&lt;input name="hidGoalID" type="hidden" value="&lt;%= intGoalID %&gt;" size="5"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;%=(Recordset1.Fields.Item("goal".Value)%&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;input name="txtpointsEarned&lt;%= intGoalID %&gt;" type="text" onChange="RecUpdate('&lt;%= intGoalID %&gt;')" value="&lt;%=(Recordset1.Fields.Item("PointsEarned".Value)%&gt;" size="1"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;input name="txtpointsPossible&lt;%= intGoalID %&gt;" type="text" onChange="RecUpdate('&lt;%= intGoalID %&gt;')" value="&lt;%=(Recordset1.Fields.Item("PointsPossible".Value)%&gt;" size="1"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td nowrap&gt;&lt;font size="1" face="Arial, Helvetica, sans-serif"&gt;&lt;input name="txtcomments&lt;%= intGoalID %&gt;" type="text" onChange="RecUpdate('&lt;%= intGoalID %&gt;')" value="&lt;%=(Recordset1.Fields.Item("comments".Value)%&gt;" size="35"&gt;&lt;/font&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%&gt;
&lt;/table&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;br&gt;
&lt;input name="hidGoalIDs" type="text" size="40"&gt;
&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;&lt;=== This would be hidden&lt;/font&gt;&lt;br&gt;
&lt;br&gt;
&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;
&lt;input type="submit" name="Submit" value="Update"&gt;
&lt;/font&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close()
Set Recordset1 = Nothing
%&gt;

</font id=code></pre id=code>

i've noticed that you used the same variable here

// Local Variable
strComments

but the other one is supposed to be a fieldname from your database. try to rename it to Comments instead.
so, your current update syntax should look something like this:

&lt;%

commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & ", Comments = '" & strComments & "' WHERE GoalID = " & arrGoalIDs(i)

%&gt;

if it still doesn't work, try this one too:

&lt;%
strComments = Replace(Request.Form("comments", "'", "''"
%&gt;

yea i just tried rewriting the line myself and it worked:
commUpdate.CommandText = "UPDATE Goals SET comments = '" & strComments & "', pointsEarned= " & intpointsEarned & ", pointsPossible = " & intpointsPossible & " WHERE GoalID = " & arrGoalIDs(i)

then i noticed my old line had strcomments=strcomments and that was the mistake!
then i was going to come on the board and post saying i found the error, but looks like you beat me to it!
thanks for all your help!!

hi, i ran into another problem
if u run drdev's demo and you try to enter a blank value for any number field u get this error:
Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.
====================================
but i want to be able to have blank values (null) for numeric fields (aka pointspossible and pointsearned)
so if it senses that these fields are blank, i want it to manually set the value to null that way i wont get that error on the update
so i modified the javascript part of drdev to the following:
<pre id=code><font face=courier size=2 id=code> &lt;script language="JavaScript"&gt;
&lt;!--
function RecUpdate(GoalID){
var ThisID = "*" + (GoalID) + "*"
if (document.form1.hidGoalIDs.value == ""{ // If the hidden field is empty
document.form1.hidGoalIDs.value = (ThisID) // Store the value in the hidden field (hidGoalIDs) as it is.
}
if (document.form1.hidGoalIDs.value != ""{ // If the hidden field isn't empty
var str = document.form1.hidGoalIDs.value; // Store the contents of the hidden field in the variable str
var pos = str.indexOf(ThisID); // Search str to see if this GoalID is allready in it.
if (pos == -1) { // If the position returned is -1 it isn't allredy in there,
document.form1.hidGoalIDs.value = document.form1.hidGoalIDs.value + ", " + (ThisID)
} // so add ", " and this ID to what is already in hidGoalIDs
} // to create a list like this *2*, *5*, *8* etc.

if (document.form1.txtpointsEarned+(GoalID)+.value) == "" {
document.form1.txtpointsEarned+(GoalID)+.value=null; }
if document.form1.txtpointsPossible+GoalID+.value == "" {
document.form1.txtpointsPossible+GoalID+.value=null; }
}
//--&gt;
&lt;/script&gt;</font id=code></pre id=code>
however it doesn't work, it says syntax error in here:
<pre id=code><font face=courier size=2 id=code>if (document.form1.txtpointsEarned+(GoalID)+.value) == "" {
document.form1.txtpointsEarned+(GoalID)+.value=null; }
if document.form1.txtpointsPossible+GoalID+.value == "" {
document.form1.txtpointsPossible+GoalID+.value=null; } </font id=code></pre id=code>

so can you either help me fix the syntax error and/or logic? am i going about this the right way if i want to be able to accept empty values? (cause i know access does accept an empty string or "null" for a value of a numeric field)
thanks a lot

what i thought a numeric value is set to 0 value by default.

why do you need this fields anyway to accept empty values?

well if they are set to 0 by default how come DrDev's example gets error on update when you have empty value for numeric field?
i want to have empty values because lets say first people update their points possible, then later when they get graded, they enter in points earned.

then you should set it manually in ASP instead using if and else statement.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
then you should set it manually in ASP instead using if and else statement.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

but its dynamic, the javascript detects which IDs its going to update
so how can i tell if the user left a field empty associated with the IDs its going to update?

basically, i didn't use the entire codes of drdev.net sample but i only based my scripts on his FOR... NEXT looping. I didn't use javascript either so that's why i can get along with you.

anyway, i got! i'll work on this later okay.

use IF.. ELSE.. statement before updating records

&lt;%

If Request("Submit" &lt;&gt; "" Then

intGoalIDs = Replace(Request("hidGoalIDs", "*", ""
arrGoalIDs = Split(intGoalIDs, ", "

For i = 0 to Ubound(arrGoalIDs)

intpointsEarned = Replace(Request("txtpointsEarned" & arrGoalIDs(i)), "'", "''"
intpointsPossible = Replace(Request("txtpointsPossible" & arrGoalIDs(i)), "'", "''"
strComments = Replace(Request("txtcomments" & arrGoalIDs(i)), "'", "''"

// Detect empty fields

If intpointsEarned = "" Then
instpointsEarned = 0
End If

If intpointsPossible = "" Then
intpointsPossible = 0
End If

set commUpdate = Server.CreateObject("ADODB.Command"
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("goals.mdb" & ";Persist Security Info=False"

commUpdate.CommandText = "UPDATE Goals SET pointsEarned = " & intpointsEarned & ", pointsPossible = " & intpointsPossible & ", Comments = '" & strComments & "' WHERE GoalID = " & arrGoalIDs(i)
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()

Next

strMessage = i & " Records Updated"

Response.Redirect("secure3.asp?Message=" & strMessage)

End If
%&gt;

actually i want the null value not 0
originally i tried just doing = null
but apparently i need quotes around it
so now i have this and it works:
<pre id=code><font face=courier size=2 id=code>
If intpointsEarned = "" Then
intpointsEarned = "null"
End If
If intpointsPossible = "" Then
intpointsPossible = "null"
End If
</font id=code></pre id=code>

thank you so much!!