?
Solved

Update access db ASP with script

Posted on 2011-10-14
21
Medium Priority
?
576 Views
Last Modified: 2012-05-12
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 ?
0
Comment
Question by:asrgrant1959
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36969008
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
 
LVL 15

Expert Comment

by:Eyal
ID: 36969038
try ...

strSQL = "UPDATE tasking SET tasking.advis = '"&abs(cint((Request.QueryString("update")))&"' WHERE taskID = "&(Request.QueryString("taskID"))&""
0
 
LVL 10

Expert Comment

by:plummet
ID: 36969055
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:asrgrant1959
ID: 36969287
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
 
LVL 10

Expert Comment

by:plummet
ID: 36969354
It should have worked. What error are you getting now?
0
 

Author Comment

by:asrgrant1959
ID: 36969388
Its simply not doing anything, click and the page returns no errors, but does not update the field ... eeek
0
 
LVL 10

Expert Comment

by:plummet
ID: 36969418
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36969462
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
 

Author Comment

by:asrgrant1959
ID: 36969715
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
 

Author Comment

by:asrgrant1959
ID: 36969720
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
 
LVL 10

Expert Comment

by:plummet
ID: 36969799
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
 
LVL 10

Expert Comment

by:plummet
ID: 36969805
Actually you'd need the id, so:

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

0
 

Author Comment

by:asrgrant1959
ID: 36969909
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
 

Author Comment

by:asrgrant1959
ID: 36969915
Brillient advice and easily understood
0
 
LVL 10

Expert Comment

by:plummet
ID: 36969971
You're welcome!

Thanks
John
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36969974
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
 
LVL 10

Expert Comment

by:plummet
ID: 36975701
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
 

Author Comment

by:asrgrant1959
ID: 36978069
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question