We help IT Professionals succeed at work.

Update access db ASP with script

asrgrant
asrgrant asked
on
Trying to update into free text field in access database. Ive used this code to update a true/false field, but doesnt do it with the following code ..Trying to update record with the free text '777'

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.tpriorityID = '"&(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=777"><img src="../images/icons/buttons/t_yes.gif" alt="A1 - Priority" width="17" height="17" border="0"></A>

many thanks
Comment
Watch Question

Kiran SonawaneProject Lead
Top Expert 2011

Commented:
Print strSQL and check it against access db. You may need

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

Commented:
be aware that this code is not checked against sql injection

Author

Commented:
its on an intranet

Author

Commented:
sonawanekiran: not sure what you mean ?
Top Expert 2011

Commented:
He means to have you web page print out strSQL (e.g., use response.write("strSQL")) to the webpage to verify that it is formatted correctly, etc.

Your strSQL line produces strSQl that looks like this (notice an extra " at the end):
UPDATE tasking SET tasking.tpriorityID = '12345' WHERE taskID = 12345"

The one he posted produces an strSQL line that looks like this:
UPDATE tasking SET tasking.tpriorityID = '12345' WHERE taskID = 12345

IMO, you may need the line to look like this:
UPDATE tasking SET tasking.tpriorityID = '12345' WHERE taskID = '12345'

Author

Commented:
The whole code works fine if updating a True/False field. The problem is that if I want to update with text into a normal text field it does not work. When I try to change this &ccc=True,  to this &ccc=free text, its doesnt update the field.

This works for true false entry
<A href="solvetask.asp?taskID=<%=(openlist.Fields.Item("taskID").Value)%>&ccc=True"
                ><img src="../images/icons/aup.gif" width="16" height="16" border="0"></A>
head code
<% If Request.QueryString("ccc") <> "" Then %>
<%
strConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\fcissu\data\fusion.mdb"
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open strConnectionString
strSQL = "UPDATE tasking SET tasking.ccc = not(ccc) WHERE taskID = "&(Request.QueryString("taskID"))&""
cnn.Execute strSQL,,adCmdText + adExecuteNoRecords
cnn.Close
%>
<% Else %>
<% End If %>
----
Top Expert 2011

Commented:
OK - so not a quotes issue.  That's good!  That means it's a syntax issue.
Using your "known good" update statement as a starting point, it looks like the changes you made should work... but, what happens when you run the strSQL line of code directly in the database?  Does it work?

Author

Commented:
Yep :-)
Top Expert 2011

Commented:
Have you tried hard-coding the strSQL = "..." line to an update statement that you know works?

Author

Commented:
Yep it all works fine. As soon as I try to update a text field, rather than a True/False field it stops working. Ive pulled the code from other pages as I use this code on many sections of our intranet. Its the first time ive used it to try to update a Text field ... yikes !!
Top Expert 2011

Commented:
You say it works, but in the next sentence you say it fails.  Perhaps you misunderstood the question.

First question: have you taken the output of this line of code:
strSQL = "UPDATE tasking SET tasking.tpriorityID = '"&(Request.QueryString("update"))&"' WHERE taskID = "&(Request.QueryString("taskID"))&""

Open in new window

and run it directly in your database?  Did it work?

The second question I asked was "have you tried hardcoding your strSQL= line to use a 'known good' update statement (one that you have run manually directly in your database)", like this:
strSQL = "UPDATE tasking SET tasking.tpriorityID = '777' WHERE taskID = 12345

Open in new window

Author

Commented:
Yes those all work -
to be clear:

1) the code shown works completely with a True/False update in the database

2) when I change the snippet  ..
From
&ccc=True" (this does update a True/False field)
To This - to update a different field in the Db, a field that is text
&ccc=fred smith"  (as an example of free text) it will not update at all.

So im guessing there maybe issues with quotes etc. Its got me stumped


     


Top Expert 2011

Commented:
Again... what you just said does not answer the question I asked.

My first set of code is for using the "free text" strSQL, NOT the true/false code.
Take your page that you are using for "free text", response.write the strSQL that it creates and try running THAT code directly in your database.  DOES that work?

If running it directly in the database works, but it does not work when the webpage runs it, then try the second step which is to hardcode the statement that you ran in the database into your ASP code and run it that way.
Commented:
Ok Solved it !

Simple matter of removing the single quotes ..after  SET tasking.tpriorityID =

from this
"UPDATE tasking SET tasking.tpriorityID = '"&(Request.QueryString("update"))&"' WHERE taskID = "&(Request.QueryString("taskID"))&""

to this
"UPDATE tasking SET tasking.tpriorityID = "&(Request.QueryString("update"))&"WHERE taskID = "&(Request.QueryString("taskID"))&""

Many thanks for all you guys time , many thanks

Author

Commented:
solved it myself - after much head scratching