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



[CODE=vb]Public Sub Delete_TableRow (ByVal RowNumber As Integer, ByVal KeyID As Integer)

myOleDbConnecti on = New _
OleDbConnection ("provider=micr osoft.jet.oledb .4.0;" & _
"user id=admin; Data Source=" & myDataSource)

Dim OleDbUpdateComm and As System.Data.Ole Db.OleDbCommand
OleDbUpdateComm and = New System.Data.Ole Db.OleDbCommand

OleDbUpdateComm and.CommandText = _
"Delete from Table1 WHERE id=" & KeyID & " ; "

OleDbUpdateComm and.Connection = myOleDbConnecti on

myOleDbDataAdap ter.UpdateComma nd = OleDbUpdateComm and

myOleDbDataAdap ter.UpdateComma nd.ExecuteNonQu ery()

End Sub[/CODE]

[John7000]

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

myOleDbConnecti on.Open()

so now the code looks like this:

[CODE=vb] Public Sub Delete_TableRow (ByVal RowNumber As Integer, ByVal KeyID As Integer)

myOleDbConnecti on = New _
OleDbConnection ("provider=micr osoft.jet.oledb .4.0;" & _
"user id=admin; Data Source=" & myDataSource)

myOleDbConnecti on.Open()
Dim OleDbUpdateComm and As System.Data.Ole Db.OleDbCommand
OleDbUpdateComm and = New System.Data.Ole Db.OleDbCommand

OleDbUpdateComm and.CommandText = _
"Delete from Table1 WHERE id=" & KeyID & " ; "
OleDbUpdateComm and.Connection = myOleDbConnecti on

myOleDbDataAdap ter.UpdateComma nd = OleDbUpdateComm and

myOleDbDataAdap ter.UpdateComma nd.ExecuteNonQu ery()

End Sub
[/CODE]
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

[CODE=vb]Private Sub DataGrid1_RowCo lChange(LastRow As Variant, ByVal LastCol As Integer)

KeyID = DataGrid1.Colum ns(1).Value' If your Unique Key Is found in Column 0 then Used DataGrid1.Colum ns(0).Value

End Sub[/CODE]


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

[CODE=vb]Public Sub CommandDelete_T ableRow()

Set myOleDbConnecti on = New ADODB.Connectio n
set myOleDbConnecti on=Nothing

'Establish Connection.
OleDbConnection .Open "provider=micro soft.jet.oledb. 4.0;user id=admin; Data Source=" & myDataSource""

'Delete Record Corresponding to the Selected Data on your datagrid.
myOleDbConnecti on.Execute "Delete from Table1 WHERE id=" & KeyID & "

End sub[/CODE]


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]

...
[CODE=vb]Private Sub DataGrid1_RowCo lChange(LastRow As Variant, ByVal LastCol As Integer)

KeyID = DataGrid1.Colum ns(1).Value' If your Unique Key Is found in Column 0 then Used DataGrid1.Colum ns(0).Value

End Sub[/CODE]


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

[CODE=vb]Public Sub CommandDelete_T ableRow()

Set myOleDbConnecti on = New ADODB.Connectio n
set myOleDbConnecti on=Nothing

'Establish Connection.
OleDbConnection .Open "provider=micro soft.jet.oledb. 4.0;user id=admin; Data Source=" & myDataSource""

'Delete Record Corresponding to the Selected Data on your datagrid.
myOleDbConnecti on.Execute "Delete from Table1 WHERE id=" & KeyID & "

End sub[/CODE]

Ervin,

For:
myOleDbConnecti on = New ADODB.Connectio n
I'm getting this error:

"Value of type 'ADODB.Connecti onClass' cannot be converted to 'System.Data.Ol eDb.OleDbConnec tion'."

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



[CODE=vb]Public Sub Delete_TableRow (ByVal RowNumber As Integer, ByVal KeyID As Integer)

myOleDbConnecti on = New _
OleDbConnection ("provider=micr osoft.jet.oledb .4.0;" & _
"user id=admin; Data Source=" & myDataSource)

Dim OleDbUpdateComm and As System.Data.Ole Db.OleDbCommand
OleDbUpdateComm and = New System.Data.Ole Db.OleDbCommand

OleDbUpdateComm and.CommandText = _
"Delete from Table1 WHERE id=" & KeyID & " ; "

OleDbUpdateComm and.Connection = myOleDbConnecti on

myOleDbDataAdap ter.UpdateComma nd = OleDbUpdateComm and

myOleDbDataAdap ter.UpdateComma nd.ExecuteNonQu ery()

End Sub[/CODE]

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

Regards
Ervin