Record Delete - ExecuteNonQuery requires an open and available Connection

[John7000]

Hello,

I am amateur with VB database usage.

I've written a little database that keeps track of names, address, phone numbers etc. It displays the data in a DataGrid and stores them in a database. I am able to store and UPDATE new records without problems. Because of my ignorance of VB databases usage, I've tried for hours to get it to DELETE a record. I'm to the point I need to ask for expert help. Here is my code, which is based off of some code I found on the Internet:

I would greatly appreciate any help.

Thanks,
John

Public Sub Delete_TableRow(ByVal RowNumber As Integer, ByVal KeyID As Integer)
 
  myOleDbConnection = New _
  OleDbConnection("provider=microsoft.jet.oledb.4.0;" & _
  "user id=admin; Data Source=" & myDataSource)
 
  Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
  OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand
 
  OleDbUpdateCommand.CommandText = _
  "Delete from Table1 WHERE id=" & KeyID & " ; "
 
  OleDbUpdateCommand.Connection = myOleDbConnection
 
  myOleDbDataAdapter.UpdateCommand = OleDbUpdateCommand
 
  myOleDbDataAdapter.UpdateCommand.ExecuteNonQuery()
 
End Sub

[John7000]

I think I got it working now. I added this line and it now appears to work.

myOleDbConnection.Open()

so now the code looks like this:

     Public Sub Delete_TableRow(ByVal RowNumber As Integer, ByVal KeyID As Integer)
 
        myOleDbConnection = New _
               OleDbConnection("provider=microsoft.jet.oledb.4.0;" & _
                "user id=admin; Data Source=" & myDataSource)
 
        myOleDbConnection.Open() 
        Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
        OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand
 
        OleDbUpdateCommand.CommandText = _
                "Delete from Table1 WHERE id=" & KeyID & " ;  "
        OleDbUpdateCommand.Connection = myOleDbConnection
 
        myOleDbDataAdapter.UpdateCommand = OleDbUpdateCommand
 
        myOleDbDataAdapter.UpdateCommand.ExecuteNonQuery()
 
    End Sub
 

Hope this helps someone.

John

[jrtox]

I've written a little database that keeps track of names ...

Hello,


As you've said you have a datagrid where all record your from database are displayed, right? and Let me Guess, you have also a Command button, A DELETE Button for Deleting? if that so, we can continue.
Heres the step for deleting:
1st, Select a record from datagrid then Click Command Button Delete.

Now On your Datagrid, RowColChange Event you can Have

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
 
KeyID = DataGrid1.Columns(1).Value' If your Unique Key Is found in Column 0 then Used DataGrid1.Columns(0).Value 
 
End Sub

2nd, On your Command Button "DELETE" Click Event, you can have

Public Sub CommandDelete_TableRow()
 
Set myOleDbConnection = New ADODB.Connection
set myOleDbConnection=Nothing
 
'Establish Connection.
 OleDbConnection.Open "provider=microsoft.jet.oledb.4.0;user id=admin; Data Source=" & myDataSource""
 
'Delete Record Corresponding to the Selected Data on your datagrid.
myOleDbConnection.Execute "Delete from Table1 WHERE id=" & KeyID & "
 
End sub

Regards
Ervin

P.S This is the way I delete records, I don't force you to use this.

[John7000]

Hello,


As you've said you have a datagrid where all record your from database are displayed, right? and Let me Guess, you have also a Command button, A DELETE Button for Deleting? if that so, we can continue.
Heres the step for deleting:
1st, Select a record from datagrid then Click Command Button Delete.

Now On your Datagrid, RowColChange Event you can Have
...

Ervin,

Yes I have a datagrid that displays all database records and I have a Delete button.

Thanks for you help - you're code is more concise and therefore better than what I have.

John

[John7000]

...

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
 
KeyID = DataGrid1.Columns(1).Value' If your Unique Key Is found in Column 0 then Used DataGrid1.Columns(0).Value 
 
End Sub

2nd, On your Command Button "DELETE" Click Event, you can have

Public Sub CommandDelete_TableRow()
 
Set myOleDbConnection = New ADODB.Connection
set myOleDbConnection=Nothing
 
'Establish Connection.
 OleDbConnection.Open "provider=microsoft.jet.oledb.4.0;user id=admin; Data Source=" & myDataSource""
 
'Delete Record Corresponding to the Selected Data on your datagrid.
myOleDbConnection.Execute "Delete from Table1 WHERE id=" & KeyID & "
 
End sub

Ervin,

For:
myOleDbConnection = New ADODB.Connection
I'm getting this error:

"Value of type 'ADODB.ConnectionClass' cannot be converted to 'System.Data.OleDb.OleDbConnection'."

John

[jrtox]

Hello,

I am amateur with VB database usage.

I've written a little database that keeps track of names, address, phone numbers etc. It displays the data in a DataGrid and stores them in a database. I am able to store and UPDATE new records without problems. Because of my ignorance of VB databases usage, I've tried for hours to get it to DELETE a record. I'm to the point I need to ask for expert help. Here is my code, which is based off of some code I found on the Internet:

I would greatly appreciate any help.

Thanks,
John

Public Sub Delete_TableRow(ByVal RowNumber As Integer, ByVal KeyID As Integer)
 
  myOleDbConnection = New _
  OleDbConnection("provider=microsoft.jet.oledb.4.0;" & _
  "user id=admin; Data Source=" & myDataSource)
 
  Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
  OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand
 
  OleDbUpdateCommand.CommandText = _
  "Delete from Table1 WHERE id=" & KeyID & " ; "
 
  OleDbUpdateCommand.Connection = myOleDbConnection
 
  myOleDbDataAdapter.UpdateCommand = OleDbUpdateCommand
 
  myOleDbDataAdapter.UpdateCommand.ExecuteNonQuery()
 
End Sub

Ok, thats why ive said that i dont force you to use my coding.

Regards
Ervin