[RESOLVED] Problems with a SPROC

[Mark Gambo]

I am trying to create a SPROC and then access it via VB6. I am able to create the SPROC by using the following code:

'Stored Procedure
USE IADATA
DROP PROCEDURE dbo.GetCasesTeam
GO
Create Procedure dbo.GetCasesTeam
    @InvUnit VarChar(100),
    @Team VarChar(100) OutPut,
    @Count int Output
 
As
 
    SELECT @Team = dbo.INCIDENTS.INV_TITLE, @Count = Count(IA_ADM.PRIMARY_CASE_REC.CASENUM)
    FROM dbo.INCIDENTS INNER JOIN IA_ADM.PRIMARY_CASE_REC ON
    dbo.INCIDENTS.INCNUM = IA_ADM.PRIMARY_CASE_REC.PC_INCNUM
    WHERE dbo.INCIDENTS.INV_UNIT in('@InvUnit') AND
    dbo.INCIDENTS.STATUS in('Active','Suspended')
    GROUP BY dbo.INCIDENTS.INV_TITLE
    ORDER BY Count(IA_ADM.PRIMARY_CASE_REC.CASENUM);
 
RETURN @@ERROR

But when I run the following code I get an error on the .Execute Line, "Type name is invalid". WHat am I doing wrong? Also when I try to use adVarChar in the create Parameter property I get an "Parameter Object is improperly define. Incoonsistant or incomplete information was provided" error

'VB Code
Private Sub Form_Load()
 
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
 
Set adoConn = New ADODB.Connection
adoConn.Open connString
 
Set adoCmd = New ADODB.Command
With adoCmd
    Set .ActiveConnection = adoConn
    .CommandText = "GetCasesTeam"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("RetVal", adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("InvUnit", adVariant, adParamInput) '<==Error when I use adVarChar
    .Parameters.Append .CreateParameter("Team", adVariant, adParamOutput, 100)
    .Parameters.Append .CreateParameter("Count", adInteger, adParamOutput)
    .Parameters("InvUnit").Value = "Unit A"
 
    .Execute , , adExecuteNoRecords 'the stored procedure does not return any records '<=== Error "Type name is invalid"
 
    Debug.Print .Parameters("Team").Value, .Parameters("Count").Value
End With

End Sub

Thanks!

[techgnome]

.Parameters.Append .CreateParameter("RetVal", adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("InvUnit", adVarChar, adParamInput, 100) 'You MUST provide the max len of the parameter when using a varchar
    .Parameters.Append .CreateParameter("Team", adVarChar, adParamOutput, 100)
    .Parameters.Append .CreateParameter("Count", adInteger, adParamOutput)
    .Parameters("InvUnit").Value = "Unit A"

One simple change.... ok, two.

-tg

[Mark Gambo]

techgnome,
Thanks alot for your help, it work like a charm! Now the SPROC is returning 4 records how step through each of the records in the ADODB.Command object similiar to rs.MoveNext in the recordset?

Thanks!

[szlamany]

If you want the 4 rows to come back do not do the:

SELECT @Team = dbo.INCIDENTS.INV_TITLE, @Count = Count(IA_ADM.PRIMARY_CASE_REC.CASENUM)

but instead do...

SELECT dbo.INCIDENTS.INV_TITLE, Count(IA_ADM.PRIMARY_CASE_REC.CASENUM)

Then do a :

Set rsObject = .Execute

That will give you the 4 rows as a recordset.

There is no way to return 4 parameter pairs.

[Mark Gambo]

Steve,
Thanks, what type of object is "rsObject"?

Set rsObject = .Execute

ADODB.Command or ADODB.Recordset?

[Mark Gambo]

Steve,
I assumed that the RSObject is a Recordset but it isn't being assigned to the recordset. WHat am I doing wrong?

[szlamany]

Did you change the SPROC to remove the OUTPUT parameters?

And change the select to no longer fill the variables?

To test the SPROC you can EXECUTE it in QUERY ANALYZER...

[Mark Gambo]

Did you change the SPROC to remove the OUTPUT parameters?

Yes

And change the select to no longer fill the variables?

Yes

To test the SPROC you can EXECUTE it in QUERY ANALYZER...

It works fine it returns the appropriate number of records, but my question is how do I access them via code? Is it similiar to a Recordset:

For a = 1 to rs.RecordCount
     MsgBox  rs.Fields(1).Value
Next a

Thanks

[Mark Gambo]

Ok, I figured out my problem, the rs.RecordCount propert is returning -1 so my For Next loop was failing. Now my next question is how do I submit more than one INV_UNIT:

USE IADATA
DROP PROCEDURE dbo.GetCasesTeam
GO
Create Procedure dbo.GetCasesTeam
    @InvUnit VarChar(100)
 
As
 
    SELECT dbo.INCIDENTS.INV_TITLE, Count(IA_ADM.PRIMARY_CASE_REC.CASENUM)
    FROM dbo.INCIDENTS INNER JOIN IA_ADM.PRIMARY_CASE_REC ON
    dbo.INCIDENTS.INCNUM = IA_ADM.PRIMARY_CASE_REC.PC_INCNUM
    WHERE dbo.INCIDENTS.INV_UNIT in(@InvUnit) AND
    dbo.INCIDENTS.STATUS in('Active','Suspended')
    GROUP BY dbo.INCIDENTS.INV_TITLE
    ORDER BY Count(IA_ADM.PRIMARY_CASE_REC.CASENUM);

When I run the SP in the Query Analyzer like this:

Execute GetCasesTeam @InvUnit="Unit A, Unit B, Unit C"

No records are returned but if I do them individually they all return records. I know I am doing something stupid, but what?!?!??

Thanks!

[Mark Gambo]

If I change my SP to the following:

USE IADATA
DROP PROCEDURE dbo.GetCasesTeam
GO
Create Procedure dbo.GetCasesTeam
As
 
    SELECT dbo.INCIDENTS.INV_TITLE, Count(IA_ADM.PRIMARY_CASE_REC.CASENUM)
    FROM dbo.INCIDENTS INNER JOIN IA_ADM.PRIMARY_CASE_REC ON
    dbo.INCIDENTS.INCNUM = IA_ADM.PRIMARY_CASE_REC.PC_INCNUM
    WHERE dbo.INCIDENTS.INV_UNIT in('Unit A', 'Unit B', 'Unit C') AND
    dbo.INCIDENTS.STATUS in('Active','Suspended')
    GROUP BY dbo.INCIDENTS.INV_TITLE
    ORDER BY Count(IA_ADM.PRIMARY_CASE_REC.CASENUM);

and then execute it it returns the required records:

Execute GetCasesTeam

[Mark Gambo]

Thanks to all, it is working now, I just needed to submit my query string correctly. Thaks to all again!

[techgnome]

The -1 form the recordscount is because a server side cursor was being used. -1 is actualy good, it means there are records, we just don't know how many.... three ways around this:
1) use a client side cursor. This will force the recordset to actualy populate itself.
2) "Jiggle the recordset" - a technique I've used inthe past. Do a .MoveLast followed by a .MoveFirst will get the records. After that .RecordCount will be correct
3) Use a while loop instead of a for loop:

Do while not rstMine.EOF
 'Do looping stuff
  rstMine.MoveNext
Loop

-tg

[szlamany]

See this link for a trick on how to pass "," separated values in a SPROC parameter and then WHERE-clause them...

http://www.vbforums.com/showpost.php...97&postcount=4

[Mark Gambo]

techgnome and szlamany thank you very much!!!!

[techgnome]

The bill is in the mail....

-tg

[Mark Gambo]

Sorry. . .