Parameterized queries

Parameterized queries

• MARL

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

• Vaibhav_Patel

  let me see the code for setting the parameter values
  
  

• de_henny

  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.

  
  
  

• Alex Jimenez

  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

  ---

  
  
  

• Dido44538

  If you have a data layer, that contains all your queries, you can do it like this:
  
  command.Parameters["theParameter"].Value = theValue + "%"
  
  
  
  
  

• manutdp

  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.

  
  
  

• Henk van Andel

  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.

   

  
  
  

• Groenewald

  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.

  
  
  

• Eric-Jan

  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.
  
  

• antonio97b

  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

  
  
  

• jwdenny

  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

  
  
  

• JayKay

  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

  
  
  

• BarBQ

  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

  
  
  

• danglen

  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