SQL Statement Enigma 
Author Message
 SQL Statement Enigma

I have a form containing the following:
- Text Field (txtFirst)
- List Box (lstResults)
- Command Button (cmdFind)

I use ADO on the cmdFind Click event to look at a database and display
retrieved data into a list box.  The code works fine when retrieving an
exact match of txtFirst.  However, the * wildcard does not work.  For
instance, this is a student directory containing many Roberts.  If I enter
"Robert" in txtFirst and click cmdFind, the results return just fine.
Unfortunately, searching on "Rob*" yields no results.  Can anybody tell me
why this isn't working?  Ideally, I would like to build the * into the SQL
statement itself (but that doesn't work either).  Here is my scaled down
code:

Private Sub cmdFind_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim Results As String
Dim ErrObject As ADODB.Error

   cn.Open "DSN=DirectoryDSN"

   strSQL = "SELECT * FROM tblStudents "
   strSQL = strSQL & "WHERE (tblStudents.First LIKE '" & txtFirst.Text &
"')"

   rs.Open strSQL, cn

   Do Until rs.EOF
      Results = rs.Fields("Last") & ", " & rs.Fields("First")
      lstResults.AddItem Results
      rs.MoveNext
   Loop

   rs.Close
   cn.Close

End Sub

Thanks plenty in advance!



Thu, 21 Mar 2002 03:00:00 GMT  
 SQL Statement Enigma
With ADO, you use % as the wildcard, not *

Quote:

> I have a form containing the following:
> - Text Field (txtFirst)
> - List Box (lstResults)
> - Command Button (cmdFind)

> I use ADO on the cmdFind Click event to look at a database and display
> retrieved data into a list box.  The code works fine when retrieving an
> exact match of txtFirst.  However, the * wildcard does not work.  For
> instance, this is a student directory containing many Roberts.  If I enter
> "Robert" in txtFirst and click cmdFind, the results return just fine.
> Unfortunately, searching on "Rob*" yields no results.  Can anybody tell me
> why this isn't working?  Ideally, I would like to build the * into the SQL
> statement itself (but that doesn't work either).  Here is my scaled down
> code:

> Private Sub cmdFind_Click()

> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim strSQL As String
> Dim Results As String
> Dim ErrObject As ADODB.Error

>    cn.Open "DSN=DirectoryDSN"

>    strSQL = "SELECT * FROM tblStudents "
>    strSQL = strSQL & "WHERE (tblStudents.First LIKE '" & txtFirst.Text &
> "')"

>    rs.Open strSQL, cn

>    Do Until rs.EOF
>       Results = rs.Fields("Last") & ", " & rs.Fields("First")
>       lstResults.AddItem Results
>       rs.MoveNext
>    Loop

>    rs.Close
>    cn.Close

> End Sub

> Thanks plenty in advance!

--

Beer, Wine and Database Programming.  What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/



Fri, 22 Mar 2002 03:00:00 GMT  
 SQL Statement Enigma
Use % as the wildcard character.

-Dan Beacom

Quote:

>I have a form containing the following:
>- Text Field (txtFirst)
>- List Box (lstResults)
>- Command Button (cmdFind)

>I use ADO on the cmdFind Click event to look at a database and display
>retrieved data into a list box.  The code works fine when retrieving an
>exact match of txtFirst.  However, the * wildcard does not work.  For
>instance, this is a student directory containing many Roberts.  If I enter
>"Robert" in txtFirst and click cmdFind, the results return just fine.
>Unfortunately, searching on "Rob*" yields no results.  Can anybody tell me
>why this isn't working?  Ideally, I would like to build the * into the SQL
>statement itself (but that doesn't work either).  Here is my scaled down
>code:

>Private Sub cmdFind_Click()

>Dim cn As New ADODB.Connection
>Dim rs As New ADODB.Recordset
>Dim strSQL As String
>Dim Results As String
>Dim ErrObject As ADODB.Error

>   cn.Open "DSN=DirectoryDSN"

>   strSQL = "SELECT * FROM tblStudents "
>   strSQL = strSQL & "WHERE (tblStudents.First LIKE '" & txtFirst.Text &
>"')"

>   rs.Open strSQL, cn

>   Do Until rs.EOF
>      Results = rs.Fields("Last") & ", " & rs.Fields("First")
>      lstResults.AddItem Results
>      rs.MoveNext
>   Loop

>   rs.Close
>   cn.Close

>End Sub

>Thanks plenty in advance!



Fri, 22 Mar 2002 03:00:00 GMT  
 SQL Statement Enigma
Hi, my is some thing like that, only that my code find only the name that
begin this let said Paul, i found Paullyn, Paulson,Paulisen.....but Names
like Joan Paul or Mike Paul are not found....meaning that i need help in how
to search in a biblio database (vb5 demo database), when user type Paul they
are able to find Joan Paul and also Paulson, Ed if the "," in the name the
problem or the code that i have

thank and follow is the code ( im sending the result to a dbgrid ) the
commandbutton, dbgrid and data are microsoft

Private Sub Command1_Click()

Dim sInput As String
    sInput = InputBox$("enter a name:")

Set Data1.Recordset = Data1.Database.OpenRecordset _
    ("Select author from authors where author like '" & sInput & "*'",
dbOpenDynaset)

End Sub


Quote:
> With ADO, you use % as the wildcard, not *


> > I have a form containing the following:
> > - Text Field (txtFirst)
> > - List Box (lstResults)
> > - Command Button (cmdFind)

> > I use ADO on the cmdFind Click event to look at a database and display
> > retrieved data into a list box.  The code works fine when retrieving an
> > exact match of txtFirst.  However, the * wildcard does not work.  For
> > instance, this is a student directory containing many Roberts.  If I
enter
> > "Robert" in txtFirst and click cmdFind, the results return just fine.
> > Unfortunately, searching on "Rob*" yields no results.  Can anybody tell
me
> > why this isn't working?  Ideally, I would like to build the * into the
SQL
> > statement itself (but that doesn't work either).  Here is my scaled down
> > code:

> > Private Sub cmdFind_Click()

> > Dim cn As New ADODB.Connection
> > Dim rs As New ADODB.Recordset
> > Dim strSQL As String
> > Dim Results As String
> > Dim ErrObject As ADODB.Error

> >    cn.Open "DSN=DirectoryDSN"

> >    strSQL = "SELECT * FROM tblStudents "
> >    strSQL = strSQL & "WHERE (tblStudents.First LIKE '" & txtFirst.Text &
> > "')"

> >    rs.Open strSQL, cn

> >    Do Until rs.EOF
> >       Results = rs.Fields("Last") & ", " & rs.Fields("First")
> >       lstResults.AddItem Results
> >       rs.MoveNext
> >    Loop

> >    rs.Close
> >    cn.Close

> > End Sub

> > Thanks plenty in advance!

> --

> Beer, Wine and Database Programming.  What could be better?
> Visit "Doug Steele's Beer and Programming Emporium"
> http://webhome.idirect.com/~djsteele/



Fri, 22 Mar 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. running sql code record by record instead of statement by statement

2. Dynamic SQL Statement With SQL 6.5

3. SQL -- Needing a Hint with SQL statements.

4. SQL statement won't consistently pass from VB to SQL

5. OCX for editing SQL-Statements (SQL-Server)

6. SQL query: from SELECT statement to stored procedure with dynamic PL/SQL

7. SQL statement updateable in Acces but not SQL Server

8. Problem on SQL-Update Statement with a Data-Control in VB 5 and SQL Server 7.0

9. Write One SQL statement execute in Oracle and SQL Server

10. Extracting SQL Statements from Views - SQL Server

11. SQL vs JET - SQL Statement Syntax

12. Sigle quote problem in SQL Statement in SQL

 

 
Powered by phpBB® Forum Software