Updating Checkbox States

[Mixaalser684]

Look at this code:

<%@LANGUAGE="VBSCRIPT"%><!--#include file="Connections/connContacts.asp" --><%If Request.Form("action")="update" Then  'Set variables for update  Dim updateSQL, i  Dim cRecordID, cbitChecked    'Loop through records on screen and updateFor i = 1 To fFormat(Request.Form("counter"))'Create the proper field names to reference on the formcRecordID = "RecordID" & CStr(i)cbitChecked = "bitChecked" & CStr(i)'Create the update sql statementupdateSQL = "UPDATE tblContact SET bitChecked=" & fFormat(Request.Form(cbitChecked)) & " WHERE RecordID=" & fFormat(Request.Form(cRecordID))'Run the sql statementCall sRunSQL(updateSQL)Next'Refresh pageResponse.Redirect("update.asp")End IfFunction fFormat(vText)  fFormat = Replace(vText, "'", "''")End FunctionSub sRunSQL(vSQL)  set cExecute = Server.CreateObject("ADODB.Command")  With cExecute	.ActiveConnection = MM_connContacts_STRING	.CommandText = vSQL	.CommandType = 1	.CommandTimeout = 0	.Prepared = true	.Execute()  End WithEnd SubDim rsContactDim rsContact_cmdDim rsContact_numRowsSet rsContact_cmd = Server.CreateObject ("ADODB.Command")rsContact_cmd.ActiveConnection = MM_connContacts_STRINGrsContact_cmd.CommandText = "SELECT * FROM tblContacts" rsContact_cmd.Prepared = trueSet rsContact = rsContact_cmd.ExecutersContact_numRows = 0%><%Dim Repeat1__numRowsDim Repeat1__indexRepeat1__numRows = -1Repeat1__index = 0rsContact_numRows = rsContact_numRows + Repeat1__numRows%><form name="form1" method="post" action="update.asp">  <table>	<tr>	  <td>Checked</td>	</tr>	<% Dim counterWhile ((Repeat1__numRows <> 0) AND (NOT rsContact.EOF)) counter = counter + 1%>	  <tr>		<td><input <%If (CStr((rsContact.Fields.Item("bitChecked").Value)) = CStr("True")) Then Response.Write("checked=""checked""") : Response.Write("")%> type="checkbox" name="checkbox<%=counter%>" id="checkbox<%=counter%>"><input name="RecordID<%=counter%>" type="hidden" id="RecordID<%=counter%>" value="<%=(rsContact.Fields.Item("RecordID").Value)%>"></td>	  </tr>	  <%   Repeat1__index=Repeat1__index+1  Repeat1__numRows=Repeat1__numRows-1  rsContact.MoveNext()Wend%>	</table>	<input name="action" type="hidden" value="update">	<input name="counter" type="hidden" value="<%=counter%>">	<input type="submit" value="update"></form><%rsContact.Close()Set rsContact = Nothing%>

I'm getting this error:

Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement./allmymtgcards/update.asp, line 39

Line 39 is this:

.Execute()

And this is the procedure this line belongs to:

Sub sRunSQL(vSQL)  set cExecute = Server.CreateObject("ADODB.Command")  With cExecute	.ActiveConnection = MM_connContacts_STRING	.CommandText = vSQL	.CommandType = 1	.CommandTimeout = 0	.Prepared = true	[b].Execute()[/b]  End WithEnd Sub

Why is there a problem and how do I fix it? Thnx.

[justsomeguy]

Look at the error message man:

Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement./allmymtgcards/update.asp, line 39

The UPDATE statement has a syntax error. If you don't know why, it would probably be a good idea to print the update statement so you can see what it looks like.

[Mixaalser684]

Look at the error message man:The UPDATE statement has a syntax error. If you don't know why, it would probably be a good idea to print the update statement so you can see what it looks like.

