ADO Trouble...Connection Closed?

[bluenoser337]

I am opening a connection (CN.Open...) in VB on "MDIForm_Load". When I try to "CN.Execute..." my insert, I get error "3704" telling me that the "Operation is not allowed when the object is closed"...but it isn't closed? A timer is activating the writing to the DB and if I put the "open" statement inside the timer each time, then it works (but is slow). I should only have to open this once...right? What am I missing? Thanks!!

 

[Antzz]

Can you post the relevant code?

 

[tlbroadbent]

Are you executing a query generated in VB or a SQL stored procedure? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bluenoser337]

Hi:

It's an attempt to write a record to the SQLServer DB from VB...

-- VB Code

Dim CN As New ADODB.Connection
Private Sub Timer1_Timer()
Dim strSQL As String
strSQL = " Insert Into Tea (Tea) Values ('" & txtName.Text & "')"
CN.Execute strSQL
End Sub

Private Sub Form_Load()
CN.Open "Provider=SQEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog= Coffee ;Data Source = XYZ"
End Sub

When CN.Execute strSQL happens, I get the error. If I open every time just before the strSQL, it works but I know this isn't right.
Thanks!

 

[cmmrfrds]

Make sure the definition is in scope while in the Subs. It appears to be out of scope when referencing in the Subs. Define the variable at a higher level and as public - maybe a Module or Form.

Public CN As New ADODB.Connection

 

[Durkin]

Maybe you should start your timer after you have opened the connection. Durkin

 

[dmlatham]

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q235340&

If you are using a stored procedure and it is returning results from a temporary table (a table prepositioned with a #) then you need to have the code "set nocount on" inside of your stored procedure.


Deb