Manually updating a bound DataGridView???

I have a DGV that is bound thru the Designer. On my form, the user has the chance to provide a parameter to retrieve a new set of data. My problem is, that I'm not sure how to tie the NEW dataset to the BindingSource or Dataset that the Designer created to reload my DGV!

Thanks,
Blake
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArkCommented:
YourBindingSource.DataSource=NewDataSet
'YourBindingSource.DataMember = TableName_in_NewDataSet_If_Not_Same
0
BlakeMcKennaAuthor Commented:
For some reason that isn't working. Because the DGV needs to be populated dynamically based on some control values when the form loads, I removed the BindingSource as the DataSource in the Designer. I am manually binding the DGV to a new BindingSource (declared in my form as Dim BS As New BindingSource). Below is my code and how I build the DGV.

        Private Sub LoadGrid()
        Try
            If cmbYear.Text.Length > 0 And cmbMonth.Text.Length > 0 Then
                Dim ds As New DataSet
                ds = BL.GetNewTransactions(GetMonthLiteral(0, cmbMonth.Text), CInt(cmbYear.Text))
                dgvTrans.DataSource = Nothing
                bs.Clear
                bs.DataSource = ds
                bs.DataMember = "Transactions"

                If ds Is Nothing Then
                    Dim column As DataGridViewColumn

                    dgvTrans.Rows.Clear()
                    dgvTrans.Columns.Clear()

                    With dgvTrans
                        column = New DataGridViewColumn
                        column.Visible = False
                        dgvTrans.Columns.Add(column)

                        column = New DataGridViewColumn
                        Dim cmb As DataGridViewComboBoxCell = New DataGridViewComboBoxCell
                        cmb.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
                        cmb.FlatStyle = FlatStyle.Popup
                        column.CellTemplate = cmb
                        column.HeaderText = "Account Name"
                        column.Width = 150
                        dgvTrans.Columns.Add(column)

                        column = New DataGridViewColumn
                        Dim dtp As CalendarCell = New CalendarCell
                        column.CellTemplate = dtp
                        column.HeaderText = "Date"
                        column.Width = 100
                        dgvTrans.Columns.Add(column)

                        column = New DataGridViewColumn
                        Dim cmb1 As DataGridViewComboBoxCell = New DataGridViewComboBoxCell
                        cmb1.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
                        cmb1.FlatStyle = FlatStyle.Popup
                        column.CellTemplate = cmb1
                        column.HeaderText = "Merchant"
                        column.Width = 175
                        dgvTrans.Columns.Add(column)

                        column = New DataGridViewColumn
                        column.HeaderText = "Amount"
                        column.Width = 90
                        dgvTrans.Columns.Add(column)

                        column = New DataGridViewColumn
                        column.HeaderText = "Notes"
                        column.Width = 350
                        dgvTrans.Columns.Add(column)

                        column = New DataGridViewColumn
                        Dim btn As DataGridViewButtonCell = New DataGridViewButtonCell
                        column.CellTemplate = btn
                        column.HeaderText = "New Row"
                        column.Width = 100
                        dgvTrans.Columns.Add(column)
                    End With
                Else
                    Dim column As DataGridViewColumn

                    With dgvTrans
                        .Columns(0).Visible = False

                        Dim cmb1 As DataGridViewComboBoxCell = New DataGridViewComboBoxCell
                        cmb1.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
                        cmb1.FlatStyle = FlatStyle.Popup
                        cmb1.DataSource = AccountsBindingSource
                        .Columns(1).CellTemplate = cmb1
                        .Columns(1).Width = 150
                        .Columns(1).HeaderText = "Account Name"

                        .Columns(2).Width = 100
                        .Columns(2).HeaderText = "Date"

                        Dim cmb2 As DataGridViewComboBoxCell = New DataGridViewComboBoxCell
                        cmb2.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
                        cmb2.FlatStyle = FlatStyle.Popup
                        cmb2.DataSource = LKUPMerchantsBindingSource
                        .Columns(3).CellTemplate = cmb2

                        .Columns(4).Width = 90
                        .Columns(4).HeaderText = "Amount"

                        .Columns(5).Width = 350
                        .Columns(5).HeaderText = "Notes"

                        Dim cell As DataGridViewButtonCell = New DataGridViewButtonCell()
                        .Columns(6).CellTemplate = cell
                        .Columns(6).Width = 100
                        .Columns(6).HeaderText = "New Row"
                    End With
                End If
            End If

        Catch ex As Exception
            Dim strErr As String = "frmTransactions/LoadGrid() - " & ex.Message
            MessageBox.Show(strErr, "User Notification", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
0
ArkCommented:
 Else
      Dim column As DataGridViewColumn
      With dgvTrans
            .DataSource = bs '<===ADDED

Open in new window

BTW, why you build unbound columns if 'BL.GetNewTransactions' returns nothing (does it really returns nothing or just emty table or what?). You can add table to ds, add columns to this table and bind this table to dgv. Then you don't need If..Else - just assign dgv columns to table. You can set
 dgv.AutoGenerateColumns = False
column = New DataGridViewColumn
Dim cmb As DataGridViewComboBoxCell = New DataGridViewComboBoxCell
cmb.DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
'......
column.DataPropertyName = "YourTableFieldName" '<== ADDED
dgvTrans.Columns.Add(column)

Open in new window

0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

BlakeMcKennaAuthor Commented:
I guess the reason I'm doing it this way is because I want the columns to always show up on the dgv whether there is data or not. If there is another way to accomplish this...I would love to know.

Thanks,
0
ArkCommented:
As I mentioned above, there are 2 ways to display columns:
Bind datatable to datagridview and:
1. Set .AutogenerateColumns property to true and mantain columns template like you did in first part pf your If
2. Set .AutogenerateColumns property to false and create columns like in your second part (adding appropriate .DataPropertyName to each column)
In both cases, even if table is empty but contains columns - dgv will contain columns you need. Don't know what BL.GetNewTransactions() returns - datatable or dataset, anyway you can do:
If ds Is Nothing Then
   ds = New DataSet
   With ds.tables.Add("Transactions") 
      .Columns.Add("Account_Name", GetType(String))
      .Columns.Add("Account_Date", GetType(Date))
      .Columns.Add("MerchantID", GetType(Integer))
   End With
End If
bs.DataSource = ds
bs.DataMember = "Transactions"

Open in new window

0
BlakeMcKennaAuthor Commented:
Well, I have the code working to a point. The number of records that should be returned populate the grid with the correct number of rows, however, there is no Data in the columns. I just tried to assign the "DataPropertyName"  and that showed data for 3 of the 5 columns. However, the other 2 columns are comboboxes and their data isn't showing up. The comboboxes aren't even being loaded. Not sure why that's happening. Again, all the databinds are dynamic based off of a year and month parameter. This grid is basically a checkbook register. Also, for some reason the button text doesn't appear on the button. You can see how I have it coded in my logic above.
0
ArkCommented:
Can you show  BL.GetNewTransactions code?
0
BlakeMcKennaAuthor Commented:
I've renamed it to GetMonthlyTransactions().

        Public Function GetMonthlyTransactions(ByVal iMonth As Integer, ByVal iYear As Integer) As DataSet
        Dim ds As New DataSet

        Try
            ds = DL.GetMonthlyTransactions(iMonth, iYear)

            If Not ds Is Nothing Then
                Return ds
            Else
                Return Nothing
            End If

        Catch ex As Exception
            strErr = "clsBusinessLayer/GetAllocations() - " & ex.Message
            Throw
        End Try
    End Function
    '
    '
    '
        Public Function GetMonthlyTransactions(ByVal iMonth As Integer, ByVal iYear As Integer) As DataSet
        Try
            If DBConnection() Then
                ds = New DataSet

                sSQL = "SELECT ID, acctName, dateOfTran, amount, merchantID, note " & _
                         "FROM dbo.Transactions " & _
                        "WHERE MONTH(dateOfTran) = " & iMonth & " AND YEAR(dateOfTran) = " & iYear
                cmd = New SqlCommand(sSQL, cnn)
                adp = New SqlDataAdapter
                adp.SelectCommand = cmd
                adp.Fill(ds, "Transactions")

                If ds.Tables(0).Rows.Count > 0 Then
                    Return ds
                Else
                    Return Nothing
                End If
            End If

        Catch ex As Exception
            strErr = "clsDataLayer/GetMonthlyTransactions() - " & ex.Message
            Throw
        End Try
    End Function
0
ArkCommented:
BTW,
If Not ds Is Nothing Then
   Return ds
Else
   Return Nothing
End If

Open in new window

equals
Return ds

Open in new window

:)
Since u'r using single datatable IMHO you don't need DataSet, use datatable (not important, you can stay with dataset)
Dim dt As New DataTable
sSQL = "SELECT ..."
Using adp As New SqlDataAdapter(sSql,cnn)
       adp.Fill(dt)
