Record set update

Hi

I am new to visual studio and I am attempting to edit records held in mysql, the code below runs and throws no errors "strAdd" is underlined and says that it is used before it has been given a value! But If I debug.print(strAdd) I get the expected string returned. What do I need to do to get the updated records saved?

' code

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim StrAdd as string

cn = New ADODB.Connection

cn.ConnectionString = "Provider=SQLNCLI;" _

& "Server=(local);" _

& "Database=customerlink;" _

& "Integrated Security=SSPI;" _

& "DataTypeCompatibility=80;" _

& "MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT*" & _

" FROM tblvsol" & _

" Where RevStatus = " & 0 & _

" And GeoCodeStatus = " & 1

cn.Open()

rs = New ADODB.Recordset

With rs

.ActiveConnection = cn

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenDynamic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

End With

Do While Not rs.EOF

Code here finds the value for the string variable ‘atrAdd’

strAdd = New Value

If Not strAdd Is Nothing Then

rs.Fields("location").Value = strAdd

rs.Fields("RevStatus").Value = 1

rs.Update()

else

end if

loop

Regards

Joe

Hi Experts

Have I posted in the wrong forum?

Regards

|||Can you add some code on how you're assigning new value to strAdd?|||Actually, yes you posted in the wrong forum, but nevertheless we will help you. Do you enter the Is Nothing part and the appropiate update or does the execution never touch this portion of the code ? Are you able to send a reproducable class to us ? as from the information you posted the code should be Ok. Did you start the profiler to see wheter the command is executed against the database or could you make sure through debugging that it never enters portion of the code ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Experts

Thanks for your response, The variable StrAdd Is a street address which is returned from a mappoint recordset:

StrAdd = objResults.Item(1).Location.StreetAddress.Value

This is returned as a string.

The update portion of the code is only entered If a string value is returned from the mappoint recordset.

Regards,

joe

|||Ok, this is now a bit clearer to me, but did you check the value during debugging time or did you run the profiler ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens K. Suessmeyer.

Thanks for your response, I had checked the value during debugging. I have had a reply posted on another forum which has provided me with the answer, I appreciate your efforts on my behalf and would not like to waste your time. I have reproduced the reply below, again..

Thank you,

Joe

instead of opening the recordset like so:

rcdSet.Open strSQL, ADO, adOpenForwardOnly, adLockReadOnly, -1

Use this method:

rcdSet.Open strSQL, ADO, adOpenDynamic, adLockBatchOptimistic, -1

Now, after looping through your recordset and changing your values, call:

rcdSet.UpdateBatch

|||Or as an alternative if you want to use Update method and not UpdateBatch - then you can use adLockOptimistic for lock type.