2

I have tried to pass parameter a value, but what it returns is nothing. If I use a simple sql command without parameter I can retrieve all the data from my database. Here is my code:

Public Shared Function RetrieveData() As DataTable
    Connection.Open()
    Try
        sql = "SELECT DEP_ID AS CODE, DEP_NAME AS DEPARTMENT FROM DEPART_TBL WHERE " & FieldName & " LIKE N'%@criteria%'"
        da = New SqlDataAdapter
        da.SelectCommand = New SqlCommand(sql, Connection.SQLConnection)
        da.SelectCommand.Parameters.Add("@criteria", SqlDbType.NChar)
        da.SelectCommand.Parameters("@criteria").Value = "KCL"
        'da.SelectCommand.Parameters.Add("@criteria", SqlDbType.NChar, 10, "DEP_ID")
        'Message.ShowInfo(da.SelectCommand.CommandText.ToString)
        'Exit Function
        dt = New DataTable
        da.Fill(dt)
    Catch ex As Exception
        Message.ShowError(ex.Message)
    End Try
    da.Dispose()
    Connection.Close()
    Return dt
End Function
F0XS
  • 1,271
  • 3
  • 15
  • 19
Sophart
  • 67
  • 1
  • 10
  • Not related to the question, but you can assign a value to your variable at the same time as adding it to the command, also it is a good idea to specify length when passing string parameters. So your code might be - `da.SelectCommand.Parameters.Add("@criteria", SqlDbType.NChar, 50).Value = "KCL"` – GarethD Sep 27 '17 at 07:53

1 Answers1

2

You are searching the string @criteria, you want to use the parameter, so use:

sql = "SELECT DEP_ID AS CODE, DEP_NAME AS DEPARTMENT FROM DEPART_TBL WHERE " & FieldName & " LIKE N'%' + @criteria + '%'"

or you can use it in the parameter:

da.SelectCommand.Parameters("@criteria").Value = "%KCL%"
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939