Frustrating Record Update

I'm going crazy over this. I can't update a database record without the table just being opened. I have to pass a variable as part of the SQL statement query in order update the correct record. But I can't pass the variable to the rs.Open command as it doesn't accept it. If I try and use the rs.Execute method which will accept the SQL statement, I can't update the record because it's locked. How am I do to this?

Dim RecordUpdateValue As String
RecordUpdateValue = "SELECT ROADID FROM MSLINK_ROADID WHERE MSLINK" & " = " & UserForm1.TextBox1.Value

Dim dbRst2 As New ADODB.Recordset
dbRst2.Open "MSLINK_ROADID", dbConn, adOpenKeyset, adLockOptimistic, adCmdTable

dbRst2!RoadID = UserForm1.TextBox2.Value
dbRst2.Update

End Sub

I somehow have to get the value in RecordUpdateValue passed to the recordset so it updates the correct record. Otherwise it just updates the first record, which is wrong.

 

Hmmm just from looking at the code you posted something seems off. Usually with ADO you want to do things in an order similar to the following:

set up and establish a connection object
create a recordset object and set it's activeconnection to the above
run your query (you can include the = as part of the first string by the way)
change the fields you want to change
update the recordset
close the recordset

note also that if the key you are using to find your record is a string then you need to include single quotes around it in your query like so:

"SELECT * FROM table WHERE key = ' " & text1.text & " ' "

I put some extra spaces in that so you can see the single quotes '


Regards,
Jacob Dinardi

 

I have the database connection esstablished further up in another piece of code which works fine. I just didn't bother to put it in there, since the problem I'm having is the incorrect record is updating.

The part that's not working is using a query string. As soon as I try using a query string I get an error, something about the provider not allowing that function.

So I have to use the recordset.Open function with the parameters specified in order to open the data and be able to edit it.

 

KB - it would be helpful if you can post the full code that you're using to establish your connection, the line of code that throws the error, and the error message.

"The part that's not working is using a query string. As soon as I try using a query string I get an error, something about the provider not allowing that function."

That sounds like there may be a problem with the way you are connecting.

"So I have to use the recordset.Open function with the parameters specified in order to open the data and be able to edit it. Unfortunately this only lets me open a table in the database, not run a SQL query against that table."

I'm not completely clear on what you are saying here. You can run SQL queries in recordset.open something like

myRecordSet.Open "SELECT * FROM any_table WHERE id = 15"

If you are getting an error trying to do that then as mentioned above we really need to see the entire code.

Regards,
Jacob.

 

The simple hack is this:

Dim RecordUpdateValue As String
RecordUpdateValue = "UPDATE MSLINK_ROADID SET RoadID = " &
UserForm1.TextBox2.Value " WHERE MSLINK = " & UserForm1.TextBox1.Value

dbConn.Execute RecordUpdateValue

I am not taking into accout the datatype for RoadID & MSLINK.

The proper way to do it is with a Command Object - here is a link with
sample code. This way you can do proper error checking and see how many
rows have been affected, if needed.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthexecutex.asp

Good Luck,

Scott

table just being opened. I have to pass a variable as part of the SQL
statement query in order update the correct record. But I can't pass the
variable to the rs.Open command as it doesn't accept it. If I try and use
the rs.Execute method which will accept the SQL statement, I can't update
the record because it's locked. How am I do to this?

recordset so it updates the correct record. Otherwise it just updates the
first record, which is wrong.

 

Don't know if your problems are over, but don't do this cause it leads to
memory leaks all sorts of trouble:

adCmdTable

Do this instead:

Dim dbRst2 as ADODB.Recordset
set dbRst2 = New ADODB.Recordset

'this next step makes the recordset properties more clear and easy to debug
later.
dbRst2 .CursorLocation = adUseClient
dbRst2 .CursorType = adOpenStatic
dbRst2 .LockType = adLockOptimistic
Set dbRst2 .ActiveConnection = dbConn

dbRst2 .Open "Select ROADID FROM MSLINK_ROADID WHERE MSLINK = ' " &
UserForm1.TextBox1.Value & " ' ", , , , adCmdText
'(I put extra space between the double quote and the single quote just to
make it easy to spot. You need the single quote, because the value is a
string)

'I also like to establish the value using the traditional form, but it's
faster using the "field number" instead of the "Field Name"
dbRst2.Fields("ROADID").Value = UserForm1.TextBox2.Value
dbRst2.Update

By the looks of your code I'm supposing that ROADID is of type CHAR

If you're going to use the connection object to "execute" the update, then
the recorset can be readonly

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

table just being opened. I have to pass a variable as part of the SQL
statement query in order update the correct record. But I can't pass the
variable to the rs.Open command as it doesn't accept it. If I try and use
the rs.Execute method which will accept the SQL statement, I can't update
the record because it's locked. How am I do to this?

recordset so it updates the correct record. Otherwise it just updates the
first record, which is wrong.

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthexecutex.asp

Thanks for the link.

 

Thank you very much. That worked perfectly!! There was a few things you did that I never tried. The cursorType you switched to adOpenStatic, and instead of adCmdTable you used adCmdText.

I had tried adCmdText, it had thrown an error saying it was a table I was trying to use in the database. Is this because of adOpenKeyset?

ROADID is acutally a int in the database, but it doesn't seem to matter once the record is populated in the database. As the properties from taht table are applied to the value in the update. There will never been a string in this column.

Thanks again, you saved my computer going out the window. :0)

 

About the type of command, you can actually leave that blank and use the
default, but that will take more time fetching the info.
adCmdTable is used when you're fetching a whole table, and adCmdText when
you're using an SQL string even if the string specifies all the records in a
table.
The cursor type depends on what functionality you want and it also depens on
the provider you are using.
The adOpenKeyset is similar to the Dynamic cursor, but they are both very
heavy on resources.
The adOpenStatic is a snapshot of the records, and you can always get a new
snapshot doing a Recordset.Requery

Also, you should never rely on automatic type convertions. If ROADID is
Integer, then you should use

dbRst2.Fields("ROADID").Value = cint(UserForm1.TextBox2.Value) <<<-- be
sure to check for a blank value before doing this or use VAL instead.

--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

did that I never tried. The cursorType you switched to adOpenStatic, and
instead of adCmdTable you used adCmdText.

trying to use in the database. Is this because of adOpenKeyset?

once the record is populated in the database. As the properties from taht
table are applied to the value in the update. There will never been a
string in this column.