A Problem with Parameters (1 Viewer)

naomidodd

New member
Local time
Today, 01:48
Joined
Dec 7, 2005
Messages
6
I want to create a parameter in SQL that will look at my unbound form in access and pull those dates. i am stumped. Can you please help me?

Here is my SQL stored procedure below:

*****FromDate=[Forms]![dlgrptb]![BegDate]
*****ToDate=[Forms]![dlgrptb]![EndDate]



CREATE PROCEDURE dbo.Excel_NB_QRY_VerifyDateInfo2
@FromDate dateTime,
@ToDate datetime
AS


SELECT ISNULL(CONVERT(VARCHAR,f.frstnm + ' ' + f.lstnm),'NONE') as 'TABLES 1',
ISNULL(CONVERT(VARCHAR,g.frstnm + ' ' + g.lstnm),'NONE') as 'TABLES 2',
ISNULL(CONVERT(VARCHAR,D.BlSys),'CHECK ID') AS 'BILL SYS',
ISNULL(CONVERT(VARCHAR,c.Chnl),'CHECK ID') AS 'CHANNEL',
UPPER(LTRIM(RTRIM(b.SIDRqstID))), UPPER(LTRIM(RTRIM(b.SIDRqst))), UPPER(LTRIM(RTRIM(b.Rqstr))),
ISNULL(CONVERT(VARCHAR,b.PrpsdLnch, 101),'NO DATE'),ISNULL(CONVERT(VARCHAR,b.CmfrmdLnch,101),'NO DATE'),
UPPER(e.Stus),ISNULL(CONVERT(VARCHAR,a.[Max Of Actual Launch Date],101),'NO DATE'),
ISNULL(CONVERT(VARCHAR,a.[Max Of RA Validation Date],101),'NO DATE'),
ISNULL(CONVERT(VARCHAR,a.[Max Of Final Bill Code Date],101),'NO DATE'),
ISNULL(CONVERT(VARCHAR,a.[Max Of Pre Bill Code Date],101),'NO DATE'),
ISNULL(CONVERT(VARCHAR,a.[Max Of Feedback Date],101),'NO DATE'),
ISNULL(CONVERT(VARCHAR,b.Fnl,101),'NO DATE'),
ISNULL(CONVERT(VARCHAR,b.Vrsn1,101),'NO DATE')
FROM dbo.QryMaxLaunchDateAll_NEW a
JOIN dbo.SIDMstr b ON a.SIDRqstID = b.[SIDRqstID]
LEFT OUTER JOIN dbo.Chnl c ON b.ChnlID = c.ChnlID
LEFT OUTER JOIN dbo.BlSys d ON B.BSID = D.BSID
LEFT OUTER JOIN dbo.Stus e ON b.StusID = e.StusID
LEFT OUTER JOIN dbo.Drctry f ON b.Tbls1 = f.TMID
LEFT OUTER JOIN dbo.Drctry g ON b.Tbls2 = g.TMID
WHERE b.CmfrmdLnch IS NOT NULL
AND b.CmfrmdLnch Between @FromDate And @ToDate
AND e.StusID=4
ORDER BY b.BSID, a.[Max Of Actual Launch Date]


GO
 

SQL_Hell

SQL Server DBA
Local time
Today, 05:48
Joined
Dec 4, 2003
Messages
1,360
Hi

Are you using access project (.adp) or .mdb?
 

naomidodd

New member
Local time
Today, 01:48
Joined
Dec 7, 2005
Messages
6
I am using .MDB
 

SQL_Hell

SQL Server DBA
Local time
Today, 05:48
Joined
Dec 4, 2003
Messages
1,360
ok then,

You can use the ado command object the envoke a stored procedure, and then add the parameters to it,

Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_AdoTest"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = 10
Set Rs1 = Cmd1.Execute()


Read this.....

http://support.microsoft.com/?kbid=185125
 

naomidodd

New member
Local time
Today, 01:48
Joined
Dec 7, 2005
Messages
6
Re: A Problem with Parameter

I am using. mdb though. I thought i could only use DAO and not ADO.

Am I Correct?
 

SQL_Hell

SQL Server DBA
Local time
Today, 05:48
Joined
Dec 4, 2003
Messages
1,360
Here is another doc for you

http://support.microsoft.com/kb/q164485/

Basically you want to load your form values into VBA variables

like so

dim id as integer
id = forms!form1!txt1


then add the values to an ado command object you have created, like so


Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "data source name", "userid", "password"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
adParamInput)

cmd("Param1") = id
cmd.Execute
 

Users who are viewing this thread

Top Bottom