Retrieve data using DataReader from multiple tables in mySQL

Question

How would I retrieve the data from the 'DAILY_CALCULATIONS' table

  Dim SqlQuery As String = "SELECT WEEKLY_TIMECARD.*,DAILY_CALCULATIONS.*,EMPLOYEE_PROFILES.EMPLOYEE_NUMBER "
    SqlQuery = SqlQuery + " FROM WEEKLY_TIMECARD, DAILY_CALCULATIONS, employee_profiles WHERE employee_profiles.employee_number"
    SqlQuery = SqlQuery + " = WEEKLY_TIMECARD.employee_number AND EMPLOYEE_PROFILES.EMPLOYEE_NUMBER = DAILY_CALCULATIONS.EMPLOYEE_NUMBER AND"
    SqlQuery = SqlQuery + " WEEKLY_TIMECARD.WEEK_NUMBER = DAILY_CALCULATIONS.WEEK_NUMBER AND WEEKLY_TIMECARD.EMPLOYEE_NUMBER ="
    SqlQuery = SqlQuery + "'" + txtENumber.Text + "' AND WEEKLY_TIMECARD.WEEK_NUMBER = '" + CBWeekNum.SelectedItem + "'"

    Try

        con.Open()

        If con.State = ConnectionState.Open Then
            con.ChangeDatabase("shopclocksoftware")
            Command = New MySqlCommand(SqlQuery, con)
            Data = Command.ExecuteReader()

            While Data.Read
                txtHUnits.Text = Data(2).ToString
                txtOUnits.Text = Data(3).ToString
            End While

I had a look at the Data.NextResult method, but I wasn't able to implement it properly, Any ideas?

You aren't calling the method above so what exactly is your issue? — Daniel Kelley, Feb 07 '13 at 16:32
You are also open to SQL injection attacks with the above code. You should parameterize your SQL to avoid this. — Daniel Kelley, Feb 07 '13 at 16:48
I don't understand what you mean, my while Data.Read part returns values from my 'WEEKLY_TIMECARD' table .. I'd like to also call from 'DAILY_CALCULATIONS' — Thaitan, Feb 07 '13 at 16:59
From what I can see you have 1 query that will return a single result set with many columns. `NextResult` is for scenarios where you have multiple selects and so multiple result sets. Your `Data.Read` will return every single column. — Daniel Kelley, Feb 07 '13 at 17:08
So, what will my data(number) be If i'm retrieving from 'weekly_timecard' 4 columns and 'daily_calculations' 7 columns .. say if I wanted to just retrieve the 3rd data item from daily calculations would it be (data(7).ToString) ? — Thaitan, Feb 07 '13 at 17:11
That's correct. However, you could make life a bit easier for yourself by changing your query. I will post an answer with some suggestions. — Daniel Kelley, Feb 07 '13 at 17:13

score 0 · Accepted Answer · answered Feb 07 '13 at 17:29

Dim SqlQuery As String = "SELECT wtc.A, wtc.B, wtc.C, dc.D, dc.E, ep.F, ep.G"
SqlQuery = SqlQuery + " FROM WEEKLY_TIMECARD wtc, DAILY_CALCULATIONS dc, employee_profiles ep "
SqlQuery = SqlQuery + " WHERE ep.employee_number"
SqlQuery = SqlQuery + " = wtc.employee_number AND ep.EMPLOYEE_NUMBER = dc.EMPLOYEE_NUMBER AND"
SqlQuery = SqlQuery + " wtc.WEEK_NUMBER = dc.WEEK_NUMBER AND wtc.EMPLOYEE_NUMBER ="
SqlQuery = SqlQuery + "@ENumber AND wtc.WEEK_NUMBER = @CBWeekNum"

Try
    con.Open()

    If con.State = ConnectionState.Open Then
        con.ChangeDatabase("shopclocksoftware")
        Command = New MySqlCommand(SqlQuery, con)
        Command.Parameters.AddWithValue("@ENumber", txtENumber.Text);
        Command.Parameters.AddWithValue("@CBWeekNum", CBWeekNum.SelectedItem);

        Data = Command.ExecuteReader()

        Dim aOrdinal As Integer = reader.GetOrdinal("a")
        Dim bOrdinal As Integer = reader.GetOrdinal("b")

        While Data.Read
            txtHUnits.Text = Data.GetString(aOrdinal)
            txtOUnits.Text = Data.GetString(bOrdinal)
        End While
....
Exit Try

A few things to note:

I changed your SQL to use aliases, and to return explicit field names. using * is generally a bad idea, especially if you only actually need a subset of columns.
The query is now parameterized, so you aren't open to SQL injection attacks
I make use of GetOrdinal to locate the position of any column in the result set. This is a minor optimisation.
Call GetString if the column you are returning is a string. Otherwise you can call as previously.

Obviously I don't now your column names so the SQL won't work but you should get the idea.

Retrieve data using DataReader from multiple tables in mySQL

1 Answers1