Parameterized Query Using Wildcards in VS2005

Parameterized Query Using Wildcards in VS2005

• moconnor

  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

  
  

• Hades255

  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

  
  

• MarkByers

  Are you able to query now with this answer or still facing some issues

  Thanks,

  Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

  
  
  

• SureshN

  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

  
  
  

• c01dfire

  Try using Parameter.AddWithValue.

  Thanks,

  Laxmi

  
  
  

• Gary Cawley

  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.
  
  
  

• BI-needs-IT

  change the select statement in the adapter wizard to this

• Pooneh

  Can you please try this

  cmd.CommandText = "select * from person where firstname = @p0 AND lastname = 'f l";
  cmd.Parameters.AddWithValue("@p0", " ");
  

  Thanks,

  Laxmi
  

  
  
  

• Star921

  Yes, Blair gave me the answer that I needed. Thanks.
  
  

• BigTimeHacker

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

• johanp

  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.
  
  
  

• Andreas Jaeger

  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.
  
  
  

• Hillgogoangel

  Hey Brian,

  Actually, this is a store proc that is created from within TableAdapter in VS2005.

  Lee

  
  
  

• puresmile

  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.

  
  
• Parameterized Query Using Wildcards in VS2005