How to search in two tables in vb.net

Question

I have a problem regarding our RFID log in system project, if i only query one table (Student) it shows the data correctly but our system includes not only the log in of the students but also for the faculty and staff but when i put this query

With cmd
        .Connection = cn
        .CommandText = "SELECT Firstname,IDnum FROM Students,Faculty WHERE RFID = '" & TextBox1.Text & "'"

    End With

it should show the data equal to the rfid if it is equal to either in the student table or faculty, i don't know what to do next please help

Before you do anything else, please, please, please read up on SQL injection and parameterized queries. This is a lawsuit waiting to happen. — Aaron Bertrand, Jan 23 '15 at 17:53
http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Ňɏssa Pøngjǣrdenlarp, Jan 23 '15 at 19:27

mrtig · Accepted Answer · 2015-01-23T19:03:42.073

0

You need to specify the table and column in the where clause. It looks like the RFID column is unique to one of the two tables.

Here's an example (assuming RFID belongs to the students table):

With cmd
        .Connection = cn
        .CommandText = "SELECT Firstname,IDnum FROM Students s,Faculty f WHERE  '" & TextBox1.Text & "' in (f.<RFID Column Name>, s.RFID)"

End With

Another approach is to union the searches in your tables:

With cmd
     .Connection = cn
     .CommandText = _
         "SELECT Firstname,IDnum FROM Students s " + _
         "WHERE  '" & TextBox1.Text & "' = s.RFID " & _
         "UNION " + _
         "SELECT Firstname,IDnum FROM faculty f " + _
         "WHERE  '" & TextBox1.Text & "' = f.RFID "
End With

edited Jan 23 '15 at 19:03

answered Jan 23 '15 at 17:55

mrtig

2,217
16
26

i dont get an error but even if i entered the correct id for the faculty it display another info from the students table – Men8 Burger Jan 23 '15 at 18:40
That looks like because the columns FirstName, IDNum are from the `students` table. You might consider doing a union of searches in the two tables. I'll update the answer. – mrtig Jan 23 '15 at 18:42
Added alternative answer. If you have any other issues please update your post with concrete data model details (ie table definitions for students and faculty). – mrtig Jan 23 '15 at 19:07
tnx! it works! but when i want to include an image to be displayed i get this error "The image data type cannot be selected as DISTINCT because it is not comparable." – Men8 Burger Jan 23 '15 at 19:37
You would then need to join the unique `union` set to whatever contains the image data. – mrtig Jan 23 '15 at 19:55
tnx i have fixed it!..what if i want to show a data that does not exist in the other table? – Men8 Burger Jan 26 '15 at 06:58

How to search in two tables in vb.net

1 Answers1