Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

arbo80

Programmer
Jan 5, 2006
53
US
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
 
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.
 
Before the line
con.Open strconn

Add one line:
Code:
[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
 
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
 
Use Query Analyzer to open the stored procedure. Add this line at the top.

SET NOCOUNT ON

Ex:

Code:
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
 
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":
Code:
[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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top