• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

A VB and SQL question

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
alexel
Asked:
alexel
  • 7
  • 4
  • 3
  • +2
1 Solution
 
arbertCommented:
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
 
alexelAuthor Commented:
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
 
arbertCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
alexelAuthor Commented:
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
 
lamdorCommented:
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
 
arbertCommented:
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
 
alexelAuthor Commented:
I'm trying to perform this in Access 2000
0
 
DSaldanaRELTDCommented:
Try this

strSQL = "select * from employee where Name Like '%" & text1.text & "%'"
0
 
arbertCommented:
That's what I posted

strSQL = "select * from employee where FirstName Like '%" & strName & "%'"
0
 
DSaldanaRELTDCommented:
strSQL = "SELECT * FROM employee WHERElName Like 'J*';"
Works!

So try this...
strSQL = "SELECT * FROM employee WHERElName Like '" & text1.text & "'"
0
 
DSaldanaRELTDCommented:
strSQL = "SELECT * FROM employee WHERElName Like 'J*';"
Works!

So try this...
strSQL = "SELECT * FROM employee WHERElName Like '" & text1.text & "'"
0
 
arbertCommented:
Yes, Access uses * for like statements and SQL Server uses % for like statements....
0
 
DSaldanaRELTDCommented:
And since the above says she's using access, it'll work
0
 
arbertCommented:
What's the status of this question?
0
 
LucFCommented:
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
 
arbertCommented:
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
 
LucFCommented:
>>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
 
LucFCommented:
<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

Independent Software Vendors: 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!

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now