0

In my TableAdapter I have a SQL Query like below:

SELECT * FROM Vehicles WHERE (VehicleID LIKE @branchList)

The Variable i pass to @branchList is a simple join on a few CHARS like ABC The problem i have is i need to enclose ABC in Brackets as a need the first character to match any char in the charlist supplied, so i combine the charlist to look like [ABC]%.

below is my VB.NET code to do this and attempt to populate the datagridview:

Try
    Dim br As String = ""
    For Each branch In MenuForm.Branches
        br = br & branch
    Next
    br = "[" & br & "]%"
    Me.VehiclesTableAdapter.FillByBranchList(Me.VehiclesDataSet.Vehicles, br)
Catch ex As Exception
    MsgBox(ex.Message)
End Try

but this yields no results, but when i copy the query to a SQL Server Editor and declare @branchList like this:

DECLARE @branchList as NVARCHAR(MAX) = '[ABC]%'

I get the rows I was expecting.

Am I missing something or is this an issue/limitation with tableadapters?

Steven de Beer
  • 108
  • 1
  • 14
  • Your answer is here [C# constructing parameter query SQL - LIKE %](http://stackoverflow.com/questions/664314/c-sharp-constructing-parameter-query-sql-like) – T.S. Feb 26 '16 at 22:06
  • Apologies but I do not see how your suggested post would assist me. **Brackets** is my keyword here. Meaning anything contained within them should match the first CHAR of the column referred to. Your suggestion refers to an exact match an a full char string. – Steven de Beer Feb 26 '16 at 22:16
  • a simple example of what i need can be found [here](http://www.w3schools.com/sql/sql_wildcards.asp) but i need the data to be populated through a tableadapter – Steven de Beer Feb 26 '16 at 22:19
  • I do understand your syntax and it is basically the same as mine but it yields no results through the table adapter, only through the DB engine – Steven de Beer Feb 26 '16 at 22:22
  • I believe, you [and I] don't have right syntax. 1 sec – T.S. Feb 26 '16 at 22:22
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104683/discussion-between-steven-de-beer-and-t-s). – Steven de Beer Feb 26 '16 at 22:24
  • Try this `VehicleID LIKE '[' + @1 + ']%'` in your adapter query. then set `@wtName` to your ABCs – T.S. Feb 26 '16 at 22:43
  • So, Table Adapter is just a wrapper for IDbDataAdapter and it probably has something to do with query builder. But even if you move to SqlCommand, you still will use this syntax `. . . .Where VehicleID LIKE '[' + @1 + ']%'` – T.S. Feb 27 '16 at 03:45
  • I know. And I know it works using the command method because alot is already built using a dll I wrote through the method. – Steven de Beer Feb 27 '16 at 07:16

0 Answers0