Run-time error 91 object variable or with block variable not set

[arbo80]

Hi all,

I'm getting the following error message when I tried to use stored procedure in VB.6. Can anyone help me?

Run-time error 91 object variable or with block variable not set


Here is my code:

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconn As String
Dim MyID As Integer

Private Sub Form_Load()

strconn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=ma;Password=p2008;Initial Catalog=T_DB;Data Source=NSERVERDEV"
con.Open strconn
conn_info
End Sub

Private Sub conn_info()
MyID = InputBox("Enter a Report ID")
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Update_Job_Status"

cmd.Parameters.Append cmd.CreateParameter("JobID", adNumeric, adParamInput, 5, MyID)
Set rs = cmd.Execute

If Not rs.EOF Then
lblId.Caption = rs.Fields(0)
lblRun.Caption = rs.Fields(20)
End If

Set cmd.ActiveConnection = Nothing

End Sub

Thanks,
A

 

[HarleyQuinn]

On which line do you get the error?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MakeItSo]

Before the line
con.Open strconn

Add one line:

[b]Set con = NEW ADODB.Connection[/b]
con.Open strconn

You must activate (initialise) your object before using it.

;-)

Cheers,
Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[arbo80]

Hi Andy,

I added the line and it worked fine but I'm getting another error message at this line:
If Not rs.EOF Then

The error is:
run-time error '3704' operation is not allowed when the object is closed

Thanks,

A

 

[gmmastros]

Use Query Analyzer to open the stored procedure. Add this line at the top.

SET NOCOUNT ON

Ex:

Alter Procedure Update_Job_Status
  @JobId Int
As 
[!]SET NOCOUNT ON[/!]

--The rest of your procedure here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MakeItSo]

Sorry, totally overlooked that you are using a GLOBAL con variable...

If you want to declare con as Global, do so with the keyword "Public":

[b]Public[/b] con as ADODB.Connection
[b]Public[/b] rs as ADODB.Recordset
[b]Public[/b] cmd as ADODB.Command

Sure you need to make it public at all? If you only need it in the conn_info procedure, you can just as well define the variables locally there. If global, use public.

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[arbo80]

Thanks guys!!