HOW CAN WE LEARN IF THE EXAMPLES WE ARE SUPOSE TO LEARN FROM THROW ERROR AT US!!!!!It would be alot more helpfull if you just wrote what was needed. After I read your "enlightening" answer I went on ADO Update W3 and tried for the hundredth time to figure why am I so stupid. I created the database, typed the code in DW only to see yet another error thrown at my face! So gentlemen next time when someone asks for you to write down an instruction for them If only thing you have to write is criticism on how we do not read enough don't write anything at all! Maybe some of us read something 3-4 times, tried that and for some (unknown) reason (to us) that sample code or wathever didn't work maybe thats why we are asking for help on forums. Bah, who am I writing this to I'll probably get spanked by some moderator for writing in caps. But I'm sick of people who are saying, "maybe you should read this a little more". To them I have to say, if you're tierd of unconditinaly helping people leave the forums.I appreciate your answers in the past Justoneguy but it would be alot more helpful if you explained to me the update statement instead of acusing me for not reading enough. Maybe I'm reading this for last three days, I've tried a hundred times and just can't get it to work, have you tought of that, huh?!

[jeffman]

I think perhaps jsg was suggesting you print the value of your updateSQL variable so you can see what it actually contains, since it seems to be the source of the error when your UPDATE statement gets executed. None of US knows what's in your database, so we can't predict the value of this variable in order to tell you what's wrong with it.

[Mixaalser684]

I think perhaps jsg was suggesting you print the value of your updateSQL variable so you can see what it actually contains, since it seems to be the source of the error when your UPDATE statement gets executed. None of US knows what's in your database, so we can't predict the value of this variable in order to tell you what's wrong with it.

Can't you see it form the code? The table i'm trying to update contains intColorID(zutonumber P.K.), txtColor(txt), bitColorChecked(Yes/No). The bitColorChecked field is the only important field I need to update. Any other suggestions?

[justsomeguy]

I'm not trying to tell you to read more. But the error message is telling you *exactly* what the problem is, all I'm trying to get you to do is pay attention to what the error message says (that's why it's there). It says that the SQL statement has a syntax error, i.e. that it is not a valid SQL statement. So you should see that and think to yourself "why isn't it a valid SQL statement?" And then it would be logical to print the SQL statement out to see exactly what you are telling the database to do, because it doesn't make sense to sit here and guess what the error might be when we can just print it out. I can't do that from where I'm sitting, I can't insert a response.write statement into your code for you and run it. Therefore, I can't tell you what the problem is, let alone how to fix it. If you think I'm giving you criticism you're wrong, I'm trying to teach you how to debug your code. You don't learn by guessing or having other people figure it out, you learn by printing everything out and figuring out exactly what the code is doing and then determining why it's not doing what you want it to do.

[Mixaalser684]

I'm not trying to tell you to read more. But the error message is telling you *exactly* what the problem is, all I'm trying to get you to do is pay attention to what the error message says (that's why it's there). It says that the SQL statement has a syntax error, i.e. that it is not a valid SQL statement. So you should see that and think to yourself "why isn't it a valid SQL statement?" And then it would be logical to print the SQL statement out to see exactly what you are telling the database to do, because it doesn't make sense to sit here and guess what the error might be when we can just print it out. I can't do that from where I'm sitting, I can't insert a response.write statement into your code for you and run it. Therefore, I can't tell you what the problem is, let alone how to fix it. If you think I'm giving you criticism you're wrong, I'm trying to teach you how to debug your code. You don't learn by guessing or having other people figure it out, you learn by printing everything out and figuring out exactly what the code is doing and then determining why it's not doing what you want it to do.

I've tried putting Response.Write(updateSQL), somewhere in my code but when I click Submit button, I don't see anything you suggested, just that error code. What am I doing wrong? I apologize JustSomeGuy, sorry, man it's not your fault I can't get this to work. Thanx anyway.

[justsomeguy]

I understand, it gets frustrating.You have this loop:

For i = 1 To fFormat(Request.Form("counter"))  'Create the proper field names to reference on the form  cRecordID = "RecordID" & CStr(i)  cbitChecked = "bitChecked" & CStr(i)  'Create the update sql statement  updateSQL = "UPDATE tblContact SET bitChecked=" & fFormat(Request.Form(cbitChecked)) & " WHERE RecordID=" & fFormat(Request.Form(cRecordID))  'Run the sql statement  Call sRunSQL(updateSQL)Next

