Problem calling Stored Procedure with VB Parms 2

[RikHess]

I have a problem when I try to call a SQL Server Stored Procedure with params from VB (actually VBA if it matters).

The SP look like this:

CREATE PROCEDURE sp_MyStoredProc
AS
DECLARE 
	@StartDat   datetime,
	@EndDate    datetime
SET NOCOUNT ON

SELECT 
...
FROM
...
WHERE 
	(Table.DATE BETWEEN @StartDate and @EndDate)

and the VB code is:

    Dim Conn        As ADODB.Connection
    Dim ConnStr     As String
    Dim Cmmd        As New ADODB.Command
    Dim Pram        As New ADODB.Parameter

    ConnStr = "Provider=MSDASQL.1"
    ConnStr = ConnStr & ";Persist Security Info=False"
    ConnStr = ConnStr & ";User ID=sa"
    ConnStr = ConnStr & ";pwd=<pwd>"
    ConnStr = ConnStr & ";Data Source=<odbc>"
    ConnStr = ConnStr & ";Catalog=<db>;"

    Set Conn = New ADODB.Connection
    Conn.Open ConnStr

    Set Cmmd.ActiveConnection = Conn
    Cmmd.CommandText = "sp_MyStoredProc"
    Cmmd.CommandType = adCmdStoredProc
    
    Set Pram = Cmmd.CreateParameter("@StartDate", adDate, adParamInput, , gd_Start)
    Cmmd.Parameters.Append Pram
    
    Set Pram = Cmmd.CreateParameter("@EndDate", adDate, adParamInput, , gd_End)
    Cmmd.Parameters.Append Pram
    Cmmd.CommandTimeout = 0
    Cmmd.Execute

I get an error on the Execute line with the error:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

This is my first try at passing parameters, so I'm probably doing something very basic wrong. I welcome any help or pointers.

Thanks.

 

[gmmastros]

The way you wrote your stored procedure will cause 2 variables to be created. Instead, you need to make parameters to stored procdure. Like this...

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] sp_MyStoredProc
    @StartDate   [COLOR=#FF00FF]datetime[/color],
    @EndDate    [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]AS[/color]

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]SELECT[/color] 
...
[COLOR=blue]FROM[/color]
...
[COLOR=blue]WHERE[/color] 
    ([COLOR=blue]Table[/color].DATE BETWEEN @StartDate and @EndDate)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RikHess]

Thank you, George!

I had missed that small but important distinction.

After that change I ran into another problem with using adDate as the data type, so I converted everything to varchar and it worked like a charm.

 

[zemp]

I had a problem with the 'adDate' parameter type a well. Changed it to 'adDBTimeStamp' and everything worked and I had the correct data type for proper date manipulation.

zemp