468,765 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Record Delete - ExecuteNonQuery requires an open and available Connection

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



Expand|Select|Wrap|Line Numbers
  1. Public Sub Delete_TableRow(ByVal RowNumber As Integer, ByVal KeyID As Integer)
  2.  
  3.   myOleDbConnection = New _
  4.   OleDbConnection("provider=microsoft.jet.oledb.4.0;" & _
  5.   "user id=admin; Data Source=" & myDataSource)
  6.  
  7.   Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
  8.   OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand
  9.  
  10.   OleDbUpdateCommand.CommandText = _
  11.   "Delete from Table1 WHERE id=" & KeyID & " ; "
  12.  
  13.   OleDbUpdateCommand.Connection = myOleDbConnection
  14.  
  15.   myOleDbDataAdapter.UpdateCommand = OleDbUpdateCommand
  16.  
  17.   myOleDbDataAdapter.UpdateCommand.ExecuteNonQuery()
  18.  
  19. End Sub
Oct 6 '07 #1
5 4417
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:

Expand|Select|Wrap|Line Numbers
  1.      Public Sub Delete_TableRow(ByVal RowNumber As Integer, ByVal KeyID As Integer)
  2.  
  3.         myOleDbConnection = New _
  4.                OleDbConnection("provider=microsoft.jet.oledb.4.0;" & _
  5.                 "user id=admin; Data Source=" & myDataSource)
  6.  
  7.         myOleDbConnection.Open() 
  8.         Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
  9.         OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand
  10.  
  11.         OleDbUpdateCommand.CommandText = _
  12.                 "Delete from Table1 WHERE id=" & KeyID & " ;  "
  13.         OleDbUpdateCommand.Connection = myOleDbConnection
  14.  
  15.         myOleDbDataAdapter.UpdateCommand = OleDbUpdateCommand
  16.  
  17.         myOleDbDataAdapter.UpdateCommand.ExecuteNonQuery()
  18.  
  19.     End Sub
  20.  
Hope this helps someone.

John
Oct 6 '07 #2
jrtox
89
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  2.  
  3. KeyID = DataGrid1.Columns(1).Value' If your Unique Key Is found in Column 0 then Used DataGrid1.Columns(0).Value 
  4.  
  5. End Sub

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub CommandDelete_TableRow()
  2.  
  3. Set myOleDbConnection = New ADODB.Connection
  4. set myOleDbConnection=Nothing
  5.  
  6. 'Establish Connection.
  7.  OleDbConnection.Open "provider=microsoft.jet.oledb.4.0;user id=admin; Data Source=" & myDataSource""
  8.  
  9. 'Delete Record Corresponding to the Selected Data on your datagrid.
  10. myOleDbConnection.Execute "Delete from Table1 WHERE id=" & KeyID & "
  11.  
  12. End sub

Regards
Ervin

P.S This is the way I delete records, I don't force you to use this.
Oct 7 '07 #3
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
Oct 7 '07 #4
...
Expand|Select|Wrap|Line Numbers
  1. Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  2.  
  3. KeyID = DataGrid1.Columns(1).Value' If your Unique Key Is found in Column 0 then Used DataGrid1.Columns(0).Value 
  4.  
  5. End Sub

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub CommandDelete_TableRow()
  2.  
  3. Set myOleDbConnection = New ADODB.Connection
  4. set myOleDbConnection=Nothing
  5.  
  6. 'Establish Connection.
  7.  OleDbConnection.Open "provider=microsoft.jet.oledb.4.0;user id=admin; Data Source=" & myDataSource""
  8.  
  9. 'Delete Record Corresponding to the Selected Data on your datagrid.
  10. myOleDbConnection.Execute "Delete from Table1 WHERE id=" & KeyID & "
  11.  
  12. 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
Oct 7 '07 #5
jrtox
89
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



Expand|Select|Wrap|Line Numbers
  1. Public Sub Delete_TableRow(ByVal RowNumber As Integer, ByVal KeyID As Integer)
  2.  
  3.   myOleDbConnection = New _
  4.   OleDbConnection("provider=microsoft.jet.oledb.4.0;" & _
  5.   "user id=admin; Data Source=" & myDataSource)
  6.  
  7.   Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
  8.   OleDbUpdateCommand = New System.Data.OleDb.OleDbCommand
  9.  
  10.   OleDbUpdateCommand.CommandText = _
  11.   "Delete from Table1 WHERE id=" & KeyID & " ; "
  12.  
  13.   OleDbUpdateCommand.Connection = myOleDbConnection
  14.  
  15.   myOleDbDataAdapter.UpdateCommand = OleDbUpdateCommand
  16.  
  17.   myOleDbDataAdapter.UpdateCommand.ExecuteNonQuery()
  18.  
  19. End Sub
Ok, thats why ive said that i dont force you to use my coding.

Regards
Ervin
Oct 7 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Tinius | last post: by
20 posts views Thread by Bryan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.