Click here to Skip to main content
14,927,127 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Greetings!

I am writing a VB.NET program that will be connecting to a SQL database programmically.

I have several SQL Statements that work just fine, however, one SQL Statements does not seem to work....

I have 2 tables in a database.

Table1
- employeeID
- employeName

Table2
- employeeID
- employeeBatchNumber
- employeezipcode
-employeephonenumber
-employeedeleted

What I need to do is search based on a text value from a combobox (employee name), however, I need to match employeeID's together to get the Table2 data..


What I have now is:

("Select Table2.employeeBatchNumber, Table2.employeezipcode, Table2.employeephonenumber, Table1.employeeName, Table1.employeeID from Table1, Table2 where Table2.employeedeleted = 'false' and Table2.employeeName like %" & ComboBox1.Text & "%' and Table1.employeeID = Table2.employee)", con)

this will fill a datagrid view with the results, however, it does not....

Can anyone look over my SQL Statement and let me know if you see anything wrong???

thanks in advanced

daveofgv
Posted

You need to use a join.

Try this:

SQL
SELECT t2.employeeBatchNumber, t2.employeezipcode, t2.employeephonenumber, t1.employeeName,    t1.employeeID
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.employeeID = t2.employeeID
WHERE t2.employeedeleted = 'false' AND t2.employeeName LIKE @textBox 


Then use a SqlCommand object and add the parameter. You need to look into SQL injections because as your code is now, someone how knows SQL could wipe out all your tables. It is very insecure.
   
C#
string EmpName = ComboBox1.Text;

SqlCommand cmd = new SqlCommand("Select T2.employeeBatchNumber, T2.employeezipcode, T2.employeephonenumber, T1.employeeName, T1.employeeID FROM Table1 T1 INNER JOIN Table2 T2 ON T2.employeeID = T1.employeedID WHERE T2.employeedeleted = 0 AND T2.employeeName LIKE '"+EmpName+"'",con);


This is what it looks like with the very insecure way you're trying to do it. This is unacceptable in any real world environment though as ryan said, but just to give you a solution on the code you've provided this would be it.
   
v2
Thank you for the reply..... With your answer - I got it to work... :)

I have accepted your answer and this can be closed.
   
Comments
JasonMacD 25-Apr-13 16:50pm
   
If you'd like to comment on a solution use the 'Have a Question of Comment?' link to add a comment. This will keep you from getting down voted.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900