Stored proc and Parameters

[Eddy]

Hello,

up until i started using Stored procedures, i had no problems whatsoever
passing date parameters (27/09/2003) as string ('20030927') to sql
server. (I do not use an ADP but do everything through Pass Through
queries).
a query that is performing perfectly well in Query Analyser does not
return the desired results when i fire it through a Stored procedure:

CREATE PROCEDURE procBeschikbareRondleidingen @Mnd int, @LocID int,
@LftID int, @DtBz datetime

AS

SELECT DISTINCT tb_Rondleidingen.ID_Locatie, ..... , LokaalBezet =
case
when tb_rondleidingen.id_rondleiding IN
(Select r.id_rondleiding from tb_Lokaalbezetting q,
tb_XRolFac r where r.id_Faciliteit = q.id_faciliteit
and datumbezoek = convert(char(8), @DtBz ,112)) then 1

when tb_rondleidingen.id_rondleiding NOT IN
(Select r.id_rondleiding from tb_Lokaalbezetting q,
Tb_XRolFac r where r.id_Faciliteit = q.id_faciliteit
and datumbezoek = convert(char(8), @DtBz ,112)) then 0

end

FROM .... etc...

The MsAccess side:

DtBezet = Format(Me.DatumBezoek, "mm/dd/yyyy")
cmd.Parameters("@DtBz") = DtBezet

Whatever combination i try, the stored proc always wants a datevalue - i
have tried all possible flavours of date format.
I run the results in a temporary Access table rst!LokaalBezet in a
Yes/NO field - all fields stay 0...
i would defenitly appreciate some help on this issue as my already gray
hairs will become even more gray....


thanks
eddy galle

 

[Van T. Dinh]

Not 100% sure but since you declared @DtBz as datetime,
you really should be passing it a date value, NOT the
return of a Format function which is a Variant of String
type.

Instead of

DtBezet = Format(Me.DatumBezoek, "mm/dd/yyyy")
cmd.Parameters("@DtBz") = DtBezet

try:

cmd.Parameters("@DtBz") = Me.DatumBezoek

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hello,

up until i started using Stored procedures, i had no problems whatsoever
passing date parameters (27/09/2003) as string ('20030927') to sql
server. (I do not use an ADP but do everything through Pass Through
queries).
a query that is performing perfectly well in Query Analyser does not
return the desired results when i fire it through a Stored procedure:

CREATE PROCEDURE procBeschikbareRondleidingen @Mnd int, @LocID int,
@LftID int, @DtBz datetime

AS

SELECT DISTINCT

tb_Rondleidingen.ID_Locatie, ..... ,
LokaalBezet =

 

[Eddy]

thanks for the hint,
but, then i will run into another problem as our Belgian dateformat is
dd/mm/yyyy .... (the original value of DatumBezoek is f.i. 27/09/2003)

so i do have to convert to mm/dd/yyyy one way or another
when i pass the date as a string to the stored proc -
with @Dtbz as varchar Access returns a type error - SQL apparently only
accepts the parameter as Datetime here
i'll experiment a bit more with different flavours of formatting - my
stored proc however complains when i give the @Dtbz something else then
datetime....????

 

[Van T. Dinh]

Is [datumbezoek] a DateTime Field in your SQL Server Table?

If it is, I think your code is wrong way around. In
addition, I found that you cannot pass date value directly
between Access/VB and SQL Server as they have a different
reference point. Hence, basically to pass datetime from
Access/Vb to SQL Server, the only choice is String /
varchar

Try this:

CREATE PROCEDURE procBeschikbareRondleidingen @Mnd int,
@LocID int, @LftID int, @DtBz varchar

AS
.....

and datumbezoek = convert(datetime, @DtBz ,101))

and exactly the same later in your SP. Note that I change
the type of @DtBz to varchar.

In your Access VB code, use:

cmd.Parameters("@DtBz") = _
Format(Me.DatumBezoek, "mm/dd/yyyy")

HTH
Van T. Dinh
MVP (Access)

 

[Eddy]

thanks,
yes, [datumbezoek] is a datetime field.

but I got it working like this - which i think is basically what you are
referring to in your answer - which inspired me to another approach:

in my Access code:
DtBezet = Mid(Me.DatumBezoek, 4, 2) & "/" & Left(Me.DatumBezoek, 2) &
"/" & Right(Me.DatumBezoek, 4)
cmd.Parameters("@DtBz") = DtBezet


it works like a dream now....

Is [datumbezoek] a DateTime Field in your SQL Server Table?

If it is, I think your code is wrong way around. In
addition, I found that you cannot pass date value directly
between Access/VB and SQL Server as they have a different
reference point. Hence, basically to pass datetime from
Access/Vb to SQL Server, the only choice is String /
varchar

Try this:

CREATE PROCEDURE procBeschikbareRondleidingen @Mnd int,
@LocID int, @LftID int, @DtBz varchar

AS
....

and datumbezoek = convert(datetime, @DtBz ,101))

and exactly the same later in your SP. Note that I change
the type of @DtBz to varchar.

In your Access VB code, use:

cmd.Parameters("@DtBz") = _
Format(Me.DatumBezoek, "mm/dd/yyyy")

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
thanks for the hint,
but, then i will run into another problem as our Belgian

dateformat is

dd/mm/yyyy .... (the original value of DatumBezoek is

f.i. 27/09/2003)

so i do have to convert to mm/dd/yyyy one way or another
when i pass the date as a string to the stored proc -
with @Dtbz as varchar Access returns a type error - SQL

apparently only

accepts the parameter as Datetime here
i'll experiment a bit more with different flavours of

formatting - my

stored proc however complains when i give the @Dtbz

something else then

datetime....????