How to delete record from MySQL using VB

I want to delete a record using VB but when i tried i got error "

Page 1 of 1

4 Replies - 11741 Views - Last Post: 20 November 2009 - 06:19 PM Rate Topic: -----

#1 bhushan_gandhi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 19-November 09

How to delete record from MySQL using VB

Posted 19 November 2009 - 05:39 AM

I want to delete a record using VB but when i tried i got error "operation is not allowed when object is closed" anyone help me....
Do While Not RS1.EOF
.
.
.
.
.
.
ConnectToLocalDatabase
If RS1.State = adStateOpen Then RS1.Close
SQL2 = "Delete from student where cardnumber='" & cardno & "'"
'If RS1.State = adStateOpen Then RS1.Close
RS1.Open SQL2, CON, 1, 2
RS1.MoveNext
Loop

Please help me.
Thanks in Advance

Is This A Good Question/Topic? 0
  • +

Replies To: How to delete record from MySQL using VB

#2 Qrueger  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-November 09

Re: How to delete record from MySQL using VB

Posted 19 November 2009 - 07:34 PM

View Postbhushan_gandhi, on 19 Nov, 2009 - 04:39 AM, said:

I want to delete a record using VB but when i tried i got error "operation is not allowed when object is closed" anyone help me....
Do While Not RS1.EOF
.
.
.
.
.
.
ConnectToLocalDatabase
If RS1.State = adStateOpen Then RS1.Close
SQL2 = "Delete from student where cardnumber='" & cardno & "'"
'If RS1.State = adStateOpen Then RS1.Close
RS1.Open SQL2, CON, 1, 2
RS1.MoveNext
Loop

Please help me.
Thanks in Advance


if you want to delete the record, you don't need the recordset, just running the sql syntax in your connection
just use your connection object.

CON.execute("Delete from student where cardnumber='" & cardno & "'")


hope this can help you... thx
Was This Post Helpful? 0
  • +
  • -

#3 Nash29  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 17-November 09

Re: How to delete record from MySQL using VB

Posted 20 November 2009 - 05:12 PM

View Postbhushan_gandhi, on 19 Nov, 2009 - 04:39 AM, said:

I want to delete a record using VB but when i tried i got error "operation is not allowed when object is closed" anyone help me....
Do While Not RS1.EOF
.
.
.
.
.
.
ConnectToLocalDatabase
If RS1.State = adStateOpen Then RS1.Close
SQL2 = "Delete from student where cardnumber='" & cardno & "'"
'If RS1.State = adStateOpen Then RS1.Close
RS1.Open SQL2, CON, 1, 2
RS1.MoveNext
Loop

Please help me.
Thanks in Advance



Hey there..

"operation is not allowed when object is closed" occurs when you are referring to an object which is closed. It seems as if your connection object is closed. Have you checked out your ConnectToLocalDatabase method?? Is the connection object name same as you've written, i.e. CON?? And if so.. then is your connection Public?? Coz if you're using the connection name (which is declared in a module) in a form, then it will have to be Public as you are calling it from a form and it is outside the scope of the module.

Cheers :D
Was This Post Helpful? 0
  • +
  • -

#4 Nash29  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 17-November 09

Re: How to delete record from MySQL using VB

Posted 20 November 2009 - 05:32 PM

View Postbhushan_gandhi, on 19 Nov, 2009 - 04:39 AM, said:

I want to delete a record using VB but when i tried i got error "operation is not allowed when object is closed" anyone help me....
Do While Not RS1.EOF
.
.
.
.
.
.
ConnectToLocalDatabase
If RS1.State = adStateOpen Then RS1.Close
SQL2 = "Delete from student where cardnumber='" & cardno & "'"
'If RS1.State = adStateOpen Then RS1.Close
RS1.Open SQL2, CON, 1, 2
RS1.MoveNext
Loop

Please help me.
Thanks in Advance



And your codes are written incorrect.. You are trying to delete a record using a recordset which you are already using for iteration??? I think that's the only reason you're getting that error. Most probably, you haven't opened the recordset before loop, so generating that error.
Well, even if you declare and open the recordset, you will still get an error generated, coz you are using the same recordset.

Well, if your using a Delete statement, it's better to stick with what Qrueger said. Use the connection itself. We use recordset to store the temporary records and fields from a table. And still if you wish to delete the record using a recordset, then use select statement and then Delete method of the recordset object.

Example : (If your database is located in the application directory)
Private Sub Command1_Click()
	Dim cnn As ADODB.Connection
	Dim rs As ADODB.Recordset
	Dim sql As String
	
	sql = "select * from student where cardnumber = '" & cardno & "'"
	
	Set cnn = New ADODB.Connection

	cnn.ConnectionString = "Provider=Microsoft.Jet.OleDB.4.0; Data Source = " & App.Path & "\<DatabaseName>.mdb"
	cnn.Open
		
	Set rs = New ADODB.Recordset
	rs.Open sql, cnn, adOpenDynamic, adLockOptimistic, adCmdText
	
	With rs
		.Delete
		.Update
	End With
	
	MsgBox "The Student with card number = " & cardno & " is successfully deleted.."
	rs.Close
	cnn.Close
End Sub

Was This Post Helpful? 0
  • +
  • -

#5 Nash29  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 17-November 09

Re: How to delete record from MySQL using VB

Posted 20 November 2009 - 06:19 PM

Provide your own ConnectionString in the above example. Rest will be the same.. Cheers :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1