-
Aug 25th, 2015, 11:52 AM
#1
[RESOLVED] VB6 using Like Operator in VB6 and Access
Never liked the LIKE operator, so steered away from this easy way of searching, but now it is the best way for my current project.
What AM I doing wrong with this visual basic 6.0 statement?
Code:
ssql = "select Firstname from myTable where trim(LASTNAME) like 'B*'"
I obviously want to find all lastnames that start with the letter "B", but even though I have "BROWN" as a last name (for example), that query does not find it.
I know it has to be simple, but can't get this one to work. Access uses the *, and SQLPLUS uses the %--have tried most things, and the obvious is not obvious to me. Sure could use a boost here.
Thanks
-
Aug 25th, 2015, 11:57 AM
#2
Re: VB6 using Like Operator in VB6 and Access
Select Lastname instead of Select Firstname ?
-
Aug 25th, 2015, 12:09 PM
#3
Re: VB6 using Like Operator in VB6 and Access
no...same thing...those are not really the fields I'm using in my project, just an example.
I'll put together a small program that mimics this behavior...maybe that will help....
-
Aug 25th, 2015, 12:27 PM
#4
Re: VB6 using Like Operator in VB6 and Access
Here's an example program that shows I am somehow improperly using the LIKE operator in my query.
Attachment 129609
code is below, but program has sample db and vb project.
Code:
Option Explicit
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Command1_Click()
List1.Clear
Set rs = New ADODB.Recordset
Dim ssql As String
ssql = "select LASTNAME, FIRSTNAME from EMPS"
Set rs = cnn.Execute(ssql)
Do While Not rs.EOF
List1.AddItem (rs!lastname & ", " & rs!firstname)
rs.MoveNext
Loop
End Sub
Private Sub Command2_Click()
List1.Clear
Set rs = New ADODB.Recordset
Dim ssql As String
ssql = "select LASTNAME, FIRSTNAME from EMPS where LASTNAME like 'B*'"
Set rs = cnn.Execute(ssql)
Do While Not rs.EOF
List1.AddItem (rs!lastname & ", " & rs!firstname)
rs.MoveNext
Loop
End Sub
Private Sub Form_Load()
Dim ConnectionString As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\db1.accdb;"
cnn.Open ConnectionString
rs.CursorLocation = adUseClient
rs.Open "emps", cnn, adOpenKeyset, adLockPessimistic, adCmdTable
End Sub
-
Aug 25th, 2015, 12:31 PM
#5
Re: VB6 using Like Operator in VB6 and Access
NEVER MIND....got it.....DO use the % sign, NOT the *...ADO thing...jeez...wasted three hours!
thanks for looking....
Sammi
-
Aug 25th, 2015, 12:42 PM
#6
Re: [RESOLVED] VB6 using Like Operator in VB6 and Access
If you read the documentation that comes with VB6 there is a discussion of Jet SQL syntax that covers this operator.
DAO uses an older SQL syntax similar to the SQL-89 standard. In this version of the language LIKE patterns are formed similarly to VB's LIKE patterns, probably because SQL-89 didn't provide a definition.
The Jet 4.0 OLEDB Provider (normal choice when using ADO) or the later ACE OLEDB Providers are used a newer syntax similar to SQL-92 is used. By then LIKE patterns were defined, and for whatever reason "%" was chosen instead of "*" and "_" instead of "?" so you should make those substitutions.
See your October 2001 MSDN Library CDs, topic: "Fundamental Microsoft Jet SQL for Access 2000."
Last edited by dilettante; Aug 25th, 2015 at 12:43 PM.
Reason: oops, too slow!
-
Aug 25th, 2015, 12:54 PM
#7
Re: [RESOLVED] VB6 using Like Operator in VB6 and Access
Thx dile.....shoulda/coulda done that. I DID search forums however, and not ONCE could I find that ADO (er, Jet 4.0 and ACE providers, excuse me) uses these 'NEW' LIKE patterns.
-
Aug 25th, 2015, 01:24 PM
#8
Re: [RESOLVED] VB6 using Like Operator in VB6 and Access
Keep in mind that you can use ADO through the thunking layer (MSDASQL Provider) to reach an ODBC Driver. While sloppy and wasteful it works, but you must use the old-style SQL syntax then because every Jet ODBC Driver I ever saw uses the DAO SQL syntax.
ADO itself knows nothing about SQL and doesn't look at it at all but merely passes it through.
OLE DB Provider for ODBC
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|