[bhushan_gandhi]

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

[Qrueger]

bhushan_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

[Nash29]

bhushan_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

[Nash29]

bhushan_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

[Nash29]

Provide your own ConnectionString in the above example. Rest will be the same.. Cheers