D
davidray1234
Hi,
I am new to database. I am having difficulty with quotaion marl
problems when I am passing string variables in ADO .NET to varchar
variable in SQL stored procedures.
In ADO .NET code, I used:
SqlParameter[] parameters = {
new SqlParameter("@StartCity", SqlDbType.VarChar, 50),
new SqlParameter("@EndCity", SqlDbType.VarChar, 50),
new SqlParameter("@TripDate", SqlDbType.DateTime, 8) }
parameters[0].Value = strStartCity; // strStartCity =
txtStartCity.Text;
parameters[1].Value = strEndCity;
parameters[2].Value = tripDate; // tripDate is System.DateTime
type
.....
DataSet trips = RunProcedure("usp_GetTrips", parameters,
"Trips");
Then in SQL stored procedure, I used:
SELECT *
WHERE StartCity LIKE @StartCity AND EndCity LIKE @EndCity
AND DateDiff(day, DateAdd(day, -3, @dtTripDate),
TripDate)<10
This does not work returning no result. I used SQL Query Analyser
debugger to check the stored procedure. It seems the string value
like 'Ottawa' is automatically padded with " (double quotation marks)
at both ends and these quotation marks are counted as a character. So
it does not work.
When I used the following in my stored procedure:
SELECT *
WHERE StartCity LIKE 'Ottawa' AND EndCity LIKE 'Toronto'
AND DateDiff(day, DateAdd(day, -3, '11/16/2004'),
TripDate)<10
It worked and returned the results.
It seems that the strings passed from ADO ,NET code is not interpreted
as string literal in stored procedure. What is the problem and what
settings do I need to turn on to solve it?
Thanks a lot for your time and help.
David
*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*
I am new to database. I am having difficulty with quotaion marl
problems when I am passing string variables in ADO .NET to varchar
variable in SQL stored procedures.
In ADO .NET code, I used:
SqlParameter[] parameters = {
new SqlParameter("@StartCity", SqlDbType.VarChar, 50),
new SqlParameter("@EndCity", SqlDbType.VarChar, 50),
new SqlParameter("@TripDate", SqlDbType.DateTime, 8) }
parameters[0].Value = strStartCity; // strStartCity =
txtStartCity.Text;
parameters[1].Value = strEndCity;
parameters[2].Value = tripDate; // tripDate is System.DateTime
type
.....
DataSet trips = RunProcedure("usp_GetTrips", parameters,
"Trips");
Then in SQL stored procedure, I used:
SELECT *
WHERE StartCity LIKE @StartCity AND EndCity LIKE @EndCity
AND DateDiff(day, DateAdd(day, -3, @dtTripDate),
TripDate)<10
This does not work returning no result. I used SQL Query Analyser
debugger to check the stored procedure. It seems the string value
like 'Ottawa' is automatically padded with " (double quotation marks)
at both ends and these quotation marks are counted as a character. So
it does not work.
When I used the following in my stored procedure:
SELECT *
WHERE StartCity LIKE 'Ottawa' AND EndCity LIKE 'Toronto'
AND DateDiff(day, DateAdd(day, -3, '11/16/2004'),
TripDate)<10
It worked and returned the results.
It seems that the strings passed from ADO ,NET code is not interpreted
as string literal in stored procedure. What is the problem and what
settings do I need to turn on to solve it?
Thanks a lot for your time and help.
David
*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*