How to select either one table to access

Question

I got two table where is table_staff and table_customer, i want to do either one user can log in with using their data(id&password), below is my code to when click "Send" button, it didn't work. Did anybody found my error?

Private Sub btn_send_Click(sender As Object, e As EventArgs) Handles btn_send.Click

        Dim mysql As String = "SELECT (SELECT count(*) as num_matches FROM tbl_staff where staff_id = """ & txt_name.Text & """ And staff_pwd = """ & txt_pwd.Text & """ ) As 1, (SELECT count(*) as num_matches FROM tbl_customer where customer_id = """ & txt_name.Text & """ And customer_pwd = """ & txt_pwd.Text & """ ) As 2"
        Dim mydatatable As New DataTable
        Dim myreader As New OleDb.OleDbDataAdapter(mysql, myconnection)
        myreader.Fill(mydatatable)
        Dim num_matches As String = mydatatable.Rows(0).Item("num_matches")
        If num_matches = 1 Or num_matches = 2 Then
            frm_mainmenu_a146292.Show()
            Me.Hide()
        Else
            txt_name.Text = ""
            txt_pwd.Text = ""
            MsgBox("Incorrect Username or Password")
        End If

End Sub

Yes!, I've found the `DidntWorkException`. You need to press the button. — Bjørn-Roger Kringsjå, Jan 05 '15 at 16:19
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Jan 05 '15 at 16:19
http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i . Also you should hash (not encrypt) passwords, and the use of an adapter seems overkill — Ňɏssa Pøngjǣrdenlarp, Jan 05 '15 at 16:40

b.pell · Accepted Answer · 2015-01-05T17:11:35.370

First, you should parameterize your SQL (I will provide an example). That will protect you from SQL injection exploits and it will keep your query from crashing if the user enters a character like a quote that breaks the SQL.

    Using conn As New OleDb.OleDbConnection("Your Connection String")
        conn.Open()

        Using cmd As OleDb.OleDbCommand = conn.CreateCommand
            cmd.CommandText = <string>
                                  select staff_id as [id] from tbl_staff where staff_id = @username and staff_pwd = @password
                                  union
                                  select customer_id as [id] from tbl_customer where customer_id = @username and customer_pwd = @password
                              </string>
            cmd.Parameters.AddWithValue("@username", txt_name.Text)
            cmd.Parameters.AddWithValue("@password", txt_pwd.Text)

            Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader

            If dr.HasRows = True Then
                ' Their login was succesful
            Else
                ' Login failure
            End If

            dr.Close()
        End Using

        conn.Close()
    End Using

Side note, related but not, if you have the means you probably want to encrypt the password that is being stored.

score 0 · Answer 2 · answered Jan 05 '15 at 16:18

I'm not sure which database engine you're using, but you cannot generally use numeric column names like "1" and "2". Use "S" and "C" instead.
You may have to use single quotes in the SQL text instead of double quotes around literal values.
You need to access 2 column values rather than retrieving a single value from the recordset into num_matches. For example Staff_Matches = mydatatable.rows(0).Item("S") and Customer_Matches = mydatatable.rows(0).Item("C"). Then check if one of them is greater than 0.

How to select either one table to access

2 Answers2