[RESOLVED] [2008] Datagridview Help

[onlyGirl]

I have the code for a datagridview style but for some reason when I run it, nothing shows up. I know the SQL is correct because I've debugged and gotten the SQL statement and executed it in Access and it does return values.

Please tell me what I am doing wrong...

 Public Sub FillProdWellData()
        DataGridView1.Visible = False
        dgWellData.BringToFront()
        dgWellData.Visible = True

        Dim cmdFillProd As New OleDbCommand
        Dim refNum As Integer

        dsWellData.Clear()

        'the sql statement that retrieves the data from the database in table Param_Prod
        cmdFillProd.CommandText = "SELECT Distinct API, StartDate, EndDate, Reservoir, " _
                    & "InitialRate, FinalRate, DeclineRate, DeclineType, HypFactor, " _
                    & "GasOilRatio, ExtendDate, GasOil, ConstFlVol, InitialWater FROM Param_Prod " _
                    & "Where Reservoir = '" & Resvr & "' " _
                    & "AND TemplateName = '" & PrmName & "'"

        'the following tells the adapter what the select command is, sets the connection for the cmd and fills the 
        'dataset with the data returned from the sql statement
        daWellData.SelectCommand = cmdFillProd
        cmdFillProd.Connection = gConn
        Try
            daWellData.Fill(dsWellData, "Param_Prod")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


        'tells dcDelete (a data column) that is is the boolean or checkbox.
        dcDelete.DataType = System.Type.GetType("System.Boolean")
        dcDelete.DefaultValue = False
        dcDelete.AllowDBNull = False
        dcDelete.ColumnName = "Delete"
        dcDelete.ReadOnly = False

        'it should only add the column once not constantly
        refNum += 1
        If refNum = 1 Then
            dsWellData.Tables("Param_Prod").Columns.Add(dcDelete)
        End If

        'Me.ProdTableStyle()
        Me.DisplayProdDGV()
    End Sub

Public Sub DisplayProdDGV()
        'dgWellData.Visible = False
        'DataGridView1.Visible = True
        DataGridView1.Columns.Clear()
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.AllowUserToDeleteRows = False

        Dim Col1 As New DataGridViewComboBoxColumn
        With Col1
            .HeaderText = "API"
            .Width = 100
            '.DataPropertyName = "API"
            .Items.Add("Y")
            .Items.Add("N")
        End With

        Dim Col2 As New DataGridViewTextBoxColumn
        With Col2
            .HeaderText = "Start Date"
            .Width = 80
            .DataPropertyName = "StartDate"
        End With

        Dim Col3 As New DataGridViewTextBoxColumn()
        With Col3
            .HeaderText = "End Date"
            .Width = 80
            .DataPropertyName = "EndDate"
        End With

        Dim Col4 As New DataGridViewTextBoxColumn()
        With Col4
            .HeaderText = "Reservoir"
            .DataPropertyName = "Reservoir"
            .Width = 100
        End With

        Dim Col5 As New DataGridViewTextBoxColumn()
        With Col5
            .HeaderText = "Initial Rate"
            .DataPropertyName = "InitialRate"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col6 As New DataGridViewTextBoxColumn()
        With Col6
            .HeaderText = "Final Rate"
            .DataPropertyName = "FinalRate"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col7 As New DataGridViewTextBoxColumn()
        With Col7
            .HeaderText = "Decline Rate"
            .DataPropertyName = "DeclineRate"
            .Width = 80
            .DefaultCellStyle.Format = "f4"
        End With

        Dim Col8 As New DataGridViewTextBoxColumn()
        With Col8
            .HeaderText = "Decline Type"
            .DataPropertyName = "DeclineType"
            .Width = 60
        End With

        Dim Col9 As New DataGridViewTextBoxColumn()
        With Col9
            .DataPropertyName = "HypFactor"
            .HeaderText = "HypFactor"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col10 As New DataGridViewTextBoxColumn()
        With Col10
            .DataPropertyName = "GasOilRatio"
            .HeaderText = "Gas\Oil Ratio"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col11 As New DataGridViewTextBoxColumn()
        With Col11
            .DataPropertyName = "ExtendDate"
            .HeaderText = "Extend Date"
            .Width = 80
        End With

        Dim Col12 As New DataGridViewTextBoxColumn()
        With Col12
            .DataPropertyName = "GasOil"
            .HeaderText = "GasOil"
            .Width = 80
        End With

        Dim Col13 As New DataGridViewTextBoxColumn()
        With Col13
            .DataPropertyName = "ConstFlVol"
            .HeaderText = "ConstFlVol"
            .Width = 80
        End With

        Dim col14 As New DataGridViewCheckBoxColumn
        With col14
            .HeaderText = "Delete"
            .Width = 60
        End With

        Dim col15 As New DataGridViewTextBoxColumn
        With col15
            .DataPropertyName = "InitialWater"
            .HeaderText = "Initial Water"
            .Width = 80

        End With

        With DataGridView1.Columns
            .Add(col14)
            .Add(Col1)
            .Add(Col2)
            .Add(Col3)
            .Add(Col4)
            .Add(Col5)
            .Add(Col6)
            .Add(Col7)
            .Add(Col8)
            .Add(Col9)
            .Add(Col10)
            .Add(Col11)
            .Add(Col12)
            .Add(Col13)
            .Add(col15)
        End With

        'DataGridView1.DataSource = RgDataSet2.Tables("ProdInj")
        DataGridView1.DataSource = dsWellData.Tables("Param_Prod")
    End Sub

