Connecting to a SQL-Server database using VB6 1

[moben]

Hi,

I'm connecting to a sql-server database using VB6. I've added a datagrid which displays records from one of the tables within the database.

Unfortunatly I can't seem to update any of the information being displayed in the datagrid.

I am using the following code to connect to the sql-server database:

ConnectString = "DSN=LondonDB;" & "UID=user1;PWD=hello;" & "DATABASE=LondonDB"

and the following code to get the data from table Sales into the datagrid.

Dim rempRS As Recordset

strsql = "SELECT * from Sales"

Set tempRS = New Recordset
tempRS.Open strsql, goConn, adOpenKeyset, adLockPessimistic, adCmdText

DataGrid1.Visible = -1
Set DataGrid1.DataSource = tempRS
DataGrid1.Refresh


The property AllowUpdate for the datagrid is set to True.

Can anyone tell me what I need to do to allow changes to be made to table Sales directly via the DataGrid ?

Thanks,

Moben

 

[MarkSweetland]

It's probably the recordset/connection that is not allowing the edit, not the grid. Try opening with a client side cursor:

Dim goConn As New ADODB.Connection
With goConn
.ConnectionString = "DSN=LondonDB;" & "UID=user1;PWD=hello;" & "DATABASE=LondonDB"
.CursorLocation = adUseClient
.Open
End With

Dim tempRS As ADODB.Recordset
Dim strsql As String

strsql = "SELECT * from Sales "
Set tempRS = New ADODB.Recordset
tempRS.Open strsql, goConn, adOpenKeyset, adLockPessimistic
DataGrid1.Visible = -1
Set DataGrid1.DataSource = tempRS
DataGrid1.AllowUpdate = True
DataGrid1.Refresh




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[bubarooni]

What actually fires the update event. I can edit the values in my datagrid but they are not saved to the database.

Thanks

 

[MarkSweetland]

When the user moves to another row in the DataGrid or the Recordset object's Update method is executed...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[bubarooni]

Do I need to add code in the ColEdit event to write the changes to a cell? Moving to the next row doesn't seem to be doing the trick by itself.

 

[bubarooni]

Never mind. I changed to adOpenDynamic and adLockOptimistic and it seems to work fine now. Hopefully it won't mess anything else up and help moben as well. Thanks MarkSweetland.