Okay, so here I am, working on a project for my CASE/4GL class, and I want to implement a search through a database, and perform matching on three columns in a table. The columns are ProductID (needs an exact match), ProductName (I want to match partials with SQL LIKE), and ProductBrand (which I also want to match partials, but that can wait until I get LIKE working). The language is VB.NET from VS.NET 2003, and the database is currently a single-table Access database. Language and database formats are fixed by the instructor, so I can't substitute a different language or database engine.
The problem is, no matter how I try to do it, it won't work. My code:
' strCN is properly initialized (I can connect to the database just
' fine), and sortOrderString is initialized to the name of the column
' I want to sort on (usually 'ProductName').
Public Overloads Function Find(ByVal aKey As String) As DataTable Implements IProductDA.Find
Dim objCN As OleDbConnection = New OleDbConnection(strCN)
Dim objDA As New OleDbDataAdapter
Dim objDS As New DataSet
Dim strSQL As String
strSQL = "SELECT * FROM Products WHERE ((ProductID=@ProductID) "
strSQL += "OR (ProductName LIKE '%@ProductName%') "
strSQL += "OR (ProductBrand=@ProductBrand) "
strSQL += "ORDER BY " + sortOrderString
objDA = New OleDbDataAdapter(strSQL, objCN)
objDA.SelectCommand.Parameters.Add("@ProductID", aKey)
objDA.SelectCommand.Parameters.Add("@ProductName", aKey)
objDA.SelectCommand.Parameters.Add("@ProductBrand", aKey)
objCN.Open()
objDA.Fill(objDS, "FindResults")
objCN.Close()
If objDS.Tables("FindResults").Rows.Count > 0 Then
Return objDS.Tables("FindResults")
Else
Return Nothing
End If
End Function I have tried various permutations, such as "ProductName LIKE %@ProductName%", "ProductName LIKE '%@ProductName%'", removing the @ProductName placeholder and putting in literal text, etc, all to no avail. Is there any way to get LIKE to work through VB.NET's bastardized SQL, or some alternative I can use to accomplish the same task?