none
Show Account Name rather than AccountID in DGV

    Question

  • I am building a WinForm application with the goal of using mssql stored procedures throughout.  It has required some additional work, but I feel like I have greater control, and will eventually be able to create a complete API to enable users to create their own interface.

    I have created a couple of working Main/Details relationships,which are functioning perfectly, but which I would like to fine-tune before creating the dozen or two more I will need for the full application (would like for them to all work as similarly as possible).  The DGB .update method employed in the code below does not work with datasources with joined tables (at least as far as I understand), so my cross table, which includes UserID and AccountID, does not include User Name or Account Name.  I have to create a combobox to display the Account Name, but am looking for something cleaner.  I am considering creating what will essentially be a Lookup function in mssql, but thought I would post the question here first.  I do not want the Account Name to be editable in the DGV.  The user will edit the Account info elsewhere.  For this example, just the Active and Comment fields in the cross table will be editable.

    Questions:  How best to replace the combobox with a textbox as a means of showing the Account Name associated with an AccountID in a DGV?

    Thank You

    **********************************************************

        Private Sub PopulateUserAccountsDGV()
            Dim Con01 As New SqlConnection(DB.strConn)
            Dim Cmd01 As New SqlCommand

            Dim CBCon As New SqlConnection(DB.strConn)
            Dim CBCmd As New SqlCommand
            Dim CBDA As New SqlDataAdapter
            Dim CBTbl As New DataTable
            Dim CBRecordCount As Integer

            Try
                ' CONFIGURE DATA FOR DGV
                If RC01 > 0 Then Tbl01.Clear()
                Cmd01.Connection = Con01
                Cmd01.CommandText = "spAccountUser"
                Cmd01.Parameters.Add("@UserID", SqlDbType.Int).Value = cboUsers.SelectedValue.ToString
                Cmd01.CommandType = CommandType.StoredProcedure
                Con01.Open()
                DA01.SelectCommand = Cmd01
                RC01 = DA01.Fill(Tbl01)
                Tbl01.Columns("UserID").DefaultValue = cboUsers.SelectedValue.ToString

                ' CONFIGURE DATA FOR USER COMBOBOX
                CBCmd.Connection = CBCon
                CBCmd.CommandText = "0010spAccount"
                CBCmd.CommandType = CommandType.StoredProcedure
                CBCon.Open()
                CBDA.SelectCommand = CBCmd
                CBRecordCount = CBDA.Fill(CBTbl)

                ' CONFIGURE USER COMBOBOX
                Dim cbocolAccounts As New DataGridViewComboBoxColumn
                With cbocolAccounts
                    .Items.Clear()
                    .DataSource = CBTbl
                    .ValueMember = "AccountID"
                    .DisplayMember = "Name"
                    .HeaderText = "Account"
                    .DataPropertyName = "AccountID"
                End With

                ' CONFIGURE DGV
                With dgvUserAccounts
                    .DataSource = Tbl01
                    .Columns.Clear()
                    .AutoGenerateColumns = False
                    .Columns.Add(cbocolAccounts)
                    .Columns(0).Width = 150
                    .Columns.Add(New DataGridViewCheckBoxColumn() With {.HeaderText = "Active", .DataPropertyName = "Active", .Width = 50})
                    .Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Comment", .DataPropertyName = "Comment", .Width = 350})
                End With

                DA01.UpdateCommand = New SqlCommandBuilder(DA01).GetUpdateCommand
                cmdSaveUserAccounts.Enabled = False
                cmdSaveUserAccounts.BackColor = SystemColors.Control

            Catch ex As Exception
                MsgBox("PopulateUserAccountsDGV: " & ex.Message)
            Finally
                If CBCon.State = ConnectionState.Open Then CBCon.Close()
                If Con01.State = ConnectionState.Open Then Con01.Close()
            End Try

        End Sub


    Saturday, April 29, 2017 6:20 PM

All replies

  • Hi Thomas,

    >>Questions:  How best to replace the combobox with a textbox as a means of showing the Account Name associated with an AccountID in a DGV?

    Can you tell me what It means? I'm not sure what you want?

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 02, 2017 9:27 AM
    Moderator
  • Thank you, Cherry. I can understand the need for clarification. I am using the update method to update the contents of my DGV. I am under the impression that the update method will not work if the datasource for the DGV is a SELECT query with one or more joins. Since I am using a flat query for my datasource, I only have AccountID as a value (Account Name is stored in Account table). To bring the Account Name into the DGV, I use a combobox so that I have both the value member and display member. I am simply asking if there is a way to have the Account Name display in the DGV while still being able to use the update method.
    Tuesday, May 02, 2017 1:47 PM