Displaying specific Data from SQL Database without a Column to filter by. (VB.NET)

Question

I have a DataGridView which is displaying Data from dbo.tblAttendanceTimes using tblAttendanceTimesTableAdapter.GetDataBy(intEmployee)

So obviously it will display all the Data for the selected Employee.

The Employees are split in to 3 groups. What i would like to do is display all records that have a value of 0 in the [intApprovedOvertime] Column in dbo.tblAttendanceTimes for the particular group i am viewing.

Just with the GetDataBy(intEmployee) is making that difficult, there is no [intAttendanceGroup] Column in that Table or i could just GetDataBy(intAttendanceGroup)

At the moment i have all the pk Values` for the Employees in the group but i'm struggling to retrieve all records for that Employee display them then do the next Employee and add to previously displayed.

I hope i am making sense, please ask for any other information you'd like.

Answer 1

Two solutions can be interesting:

• Linking the database result directly to the DGV very good for easy update but less to move the rows if that's needed
• Inserting the data from the query as rows for that you need first define the columns of the DGV DataGridView2.Columns.Add()

1/

Using con As New SqlClient.SqlConnection("YOUR SQL CONNECTION STRING")
    Dim sqlString As String = "SELECT * FROM Table WHERE intApprovedOvertime = 0 AND fkEmployee = pkEmployee"
    con.Open()
    Dim da As New SqlClient.SqlDataAdapter(sqlString, con)
    Dim ds As New DataSet
    da.Fill(ds, "Table")
    DataGridView1.DataSource = ds.Tables("Table")
    ds.Dispose()
    da.Dispose()
    SqlConnection.ClearPool(con)
End Using

2/

Using con As New SqlClient.SqlConnection("YOUR SQL CONNECTION STRING")
 Dim command As New SqlCommand("SELECT * FROM Table WHERE intApprovedOvertime = 0 AND fkEmployee = pkEmployee", con)
        Dim reader As SqlDataReader = command.ExecuteReader()
        While reader.Read()
            DataGridView1.Columns.Rows.Add(reader(0), reader(1))
        End While
End Using

In number 2 you need to remember to adapt reader(x) to the number of values you need to add to your Row. You can also get just certain column through:

"SELECT Table.Name, Table.Position FROM Table WHERE intApprovedOvertime = 0 AND fkEmployee = pkEmployee"

There might be few little syntax mistakes I didn't have the IDE with me haha.