none
How do I code the WHERE LIKE clause in fill "ListOf" request?

    Question

  • Public Shared Function FillListOfPiece_AssemblyCode(ByVal asmblyCode As String) _ As List(Of Piece) Dim listOfPiece As New List(Of Piece) Dim connection As SqlConnection = WoodCutDB.GetConnection Dim selectStatement As String = "SELECT * FROM PieceTbl " & _ "WHERE (PieceID LIKE 'asmblyCode%')" Dim selectCommand As New SqlCommand(selectStatement, connection) 'selectCommand.Parameters.AddWithValue("@PieceID", asmblyCode) Try connection.Open() Dim reader As SqlDataReader = selectCommand.ExecuteReader() Dim piece As Piece Do While reader.Read

    In the above code block, SQL returns nothing, when I know it's there. I'm trying to find all PieceID="E%". PieceID is a char(3) field where the first byte is always the Assembly Code or *, and the remaining two bytes are the ID for the Piece. I've commented out the select.Command.Parameters.AddWithValue, because it doesn't appear to have any affect whether it's commented or not commented. When the call is made to the code block, asmblyCode=E.

    How should I code the SQL clauses to the result wanted?


    Terry 01


    • Edited by Terry 01 Monday, July 01, 2013 2:53 PM
    Monday, July 01, 2013 2:48 PM

Answers

  • Try

    Dim selectStatement As String = _
      "SELECT * FROM PieceTbl " & _
      "WHERE ( PieceID LIKE @PieceID );"
    

    and include the %-sign:

    selectCommand.Parameters.AddWithValue("@PieceID", asmblyCode & "%")
    

    From the database perspecitve: Using a compound key is in most scenarios not a good idea. But a concrete advice depends on you concrete requirements.
    • Proposed as answer by Olaf HelperMVP Thursday, September 05, 2013 12:26 PM
    • Marked as answer by Terry 01 Thursday, September 05, 2013 6:39 PM
    Thursday, September 05, 2013 12:12 PM

All replies

  • Try

    Dim selectStatement As String = _
      "SELECT * FROM PieceTbl " & _
      "WHERE ( PieceID LIKE @PieceID );"
    

    and include the %-sign:

    selectCommand.Parameters.AddWithValue("@PieceID", asmblyCode & "%")
    

    From the database perspecitve: Using a compound key is in most scenarios not a good idea. But a concrete advice depends on you concrete requirements.
    • Proposed as answer by Olaf HelperMVP Thursday, September 05, 2013 12:26 PM
    • Marked as answer by Terry 01 Thursday, September 05, 2013 6:39 PM
    Thursday, September 05, 2013 12:12 PM
  • Thanks, Stephen.

    Terry 01

    Thursday, September 05, 2013 6:39 PM