Parameterized Query Using Wildcards in VS2005
Hey everyone,
I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:
SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%')
However, when I test this on my PDA, I get the following error:
SQL Execution Error.
Executed SQL statement: SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%')
Error Source: SQL Server Mobile Edition ADO.NET Data Provider
Error Message: @StreetName : deerbrook - FormatException
Does anyone know how to add wildcards to a parameter?
Thanks,
Lee
Hey,
This is an a stored proc, or in an ADO.NET query? What I've had to done in the past is to create a dynamic SQL string, and execute that string using exec or exec sp_executeSQL.
Brian
Hey Brian,
Actually, this is a store proc that is created from within TableAdapter in VS2005.
Lee
Hey,
Well then, being in code, I don't think you could use a variable with that kind of string append... you may have to hard code that value into the string, instead of using a variable. But being in a table adapter, I don't know if that will work. You could try passing the %% in with the string by yourself.
Brian
change the select statement in the adapter wizard to this
SELECT LocationID, StreetNum, StreetName, rowguid
FROM tblLocations
WHERE(StreetNum = @StreetNum) AND (StreetName LIKE @StreetName)
change 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
Impotant to note - %value% will not utilize indexes and can lead to poor performance. just keep that in mind.
In places where we have a lot of data that may be seached like this, we provide a combo box with values "equals|begins with|ends with|contains" and attach %'s appropriately. Using the Above formatted SQL accomodates this.
cheers
This question was already answered, but it did not provide you with the internals. You need to set the % in the parameters value.
You create for example the following query:
select * from FOO where BAR like @P1;
After that you have to create a SqlCeParameter object and set the % in the value of that parameter:
SqlCeParameter parameter = new SqlCeParameter();
parameter.Value = string.Format("%{0}%", value);
The DataAdapter is hiding this here. But if you are working with SqlCeCommand and objects you have to know it.
Are you able to query now with this answer? or still facing some issues?
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
Yes, Blair gave me the answer that I needed. Thanks.
Hi All.
I have same problem with string concatenation.
SqlCeConnection con = new SqlCeConnection("Test.sdf");
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "select firstname + ' ' + lastname from person";
bool i = cmd.ExecuteReader().Read();
cmd.CommandText = "select firstname + @p0 + lastname from person";
cmd.Parameters.Add("@p0", " ");
i = cmd.ExecuteReader().Read();
The first query executes fine, but second throws FormatException.
It looks like db expects double value instead of string.
Try using Parameter.AddWithValue.
Thanks,
Laxmi
Thanks for reply. No difference, i get same result :(.
Here is complete test:
string fileName = "Test.sdf";
File.Delete(fileName);
SqlCeConnection con = new SqlCeConnection("data source=" + fileName);
SqlCeEngine eng = new SqlCeEngine(con.ConnectionString);
eng.CreateDatabase();
con.Open();
SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "create table person (firstname nvarchar(100), lastname nvarchar(100))";
cmd.ExecuteNonQuery();
cmd.CommandText = "select firstname + ' ' + lastname from person";
bool i = cmd.ExecuteReader().Read();
cmd.CommandText = "select firstname + @p0 + lastname from person";
cmd.Parameters.AddWithValue("@p0", " ");
i = cmd.ExecuteReader().Read();
It was my oversight. Parameters can only be used for WHERE Clause. But I see that you are using for SELECT Clause. I really dont know what you are trying to achieve and started using PARAMETERS in SELECT Clause. Can you please elaborate on what is your problem, what is the context ..so that we can have better understanding before we reply.
Thanks,
Laxmi
I don't think it depends from context.
cmd.CommandText = "select * from person where firstname + @p0 + lastname = 'f l";
cmd.Parameters.AddWithValue("@p0", " ");
bool i = cmd.ExecuteReader().Read();
I this case i get:
The data type is not valid for the boolean operation. [ Data type (if known) = float,Data type (if known) = nvarchar ]
Actually i have generic sql generation system and want to decide - put string in text or pass it as a parameter to sql command.
Can you please try this?
cmd.CommandText = "select * from person where firstname = @p0 AND lastname = 'f l";
cmd.Parameters.AddWithValue("@p0", " ");
Thanks,
Laxmi
After adding missing quote at the end of command text, it executes just fine.
But this is not what i expect. It produces different result.
