Confusing quotation marks from ADO .NET to stored procedures

[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
*-----------------------*

 

[Guest]

Try wrapping your string value paramter with single quotes: '

That way you're parameter will already be wrapped in quotes when ADO inserts
it into the SP

Fairly sure it's worked for me in the past - memory failing

(e-mail address removed)

 

[yonggangwang]

HI: david
Can you try strStartCity =txtStartCity.Text.ToString();
I am not sure.

 

[Jeff Dillon]

You don't need LIKE since you aren't using any wildcards. Just use =

What is RunProcedure? Looks like it's putting in the extra quotes. Show
the code.

Jeff