SP Seems to work but no data in recordset retrieved

[McNeillyC]

I am using SQL 2000 (edition installed with SBS2003 Premium) and converted an Access Database application to an .adp file. We are using Access 2003 to develop the .adp.

My issue is working with stored procedures. I created a stored procedure that works fine in Query Analyzer. For reference here it is:

CREATE PROCEDURE dbo.usp_ShowAllBetweenDates (@dtStartDate As DateTime, @dtEndDate As DateTime)
As
SELECT dbo.tblComments.chrSubjectType, dbo.tblComments.chrSubject, dbo.tblComments.chrUser, dbo.tblComments.dtmDateEnt,
dbo.tblComments.intHeadingID AS HeadingID, dbo.tblComments.intDoorID, dbo.tblDoors.chrDoorNr AS chrDoorN, dbo.tblHeadings.chrHeadingNr,
dbo.tblHeadings.intProjID, dbo.tblHeadings.chrH_Flag, dbo.tblDoors.chrD_Flag, dbo.tblComments.CommentsID, dbo.tblHeadings.chrH_Flag2,
dbo.tblDoors.chrD_Flag2
FROM dbo.tblComments INNER JOIN
dbo.tblHeadings ON dbo.tblComments.intHeadingID = dbo.tblHeadings.HeadingID INNER JOIN
dbo.tblDoors ON dbo.tblComments.intDoorID = dbo.tblDoors.DoorID AND dbo.tblHeadings.HeadingID = dbo.tblDoors.intHeadingID
WHERE (dbo.tblHeadings.chrHeadingNr <> N'Global') AND (dbo.tblHeadings.chrHeadingNr <> N'Schedule') AND (dbo.tblComments.dtmDateEnt >= @dtStartDate) AND
(dbo.tblComments.dtmDateEnt <= @dtEndDate)
GO

I wrote the following code in the .adp project which runs when the form loads that the data is supposed to populate into the form (that is what I would like it to do):

Private Sub Form_Load()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As ADODB.Parameter, parama2 As ADODB.Parameter

Dim dtStartDateH As Date
Dim dtEndDateH As Date

dtStartDateH = Forms![frmMain]![txtSortStartDate]
dtEndDateH = Forms![frmMain]![txtSortEndDate]

Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn

cmd.CommandText = "dbo.usp_ShowAllBetweenDates"
cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter("@dtStartDate", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = dtStartDateH
Set param2 = cmd.CreateParameter("@dtEndDate", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = dtEndDateH

cmd.Execute

It seems to run fine as I get no errors. However when the form loads I get the typical '#Name?' showing up in all my form boxes. The form label names all match the names in the stored procedure. If I use the Stored Procedure as the record source and manually enter the dates the form populates perfectly. It is just when I am trying to pass data through the VB code that nothing happens. I checked the security and the Stored Procedure has the correct Execute permisions.

Can anyone tell me what I doing wrong? Any help would be greatly appreciated.

Thanks,

Chris

 

[belovedcej]

I think maybe you should actually declare a recordset and then make that recordset the source of the form. I don't see anything in your code that tells the form to use the SP as the recordset.

I have never actually used it as a recordset for an entire form, but here's my thought, adapting something I've done before:

Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

With cmd
        .ActiveConnection = CurrentProject.BaseConnectionString
        .CommandType = adCmdStoredProc
        .CommandText = "procedure name"
        .Parameters.Refresh
        .Parameters(1) = strPermissions
        

Set rst = .Execute

Set Form.FormName.recordset = rst

Set rst = nothing
End With

 

[McNeillyC]

Thanks I will try this. One question is how do I pass on my parameters? Do you replace 'strPermissions' with the value or do I state something like '@dtStartDate = ...'?

I tried implementing the code and get the following error:

rst<object variable or With Block variable not set>

I tried changing the following with the same results:

Set rst = .Execute

to

Set rst = cmd.Execute

Any ideas?

Thanks,

Chris

 

[cmmrfrds]

Make you stored procedure the record source on the Form and put the parameters on the parameter tab.
@dtStartDate=yourstartdate,@dtEndDate=yourenddate

Make sure the variables that hold your dates are in scope when the Form opens. I usually make these variables public and retrieve through a function.

For example.
@dtStartDate=ReturnStartDate(),@dtEndDate=ReturnEndDate()

 

[belovedcej]

McNeilly, yes, strPermissions was a parameter for my SP.

they go in the order that they are declared in the stored procedure and are indexed numberically. So if you have five parameters, you would put:

.Parameters(1) = myinfo
.Parameters(2) = myinfo
.Parameters(3) = myInfo

etc.

If my parameter info is variable, I declare and initialize a variable and put the variable name in the "myinfo" spot. Otherwise, if it's constant, I just hardcode it in. Might be "bad style" - but it works.

 

[McNeillyC]

Thanks. I got it all to work.

Chris