Problem using LIKE in a command object

[Guest]

Hi everyone,

I'm trying to use a select command as the CommandText of an ADO.NET
SQLCommand object (I'm using SQL Server as the database). From the Query
Analyzer I can run the query and get the correct result, but in my program,
the command using this query returns no rows.

My code looks like this...

Private Const availFutureRes = "SELECT Id from AlcResIndx WHERE ResType IN
('PF','PW') " & _
"AND Date <= @Date AND SKU LIKE @SKU AND HasBalance = 'Y' " & _
"ORDER BY Date DESC"

Dim dr as SqlDataReader
Dim cn As SqlConnection = New SqlConnection(connectStr)
Dim cmd As SqlCommand = New SqlCommand(availFutureRes, cn)
cmd.Parameters.Add("@Date", SqlDbType.DateTime, 8, "Date")
cmd.Parameters.Add("@SKU", SqlDbType.Char, 18, "SKU")
cmd.Parameters("@Date").Value = r.StartDate
cmd.Parameters("@SKU").Value = RTrim(key) & "%"
cn.Open()
Try
dr = cmd.ExecuteReader()

So what's wrong with this? I've tried setting up the "@SKU" value so that
it's enclosed in single quotes (like it would be in the Query Analyzer like
'AA127%'). Didn't help.

Any help is appreciated.

BBM

 

[W.G. Ryan eMVP]

http://support.microsoft.com/default.aspx?scid=kb;en-us;555167

 

[W.G. Ryan eMVP]

Oops, I jusst posted the link without an explanation. The parameter version
escapes everything, so it's LIKE 1 values instead of a bunch that are
separated by commas. the last link should show you how to get around it (if
not, let me know b/c I wrote it and If it doesn't fix your problem, then I
need to rework it ;-) )

 

[Guest]

Thanks for your response, but I don't think I fit the case that you cite.
I'm only passing in a single value to my LIKE clause so the parsing algorithm
that you use in your write-up I don't think will help me. I use the IN
statement in my SQL statement, but the values to match are literals, not
passed as a parm.

The situation I'm addressing is one where I'm interested in partial matches
on one of the keys of the FROM file. For example the full key of a row
might be
"AA127REDOFA"

Sometimes I want to be able to retrieve that record using LIKE "AA127%".
That's what my code is supposed to do. The value to search is passed into
the method that builds the query, so that's where "key" comes from in my code.

I must not be setting the value of the @SKU parm correctly? Like I said, it
works like a champ in Query Analyzer - just not in my code!

Thanks

BBM

 

[Jim Hughes]

I suspect that using Char instead of VarChar is causing the value to be
padded with spaces

cmd.Parameters.Add("@SKU", SqlDbType.Char, 18, "SKU")

So instead of 'AA127%' it is using 'AA127% ' which of course
doesn't match.

Try cmd.Parameters.Add("@SKU", SqlDbType.VarChar, 18, "SKU")

 

[Guest]

Hi Jim,

Thanks, that was exactly it. I was taking some pains to make sure that my
key string wasn't "padded" when I assigned it to the parm value, but I guess
by declaring it as a char it had spaces appended onto it by the parameter
when it was assigned.

Thanks again for your help.

BBM