Insert the response.write right after setting updateSQL, before you run the query:

  'Create the update sql statement  updateSQL = "UPDATE ....  Response.Write(updateSQL & "<br>")

If you're still not seeing that, it might be because the server is buffering output. To force it to print that to the browser immediately you can flush the buffer (not the most efficient thing to do in a loop, but it works for debugging):

  'Create the update sql statement  updateSQL = "UPDATE ....  Response.Write(updateSQL & "<br>")  Response.Flush

The last UPDATE statement you see printed before the error is the one causing the problem.

[Mixaalser684]

  'Create the update sql statement  updateSQL = "UPDATE ....  Response.Write(updateSQL & "<br>")  Response.Flush

The last UPDATE statement you see printed before the error is the one causing the problem.I added response.write and response.flush and I'm getting this:

UPDATE tblContact SET bitChecked= WHERE RecordID=1Microsoft OLE DB Provider for ODBC Drivers error '80040e14'[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement./allmymtgcards/update.asp, line 44

Can someone translate this to me? If I'm right I'm trying to update the field bitChecked with nothing right? If thats true where do I go from here?

[justsomeguy]

That's right, there's no value for bitChecked. So that's coming from here:fFormat(Request.Form(cbitChecked))The fFormat function is fine, that just replaces quotes. The cbitChecked variable gets built here:cbitChecked = "bitChecked" & CStr(i)So it will be something like "bitChecked1". So it's checking the form for a field called "bitChecked1". Look at the input tag:<input <%If (CStr((rsContact.Fields.Item("bitChecked").Value)) = CStr("True")) Then Response.Write("checked=""checked""") : Response.Write("")%> type="checkbox" name="checkbox<%=counter%>" id="checkbox<%=counter%>">

[Mixaalser684]

So it will be something like "bitChecked1". So it's checking the form for a field called "bitChecked1". Look at the input tag:<input <%If (CStr((rsContact.Fields.Item("bitChecked").Value)) = CStr("True")) Then Response.Write("checked=""checked""") : Response.Write("")%> type="checkbox" name="checkbox<%=counter%>" id="checkbox<%=counter%>">

I tried changing

name="checkbox<%=counter%>" and id="checkbox<%=counter%>"

to

name="bitChecked<%=counter&>" and id="bitChecked<%=counter%>"

, and again its the error in update statement. Do I need to change the

<%=counter%>

part to something else? To what? Maybe Cstr (i)? but that's while loop it doesn't contain I?

[justsomeguy]

One thing to check is to view the HTML source that the page generates. So when you pull up the browser go to View->Source and see what HTML is being generated for the form. It looks like it should work, but I did notice that the counter variable doesn't get initialized to a value, so the first checkbox you see might be named "bitCheckedundefined", or maybe just "bitChecked". Since the for loop starts at 1, counter should start at 1 too. So after this line:Dim counterjust set it to 1 to start withcounter = 1Is this an example you got online somewhere? You'd think someone would have tested this before posting it.

[Mixaalser684]

One thing to check is to view the HTML source that the page generates. So when you pull up the browser go to View->Source and see what HTML is being generated for the form. It looks like it should work, but I did notice that the counter variable doesn't get initialized to a value, so the first checkbox you see might be named "bitCheckedundefined", or maybe just "bitChecked". Since the for loop starts at 1, counter should start at 1 too. So after this line:Dim counterjust set it to 1 to start withcounter = 1Is this an example you got online somewhere? You'd think someone would have tested this before posting it.

I'm sorry if posting links to other forums is forbidden, but you asked me if this is an example I got online. Yes and here's the link Update Multiple Records Tutorial.I'm trying to redo the original tutorial just for updateing the checkbox state, but even when I just copied and pasted this tutorial into DW I got an error message, so you can see why is it hard for me, because the examples I'm learning from are not 100% tested. I tried adding counter = 1 but the error message is the same. Other suggestions. Look at the original example.

[Mixaalser684]

I've removed this part from the code (in bold):

input [b]<%If (CStr((rsContact.Fields.Item("bitChecked").Value)) = CStr("True")) Then Response.Write("checked=""checked""") : Response.Write("")%>[/b] type="checkbox" name="bitChecked<%=counter%>" id="bitChecked<%=counter%>"<input name="RecordID<%=counter%>" type="hidden" id="RecordID<%=counter%>" value="<%=(rsContact.Fields.Item("RecordID").Value)%>"></td>

I've removed that part, and this part:

cRecordID = "RecordID" & CStr(i)cbitChecked = "bitChecked" & CStr(i)'Create the update sql statementupdateSQL = "UPDATE tblContact SET bitChecked=" & fFormat(Request.Form(cbitChecked)) & " WHERE RecordID=" & fFormat(Request.Form(cRecordID))

Instead of

cRecordID = "RecordID" & CStr(i)cbitChecked = "bitChecked" & CStr(i)

I simply entered

'Create the update sql statementupdateSQL = "UPDATE tblContact SET bitChecked=" & fFormat(Request.Form( ""bitChecked" & CStr(i))) & " WHERE RecordID=" & fFormat(Request.Form("RecordID" & CStr(i)))

And removed fFormat.When I ran the page and checked the first button and clicked submit I got this message:

UPDATE tblContacts SET bitChecked=on WHERE RecordID=1UPDATE tblContacts SET bitChecked= WHERE RecordID=2Microsoft OLE DB Provider for ODBC Drivers error '80040e14'[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement./allmymtgcards/update.asp, line 41

If I see well the page recognized the first entered value as on, and updated but when it got to the second button the button didn't had the default value because it wasn't clicked, so maybe I should ad somethin to take the values from the database and use them if they are not entered, but what?After that I tried checking all 3 checkboxes and submit and I got this error message:

UPDATE tblContacts SET bitChecked=on WHERE RecordID=1UPDATE tblContacts SET bitChecked=on WHERE RecordID=2UPDATE tblContacts SET bitChecked=on WHERE RecordID=3Response object error 'ASP 0156 : 80004005'Header Error/allmymtgcards/update.asp, line 30The HTTP headers are already written to the client browser. Any HTTP header modifications must be made before writing page content.

Any explanations and Ideas?P.S. - Don't forget to check the original tutorial I'm using from my previous post.

[justsomeguy]

The part you commented out will check the box if it's selected in the database. So if the bitChecked field in the database is true then it will write out the "checked" attribute to check the box on the page. I think there's one issue with the checkbox, they don't give it a value. It would be better to give the checkbox a value and then check to see if the value was sent and update the database with that. With checkboxes, if you don't select a checkbox then it doesn't even get submitted with the form. So that's why the second database query failed, because instead of the checkbox being "off" or whatever, it was not even there. So we need to check if the box was submitted or not and update the query. So add a value attribute to the checkbox, I usually just use "true".<input <%If (CStr((rsContact.Fields.Item("bitChecked").Value)) = CStr("True")) Then Response.Write("checked=""checked""") : Response.Write("")%> type="checkbox" name="bitChecked<%=counter%>" id="bitChecked<%=counter%>" value="true">Then inside the loop we set a variable 1 if the checkbox value is "true", or 0 otherwise. Then we can use that variable in the query for the value instead of the value from the form directly.

For i = 1 To fFormat(Request.Form("counter"))  'Create the proper field names to reference on the form  cRecordID = "RecordID" & CStr(i)  cbitChecked = "bitChecked" & CStr(i)    if Request.Form(cbitChecked) = "true" then	val = 1  else	val = 0  end if  'Create the update sql statement  updateSQL = "UPDATE tblContact SET bitChecked=" & val & " WHERE RecordID=" & fFormat(Request.Form(cRecordID))  'Run the sql statement  Call sRunSQL(updateSQL)Next

[Mixaalser684]

Yes it works, thanx Justsomeguy Now I'll go and reupdate my dynamic application and If I have new trouble I'll call in. I hope I won't.