[Tom Sawyer]

In the FillProdWellData method, you have DataGridView1.Visible = False.

In the DisplayProdDGV method, you have DataGridView1.Visible = True commented out.

Is the problem that the datagrid is invisible?

[onlyGirl]

No, the dgv is there but just empty with no data or header text. I fixed what you pointed out though, thx.

[onlyGirl]

Ooops, you were right. That was the problem. But now, I have another question, when you look at the dgv, it has the columns API and Delete twice. Could you help me with that please?

[SunshineBH]

Colums API and Delete are being added more than once

Your code

 Public Sub FillProdWellData()
        DataGridView1.Visible = False
        dgWellData.BringToFront()
        dgWellData.Visible = True

        Dim cmdFillProd As New OleDbCommand
        Dim refNum As Integer

will reset refNum to zero each time you come into the sub. so the check

 'it should only add the column once not constantly
        refNum += 1
        If refNum = 1 Then
            dsWellData.Tables("Param_Prod").Columns.Add(dcDelete)
        End If

will never work. In fact each time you access the dgv these colums will be added. Probly not the best thing to do but why not make RefNum a public variable or check your column adds in some other way?

Hope this helps.

[onlyGirl]

Thank you for your suggestions. I will try them out.

[onlyGirl]

I mad refNum public but it still adds the rows twice. I changed a couple more columns to a different type and it adds another duplicate row. Anybody know how to fix this?

[SunshineBH]

How about this

cmdFillProd.CommandText = "SELECT Distinct API, StartDate, EndDate, Reservoir, " _
                    & "InitialRate, FinalRate, DeclineRate, DeclineType, HypFactor, " _
                    & "GasOilRatio, ExtendDate, GasOil, ConstFlVol, InitialWater , "" as API, "" as Deleted FROM Param_Prod " _
                    & "Where Reservoir = '" & Resvr & "' " _
                    & "AND TemplateName = '" & PrmName & "'"

This will add the extra 2 columns you need. You will not need to worry about adding them manually as they will be added by the SQL.

hope this is more helpful

[onlyGirl]

I deleted the code adding the columns but it still adds the columns more than once. But only the ones that I have changed the column type.

Public Sub FillProdWellData()
        'DataGridView1.Visible = False
        'dgWellData.BringToFront()
        'dgWellData.Visible = True

        Dim cmdFillProd As New OleDbCommand


        dsWellData.Clear()

        'the sql statement that retrieves the data from the database in table Param_Prod
        cmdFillProd.CommandText = "SELECT Distinct API, StartDate, EndDate, Reservoir, " _
                    & "InitialRate, FinalRate, DeclineRate, DeclineType, HypFactor, " _
                    & "GasOilRatio, ExtendDate, GasOil, ConstFlVol, InitialWater FROM Param_Prod " _
                    & "Where Reservoir = '" & Resvr & "' " _
                    & "AND TemplateName = '" & PrmName & "'"

        'the following tells the adapter what the select command is, sets the connection for the cmd and fills the 
        'dataset with the data returned from the sql statement
        daWellData.SelectCommand = cmdFillProd
        cmdFillProd.Connection = gConn
        Try
            daWellData.Fill(dsWellData, "Param_Prod")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


        ''tells dcDelete (a data column) that is is the boolean or checkbox.
        'dcDelete.DataType = System.Type.GetType("System.Boolean")
        'dcDelete.DefaultValue = False
        'dcDelete.AllowDBNull = False
        'dcDelete.ColumnName = "Delete"
        'dcDelete.ReadOnly = False

        'dcExtendDate.DataType = System.Type.GetType("System.Boolean")
        'dcExtendDate.DefaultValue = False
        ''dcExtendDate.ColumnName = "ExtendDate"

        ''it should only add the column once not constantly
        'refNum2 += 1
        'If refNum2 = 1 Then
        '    'dsWellData.Tables("Param_Prod").Columns.Add(dcDelete)
        '    'dsWellData.Tables("Param_Prod").Columns.Add(dcExtendDate)
        'End If

        'Me.ProdTableStyle()
        Me.DisplayProdDGV()
    End Sub

