SQL search with "like" operator

[Ray S]

I have an Access database with hyperlink fields that I have brought into the dataset. Now I want to search the dataset for the visible portion of the hyperlink field and delete the row I find (I haven't started the error handling yet). Thus I am trying to use the "Like" operator in a SQL statement but keep getting an error. Here is my code.

Dim strSQL As String
strSQL = "[MSRP] like " & "'" & "999" & "*" & "'"
Dim anyRow() As DataRow = DsInvoiceParse1.Table1.Select(strSQL)
DsInvoiceParse1.Table1.Rows.Remove(anyRow(0))

Any ideas how to correct this would be appreciated.

 

[kulrom]

hey your statement is missing some parts (is not complete)
i.e.
strSQL = "SELECT * FROM Table WHERE field LIKE 'a%'"
this will return all records which starts with "a"

Regards

 

[TechGnome]

It would have been nice to know what the error is/was.

FYI: * is the wildcard in Access, while % is used in SQL Server. Not sure what Oracle uses. I think MySQL also uses the % (I suspect it's the ANSI standard but couldn't prove it at this time.)

-tg

 

[kulrom]

As much as i know all the bigger backends (DB2, Oracle, Postgres, mySQL, SQL Server) support the LIKE operator in conjunction with the SQL wildcard operator ("%") to fetch data by particular substring or pattern.

 

[Ray S]

I appreciate everyone's input, so thanks for the help.

I did get it working, although I'm not sure why this works and the original code doesn't. I forget the exact error message I was getting but it was balking at the "Like" expression. All I really did was change fields to the actual Hyperlink field "VIN" (which is the automobile VIN number, hyperlinked to the dealer invoice). I had tried a plain text field first (MSRP), just to test it on a simple field with no hyperlink to reduce the complexity of debugging, but....anyway...... this works and thanks again.

Dim strVIN As String
strVIN = "2G1 WF52 E1 39213072"
Dim strSQL As String
strSQL = "[VIN] like " & "'" & strVIN & "*" & "'"
Dim anyRow() As DataRow = DsInvoiceParse1.Table1.Select(strSQL)
DsInvoiceParse1.Table1.Rows.Remove(anyRow(0))