Re: Passing Dates to stored procedure - parameter headache.

Hi Alex

Thanks once again. I eventually also worked out that I had to change the
command type because I'm now running a command, not a stored procedure. This
works a treat though and is much easier than adding the parameters manually.

This has moved me on a big step so thanks again.

"Alex Dybenko" wrote:

Hi,
try to add exec:

"exec spGetOrdhed '4332', '20070102'"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


"blueboy1894" <blueboy1894@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6AF21EE3-EE42-498B-B73A-C0FE5176A6C6@xxxxxxxxxxxxxxxx
Hi Alex. many thanks for your response.

This nearly works, updating the command text to read { call spGetOrdhed
'4332', '20070102' }

but then I get a Syntax error or Access Violation (80040e14 ) when I try
and
execute the command, and not entirely sure why - the same values work fine
in
the SQL Query Window.

Any idea why?

"Alex Dybenko" wrote:

Hi,
try to use adDate instead of adDBTimeStamp

then you can also pass parameters in command string as text:

"spGetOrdhed " & "'" & Me!custcode & "','" &
format(Me!deldate,"yyyymmdd") &
"'"

so both customer and date in single quotes


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



"blueboy1894" <blueboy1894@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E918EB0E-0352-48DF-B409-2F9DB842B40E@xxxxxxxxxxxxxxxx
Hi all

I am trying to pass a date as an input parameter from VBA to a stored
procedure in SQL 2005. The stored procedure looks like this:

ALTER PROCEDURE [dbo].[spGetOrdhed]

@customer varchar(5),
@deliverydate datetime

AS
BEGIN
SET NOCOUNT ON;

SELECT ordhed.ordhedid, ordhed.deldate, custac.[name], ulpfil.descr
FROM freshsql1.Company1.dbo.custac INNER JOIN
freshsql1.Company1.dbo.ordhed ON custac.custcode = ordhed.custcode
INNER
JOIN
freshsql1.Company1.dbo.ulpfil ON ordhed.dp = ulpfil.dp
WHERE (ordhed.custcode = @customer) AND (ordhed.deldate =
@deliverydate)
END

And the VBA passing the parameter values looks like this:

Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim rst1 As New ADODB.Recordset
Dim cust1 As String
Dim date1 As Date
Dim myDate As Date
Dim str1 As String

cust1 = Me!custcode
date1 = Me!deldate
myDate = Format(CDate(date1), "yy/mm/dd")

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = "DSN=complaints2005;"
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "[dbo].[spGetOrdhed]"

Set prm1 = cmd1.CreateParameter("@customer", adVarChar, adParamInput,
5)
prm1.Value = cust1
cmd1.Parameters.Append prm1

Set prm2 = cmd1.CreateParameter("@deliverydate", adDBTimeStamp,
adParamInput)
prm2.Value = myDate
cmd1.Parameters.Append prm2

Set rst1 = New ADODB.Recordset
Set rst1 = cmd1.Execute

(then do some looping to display the results).

I have a problem with the value which is being passed to the variable
myDate. Basically, I cannot get it to form a date value which is valid
on
the
SQL server. The form control accepts values in dd/mm/yyyy format.

The deldate field in SQL is a datetime type and looks like this:

YYYY-MM-DD HH:MM:SS.mmm (milliseconds at the end), though the time part
of
this in the data is always represented as all zeros.

Running the procedure in SQL is fine, and accepts values OK. The
problem
is
on inspection in the immediate window my code generates the value for
myDate
of 07/01/2002, instead of the 07/01/02 which I am trying to pass. I
have
tried all the date functions I can think of to get this to pass
correctly -
Format, CDate, Date Serial etc, and even tried writing it into a
CONVERT
statement as part of the stored procedure. The result is, of course
that
the
recordset winds up empty because there are no entires for that customer
on
that date.

Can anyone enlighten me as to where I am going wrong with this? I'd be
very
grateful as if I can get this to work then the rest of my task should
be
relatively simple (select a specific record and pull the detail on an
order
number).

Thanks,




.

Relevant Pages

  • Re: Help again
    ... What is the name of the parameter in the stored procedure? ... the schema in the system naming convention on parameters and SQL ... command and qualify the object names in the form ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Cancel/Terminate Connection from the control
    ... Dim Cmd As ADODB.Command ... >>> from SQL server database. ... if my sql takes long time), can we do it from the>>> form/control using a command button. ... >>> Private sub getData ...
    (microsoft.public.vb.database.ado)
  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • Passing Dates to stored procedure - parameter headache.
    ... Dim prm1 As ADODB.Parameter ... Dim myDate As Date ... SQL server. ... statement as part of the stored procedure. ...
    (microsoft.public.access.modulesdaovba)
  • Re: why is dataAdapter.UpdateCommand not updating?
    ... SQL command. ... while using the dataadapter after doing this command. ... Dim da As New SqlDataAdapter ...
    (microsoft.public.dotnet.languages.vb)
Quantcast