• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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
0
zimmer9
Asked:
zimmer9
  • 5
  • 3
1 Solution
 
BadotzCommented:
Possibly because there is no data returned from the query?

First, you use this format:

.CommandText = "exec getTATGATCDSAByAccount @ApplnNm='test', @accountNumber='019228238'"

Next, you try this format:

.CommandText = "exec sp3D_GetListOfDocuments 1"

Possibly your syntax is wrong?
0
 
BadotzCommented:
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.
0
 
zimmer9Author Commented:
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"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BadotzCommented:
If what I proposed was not the solution, then why did you award me points and a grade?
0
 
zimmer9Author Commented:
You were the only one who responded to my inquiry and I felt it was my prerogative to award you points. You did help.
0
 
BadotzCommented:
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.
0
 
zimmer9Author Commented:
I also modified the code to use:

.CommandType = adCmdStoredProc

thanks for your assistance and for pointing out some of the guidelines   :)
0
 
BadotzCommented:
No worries.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now