Programming Forums > Application Development > Visual Basic .NET

lectricpharaoh · Dec 5th, 2005, 2:15 AM

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:

(Toggle Plain Text)

' 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?

Dec 5th, 2005, 2:15 AM	#1
lectricpharaoh SEXY SHOELESS GOD OF WAR! Join Date: Jun 2005 Location: Wet west coast of Canada Posts: 1,183 Rep Power: 5	SQL 'LIKE' problems in VB.NET with OleDb 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: (Toggle Plain Text) ' 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 ' 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? __________________ And once again, Probability proves itself willing to sneak into a back alley and service Drama as would a copper-piece harlot. - Vaarsuvius, Order of the Stick

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic .NET

SQL 'LIKE' problems in VB.NET with OleDb