Solved

Combining objects in a user control in Visual Studio/VB.net

Posted on 2013-06-07
9
585 Views
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.
UserControl.pdf
0
Comment
Question by:thoecherl
  • 4
  • 3
9 Comments
 
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"
        Else
            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.
0
 
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.
0
 

Author Comment

by:thoecherl
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:thoecherl
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
        cmd.CommandText = "SELECT PAYRCORD, DSCRIPTN FROM UPR40600 ORDER BY PAYRCORD"

        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
        dataAdapter.Fill(table)
        SQLSourceBindingSource.DataSource = table
        dgvAssignment.DataSource = SQLSourceBindingSource

        cn.Open()
        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"
        cn.Close()


    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
        cn.Open()

        cmd.CommandText = "SELECT CUSTNAME FROM RM00101 WHERE CUSTNMBR = '" & strCust & "'"
        txtCustName.Text = cmd.ExecuteScalar()
        cn.Close()
    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?
0
 
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?
0
 

Author Comment

by:thoecherl
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.

T
Example.pdf
0
 
LVL 40

Accepted Solution

by:
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now