Dim cmd as New ADODB.Command
Dim RSList as ADODB.Recordset
Dim lRowID as Long
Dim sSQL as string
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=MYSQLSERVER;user ID=sa;Password=qwerty;Initial Catalog=MYDATABASE"
'this works just fine
cmd.CommandText = "update my_table set my_fielda = 0.0 where my_fielda is null"
sSQL = "select next_row_id from my_glossary where table_number = 15" 'this would return a longint
'this syntax is wrong (these next two lines of code) HOW IS THIS DONE? All I want to do, using ADO is assign a value from a record set to a variable
RSList.Open sSQL, cmd.ActiveConnection
lRowID = RSList("next_row_id")
'this works if I set lRowID = to a number so the syntax of the next 2 lines is good
cmd.CommandText = "update my_table set my_fieldb = 'Test' where row_id = " & lRowID & ";"