Lookup and retrieve values for each DataGrid row to add and populate a second column within (From SQ-CodePudding

everyone.

I am trying to build a project to record production faults by test operatives.

I have a code which will retrieve operator ID's that worked on that specific job.

CODE:

Private Sub TextBox1_Leave(sender As Object, e As EventArgs) Handles TextBox1.Leave
        Dim sqlconn As New SqlClient.SqlConnection
        Dim CurSel As String = TextBox1.Text

        sqlconn.ConnectionString = "server = ESS/SQL;" &
       "Database = Maindb;integrated security=true"

        sqlconn.Open()

        Dim CurList3 As New SqlClient.SqlCommand("SELECT Employee_Clock_No FROM wip.WIP_Book_Trans WHERE WO_No ='" & CurSel & "'", sqlconn)

        Dim dt3 As New DataTable()
        dt3.Load(CurList3.ExecuteReader)

        DataGridView1.DataSource = dt3

        sqlconn.Close()
    End Sub

What I am trying to achieve is to add a second column to the DataGridView that will theoretically lookup and return the Employee name beside their code from a different SQL table

See image below Employee_Clock_No -> Employee_Name

Thanks in advance.

SQL to return datatable in datagridview and further return information to a different column based on values in the first column.

CodePudding user response：

Welcome to the wonderful world of JOINs. These are a mainstay of SQL, so get comfortable with them. Also, you NEED to use parameterized queries rather than string concatenation to include user data with the SQL statement. Anything else will eventually get you in big trouble, usually sooner than later.

Private Const connectionString As String = "server = ESS/SQL;Database = Maindb;integrated security=true"

Private Sub TextBox1_Leave(sender As Object, e As EventArgs) Handles TextBox1.Leave
    Dim SQL As String = "
        SELECT bt.Employee_Clock_No, ed.Employee_Name
        FROM wip.WIP_Book_Trans bt 
        INNER JOIN personnel.Employee_Details ed ON ed.Employee_Clock_No = bt.Employee_Clock_No
        WHERE bt.WO_No = @WorkOrder"

    Dim dt As New DataTable()
    Using conn As New SqlClient.SqlConnection(connectionString), _
          cmd  As New SqlClient.SqlCommand(SQL, conn)

        cmd.Parameters.Add("@WorkOrder", SqlDbType.VarChar, 8).Value = TextBox1.Text

        conn.Open()
        dt.Load(cmd.ExecuteReader())
    End Using
    DataGridView1.DataSource = dt

End Sub

Adjust accordingly if the WO_No column is really an integer.

Also, note the Using block. This handles closing the connection for, and does it in a much safer way than calling .Close() yourself. As it was, if an exception was thrown (which, given the lack of query parameters, can happen at any time) the .Close() line would never run, leaving the open connection hanging. Do that enough, and you can lock everyone out of the database.