Combining objects in a user control in Visual Studio/

I need to create a user control that looks like the image in the attached pdf.

The first two columns will be a datagridview from a SQL query.  I know how to do this.

The 3rd column is a combo box that will be populated by a SQL query so the user can use the drop down to select a customer ID.  I know how to do this.

The 4th column with be a text box or a grid view or something ( I don't know what control to use for this).  But when the Customer ID in column 3 is selected, T-SQL will pull the corresponding customer name from the Customer table and supply the name in column 4.  I know how to write the SQL to retrieve the Customer Name, but I'm not sure what kind of control to use for Column 4.

Then I need to put them all together so for each line in the datagridview (columns 1 and 2), there is a corresponding line in column 3 that can be used for drop down selection and a line in column 4 that can be populated by the Customer Name as soon as the Customer ID is selected.
T HoecherlDeveloperAsked:
Who is Participating?
Vadim RappConnect With a Mentor Commented:
OK, let's look at the 1st post.

The code in it represents the following trivial example. Once the user has made selection in the combobox, and then left the cell with the combobox by clicking another cell, the event "cellendedit" fires up. Of course, you can choose another event, such as the event of selecting an entry in the dropdown. Which one is better to choose mostly depends on what your users are used to - leaving the control as the way to commit the change is very familiar to the users of the applications written in Microsoft Access, for instance.

Once the event fires up, the code looks at the selected value, which is found in sender.Rows(e.RowIndex).Cells(e.ColumnIndex).Value (by the way, note that we have changed the declaration of the sender in the parameters from "as object" to "as datagridview" which it actually is - so it becomes more strong-typed, and you can enjoy intellisense on it) . If the value starts with letter "s", then the code places value "yes" in the cell "col2". If it does not start with "s", then it puts "no".

This is of course totally artificial toy code which only purpose is to demonstrate how to look at the value selected in the datagrid cell and to set another cell according to some logic. That's why I said that in your real code instead of "if starts with "s" then put "yes"", you will probably use database lookup - which you actually do.

Regarding Linq query. Be prepared to somewhat overwhelming learning curve (Intellisense helps tremendously), but I guarantee that once you get through, you will be glad you did, for many years and projects.
Vadim RappCommented:
Here's one way to do the first part: "col2" in the grid is unbound textbox.

    Private Sub DataGridView1_CellEndEdit(ByVal sender As DataGridView, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        If sender.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.StartsWith("s") Then
            sender.Rows(e.RowIndex).Cells("col2").Value = "yes"
            sender.Rows(e.RowIndex).Cells("col2").Value = "no"
        End If
    End Sub

Open in new window

Instead of this trivial "yes/no", you will probably want to use database lookup based on sender.Rows(e.RowIndex).Cells(e.ColumnIndex).Value; can be a query.

Let us know if it makes sense, then we will move to the next problem.
Vadim RappCommented:
...and here's even more efficient way. Since you are already have populated the datatable that constitutes the items in the combobox, you can use it to look up the value. For example, let's say you have table Employees(employeeid primary key,firstname,fullname). You populate the combobox by query

SELECT     EmployeeID, FirstName, FullName FROM  Employees

but only use one column FirstName to display in the combobox, by using property DisplayMember. Once it's selected, you want to display Fullname in another column col2.

We can use Linq query from the same already populated datatable:
        Dim v As String
        v = sender.Rows(e.RowIndex).Cells(e.ColumnIndex).Value
        sender.Rows(e.RowIndex).Cells("col2").Value = (From s In Me.DataSet1.Employees Select s.FullName, s.FirstName Where FirstName = v Select FullName Take 1).First

Open in new window

This way, we avoid trip to the database. This approach makes sense if this secondary lookup is simple and can be combined with the one that populates the combobox.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

T HoecherlDeveloperAuthor Commented:
Thanks for the response, vadimrapp1.  I'm a bit confused about the first suggestion, but I am hoping when I try it, it will become more clear.  On my way to client location now.  I will give it a try and we can correspond further from there.  Thank you.
T HoecherlDeveloperAuthor Commented:
I'm just not quite understanding your instructions.  The attached file shows what I have now.  the first two columns are column 0 and column 1 or the datagridview, with column headings PAYRCORD and DSCRIPTN.  The third column is a ComboBox with the heading Custome ID.  Those columns are populated using this code on the form load event:

    Private Sub frmPCAssign_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim SQLSourceBindingSource As BindingSource = New BindingSource
        Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source = t-pc;Initial Catalog = TWO; " & _
                                                                 "Persist Security Info= True;User ID=sa;Password=123!")
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand

        Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd.CommandText, cn)
        dataAdapter.SelectCommand.CommandTimeout = 0
        Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dataAdapter)
        Dim table = New DataTable
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        dataAdapter.SelectCommand.CommandTimeout = 0
        SQLSourceBindingSource.DataSource = table
        dgvAssignment.DataSource = SQLSourceBindingSource

        Dim dat As SqlDataAdapter = New SqlDataAdapter("SELECT DISTINCT CUSTNMBR FROM RM00101 ORDER BY CUSTNMBR", cn)
        Dim dt As New DataSet
        dat.Fill(dt, "table")
        lstCust.DataSource = dt.Tables("table").DefaultView
        lstCust.DisplayMember = "CUSTNMBR"
        lstCust.ValueMember = "CUSTNMBR"

    End Sub

The fourth column is the Customer Name populated when the Customer ID in the Combo Box changes.  It is populated using this code:

    Private Sub lstCust_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lstCust.SelectedIndexChanged
        Dim strCust As String
        Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source = t-pc;Initial Catalog = TWO; " & _
                                                          "Persist Security Info= True;User ID=sa;Password=123!")
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
        strCust = lstCust.Text

        cmd.CommandType = CommandType.Text
        cmd.Connection = cn
        cmd.CommandTimeout = 0

        cmd.CommandText = "SELECT CUSTNAME FROM RM00101 WHERE CUSTNMBR = '" & strCust & "'"
        txtCustName.Text = cmd.ExecuteScalar()
    End Sub

What I am trying to do is put them all together so I can select a customer ID for any of the datagridview rows.  so when the display comes up, there should be a third and fourth column for each of the rows in the dgv.  Is this possible?
Vadim RappCommented:
So, your datagrid has 4 columns. The first 2 are databound. The 3rd and 4th are not. The 3rd is dropdown box with the items populated by dataadapter. Initially, the combobox has no selected index, but once the user makes selection, you have the code that looks up custname and puts it in the 4th column of the selected row (and my 2nd post was about how to eliminate this database lookup by using linq query instead of executescalar.).

So, what do you need, what do you mean by "put them all together"? Do you want 3rd and 4th column initially populated in all rows even before the user has made the choice in the combobox?
T HoecherlDeveloperAuthor Commented:
I think I understand your second post now.  I am going to try that.  I didn't expect the 3rd and 4th columns initially populated, but right now I have 10 rows in the datagridview (see attached pdf) but only one row each for columns 3 and 4.  I wanted a row in columns 3 and 4 for every row in the dgv.  But I am rethinking my design and I don't think I need that, after all.

Your second post will be very helpful if I can get it to work.  Thank you.

I'm still curious about the first post.  It is obviously a different method to populate the columns in the dgv, and it may be better than what I am doing, but I don't understand it.  Looking at the data in the dgv in my attached example, can you help me understand your method a little better.  As you can see, I'm not even sure what questions to ask.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.