May 12, 2006 at 6:57 am
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 wrorte 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
May 12, 2006 at 7:51 am
not sure if this is right - but you might have to use
dim param1 as NEW adodb.parameter
although after a beer at lunch i'm not sure
MVDBA
May 12, 2006 at 8:11 am
Thanks for the suggestion Mike. However I got the same results. Nothing. No error...no data.
Beer sounds like a good idea however it is only 10am where I am.
Chris
May 12, 2006 at 8:39 am
just a though. you're using labels to show this data - not text boxes?
but i'm guessing since you're getting #name thats not the problem.
looking at it though - you're using cmd.execute?
where are the results going? usualkly you'd use set rst=cmd.execute and then use the recordset object?
nornmally when i'm using stored procs i use pass through queries.
use this code in the form open and set the datasource to be the query "myquery"
dim qdef as querydef
dim db as database
set db=currentdb()
set qd=db.querydefs('myquery')
qd.sql="exec myproc '"+cstr(me.txtparameter1)+"'"
qd.close
MVDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply