Stored procedure syntax error....

  • Thread starter Thread starter Parag
  • Start date Start date
P

Parag

hi,

i am trying to write a stored procedure that selects the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in the
code as the parameters: @ArrivalDate and @DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance
 
Dear Parag:

I would start out by killing the two right parens on the end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you buy SQL Server
Developer Edition for $50. Then you can use Query Analyzer to work in
your queries. When there is an error in the code like this, it will
usually tell you in what line it encountered the error. That would
save you a lot of time here.

In the mean time, try to get the subquery in your last 12 lines
working first, then add the icing in the first 10 lines later.
Perfecting one piece at a time will also save a lot of time. This
piece should tell you which caravans are being excluded, right? You
might want to temporarily include columns for @ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This would allow you
to test the logic of this part. Then eliminate the lines that display
these by commenting them out, but leave them in for ease of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part that
selects the caravans that are already booked for the date
specified by the user) separtly and it seems to work ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery. Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert my .mdb
to .adp (MSDE instead of jet)



-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on the end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you buy SQL Server
Developer Edition for $50. Then you can use Query Analyzer to work in
your queries. When there is an error in the code like this, it will
usually tell you in what line it encountered the error. That would
save you a lot of time here.

In the mean time, try to get the subquery in your last 12 lines
working first, then add the icing in the first 10 lines later.
Perfecting one piece at a time will also save a lot of time. This
piece should tell you which caravans are being excluded, right? You
might want to temporarily include columns for @ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This would allow you
to test the logic of this part. Then eliminate the lines that display
these by commenting them out, but leave them in for ease of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

hi,

i am trying to write a stored procedure that selects the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in the
code as the parameters: @ArrivalDate and @DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance
Click to expand...

.
Click to expand...
 
Dear Parag:

I take it you have removed the two parens I recommended. Post your
new SQL again so I can check that. Maybe I'll see something else, but
I want to make sure I'm studying the exact SQL that still has a
problem.

Upsizing wizard may not be perfect. You're still going to have to
know how to program. Anyway, upsizing wizard isn't creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part that
selects the caravans that are already booked for the date
specified by the user) separtly and it seems to work ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery. Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert my .mdb
to .adp (MSDE instead of jet)



-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on the end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you buy SQL Server
Developer Edition for $50. Then you can use Query Analyzer to work in
your queries. When there is an error in the code like this, it will
usually tell you in what line it encountered the error. That would
save you a lot of time here.

In the mean time, try to get the subquery in your last 12 lines
working first, then add the icing in the first 10 lines later.
Perfecting one piece at a time will also save a lot of time. This
piece should tell you which caravans are being excluded, right? You
might want to temporarily include columns for @ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This would allow you
to test the logic of this part. Then eliminate the lines that display
these by commenting them out, but leave them in for ease of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

hi,

i am trying to write a stored procedure that selects the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in the
code as the parameters: @ArrivalDate and @DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance
Click to expand...

.
Click to expand...
Click to expand...
 
Hi Tom
thx for helpin me out :o)

here is my amended code:

SELECT dbo.Caravan_details.Caravan_Inv_No,
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_Inv_No
NOT IN
(SELECT
dbo.Caravan_booking.Caravan_Inv_No
FROM dbo.Caravan_booking INNER JOIN
dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature
OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

When i try to run this is get a message to save it
first...which i accept. but then i get a message stating:
ADO error: must declare the varible @ArrivalDate.

I still get the same message when i press the 'verify sql
syntax' button:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

:o(

Do u have any idea how i can resolve this?



-----Original Message-----
Dear Parag:

I take it you have removed the two parens I recommended. Post your
new SQL again so I can check that. Maybe I'll see something else, but
I want to make sure I'm studying the exact SQL that still has a
problem.

Upsizing wizard may not be perfect. You're still going to have to
know how to program. Anyway, upsizing wizard isn't creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part that
selects the caravans that are already booked for the date
specified by the user) separtly and it seems to work ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery. Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert my .mdb
to .adp (MSDE instead of jet)



-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on
Click to expand...
the
end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you buy SQL Server
Developer Edition for $50. Then you can use Query Analyzer to work in
your queries. When there is an error in the code like this, it will
usually tell you in what line it encountered the
Click to expand...
error.
That would
save you a lot of time here.

In the mean time, try to get the subquery in your last 12 lines
working first, then add the icing in the first 10
Click to expand...
lines
later.
Perfecting one piece at a time will also save a lot of time. This
piece should tell you which caravans are being
Click to expand...
excluded,
right? You
might want to temporarily include columns for @ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This would allow you
to test the logic of this part. Then eliminate the lines that display
these by commenting them out, but leave them in for
Click to expand...
ease
of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 13:15:57 -0800, "Parag"

hi,

i am trying to write a stored procedure that selects the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in the
code as the parameters: @ArrivalDate and @DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance

.
Click to expand...
Click to expand...

.
Click to expand...
 
Dear Parag:

Open your Stored Procedure in SQL View and be sure to paste all the
code into your message. It should start with:

ALTER PROCEDURE

If not, then you have not been creating a Stored Procedure. To create
a Stored Procedure, when you click New choose "Create Text Stored
Procedure" and paste in your code so far. Create your parameters
here.

I'm thinking you may have been creating a View instead of a Stored
Procedure. A view does not have parameters passed to it.

Maybe the mystery is in here somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
thx for helpin me out :o)

here is my amended code:

SELECT dbo.Caravan_details.Caravan_Inv_No,
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_Inv_No
NOT IN
(SELECT
dbo.Caravan_booking.Caravan_Inv_No
FROM dbo.Caravan_booking INNER JOIN
dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature
OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

When i try to run this is get a message to save it
first...which i accept. but then i get a message stating:
ADO error: must declare the varible @ArrivalDate.

I still get the same message when i press the 'verify sql
syntax' button:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

:o(

Do u have any idea how i can resolve this?



-----Original Message-----
Dear Parag:

I take it you have removed the two parens I recommended. Post your
new SQL again so I can check that. Maybe I'll see something else, but
I want to make sure I'm studying the exact SQL that still has a
problem.

Upsizing wizard may not be perfect. You're still going to have to
know how to program. Anyway, upsizing wizard isn't creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part that
selects the caravans that are already booked for the date
specified by the user) separtly and it seems to work ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery. Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert my .mdb
to .adp (MSDE instead of jet)




-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on the
end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you
buy SQL Server
Developer Edition for $50. Then you can use Query
Analyzer to work in
your queries. When there is an error in the code like
this, it will
usually tell you in what line it encountered the error.
That would
save you a lot of time here.

In the mean time, try to get the subquery in your last
12 lines
working first, then add the icing in the first 10 lines
later.
Perfecting one piece at a time will also save a lot of
time. This
piece should tell you which caravans are being excluded,
right? You
might want to temporarily include columns for
@ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This
would allow you
to test the logic of this part. Then eliminate the
lines that display
these by commenting them out, but leave them in for ease
of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 13:15:57 -0800, "Parag"

hi,

i am trying to write a stored procedure that selects
the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in the
code as the parameters: @ArrivalDate and @DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance

.
Click to expand...

.
Click to expand...
Click to expand...
 
Hi Tom,

I have checked all the SPROCS i have created so far and
when i open the in SQL view they do start with...ALTER
PROCEDURE.
The problem i am having with the SPROC that im writing
now is that access will not let me save it due to the
error in my sql coding and therefore i cannot view it in
SQL mode. Each time i try, i get the same error message:

ADO error: must declare the varibale '@ArrivalDate'

:o(

It is definatly a SPROC i have created though.
I dont think it likes the way i have placed the
parameters in the sub-select.
Also when i create my SPROC using the query builder, i
cannot show all three tables on the wizard as it
automaticcaly joins them. I do not want this as i want 2
of the tables in the NOT IN part of my query (Sub-select)
and therefore do not want all 3 of them joined directly.
I have got around this by only selecting the Main table
(caravan details) and then selecting the coloumns i want
form here. I then created another SPROC to show the other
2 tables that are supposed to be in the sub-select. I
then copied and pasted this into the my first SPROC to
represent the Sub-select.
That is how i arrived at the SPROC i sent u.

Dont know if that makes much sense to u? :o)

-----Original Message-----
Dear Parag:

Open your Stored Procedure in SQL View and be sure to paste all the
code into your message. It should start with:

ALTER PROCEDURE

If not, then you have not been creating a Stored Procedure. To create
a Stored Procedure, when you click New choose "Create Text Stored
Procedure" and paste in your code so far. Create your parameters
here.

I'm thinking you may have been creating a View instead of a Stored
Procedure. A view does not have parameters passed to it.

Maybe the mystery is in here somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
thx for helpin me out :o)

here is my amended code:

SELECT dbo.Caravan_details.Caravan_Inv_No,
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_Inv_No
NOT IN
(SELECT
dbo.Caravan_booking.Caravan_Inv_No
FROM dbo.Caravan_booking INNER JOIN
dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature
OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

When i try to run this is get a message to save it
first...which i accept. but then i get a message stating:
ADO error: must declare the varible @ArrivalDate.

I still get the same message when i press the 'verify sql
syntax' button:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

:o(

Do u have any idea how i can resolve this?



-----Original Message-----
Dear Parag:

I take it you have removed the two parens I recommended. Post your
new SQL again so I can check that. Maybe I'll see something else, but
I want to make sure I'm studying the exact SQL that still has a
problem.

Upsizing wizard may not be perfect. You're still
Click to expand...
going
to have to
know how to program. Anyway, upsizing wizard isn't creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 17:28:07 -0800, "Parag"

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part that
selects the caravans that are already booked for the date
specified by the user) separtly and it seems to work ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery. Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert my .mdb
to .adp (MSDE instead of jet)




-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on the
end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you
buy SQL Server
Developer Edition for $50. Then you can use Query
Analyzer to work in
your queries. When there is an error in the code like
this, it will
usually tell you in what line it encountered the error.
That would
save you a lot of time here.

In the mean time, try to get the subquery in your last
12 lines
working first, then add the icing in the first 10 lines
later.
Perfecting one piece at a time will also save a lot of
time. This
piece should tell you which caravans are being excluded,
right? You
might want to temporarily include columns for
@ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This
would allow you
to test the logic of this part. Then eliminate the
lines that display
these by commenting them out, but leave them in for ease
of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 13:15:57 -0800, "Parag"

hi,

i am trying to write a stored procedure that selects
the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in the
code as the parameters: @ArrivalDate and @DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON
Click to expand...
dbo.Caravan_booking.BookingNo
=
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance

.


.
Click to expand...
Click to expand...

.
Click to expand...
 
Dear Parag:

It may be that you have correctly diagnosed this. That is, it may be
that parameters in the subquery are messing with the Access interface
into SQL Server. This would not be a surprise. This interface is
known to be limited. I'll go back to my earlier suggestion that you
use SQL Server's Query Analyzer to do this work instead of the Access
provided tools.

However, in the short run, I recommend you avoid using the design view
for this query and instead use the text view. I cannot guarantee it
will work there, but it would seem to have a much higher likelihood.

Just create a new SP in text mode and paste in your code.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

I have checked all the SPROCS i have created so far and
when i open the in SQL view they do start with...ALTER
PROCEDURE.
The problem i am having with the SPROC that im writing
now is that access will not let me save it due to the
error in my sql coding and therefore i cannot view it in
SQL mode. Each time i try, i get the same error message:

ADO error: must declare the varibale '@ArrivalDate'

:o(

It is definatly a SPROC i have created though.
I dont think it likes the way i have placed the
parameters in the sub-select.
Also when i create my SPROC using the query builder, i
cannot show all three tables on the wizard as it
automaticcaly joins them. I do not want this as i want 2
of the tables in the NOT IN part of my query (Sub-select)
and therefore do not want all 3 of them joined directly.
I have got around this by only selecting the Main table
(caravan details) and then selecting the coloumns i want
form here. I then created another SPROC to show the other
2 tables that are supposed to be in the sub-select. I
then copied and pasted this into the my first SPROC to
represent the Sub-select.
That is how i arrived at the SPROC i sent u.

Dont know if that makes much sense to u? :o)

-----Original Message-----
Dear Parag:

Open your Stored Procedure in SQL View and be sure to paste all the
code into your message. It should start with:

ALTER PROCEDURE

If not, then you have not been creating a Stored Procedure. To create
a Stored Procedure, when you click New choose "Create Text Stored
Procedure" and paste in your code so far. Create your parameters
here.

I'm thinking you may have been creating a View instead of a Stored
Procedure. A view does not have parameters passed to it.

Maybe the mystery is in here somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom
thx for helpin me out :o)

here is my amended code:

SELECT dbo.Caravan_details.Caravan_Inv_No,
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_Inv_No
NOT IN
(SELECT
dbo.Caravan_booking.Caravan_Inv_No
FROM dbo.Caravan_booking INNER JOIN
dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature
OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

When i try to run this is get a message to save it
first...which i accept. but then i get a message stating:
ADO error: must declare the varible @ArrivalDate.

I still get the same message when i press the 'verify sql
syntax' button:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

:o(

Do u have any idea how i can resolve this?




-----Original Message-----
Dear Parag:

I take it you have removed the two parens I
recommended. Post your
new SQL again so I can check that. Maybe I'll see
something else, but
I want to make sure I'm studying the exact SQL that
still has a
problem.

Upsizing wizard may not be perfect. You're still going
to have to
know how to program. Anyway, upsizing wizard isn't
creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 17:28:07 -0800, "Parag"

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part
that
selects the caravans that are already booked for the
date
specified by the user) separtly and it seems to work
ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery.
Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert
my .mdb
to .adp (MSDE instead of jet)




-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on
the
end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you
buy SQL Server
Developer Edition for $50. Then you can use Query
Analyzer to work in
your queries. When there is an error in the code like
this, it will
usually tell you in what line it encountered the
error.
That would
save you a lot of time here.

In the mean time, try to get the subquery in your last
12 lines
working first, then add the icing in the first 10
lines
later.
Perfecting one piece at a time will also save a lot of
time. This
piece should tell you which caravans are being
excluded,
right? You
might want to temporarily include columns for
@ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This
would allow you
to test the logic of this part. Then eliminate the
lines that display
these by commenting them out, but leave them in for
ease
of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 13:15:57 -0800, "Parag"

hi,

i am trying to write a stored procedure that selects
the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in
the
code as the parameters: @ArrivalDate and
@DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details.[Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo
=
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get an
error message stating:
ADO error:Parameter Information cannot be derived
from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance

.


.
Click to expand...

.
Click to expand...
Click to expand...
 
Thx Tom,

Do u know if MSDE comes with Query analyzer, as this is
what i am using? Do i have to purchase this separatly and
can it be used with MSDE?? or do i need sql server?
Do u know if there is a freeware version of
this.........as im a final year uni student my funds are
very limited :o(

thx for all your help in helping me resolve this issue :o)
-----Original Message-----
Dear Parag:

It may be that you have correctly diagnosed this. That is, it may be
that parameters in the subquery are messing with the Access interface
into SQL Server. This would not be a surprise. This interface is
known to be limited. I'll go back to my earlier suggestion that you
use SQL Server's Query Analyzer to do this work instead of the Access
provided tools.

However, in the short run, I recommend you avoid using the design view
for this query and instead use the text view. I cannot guarantee it
will work there, but it would seem to have a much higher likelihood.

Just create a new SP in text mode and paste in your code.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

I have checked all the SPROCS i have created so far and
when i open the in SQL view they do start with...ALTER
PROCEDURE.
The problem i am having with the SPROC that im writing
now is that access will not let me save it due to the
error in my sql coding and therefore i cannot view it in
SQL mode. Each time i try, i get the same error message:

ADO error: must declare the varibale '@ArrivalDate'

:o(

It is definatly a SPROC i have created though.
I dont think it likes the way i have placed the
parameters in the sub-select.
Also when i create my SPROC using the query builder, i
cannot show all three tables on the wizard as it
automaticcaly joins them. I do not want this as i want 2
of the tables in the NOT IN part of my query (Sub- select)
and therefore do not want all 3 of them joined directly.
I have got around this by only selecting the Main table
(caravan details) and then selecting the coloumns i want
form here. I then created another SPROC to show the other
2 tables that are supposed to be in the sub-select. I
then copied and pasted this into the my first SPROC to
represent the Sub-select.
That is how i arrived at the SPROC i sent u.

Dont know if that makes much sense to u? :o)

-----Original Message-----
Dear Parag:

Open your Stored Procedure in SQL View and be sure to paste all the
code into your message. It should start with:

ALTER PROCEDURE

If not, then you have not been creating a Stored Procedure. To create
a Stored Procedure, when you click New choose "Create Text Stored
Procedure" and paste in your code so far. Create your parameters
here.

I'm thinking you may have been creating a View instead of a Stored
Procedure. A view does not have parameters passed to it.

Maybe the mystery is in here somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Tue, 11 Nov 2003 02:35:38 -0800, "Parag"

Hi Tom
thx for helpin me out :o)

here is my amended code:

SELECT dbo.Caravan_details.Caravan_Inv_No,
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_Inv_No
NOT IN
(SELECT
dbo.Caravan_booking.Caravan_Inv_No
FROM dbo.Caravan_booking INNER JOIN
dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature
OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

When i try to run this is get a message to save it
first...which i accept. but then i get a message stating:
ADO error: must declare the varible @ArrivalDate.

I still get the same message when i press the 'verify sql
syntax' button:
ADO error:Parameter Information cannot be derived from
SQL statments with sub-select queries. Set parameter
information before preparing command

:o(

Do u have any idea how i can resolve this?




-----Original Message-----
Dear Parag:

I take it you have removed the two parens I
recommended. Post your
new SQL again so I can check that. Maybe I'll see
something else, but
I want to make sure I'm studying the exact SQL that
still has a
problem.

Upsizing wizard may not be perfect. You're still going
to have to
know how to program. Anyway, upsizing wizard isn't
creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 17:28:07 -0800, "Parag"

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part
that
selects the caravans that are already booked for the
date
specified by the user) separtly and it seems to work
ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery.
Am
i writing the parameters in the right place i.e ca they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert
my .mdb
to .adp (MSDE instead of jet)




-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on
the
end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend you
buy SQL Server
Developer Edition for $50. Then you can use Query
Analyzer to work in
your queries. When there is an error in the code like
this, it will
usually tell you in what line it encountered the
error.
That would
save you a lot of time here.

In the mean time, try to get the subquery in your last
12 lines
working first, then add the icing in the first 10
lines
later.
Perfecting one piece at a time will also save a
Click to expand...
lot
of
time. This
piece should tell you which caravans are being
excluded,
right? You
might want to temporarily include columns for
@ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure. This
would allow you
to test the logic of this part. Then eliminate the
lines that display
these by commenting them out, but leave them in for
ease
of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 13:15:57 -0800, "Parag"

hi,

i am trying to write a stored procedure that selects
the
caravans that are available for booking based on the
particular date specifed by the user at the interface
(arrival-date and departure-date, which is shown in
the
code as the parameters: @ArrivalDate and
@DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length, dbo.Caravan_details.Beds,
dbo.Caravan_details. [Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON dbo.Caravan_booking.BookingNo
=
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I
Click to expand...
get
an
error message stating:
ADO error:Parameter Information cannot be derived
from
SQL statments with sub-select queries. Set parameter
information before preparing command

can anyone help me resolve this?
thx in adavance

.


.


.
Click to expand...
Click to expand...

.
Click to expand...
 
MSDE comes with Access, and probably also with SQL Server Developer
Edition. If you are using Access 2002, then it comes with the same
versions as the SQL Server 2000 Editions.

You may not need SQL Server. Certainly you cannot distribute SQL
Server Editions other than MSDE with your applications free of charge,
but you can distribute MSDE with the Developer Edition License.

When you get MSDE with Access, you do NOT get Query Analyzer. That's
why I'm suggesting you get SQL Server Developer Edition for this, and
other tools. If you're distributing Access Run Time you may also need
Office Developer Edition to have distribution rights.

There used to be, and probably still is a 6 month trial version of SQL
Server Standard Edition that might be enough to get you through the
school year. You'd have to buy something like the Developer Edition
to go past that point. But the trial version used to have the tools,
including Query Analyzer, and all you pay is shipping as I remember
it.

Note to Microsoft: might be a good idea to bundle SQL Server DE with
Office DE!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Thx Tom,

Do u know if MSDE comes with Query analyzer, as this is
what i am using? Do i have to purchase this separatly and
can it be used with MSDE?? or do i need sql server?
Do u know if there is a freeware version of
this.........as im a final year uni student my funds are
very limited :o(

thx for all your help in helping me resolve this issue :o)
-----Original Message-----
Dear Parag:

It may be that you have correctly diagnosed this. That is, it may be
that parameters in the subquery are messing with the Access interface
into SQL Server. This would not be a surprise. This interface is
known to be limited. I'll go back to my earlier suggestion that you
use SQL Server's Query Analyzer to do this work instead of the Access
provided tools.

However, in the short run, I recommend you avoid using the design view
for this query and instead use the text view. I cannot guarantee it
will work there, but it would seem to have a much higher likelihood.

Just create a new SP in text mode and paste in your code.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi Tom,

I have checked all the SPROCS i have created so far and
when i open the in SQL view they do start with...ALTER
PROCEDURE.
The problem i am having with the SPROC that im writing
now is that access will not let me save it due to the
error in my sql coding and therefore i cannot view it in
SQL mode. Each time i try, i get the same error message:

ADO error: must declare the varibale '@ArrivalDate'

:o(

It is definatly a SPROC i have created though.
I dont think it likes the way i have placed the
parameters in the sub-select.
Also when i create my SPROC using the query builder, i
cannot show all three tables on the wizard as it
automaticcaly joins them. I do not want this as i want 2
of the tables in the NOT IN part of my query (Sub- select)
and therefore do not want all 3 of them joined directly.
I have got around this by only selecting the Main table
(caravan details) and then selecting the coloumns i want
form here. I then created another SPROC to show the other
2 tables that are supposed to be in the sub-select. I
then copied and pasted this into the my first SPROC to
represent the Sub-select.
That is how i arrived at the SPROC i sent u.

Dont know if that makes much sense to u? :o)


-----Original Message-----
Dear Parag:

Open your Stored Procedure in SQL View and be sure to
paste all the
code into your message. It should start with:

ALTER PROCEDURE

If not, then you have not been creating a Stored
Procedure. To create
a Stored Procedure, when you click New choose "Create
Text Stored
Procedure" and paste in your code so far. Create your
parameters
here.

I'm thinking you may have been creating a View instead
of a Stored
Procedure. A view does not have parameters passed to it.

Maybe the mystery is in here somewhere.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Tue, 11 Nov 2003 02:35:38 -0800, "Parag"

Hi Tom
thx for helpin me out :o)

here is my amended code:

SELECT dbo.Caravan_details.Caravan_Inv_No,
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_Inv_No
NOT IN
(SELECT
dbo.Caravan_booking.Caravan_Inv_No
FROM dbo.Caravan_booking INNER JOIN
dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo =
dbo.Booking_Details.BookingNo
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature
OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

When i try to run this is get a message to save it
first...which i accept. but then i get a message
stating:
ADO error: must declare the varible @ArrivalDate.

I still get the same message when i press the 'verify
sql
syntax' button:
ADO error:Parameter Information cannot be derived
from
SQL statments with sub-select queries. Set
parameter
information before preparing command

:o(

Do u have any idea how i can resolve this?




-----Original Message-----
Dear Parag:

I take it you have removed the two parens I
recommended. Post your
new SQL again so I can check that. Maybe I'll see
something else, but
I want to make sure I'm studying the exact SQL that
still has a
problem.

Upsizing wizard may not be perfect. You're still
going
to have to
know how to program. Anyway, upsizing wizard isn't
creating a SP is
is? If so, what was it before?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 17:28:07 -0800, "Parag"

Hi Tom,

I have made the edits as u suggested, but im still
getting the same error message!!
I tested out the second part of the query (The part
that
selects the caravans that are already booked for the
date
specified by the user) separtly and it seems to work
ok.
The problem seems to occur when i put it into the sub
query. Am i using the corect syntax for the subquery.
Am
i writing the parameters in the right place i.e ca
they
go in the sub-select part of the query?

I am using an Access .adp database. (MSDE)

I was told to use the upsizing wizard to convert
my .mdb
to .adp (MSDE instead of jet)




-----Original Message-----
Dear Parag:

I would start out by killing the two right parens on
the
end of this
line:

dbo.Booking_Details.BookingNo))

If you're working in SQL Server here, I recommend
you
buy SQL Server
Developer Edition for $50. Then you can use Query
Analyzer to work in
your queries. When there is an error in the code
like
this, it will
usually tell you in what line it encountered the
error.
That would
save you a lot of time here.

In the mean time, try to get the subquery in your
last
12 lines
working first, then add the icing in the first 10
lines
later.
Perfecting one piece at a time will also save a lot
of
time. This
piece should tell you which caravans are being
excluded,
right? You
might want to temporarily include columns for
@ArrivalDate,
Dt_of_arrival, Dt_of_departure, and @departure.
This
would allow you
to test the logic of this part. Then eliminate the
lines that display
these by commenting them out, but leave them in for
ease
of later
testing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Mon, 10 Nov 2003 13:15:57 -0800, "Parag"

hi,

i am trying to write a stored procedure that
selects
the
caravans that are available for booking based on
the
particular date specifed by the user at the
interface
(arrival-date and departure-date, which is shown in
the
code as the parameters: @ArrivalDate and
@DepartureDate

here is my code:

SELECT dbo.Caravan_details.Caravan_No,
dbo.Caravan_details.Caravan_Model,
dbo.Caravan_details.Length,
dbo.Caravan_details.Beds,
dbo.Caravan_details. [Cost/day]
FROM dbo.Caravan_details
WHERE (dbo.Caravan_details.Caravan_No NOT IN

(SELECT
dbo.Caravan_booking.Caravan_No
FROM dbo.Caravan_booking INNER JOIN

dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo
=
dbo.Booking_Details.BookingNo))
WHERE @ArrivalDate BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature OR
@departure BETWEEN
dbo.Booking_Details.Dt_of_arrival AND
dbo.Booking_Details.Dt_of_depature))

when i press the 'verify sql syntax' button, I get
an
error message stating:
ADO error:Parameter Information cannot be derived
from
SQL statments with sub-select queries. Set
parameter
information before preparing command

can anyone help me resolve this?
thx in adavance

.


.


.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top