Database Update

[nbcrockett]

Can anyone tell me why the code below won't work. I've tested to make sure that it's pulling the right record when the recordset is opened. I've made sure that the value is being pulled over from the form. I've even made sure that it's updating the recordset object, but for some reason the rsCore.Update isn't actually updating the database. I know the database can be written to, because there is other code on the page that is adding a new record to another table in the same database.


<%
Dim connDatabase
Dim rsCore
Dim strSelect
Dim strUID

strUID = Request.Form("txtUID")

Set connDatabase = Server.CreateObject("ADODB.Connection")
connDatabase.ConnectionString = "DSN=Database.dsn"
connDatabase.Open

Set rsCore = Server.CreateObject("ADODB.Recordset")
strSelect = "SELECT * FROM tblCore WHERE UID = " & strUID
rsCore.Open strSelect, connDatabase, , adLockPessimistic

If Request.Form("txtFirstName") <> "" then
rsCore("FirstName") = Request.Form("txtFirstName")
Else
rsCore("FirstName") = Null
End If

rsCore.Update

rsCore.Close
Set rsCore = Nothing

connDatabase.Close
Set connDatabase = Nothing
%>

[chazzy]

you're getting a record set. you need to send an additional request to the DSN to update the data. i'm fairly confident that there is no resultset.Update method.

UPDATE tblCore SET FirstName= its value, ...

See also this article
http://www.stardeveloper.com/articles/display.html?article=2000032801&page=1

[nbcrockett]

I've got books that use recordset.update and have also seen it used in tutorials on other sites. I'm also using the same code to update to a database in over a hundred other locations. Any ideas why it wouldn't work in this one location?

[ProWeb]

Agreed with Chazzy. Ive never seen Recordset.update used without the SQL statement.

Otherwise how would the SQL server know what you want to update?

[nbcrockett]

It's updating an Access database not a SQL Server.

[Master Shake]

strUID = Request.Form("txtUID")

strSelect = "SELECT * FROM tblCore WHERE UID = " & strUID

the top statement indicates to me that strUID is a string as you have identified it. so in the select statement you need to delimit it.

strUID = Request.Form("txtUID")

strSelect = "SELECT * FROM tblCore WHERE UID = '" & strUID & "'"

now if this needs to be a number then you will have to change strUID to and integer or long integer whichever is appropriate, then you will not need the delimiters.

Master Shake

[nbcrockett]

The select statements not the problem. It's pulling the correct record and it is an integer so the delimiter isn't needed. I just need to know why the rsCore.Update code isn't working. Any ideas?

[Master Shake]

Only thing I could suggest is to play with cursortype and locktype, but I am a little confused how strUID has become an integer.

Master Shake

[nbcrockett]

I changed the cursortype to adOpenDynamic and it started to work fine. This makes absolutely no sense to me. I've used this exact code in countless other locations and didn't have to use a cursortype. If anyone can explain why this one page would require it please feel free to enlighten me. Otherwise, thanks greatly for helping me out.
Just to clairify strUID is getting it's value from Request.Form("txtUID"). Request.Form("txtUID") is a hidden value on the previous page that I control and already know is a number.