?
Solved

A VB and SQL question

Posted on 2003-03-16
19
Medium Priority
?
206 Views
Last Modified: 2010-05-01
I have a table "Employees" with 3 Rows: Name, Age, Salary.
I have one text box: text1
One listbox: list1

I am trying to write a program that will search names by the name insered in the textbox

For example if i'll insert the name John it will return me all of John's data, but if there isn't John in the table, it will search names starting with Joh.....Jo.......J and return all the names and data relevant (print it in the listbox)

I've tried to use the "Like" option in the SQL statement but it seems to have problems with variables.

Thanks
Alex
0
Comment
Question by:alexel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
19 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 8146878
This doesn't have anything to do with storage......

You shouldn't have a problem with like

select * from employees where name like '%john%'


The only thing about that, you will get John Smith, Mike Johnson.  You really should have last and firstname fields.

Brett
0
 

Author Comment

by:alexel
ID: 8147108
What do you mean it doesn't have anything to do with the storage?
I have a table with 3 columns, the first is FirstName.
I wrote a code:

Dim strName As String
Dim strSQL As String

Text0.SetFocus
strName = Text0.Text

strSQL = "select * from employee where FirstName Like '%strName%'"

List2.RowSource = strSQL


But it doesn't work.
Also, the point is that if I enter a name like Alex and in the database table, in FirstName column I have only Alexander it will return me the full name (same if I'll enter only Ale...).
 
Thanks
Alex
0
 
LVL 34

Expert Comment

by:arbert
ID: 8147153
Well, you posted your question to the storage group--it has nothing to do with storage....


The SQL String you're building is wrong--try this:


Dim strName As String
Dim strSQL As String

Text0.SetFocus
strName = Text0.Text

strSQL = "select * from employee where FirstName Like '%" & strName & "%'"

List2.RowSource = strSQL


Brett

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:alexel
ID: 8148070
Sorry acout posting the question in the wrong area.
Anyway, the SQL String you gave me doesn't return anything.
It does work when I write:

strSQL = "select * from employee where FirstName Like text0.text"

But this case covers only the situation when the FirstName is identical to the text I enter.
What I'm trying to do is to perform a kind of loop? that will downsize the name in a case of incomparment and return all the names starting with the "like" startings (as I showd in me previous comment)
In order to do this I have to use variables which the SQL statement for some reason don't receive.
 
Thanks
Alex
0
 
LVL 1

Expert Comment

by:lamdor
ID: 8148386
Please maintain the topic wise questions..

this is not related to storage it regard to SQL..

 I think what ever arbert told is rite.

regards,
marian.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8149455
What database are you even using?  That sql statement that you posted shouldn't even work--you're pass text0.text as a literal and not the actual variable contents.

strSQL = "select * from employee where FirstName Like text0.text"

What I posted originally should work--it does in SQL Server anyway.

If a users enters Jo, it should return John, Jon, Joe, Johnny etc....

Brett

0
 

Author Comment

by:alexel
ID: 8150644
I'm trying to perform this in Access 2000
0
 

Expert Comment

by:DSaldanaRELTD
ID: 8161738
Try this

strSQL = "select * from employee where Name Like '%" & text1.text & "%'"
0
 
LVL 34

Expert Comment

by:arbert
ID: 8161788
That's what I posted

strSQL = "select * from employee where FirstName Like '%" & strName & "%'"
0
 

Accepted Solution

by:
DSaldanaRELTD earned 120 total points
ID: 8161804
strSQL = "SELECT * FROM employee WHERElName Like 'J*';"
Works!

So try this...
strSQL = "SELECT * FROM employee WHERElName Like '" & text1.text & "'"
0
 

Expert Comment

by:DSaldanaRELTD
ID: 8161851
strSQL = "SELECT * FROM employee WHERElName Like 'J*';"
Works!

So try this...
strSQL = "SELECT * FROM employee WHERElName Like '" & text1.text & "'"
0
 
LVL 34

Expert Comment

by:arbert
ID: 8161900
Yes, Access uses * for like statements and SQL Server uses % for like statements....
0
 

Expert Comment

by:DSaldanaRELTD
ID: 8161962
And since the above says she's using access, it'll work
0
 
LVL 34

Expert Comment

by:arbert
ID: 8718198
What's the status of this question?
0
 
LVL 32

Expert Comment

by:LucF
ID: 10303610
alexel,
No comment has been added lately (240 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Delete/No Refund

Please leave any comments here within 4 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

LucF
EE Cleanup Volunteer
0
 
LVL 34

Expert Comment

by:arbert
ID: 10304227
I disagree--LucF, all of you close "recommendations" that I've seen involve deleting the question--do you actually look at the thread and try and figure out who has the correct answer????
0
 
LVL 32

Expert Comment

by:LucF
ID: 10304493
>>all of you close "recommendations" that I've seen involve deleting the question
Then I guess you haven't seen them all.

This Question has only 30points assigned, I don't think I could make a fair split out of it.
Also, this is not a storage question again.
0
 
LVL 32

Expert Comment

by:LucF
ID: 10347454
<Edited>, thanx for giving away your Moderator ID...
You'll find my answer in the CS Q.

I'm out of here....

thanx all, I'll miss you...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question