ASP ADO SQL Query

Using ADO connection with DSN I don't have any problems.
Using ADO connection without DSN I have problems using insert and update queries.

An Example (Code is not complete, only revelant parts shown):

m_Provider= "DRIVER=Microsoft Access Driver (*.mdb); DBQ=" & Server.MapPath("\iisadmin") & "\SVV_Db\Links.mdb;"
...
Set m_ObjConn= Server.CreateObject("ADODB.Connection")
m_ObjConn.ConnectionString= m_Provider
m_ObjConn.Open
...
Set rstLinks= Server.CreateObject("ADODB.recordset")
rstLinks.Open m_Query, m_ObjConn, adOpenKeyset, adLockOptimistic
...
with rstLinks
.MoveFirst
.Fields("URL" )= m_URL
.Fields("Name" )= m_SName
.Fields("Description")= m_Description
.Fields("Keywords" )= m_Keywords
.Fields("Misc" )= m_Memo
.Update
End With

works fine.

The following code does not give any error, but does not update anything at all:

m_Provider= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath("\iisadmin") & "\SVV_Db\Links.mdb;"
...
Set m_ObjConn= Server.CreateObject("ADODB.Connection")
m_ObjConn.ConnectionString= m_Provider
m_ObjConn.Open
...
' A Query has been performed returning a valid Id
...
ID= rstLinks.Fields("Id")
Query= "UPDATE [Links] " & _
"SET Name = '" & m_SName & "', " & _
" Description = '" & m_Description & "', " & _
" Keywords = '" & m_Keywords & "', " & _
" Misc = '" & m_Memo & "' " & _
"WHERE [Links].Id=" & ID & ";"
...
Set rstLinks= m_objConn.execute(SQL)
...

This performs without an error but no update has been made.

Does anybody understands why???

Your UPDATE SQL string is being stored in a variable call "Query", whereas you are attempting to run the SQL using a variable called "SQL".

Also, if no update is occuring, are you 100% sure that the ID you are updating exists, as this will not throw an error.

You are right, but that's not the reason why it does not work. As I already stated, this is a shortcut from a large asp file. The function is wrapped in another function where the called parameter is SQL. So I made a slight mistake in copy and pasting this example

I would do a Response.Write on your query and make sure it looks the way you want it.

I have done a response write and it looks like this:

Record Updated
SQL= UPDATE [Links] SET Name = 'ASP Classes Examples', Description = 'ASP Classes Examples', Keywords = 'asp classes examples vba', Misc = 'ASP Class Modules vba examples.' WHERE [Links].Id=876;

URL = http://www.thevbzone.com/s_asp_classes.htm
Name= ASP Classes Examples
Misc= ASP Class Modules vba examples

In the first part you may notice I have added a dot (.) behind '...vba examples.'
Notice also that behind the response the dot (.) does not appear.

You don't need the semicolon at the end of your update statement. Also you might consider doing a Cint(ID) to ensure that it actually an integer and not a string.

so I would write your statement as follows:

ID= Cint(rstLinks.Fields("Id"))
Query= "UPDATE [Links] " & _
"SET Name = '" & m_SName & "', " & _
" Description = '" & m_Description & "', " & _
" Keywords = '" & m_Keywords & "', " & _
" Misc = '" & m_Memo & "' " & _
"WHERE [Links].Id=" & ID

Another thing you might want to consider is doing a test to see if ID is numeric. If something other than an alphanumberic value is being passed you may want your db to update (to avoid sql injection attacks).

if IsNumeric(ID) then ....

End if

I will show the 2 routines below. The variable m_UseOLEDB is used to switch between both methods.
If m_UseOLEDB is false everything does work fine.
If m_UseOLEDB is true the updates query does not work and gives no error message at all.


' Updates Link information for a given link
Public Function UpdateLink2Database()
Dim rstLinks ' recordset
Dim AUE_Stat ' Add/Update/Edit Status
Dim ID ' ID found

AUE_Stat = cAUE_Unknown
Query = "SELECT * " & _
"FROM [Links] " & _
"Where (Links.URL='" & m_URL & "');"

OpenRecSet rstLinks, Query
if m_UseOLEDB= false then
rstLinks.Open m_Query, m_ObjConn, adOpenKeyset, adLockOptimistic
End if
if rstLinks.EOF then AUE_Stat= cAUE_NotFound else AUE_Stat= cAUE_Found

if AUE_Stat= cAUE_Found then
if m_UseOLEDB= false then
with rstLinks
.MoveFirst
.Fields("URL" )= m_URL
.Fields("Name" )= m_SName
.Fields("Description")= m_Description
.Fields("Keywords" )= m_Keywords
.Fields("Misc" )= m_Memo
.Update
AUE_Stat= cAUE_Updated
End With
else
ID= Cint(rstLinks.Fields("Id"))
Query= "UPDATE [Links] " & _
"SET Name = '" & m_SName & "', " & _
" Description = '" & m_Description & "', " & _
" Keywords = '" & m_Keywords & "', " & _
" Misc = '" & m_Memo & "' " & _
"WHERE [Links].Id=" & ID '& ";"
if OpenRecSet(rstLinks, Query)<> 0 then
AUE_Stat= cAUE_NotFound
else
AUE_Stat= cAUE_Updated
end if
if rstLinks.EOF then
response.write "No records updated"
else
response.write "Record Updated"
response.write "<br>SQL= " & Query
response.write "<br>URL = " & rstLinks.Fields("URL")
response.write "<br>Name= " & rstLinks.Fields("Name")
response.write "<br>Misc= " & rstLinks.Fields("Misc")
Response.write "<br>"
End if
'AUE_Stat= cAUE_Updated
End if
End if

UpdateLink2Database= AUE_Stat

CloseRecSet AUE_Stat
End Function ' UpdateLink2Database


Private Function OpenRecSet(rstR, SQL)
' Init
OpenRecSet= 0

' Body
if not IsObject(rstR) then
if m_UseOLEDB= false then
Set rstR= Server.CreateObject("ADODB.recordset")
else
'response.write SQL
on error resume next
Set rstR= m_objConn.execute(SQL)
if Err.Number> 0 then
OpenRecSet= -1
Response.write "Error: " & Err.Number & "<br>" & Err.Description & "<br>"
End if
on error goto 0
End if
End if
'if m_UseOLEDB= false then response.write SQL & "<br>"
on error goto 0
End Function ' OpenRecSet

I have found the error. It is rather sneaky.

The update is never reached, because rstR is an object after the select query. The only times this is true, is before the insert and update queries.

Thx all for your help.

The openrecset function has to be as follows:
[pre]
Private Function OpenRecSet(rstR, SQL)

' Init
OpenRecSet= 0

' Body
if not IsObject(rstR) then
if m_UseOLEDB= false then
Set rstR= Server.CreateObject("ADODB.recordset")
End if
End if

if m_UseOLEDB= true then
'on error resume next
Set rstR= m_objConn.execute(SQL)
if Err.Number> 0 then
OpenRecSet= -1
Response.write "Error: " & Err.Number & "<br>" & Err.Description & "<br>"
End if
on error goto 0
End if
'if m_UseOLEDB= false then response.write SQL & "<br>"
on error goto 0
End Function ' OpenRecSet
[/pre]