Problem using LIKE in a command object

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: Problem using LIKE in a command object by W

W
Tue Jun 28 23:23:53 CDT 2005

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



--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"BBM" <bbm@bbmcompany.com> wrote in message
news:FF9D9BD7-D695-4BE5-8564-DD7B1BBB5A23@microsoft.com...
> 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


Top

Re: Problem using LIKE in a command object by W

W
Tue Jun 28 23:25:19 CDT 2005

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 ;-) )

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"BBM" <bbm@bbmcompany.com> wrote in message
news:FF9D9BD7-D695-4BE5-8564-DD7B1BBB5A23@microsoft.com...
> 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


Top

Re: Problem using LIKE in a command object by bbm

bbm
Wed Jun 29 05:48:02 CDT 2005

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


"W.G. Ryan eMVP" wrote:

> 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 ;-) )
>
> --
> W.G. Ryan MVP (Windows Embedded)
>
> TiBA Solutions
> www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
> "BBM" <bbm@bbmcompany.com> wrote in message
> news:FF9D9BD7-D695-4BE5-8564-DD7B1BBB5A23@microsoft.com...
> > 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
>
>
>
Top

Re: Problem using LIKE in a command object by Jim

Jim
Wed Jun 29 10:48:24 CDT 2005

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")

"BBM" <bbm@bbmcompany.com> wrote in message
news:03EAEDDB-BCA5-4AEE-85DD-390FE3D922CB@microsoft.com...
> 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
>
>
> "W.G. Ryan eMVP" wrote:
>
>> 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 ;-) )
>>
>> --
>> W.G. Ryan MVP (Windows Embedded)
>>
>> TiBA Solutions
>> www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
>> "BBM" <bbm@bbmcompany.com> wrote in message
>> news:FF9D9BD7-D695-4BE5-8564-DD7B1BBB5A23@microsoft.com...
>> > 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
>>
>>
>>


Top

Re: Problem using LIKE in a command object by bbm

bbm
Thu Jun 30 19:58:02 CDT 2005

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

"Jim Hughes" wrote:

> 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")
>
> "BBM" <bbm@bbmcompany.com> wrote in message
> news:03EAEDDB-BCA5-4AEE-85DD-390FE3D922CB@microsoft.com...
> > 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
> >
> >
> > "W.G. Ryan eMVP" wrote:
> >
> >> 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 ;-) )
> >>
> >> --
> >> W.G. Ryan MVP (Windows Embedded)
> >>
> >> TiBA Solutions
> >> www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
> >> "BBM" <bbm@bbmcompany.com> wrote in message
> >> news:FF9D9BD7-D695-4BE5-8564-DD7B1BBB5A23@microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>
>
Top