asked on

How to avoid a runtime error 91, object variable or with block variable not set ?

I am developing an Access MDB application. In the application I call a Sybase Stored Procedure and return the result set to a recordset. I can only call this Stored Procedure. I do not own this Stored Procedure.

In a prior application I performed a Sybase Stored Procedure with the following code:
--------------------------------------------------------------------------------------------------------------------
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DSN=PRPT1;" & _
"Uid=ina2;" & _
"Pwd=rkName"
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = oConn
.CommandType = adCmdText
.CommandText = "exec getTATGATCDSAByAccount @ApplnNm='test', @accountNumber='019228238'"
.CommandText = .CommandText & ",@accountSystem=1435,@selectFields='accountNumber, accountStatus"
.CommandText = .CommandText & ", fullName1, address1, city, stateCode, postalCode, taxID, accountClassification"
.CommandText = .CommandText & ", fullName2, countryCode, countryOfResidence, countryOfCitizenship, accountCategory"
.CommandText = .CommandText & ", cashIndicator, marginIndicator, codIndicator'"
Set recNameAdress = .Execute
End With
-------------------------------------------------------------------------------------------------------------------------
When I set the breakpoint to the line"
"Set recNameAdress = .Execute" and then execute this line, I can issue the command
?recNameAddress.GetString and I will get back a record.
-------------------------------------------------------------------------------------------------------------------

However, when I use the following code to call the other Stored Procedure,
I set the breakpoint to the line:
"Set recNameAdress = .Execute"
and then when I execute this line, I then display the recordset with the following command:
?recNameAddress.GetString and I will get the following error:

Runtime error 91
Object Variable or With Block Variable not set
---------------------------------------------------------------------

Set oConn = CreateObject("ADODB.Connection")

oConn.Open "DSN=NYQ_DIG;" & _
"Uid=PClie;" & _
"Pwd=p4tX"

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = oConn
.CommandType = adCmdText
.CommandText = "exec sp3D_GetListOfDocuments 1" '(the parameter 1 signifies that I want 1 record)
Set recNameAddress = .Execute
End With

ASKER CERTIFIED SOLUTION

Badotz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Badotz

Ah, the strong, silent type, eh?

So, what, exactly, was the problem? Was it simply a syntax error?

It is always nice to hear from the Asker when a solution is accepted. In this case, we assume it was syntax, but one never knows.

Oh, and no worries - glad to help.

zimmer9

ASKER

Believe it or not, I was informed to remove the Exec statement when calling the stored procedure and that works.

For ex:

.CommandText = "sp3D_GetListOfDocuments 1"

Badotz

If what I proposed was not the solution, then why did you award me points and a grade?

zimmer9

ASKER

You were the only one who responded to my inquiry and I felt it was my prerogative to award you points. You did help.

Badotz

OK - just as long as you know it is not mandatory to award points.

If you decide there is no solution, just delete the question. Participants get four days to comment before the question is *really* closed.

zimmer9

ASKER

I also modified the code to use:

.CommandType = adCmdStoredProc

thanks for your assistance and for pointing out some of the guidelines :)

Badotz

No worries.