Parameterized queries

When using a parameterized query such as:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @StreetNum) AND (StreetName LIKE @StreetName)

Is there a way to automatically add % to the beginning and ending of the parameter I've tried the following command:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%')

But I get a FormatException. I don't want the user to have to type % in the parameter field. Any suggestions

Thanks,

Lee

Answer this question

Parameterized queries

sansara13
It doesn't like that one either. Gives a parsing error on the AS statement. That is the correct data type for that column, however, it doesn't like the AS in the CAST statement.
jehan
If you have a data layer, that contains all your queries, you can do it like this:

command.Parameters["theParameter"].Value = theValue + "%"
jnf

Lee,

When you assign value to the parameter, then add % characters at the end and beginng of the value and it will work. Something like

MyParameters.Value="%" & MyValue & "%"

In this case you will need to use first type of your query
T_Cavallari

Hi!

In the query builder modify your query as under:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @StreetNum) AND (StreetName LIKE CAST('%' + @StreetName + '%' AS NVARCHAR))

cheers.
blue-ice

The parameters are actually filled from user input. The click event is below:

Private Sub FillByButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByButton.Click

Try

Me.TblLocationsTableAdapter.FillBy(Me.WorkOrdersDataSet.tblLocations, StreetNumTextBox.Text, StreetNameTextBox.Text)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try

End Sub

Is this what you are wanting to see

Lee
ApSav

Thanks Blair. You are awesome.

VMazur & Frederik, thanks for your suggestions as well. If I had more time I would look at trying your code. I'm just not sure where it would go.

Thanks everyone!

Lee
Stan Kitsis

The CONVERT command was good thinking, however, it gives me a FormatException as well.

I'm beginning to think that maybe it is just a limitation of SQL Mobile Edition.

Lee
Bryan Kardisco

yep. . .

try changing the select statement in the adapter wizard to this

SELECT     LocationID, StreetNum, StreetName, rowguid
FROM         tblLocations
WHERE(StreetNum = @StreetNum) AND (StreetName LIKE @StreetName)

try changing your event code to this
Private Sub FillByButton_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) _
                            Handles FillByButton.Click
    Try
        Me.TblLocationsTableAdapter.FillBy(Me.WorkOrdersDataSet.tblLocations, _
               StreetNumTextBox.Text, _
               String.format("%{0}%",StreetNameTextBox.Text))
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try
End Sub
waison

Hi!

It can't be if you are using SQL Server that's what I assumed but that doesn't seem to be the case. If you are using Access then following sytanx will be used:

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @StreetNum) AND (StreetName LIKE '%' + + '%')

cheers.
ooartist
Indeed, this is what I also suggested, and I guess this is the best solution.
popeyesailor

I have split this post into a separate thread, and moved it to a more appropriate forum.

You want the second query. What is throwing the FormatException It may have something to do with the single quotes.
Nathan Yokoyama
let me see the code for setting the parameter values
davcrist

Ok in that case try following hopefully it'll work but I'm not sure since I don't have Mobile Edition Installed. If it works let me know as well by marking it as an answer.

SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @StreetNum) AND (StreetName LIKE CONVERT(nvarchar,'%' + @StreetName + '%'))

cheers.
Olav Tollefsen

Hey Sohail,

Sorry, I just have specified. This is actually a TableAdapter in VS2005 for a SQL Server 2005 Mobile Edition database.

Lee
Parameterized queries

Answer this question