Combining objects in a user control in Visual Studio/

Posted on 2013-06-07
Last Modified: 2013-11-26
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.
Question by:thoecherl
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39235383
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.
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39235493
...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.

Author Comment

ID: 39235508
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.
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.


Author Comment

ID: 39236279
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?
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39236571
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?

Author Comment

ID: 39237779
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.

LVL 40

Accepted Solution

Vadim Rapp earned 500 total points
ID: 39238013
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.

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question