Emd Using
Return dt '<== Return datatable even without rows - it already has columns from your SELECT statement.

Open in new window

Now
Private Sub LoadGrid()
   Dim dt As datatable =  BL.GetNewTransactions(...)
   With dgvTrans
        .AutogenerateColumns=False
        .dataSource=dt
        .Columns.Add(New DataGridViewComboBoxColumn With
                         {
                            .DataSource = AccountsBindingSource,
                            .HeaderText = "Account name",
                            .DisplayMember = "acctName",
                            .ValueMember = "acctName",
                            .DataPropertyName = "acctName"
                             })
        .Columns.Add(New DataGridViewCalendarColumn With
                         {
                            .HeaderText = "Account Date",
                            .DataPropertyName = "dateOfTran"
                             })
        .Columns.Add(New DataGridViewTextBoxColumn With
                         {
                            .HeaderText = "Amount",
                            .DataPropertyName = "amount"
                             })
        .Columns.Add(New DataGridViewComboBoxColumn With
                         {
                            .DataSource = LKUPMerchantsBindingSource,
                            .HeaderText = "Merchants",
                            .DisplayMember = "merchantName",
                            .ValueMember = "merchantID",
                            .DataPropertyName = "merchantID"
                             })
   End With 
End Sub

Open in new window

Note for combobox column:
.DataSource - source to fill combo
.DisplayMember - field name from .DataSource to display
.ValueMember  - field name from .DataSource binded to dgv.dataSource - in your case seems they are equals but usualy relations builds using id, like .DisplayMember="acctName",.ValueMember = acctID (see merchants column)
.DataPropertyName  - field in dgvDataSource, binded to cmb.DataSource.ValueMember field
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.