Re: Problem using LIKE in a command object

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@xxxxxxxxxxxxxx> wrote in message
news:03EAEDDB-BCA5-4AEE-85DD-390FE3D922CB@xxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxx> wrote in message
>> news:FF9D9BD7-D695-4BE5-8564-DD7B1BBB5A23@xxxxxxxxxxxxxxxx
>> > 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
>>
>>
>>


.

Relevant Pages

  • Re: Counting Concurrent timed events
    ... When you make a command button, Access gives it an ambiguous name like "Command2" ... ... then compile your code ... Exit Sub ... what you need to be able to do is replace the SQL using a different date before you run the query that uses it. ...
    (microsoft.public.access.queries)
  • Re: Multiple table in dataset query
    ... If a row is marked as Modified, it will run your query defined in the ... Update Command object. ... Dim UpdateStaffCmd As New OleDb.OleDbCommand ... Private Sub frmStaff_Load(ByVal sender As System.Object, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Complex form popup problem: warning...longwinded
    ... command button that launched the other form. ... main form that allows the user to fill out the scenario information, ... The majority of the fields in the query that are the recordsource of the ... remainder belong to the new record that was programmatically saved. ...
    (microsoft.public.access.formscoding)
  • RE: ODBC query in VB code Need HELP
    ... The vb part could be to set up a query string by concatenation or to go ... or call when you press your command button ... the connection string, cursor location, etc. ...
    (microsoft.public.access.formscoding)
  • Re: Problem using LIKE in a command object
    ... the method that builds the query, so that's where "key" comes from in my code. ... >> I'm trying to use a select command as the CommandText of an ADO.NET ... >> Dim dr as SqlDataReader ... >> it's enclosed in single quotes (like it would be in the Query Analyzer ...
    (microsoft.public.dotnet.framework.adonet)
Loading