Public Sub DisplayProdDGV()
        'dgWellData.Visible = False
        DataGridView1.Visible = True
        dgWellData.Visible = False
        DataGridView1.Columns.Clear()
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.AllowUserToDeleteRows = False

        Dim Col1 As New DataGridViewTextBoxColumn
        With Col1
            .HeaderText = "API"
            .Width = 100
            .DataPropertyName = "API"
        End With

        Dim Col2 As New DataGridViewTextBoxColumn
        With Col2
            .HeaderText = "Start Date"
            .Width = 80
            .DataPropertyName = "StartDate"
        End With

        Dim Col3 As New DataGridViewTextBoxColumn()
        With Col3
            .HeaderText = "End Date"
            .Width = 80
            .DataPropertyName = "EndDate"
        End With

        Dim Col4 As New DataGridViewTextBoxColumn()
        With Col4
            .HeaderText = "Reservoir"
            .DataPropertyName = "Reservoir"
            .Width = 100
        End With

        Dim Col5 As New DataGridViewTextBoxColumn()
        With Col5
            .HeaderText = "Initial Rate"
            .DataPropertyName = "InitialRate"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col6 As New DataGridViewTextBoxColumn()
        With Col6
            .HeaderText = "Final Rate"
            .DataPropertyName = "FinalRate"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col7 As New DataGridViewTextBoxColumn()
        With Col7
            .HeaderText = "Decline Rate"
            .DataPropertyName = "DeclineRate"
            .Width = 80
            .DefaultCellStyle.Format = "f4"
        End With

        Dim Col8 As New DataGridViewComboBoxColumn
        With Col8
            .HeaderText = "Decline Type"
            '.DataPropertyName = "DeclineType"
            .Width = 60
            .Items.Add("H")
            .Items.Add("L")
        End With

        Dim Col9 As New DataGridViewTextBoxColumn()
        With Col9
            .DataPropertyName = "HypFactor"
            .HeaderText = "HypFactor"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col10 As New DataGridViewTextBoxColumn()
        With Col10
            .DataPropertyName = "GasOilRatio"
            .HeaderText = "Gas\Oil Ratio"
            .Width = 80
            .DefaultCellStyle.Format = "f2"
        End With

        Dim Col11 As New DataGridViewCheckBoxColumn
        With Col11
            '.DataPropertyName = "ExtendDate"
            .HeaderText = "Extend Date"
            .Width = 80
        End With

        Dim Col12 As New DataGridViewComboBoxColumn
        With Col12
            '.DataPropertyName = "GasOil"
            .HeaderText = "GasOil"
            .Width = 80
            .Items.Add("Gas")
            .Items.Add("Oil")
        End With

        Dim Col13 As New DataGridViewTextBoxColumn()
        With Col13
            .DataPropertyName = "ConstFlVol"
            .HeaderText = "ConstFlVol"
            .Width = 80
        End With

        Dim col14 As New DataGridViewCheckBoxColumn
        With col14
            .HeaderText = "Delete"
            .Width = 60
        End With

        Dim col15 As New DataGridViewTextBoxColumn
        With col15
            .DataPropertyName = "InitialWater"
            .HeaderText = "Initial Water"
            .Width = 80

        End With

        DataGridView1.Columns.Clear()
        With DataGridView1.Columns
            .Add(col14)
            .Add(Col1)
            .Add(Col2)
            .Add(Col3)
            .Add(Col4)
            .Add(Col5)
            .Add(Col6)
            .Add(Col7)
            .Add(Col8)
            .Add(Col9)
            .Add(Col10)
            .Add(Col11)
            .Add(Col12)
            .Add(Col13)
            .Add(col15)
        End With
        'DataGridView1.Rows(0).Cells(1).Value = "L"

        'DataGridView1.DataSource = RgDataSet2.Tables("ProdInj")
        DataGridView1.DataSource = dsWellData.Tables("Param_Prod")
    End Sub

[onlyGirl]

anybody?