Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Other Development Tools
 Errors Calling a Paramertized Query

Author  Topic 

iwrk4dedpr
Starting Member

4 Posts

Posted - 2011-11-07 : 12:32:23
I have a stored procedure ( SQL Server 2005 )


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetLotAttributes]
@AttrNum int = NULL,
@LotID varchar(11) = NULL
AS
SELECT *
FROM Fab5IE_LastTables.dbo.tblLastTables
WHERE RecordIsOpen = 1 AND (LastAttr = ISNULL(@AttrNum,LastAttr) AND LotNum = ISNULL(@LotID,LotNum))
ORDER BY LastAttr


I'm writting code in MSExcel to extract information
using the following code!

The function to run the stored procedure is
Function Fab5IE_GetDataStoredProc(strItem As String, strParamString As String, tgtForData As Variant) As Boolean

' Local Variables
Dim adoConn As New ADODB.Connection
Dim adoRst As New ADODB.Recordset
Dim adoPrm As ADODB.Parameter
Dim adoCmd As command
Dim strDBConn As String

Fab5IE_GetDataStoredProc = False
On Error GoTo L___ExitEarly

' Open a connection
strDBConn = strFab5IE
adoConn.Open strDBConn

' Set query
Set adoCmd = New ADODB.command
With adoCmd
.ActiveConnection = adoConn
.CommandText = strItem
.CommandType = adCmdStoredProc
End With
Set adoPrm = adoCmd.CreateParameter("@AttrNum", adInteger, adParamInput)
adoCmd.Parameters.Append adoPrm
Set adoPrm = adoCmd.CreateParameter("@LotNum", adVarChar, adParamInput, 11)
adoCmd.Parameters.Append adoPrm
adoCmd.Parameters("@AttrNum") = CInt(Split(strParamString, ",")(0))
adoCmd.Parameters("@LotNum") = Split(strParamString, ",")(1)

' Loop the incomming device list and ret template info
Set adoRst = adoCmd.Execute

' Disconnect the Recordset
Set adoRst.ActiveConnection = Nothing

' Return the Recordset
Set tgtForData = adoRst

' Clean up...
adoConn.Close
' adoRst.Close
Set adoConn = Nothing
Set adoRst = Nothing

Exit Function
L___ExitEarly:

Fab5IE_GetDataStoredProc = False

End Function


The call to the function is this
    Call Fab5IE_GetDataStoredProc("usp_GetLotAttributes", "102,1J5199", adoRst)


I keep getting a recordset that has a record count of 1...


1. I need to know how to use the execute the stored procedure with null values
2. Can on a given stored procedure can I get a parameter list returned?

iwrk4dedpr
Starting Member

4 Posts

Posted - 2011-11-07 : 12:33:31
oooops typo...

I'm getting a recordset of -1 in the recordcount property.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 12:35:18
1. for that just call procedure without passing any values
2.you need parameter list back to application you mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

iwrk4dedpr
Starting Member

4 Posts

Posted - 2011-11-07 : 13:30:43
Well....

First off I'm trying to pass in 2 values cause that will be the greatest used method. With the two values I should have a recordset of 1 item but it keeps sending back -1 or and empty recordset.

However, I may at any given time want to leave 1 or both items NULL so that I get a complete listing.

But nothing is working? What do I need to check?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 23:54:56
quote:
Originally posted by iwrk4dedpr

Well....

First off I'm trying to pass in 2 values cause that will be the greatest used method. With the two values I should have a recordset of 1 item but it keeps sending back -1 or and empty recordset.

However, I may at any given time want to leave 1 or both items NULL so that I get a complete listing.

But nothing is working? What do I need to check?


first check if its correctly splitting values to parameter values from your passed string 102,1J5199

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -