CODEWITHSUNDEEP
sqlsql-servervb6sql-updaterecordset
BubbaSparxx
BubbaSparxx

Reputation: 23

Updating recordset in VB6

Quick question, new to this site, new to programming especially.

I'm trying to Select data from SqlServer and then update it while it's still a recordset. I don't want to update the actual table with the data.... So if I have a field called RS!("FirstName") and I want to add a string to it, for example "MR"

This is how I go about it

str = "Select FirstName from tblClient"
rs.Open SQL, g_cn, adOpenStatic

rs.movefirst
do while not rs.eof 
    rs("FirstName") = "MR" & rs("FirstName") <--- this is what i'm trying to do but it tells me I cannot update it. 
    rs.movenext
loop

How to fix this

Upvotes: 0

Views: 5644

Answers (2)

dbmitch
dbmitch

Reputation: 5386

You should show all your code to make it easier to help you.

From what you DO show - an obvious fix is to use your str variable. It would show up quicker if you add an Option Explicit at the very top of your module, then you could tell you havn't declared some of your vars

In any case if you use the sql you declared - and - change your recordset type it should work

Change:

str = "Select FirstName from tblClient"
rs.Open SQL, g_cn, adOpenStatic

To:

str = "Select FirstName from tblClient"
rs.Open str, g_cn, adOpenDynamic, adLockOptimistic

Loop:

rs.Edit
rs("FirstName") = "MR" & rs("FirstName") 
rs.Update
rs.movenext

Upvotes: 0

jac
jac

Reputation: 9726

The real answer is to create a new disconnected recordset. You can do to it what you want and just set it to nothing when you're finished. You can do it specifically to the recordset you want to modify or write a more generic method that copies the source recordset.

Here is a generic example. rsOriginal is the recordset passed in and used in code later. rsUpdateable is a local recordset built as a copy of the original, then assigned to the original recordset variable. It is now completely updateable and changes cannot be saved back to the source table.

...

'clone a recordset into a new updateable recordset
Dim rsUpdateable As New ADODB.Recordset
Dim fld As ADODB.Field
'errors here are unlikely, but if  happens I don't want to alert the user
On Error Resume Next
'build the table schema
For Each fld In rsOriginal.Fields
    rsUpdateable.Fields.Append fld.Name, fld.Type, fld.DefinedSize
Next fld
rsUpdateable.Open
'populate the new recordset with the original values
rsUpdateable.AddNew
For Each fld In rsOriginal.Fields
    rsUpdateable.Fields(fld.Name).Value = fld.Value
Next fld
Set rs = rsUpdateable   'done
On Error GoTo OriginalErrorHandler    'restore error handler

...

Upvotes: 1

Related Questions