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

    Frage

  • 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


    • Bearbeitet Terry 01 Montag, 1. Juli 2013 14:53
    Montag, 1. Juli 2013 14:48

Antworten

  • 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.
    • Als Antwort vorgeschlagen Olaf HelperMVP Donnerstag, 5. September 2013 12:26
    • Als Antwort markiert Terry 01 Donnerstag, 5. September 2013 18:39
    Donnerstag, 5. September 2013 12:12

Alle Antworten

  • 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.
    • Als Antwort vorgeschlagen Olaf HelperMVP Donnerstag, 5. September 2013 12:26
    • Als Antwort markiert Terry 01 Donnerstag, 5. September 2013 18:39
    Donnerstag, 5. September 2013 12:12
  • Thanks, Stephen.

    Terry 01

    Donnerstag, 5. September 2013 18:39