We help IT Professionals succeed at work.

DBGrid with ADO and insert

Using ADO how to bound records to a data grid or db grid using SQL queries. How to insert, edit, delete the data grid rows / columns?
Comment
Watch Question

Do you already have the connection and recordset knowledge?

If so, it is not too difficult

You simply Set Datagrid.DataSource = RecordSet

The recordset should be returned as a disconnected (AdLockBatchOptimistic) recordset, and set its activeconnection Property = nothing.

make sure you have the properties for the datagrid set to allow edits, or add new, or whatever you wish.

You will also have to handle data validation (making sure the user does not enter invalid data in the fileds) within the events for the datagrid.

Then when the users clicks your update button(or whatever you are using), simply open the connection and call the recordset.UpdateBatch method.
Dim pCon as ADODB.Connection
Dim pRS as ADODB.Recordset

set pCon = New ADODB.Connection
Set pRS = New ADODB.Connection

'open connection
pCon.Connectionstring = strConString
pCon.Open

With pRS
  .ActiveConnection = pCon
  .CursorType = adOpenStatic
  .CursorLocation = adUseClient
  .Locktype = adLockBatchOptimistic
  .Open "SELECT * FROM Mytable WHERE myField
   Like 'lkll'",,,adcmdtext
  .activeconnection = nothing
end with

Set Datagrid1.Datasource = pRS


'To Update records in DB

With pRS
    .ActiveConnection = pCon
    .UpdateBatch
    If m_Connection.Errors.Count <> 0 Or Err.number <> 0
       Then .CancelBatch
    .ActiveConnection = Nothing
End With
oops, sorry forgot to chage that "m_Connection" to pCon in the last With statement

Explore More ContentExplore courses, solutions, and other research materials related to this topic.