[RESOLVED] SPROC and VB6 Question

[Mark Gambo]

I have the following SPROC:

USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
    DROP PROCEDURE TestSP
 
GO
CREATE PROCEDURE TestSP
    /*Declare Variables*/
    @ListStr varchar(100) /*Hold Delimited String*/
AS
 
DECLARE @ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as varchar)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
 
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

and I am using the following VB code to access the SPROC:

Private Sub Command1_Click()
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
 
Set adoConn = New ADODB.Connection
adoConn.Open connString
 
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
 
With adoCmd
    Set .ActiveConnection = adoConn
    .CommandText = "TestSP"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
    .Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
    
    Set adoRS = .Execute
    
    Do While Not adoRS.EOF
        Debug.Print adoRS.Fields(0).Value
    Loop
    
End With
 
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
 
End Sub

I get an operation not allowed when object is closed error on the:

Do While Not adoRS.EOF

line. Any ideas?

Thanks!

[Mark Gambo]

BTW I am using MSSQL 2005 Database.

[szlamany]

I believe you need a SET NOCOUNT ON at the top of that SPROC.

The "rows affected" messages get in the way of the returned recordset - and those INSERT's are producing that from the SPROC.

(sorry - I think I should have mentioned that in the other thread )

[Mark Gambo]

Thanks I'll give it a try.

[Mark Gambo]

Steve,
I added the "Set NoCount On" right after the "AS" Keyword in my SPROC and it works fine now. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?

Here is my complete code:

USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
    DROP PROCEDURE TestSP
 
GO
CREATE PROCEDURE TestSP
    /*Declare Variables*/
    @ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE @ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as varchar)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
 
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

and my VB6 Code:

Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
 
Set adoConn = New ADODB.Connection
adoConn.Open connString
 
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
 
With adoCmd
    Set .ActiveConnection = adoConn
    .CommandText = "TestSP"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
    .Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
    
    Set adoRS = .Execute
    
    Do While Not adoRS.EOF
        Debug.Print adoRS.Fields(0).Value
        adoRS.MoveNext
    Loop
    
End With
 
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
 
End Sub

[szlamany]

I have never seen an error like that related to SQL.

Whenever we have issues calling a SPROC we immediately go into QUERY ANALYZER and try to duplicate the error within QA.

In this case I would put two EXEC TESTSP lines in QA - with the two different list values being pass in.

If the problem is with the SPROC then it will appear in QA as well.

We have one central place in our VB code that sets parameters for calling all our SPROCS. We have DEBUG.PRINT statements in those spots so that we can see the actual values going into the parameters. We actually print them so we can COPY/PASTE them into QA - like:

SET @LISTSTR='ABC, XYZ, SSS, TTT, VVV'

[Mark Gambo]

Back to the drawing board, Thanks Steve!

[Mark Gambo]

I had an interseting problem when I run the code on one machine which has MS Visual Studio 6.0 Pro the code fails on the second run but on another machine which has MS Visual Basic 6.0 Professional (Non-Studio) it runs fine. Hmmm, I wonder if it has anything to do with the Automation Manager?

the investigation continues . . . . .

[szlamany]

Do you have an ON ERROR GOTO XYZ error trap in that routine?

If not put one in - and in the ERROR TRAP put a MSGBOX to look at the ERR.DESCRIPTION and stuff like that.

Sometimes an error occurs and if not properly trapped will cause the app to run through functions and events in an unexpected fashion...

[Mark Gambo]

Yep, it seems the Automation Manager is the cause of the problem. How do I go about disabling it?

[szlamany]

This is the first I have heard of an automation manager...

I google for it and it appears to be a service that you can run on a server ??

I found this KB article...

http://support.microsoft.com/default...;EN-US;q193238

[Mark Gambo]

Steve,

Thanks, again!!

[Mark Gambo]

Steve,
One more question, if I wanted to return all records or use a wildcard can I still use my SPROC or should I write another one for these types of queries?

[szlamany]

We believe in using one SPROC unless there is a really good reason to have a second or third.

Passing in two parameters should handle it.

Change the declaration to something like this for example:

CREATE PROCEDURE TestSP
	/*Declare Variables*/
	@ListStr varchar(100) /*Hold Delimited String*/
	@LookupValue varchar(10) /*Hold Single Lookup Value*/

Then an IF statement to run your existing code only if @ListStr is not blank...

If IsNull(@ListStr,'')<>''
Begin
.
.
. all your existing code
.
.
End
Else
Begin -- new code goes here
Select InvUnit From dbo.Incidents
   Where InvUnit like @LookupValue+'%' or Isnull(@LookupValue,'')=''
End

You might not actually need the "or" statement - the Like with a blank @LookupValue will probably work fine. You can also do three different queries if you are so inclined.

[Mark Gambo]

You are a magician!! Thanks. One last question (I Promise) I am still now able to get the RecordCount, I have set the CursorLocation as Client and when I try to "jiggle" the recordset like technome stated in another post I get an Run-time error '-2147217884(80040e24)'; Rowset does not support fetching backward:

Set adoRS = New ADODB.Recordset
    With adoRS
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
     End With
 
 
    Set adoRS = .Execute
    Debug.Print adoRS.RecordCount ' <== -1
    adoRS.MoveLast  '<=== Error Rowset does not support fetching backward
    Debug.Print adoRS.RecordCount ' <== -1
    adoRS.MoveFirst
    Debug.Print adoRS.RecordCount ' <== -1

It appears that the cursor is a Forward Only type, is this a SQL DB Permission problem or an ADO Problem?

[szlamany]

Normally I live without knowing the recordcount - why do you need to know it?

The fastest cursor type is a forward-only, read-only (I believe - I could be wrong - it's the only cursor I ever use) - so that jiggle trick won't work.

[Mark Gambo]

I always thought that a For - Next Loop was faster that a Do While Loop:

For a = 1 to adoRS.RecordCount
       'Faster 
Next a
 
Do While NOT adoRS.EOF
     'Slower
Loop

Because I have been using the DoWhile Loop but I am more familiar with the For Next Style.

Thanks again!

[szlamany]

The speed difference of a loop is so meaningless compared to forcing the whole recordset to come from the client and be memory resident (or paged to local disk) just so you can MOVELAST and MOVEFIRST to get the recordcount.

That MOVELAST operation could be noticeable to the user.

We always choose the DO WHILE/rs.EOF technique.

[Mark Gambo]

Ok, so DO While it is, Thanks again for all of your help!!!

[szlamany]

No problem - glad to be of help!