I am accessing data from SQL server

[cfriedberg]

I am accessing data from SQL server using VB 6.0. I have reconciled myself to the fact that I will not be able to use the recordsets to update data to the server. However, I would like to be able to update the recordset that was returned because I use it to populate a grid.

The recordsets all have
CursorLocation = adUseClientBatch
CursorType = adOpenStatic
EditMode = adEditNone
LockType = adLockReadOnly.

Trying to change any of these properties is futile. Am I to assume that the SQEDB provider will not let me? If that is the case, then is there another provider or another method that will work with SQL 2000?

Carolyn

 

[sunaj]

Hi,

Open you recordset as

Rst.Open "TableName", YourConnection[String], adOpenKeyset, adLockOptimistic, adCmdTable
Rst.addnew ..... 'add new row
Rst.Update

If you want to add records.

You can also send sql commands to the server and then requery your recordset:

YourConnection.Execute "INSERT INTO tblMyTable(MyField) VALUES ('test')"
rst.requery
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[cfriedberg]

After looking at your suggestion, and made a futile attempt, I must tell you that I am using a stored procedure rather than a simple query.

I still have the same values as before and an inability to update the recordset.

Is there any other way?

Thank you,

Carolyn

 

[sunaj]

Hi,

You'll probably have to explain a little more and maybe show some code for me to understand extactly what you are trying to do.

There is basically 3 way of executing stored procedures:

If you don't need to input any values and don't need to return anything, use the .execute method:
YourConn.Execute "EXEC UP_MyStoredProc"

If you don't need to input any values but want to return a recordset, use a recordset:
Rst.open "EXEC UP_MyStoredProc", YourConn[String]

If you want to input values to the stored procedure and return values use the commadn object. See e.g.

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

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[cfriedberg]

Here is my code:

Dim i As Integer
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim provStr As String
ReDim Preserve pstrParmValues(4)
provStr = "Provider=sqloledb;Server=" & pubstrServer & ";Database=" & pubstrDatabase & ";Trusted_Connection=yes"

'Fill in the array elements
For i = 0 To UBound(pstrParmValues)
If IsEmpty(pstrParmValues(i)) Then
pstrParmValues(i) = Null
End If
Next

'Perform the sets
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenKeyset
'Open the connection
cn.Open provStr
If cn.State <> 1 Then
GoTo ErrorHandler
End If

'Set the command to the connection
Set cmd.ActiveConnection = cn

'Give the name of the stored procedure
cmd.CommandText = pstrStoredProc
cmd.CommandType = adCmdStoredProc

'Tell the data provider where to send the recordset.
cn.CursorLocation = adUseClient
'Get the records

Set rs = cmd.Execute(, Array(pstrParmValues(0), pstrParmValues(1), pstrParmValues(2), pstrParmValues(3), pstrParmValues(4)), adCmdStoredProc)

If rs.RecordCount = -1 Then
GoTo ErrorHandler
End If

'Assign the recordset to the function name.
Set SelectByStoredProc = rs


Thank you for your help.

Carolyn

 

[cfriedberg]

Keep in mind that I am only interest in updating the recordset. I have other code that will update the SQL data.

Carolyn

 

[NickBulka]

Here's some example code that may help:

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

nick bulka

 

[cfriedberg]

Thank you for your suggestion. I have looked at the code and tried to implement it except for the option adCmdTable. I used adCmdStoredProc. Also this example updates the sources of the recordset. I only want to change the value of one field of the recordset that the user has previously selected.

Carolyn

 

[NickBulka]

So modify the code to meet your needs. Are you having problems understanding how?
nick bulka

 

[cfriedberg]

The problem is that I was trying to update a recordset that had been opened adOpenStatic instead of adOpenKeyset. When drawing up a recordset from a Stored Procedure, the recordset seems to always be adOpenStatic. Therefore, the source of the recordset is unidentifiable..

Carolyn