CODEWITHSUNDEEP
sqlsql-serverstored-proceduresms-access-2010
VictoriaJay
VictoriaJay

Reputation: 391

Output parameter from Stored Procedure only returning zero

System background: Coding in VBA using MS-Access 2010. Currently working on code behind module and calling stored procedure. The stored procedure is written in SQL and run on the Ms-SQL server 2008 application where the database is stored.

Stored Procedure: The stored procedure's purpose is to:

note: WOID and SampleID column are in tblWoSampleTest and Analyte is in tbltest

Once the stored procedure is called, the testId is saved to a local variable ThisTestID

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @TestId int OUT
AS

   SELECT @TestID = (Select TestID = t1.TestID
   FROM tblWOSampleTest t1
   JOIN tblTest t2
   ON t1.TestID=t2.TestID 
   WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte)

GO

My issue is every time I call the stored procedure, The value ThistestId was previously initialized to is returned even though I know the test Id exists and the stored procedure seemed to run correctly. To verify it exists I took my stored procedure and simply ran:

Select TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID 
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte

and had the correct testId returned (there will only ever be one value). I don't think there is an issue with the data type because the testid is a number not a string. Also here is the way I call it, although I am pretty sure this method is correct.

ThisTestId = 5
Set Conn = New ADODB.connection


Conn.ConnectionString = "connection string"
Conn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = Conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "upGetTestIDForAnalyte"

    cmd.Parameters.Append cmd.CreateParameter("@Analyte", adVarChar, adParamInput, 60, Analyte)
    cmd.Parameters.Append cmd.CreateParameter("@WOID", adVarChar, adParamInput, 60, ThisWOID)
    cmd.Parameters.Append cmd.CreateParameter("@SampleID", adVarChar, adParamInput, 60, 1)
    cmd.Parameters.Append cmd.CreateParameter("@testid", adDouble, adParamOutput, , ThisTestID)

cmd.Execute
Conn.Close
msgbox ThisTestId

In this case a 5 will be printed

Upvotes: 0

Views: 319

Answers (3)

Jesuraja
Jesuraja

Reputation: 3844

You need to get the output parameter after command execution.

  ThisTestId = Convert.ToInt32(cmd.Parameters("@testid").Value);

Upvotes: 0

NicoD
NicoD

Reputation: 1189

Check that your your parameter is marked with OUTPUT keyword in your stored procedure

Try to specify adParamReturnValue for your output parameter

cmd.CreateParameter("@testid", adDouble, adParamOutput, , adParamReturnValue)

Then once you called the store procedure with cmd.Execute you have to read the value

ThisTestId = cmd.Parameters("@testid").Value

Upvotes: 1

gwhenning
gwhenning

Reputation: 138

I'm pretty new to SQL, but where's your return?

http://msdn.microsoft.com/en-us/library/ms188655.aspx

Upvotes: 0

Related Questions