Update access db ASP with script

Hi im trying to updat an Access Db table (tasking)using a button.
It does work if im updating a text field but not when Im updating a true/false field. The code that im stuck on is "&update=true". This will place the text "true" into a text field but wont change the conditon in the True/False field

Code
Head >>>>>>>>>>>>>>>
<% If Request.QueryString("update") <> "" Then %>
<%
strConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\www\data\fusion.mdb"
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open strConnectionString
strSQL = "UPDATE tasking SET tasking.advis = '"&(Request.QueryString("update"))&"' WHERE taskID = "&(Request.QueryString("taskID"))&""
cnn.Execute strSQL,,adCmdText + adExecuteNoRecords
cnn.Close
%>
<% Else %>
<% End If %>
----------------------------------------
Body
 <A href="solvetask.asp?taskID=<%=(openlist.Fields.Item("taskID").Value)%>&update=true"><img src="../images/icons/buttons/t_yes.gif" width="20" height="14" border="0"></A>

Any Ideas ?
asrgrant1959Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
If advis is a True/False (Yes/No) field, drop the single quotes (') that you are using as delimiters:

strSQL = "UPDATE tasking SET tasking.advis = "&(Request.QueryString("update"))&" WHERE taskID = "&(Request.QueryString("taskID"))&""
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EyalCommented:
try ...

strSQL = "UPDATE tasking SET tasking.advis = '"&abs(cint((Request.QueryString("update")))&"' WHERE taskID = "&(Request.QueryString("taskID"))&""
0
plummetCommented:
Hi,

Assuming that tasking.advis is a Yes/No field (AKA true/false, boolean etc) the problem is that you are trying to set it to a string: 'True'. It can only contain 0 or -1. The code should work if you remove the single quote, ie:

...
strSQL = "UPDATE tasking SET tasking.advis = " & (Request.QueryString("update")) & " WHERE taskID = "&(Request.QueryString("taskID"))&""
...

Open in new window


I hope that helps!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

asrgrant1959Author Commented:
Tried Eyal, but no luck and not much with the rest ..

strSQL = "UPDATE tasking SET tasking.advis ="&(Request.QueryString("advis"))&" WHERE taskID = "&(Request.QueryString("taskID"))&""

and in the body
<A href="solvetask.asp?taskID=<%=(openlist.Fields.Item("taskID").Value)%>&advis=-1"
                ><img src="../images/icons/buttons/t_yes.gif" width="17" height="17" border="0"></A>

Any ideas ... thanks guys btw
0
plummetCommented:
It should have worked. What error are you getting now?
0
asrgrant1959Author Commented:
Its simply not doing anything, click and the page returns no errors, but does not update the field ... eeek
0
plummetCommented:
How about writing the output to the HTML page and trying to run it from VBA? And you can post it here to make sure it looks OK too. Just do a response.write:

...
strSQL = "UPDATE tasking SET tasking.advis = " & Request.QueryString("update") & " WHERE taskID = " & Request.QueryString("taskID")
response.write strSQL
...

Open in new window


You could copy the resulting SQL statement into an Access query in SQL view, see what that does.

Also, I'd try just a simple .execute without the parameters
cnn.Execute strSQL
cnn.Close

Open in new window

0
mbizupCommented:
Try explicitly converting it to an integer:

strSQL = "UPDATE tasking SET tasking.advis = "&(Convert.ToInt32(Request.QueryString("update")))&" WHERE taskID = "&(Request.QueryString("taskID"))&""


Double-check the spacing, parens, etc.  
0
asrgrant1959Author Commented:
Thanks Guys it works .. Think it must have been a spacing issue ...Duh !
Final code

strSQL = "UPDATE tasking SET tasking.advis = "&(Request.QueryString("update"))&" WHERE taskID = "&(Request.QueryString("taskID"))&""

and in the body

<A href="solvetask.asp?taskID=<%=(openlist.Fields.Item("taskID").Value)%>&update=True"
                ><img src="../images/icons/buttons/t_yes.gif" width="17" height="17" border="0"></A>
0
asrgrant1959Author Commented:
As an addition -- if i wanted to use this update for updating to True if false, and vica versa. Could that be done ? Would it have to check the content of the field ?

Many Thanks Guys
0
plummetCommented:
yes, you could do that by doing something like this:
Update tasking set advis = not(advis)

Open in new window


which will set the field advis to True if currently False, and False if currently True
0
plummetCommented:
Actually you'd need the id, so:

Update tasking set advis = not(advis) WHERE taskID = "&(Request.QueryString("taskID"))

0
asrgrant1959Author Commented:
Works beautiful man .. so easy when you know how, as you can tell im  on my learning path, but im trying

Seriously many many thanks :-)
0
asrgrant1959Author Commented:
Brillient advice and easily understood
0
plummetCommented:
You're welcome!

Thanks
John
0
mbizupCommented:
asrgrant1959,

The answer you awarded full points to was to a follow-up question.  The code that you said worked as the "final code" for your original question is exactly what I posted in the first comment:

http:#a36969008

0
plummetCommented:
It would seem that two of us posted a correct solution at roughly the same time. I certainly don't post if I see a correct solution has already been given. Life is, however, too short to argue about such things and I have better things to do. The main thing is that asrgrant's problem was solved.
0
asrgrant1959Author Commented:
Sorry for me misunderstanding the thread - Im a newbie here so apologies.

Many thanks mbizup and many thanks to Plumment for the extra help on an additional to the question.

You guys are amazing help :-)


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.