correct way to pass as string to CreateParameter

If I call a stored procedure by

DECLARE @return_value int EXEC @return_value = [dbo].[GetValueProc] @startDate = '1/1/2010', @endDate = '12/31/2010', @groupNo = N'02' SELECT 'Return Value' = @return_value

From sql command window it works great, but using VB6 it returns an empty record set.

Dim cmd As New ADODB.Command Set cmd.ActiveConnection = cn cmd.CommandText = "GetValueProc" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append cmd.CreateParameter("@startDate", adDate, adParamInput, 0, startDate) cmd.Parameters.Append cmd.CreateParameter("@endDate", adDate, adParamInput, 0, endDate) cmd.Parameters.Append cmd.CreateParameter("@groupNo", adVarChar, adParamInput, 3, "02") Set RstRecordSet = New ADODB.Recordset With RstRecordSet .CursorType = adOpenStatic .CursorLocation = adUseClient .LockType = adLockOptimistic .Open cmd End With

I get an empty Record set from vb. However, from sql command window it works as expected.

Note: If I comment out the line with the parameter in question it also works as expected.

the stored procedure looks like:

ALTER PROCEDURE [dbo].[GetValueProc] @startDate as date, @endDate as date, @proposalNo nvarchar(30) = null, @groupNo nvarchar(3) = null select... where ... and j.[Global Dimension 1 Code] = COALESCE(@groupNo,[Global Dimension 1 Code])

[Global Dimension 1 Code] is a varchar(20)

I have also tried

cmd.Parameters.Append cmd.CreateParameter("@groupNo", adVarChar, adParamInput, 4, "'02'")

doesn't help.

Any help about the correct way to pass the parameter would be great!

Thanks

-------------Problems Reply------------

I note you are omitting the @proposalNo parameter in the call. Therefore, I think you need to flag that you are using named parameters, otherwise they will be assumed to be in ordinal position order i.e. @groupNo in the call is the third in the Parameters collection and therefore will be used for the third param in the stored proc which is actually @proposalNo.

Try inserting this line

cmd.NamedParameters = True

immediately before the cmd.Parameters.Append... lines. Note only the later versions of ADO (e.g. 2.8) support named parameters.

Here's a repro:

First, change your stored proc to simply select the parameters e.g.

ALTER PROCEDURE [dbo].[GetValueProc]
@startDate as date,
@endDate as date,
@proposalNo nvarchar(30) = null,
@groupNo nvarchar(3) = null
AS
BEGIN
SELECT @startDate, @endDate, @proposalNo, @groupNo;
END;

Second, try this VBA (need to edit it for your connection string) which corrects your typos:

Sub hngorhio()
Dim cmd As New ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = "Your connection string here"

cmd.CommandText = "GetValueProc"
cmd.CommandType = adCmdStoredProc
cmd.NamedParameters = True ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< '
cmd.Parameters.Append _
cmd.CreateParameter("@startDate", adDate, adParamInput, 0, Date)
cmd.Parameters.Append _
cmd.CreateParameter("@endDate", adDate, adParamInput, 0, Date + 1)
cmd.Parameters.Append _
cmd.CreateParameter("@groupNo", adVarChar, adParamInput, 3, "02")

Dim RstRecordSet As New ADODB.Recordset
Set RstRecordSet = New ADODB.Recordset
With RstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
If Not .EOF Then
MsgBox .GetString(, , , , "<NULL>")
End If
End With
End Sub

If you comment and uncomment the NamedParameters lines you'll see the 02 and <NULL> switch places in the messagebox, proving that the SQL engine is uses ordinal position of parameters when NamedParameters is not enabled.

UPDATED:

Perhaps this stored proc gives a more explicit result with the same VBA code above:

ALTER PROCEDURE [dbo].[GetValueProc]
@startDate as date,
@endDate as date,
@proposalNo nvarchar(30) = null,
@groupNo nvarchar(3) = null
AS
BEGIN
SELECT '@proposalNo = ' + COALESCE(@proposalNo, '<NULL>') + CHAR(10)
+ '@groupNo = ' + COALESCE(@groupNo, '<NULL>');
END;

Category:sql Views:0 Time:2011-01-27
Tags: sql vb6

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.110 (s). 11 q(s)