Question DataGridViewTextBoxColumn with dataset

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
I hope you can help me.

I have a form with the normal controls as well as a datagridview which get data from another table based on the record displayed. I have hidden the first two columns, as I do not want to show ID and QuoteNumber. However, I do want to display NumberOfPeople, StartDate and EndDate.

Initially, with the DataGridViewTextBoxColumn properties set, I have achieved just this. However, when I navigate up and down the recordset the ID column is displayed. The problem is behaving consistently. When there are no records to display, the behaviour is okay. When a set of records is displayed in the datagridview, this also is okay. However, when navigating to the next record the ID column will show.

The code is as follows:

VB.NET:
Dim strQry As String = "SELECT ID, Quote, NumberOfPeople, StartDate, EndDate FROM Resources WHERE Quote = " & QuoteID
        Dim dsResources As New DataSet
        Dim cmdResources As New OleDb.OleDbDataAdapter(strQry, con)

        con.ConnectionString = strDataSource

        Try

            con.Open()

        Catch ex As Exception

            MsgBox("An error occurred while trying to connect to the database.", _
                   MsgBoxStyle.Critical, _
                   "Database Connection Error")
            End

        End Try

        Try

            cmdResources.Fill(dsResources, "Resources")
            'gdResources.DataSource = dsResources.Tables("Resources")
            Me.gdResources.Columns.Clear()

            Dim IDCol As New DataGridViewTextBoxColumn()
            IDCol.DataPropertyName = "ID"
            IDCol.Name = "ID"
            IDCol.HeaderText = "ID"
            IDCol.Visible = False
            Me.gdResources.Columns.Add(IDCol)

            Dim QuoteCol As New DataGridViewTextBoxColumn()
            QuoteCol.DataPropertyName = "Quote"
            QuoteCol.Name = "Quote"
            QuoteCol.HeaderText = ""
            QuoteCol.Visible = False
            Me.gdResources.Columns.Add(QuoteCol)

            Dim NumberOfPeopleCol As New DataGridViewTextBoxColumn()
            NumberOfPeopleCol.DataPropertyName = "NumberOfPeople"
            NumberOfPeopleCol.Name = "NumberOfPeople"
            NumberOfPeopleCol.HeaderText = "Number Of People"
            NumberOfPeopleCol.Width = 120
            NumberOfPeopleCol.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            Me.gdResources.Columns.Add(NumberOfPeopleCol)

            Dim StartDateCol As New DataGridViewTextBoxColumn()
            StartDateCol.DataPropertyName = "StartDate"
            StartDateCol.Name = "StartDate"
            StartDateCol.HeaderText = "Start Date"
            StartDateCol.Width = 80
            StartDateCol.DefaultCellStyle.Format = "dd/MM/yyyy"
            Me.gdResources.Columns.Add(StartDateCol)

            Dim EndDateCol As New DataGridViewTextBoxColumn()
            EndDateCol.DataPropertyName = "EndDate"
            EndDateCol.Name = "EndDate"
            EndDateCol.HeaderText = "End Date"
            EndDateCol.Width = 80
            EndDateCol.DefaultCellStyle.Format = "dd/MM/yyyy"
            Me.gdResources.Columns.Add(EndDateCol)

            Me.gdResources.DataSource = dsResources
            Me.gdResources.DataMember = "Resources"

        Catch ex As Exception

            MsgBox("An error occurred while trying to retrieve data from the database.", _
                   MsgBoxStyle.Critical, _
                   "Data Retrieval Error")

        Finally

            con.Close()

        End Try

The navigation works by using an integer as an index and calls a procedure called NavigateRecords. This as follows:

VB.NET:
With dsQuotes.Tables("Quotes").Rows(inc)

            txtQuoteNumber.Text = .Item(1)
            cboClient.SelectedValue = .Item(2)
            cboBidOwner.SelectedValue = .Item(3)
            dtpDateRecieved.Value = .Item(4)
            dtpDateDue.Value = .Item(5)
            txtDescription.Text = .Item(6)
            cboStage.SelectedValue = .Item(7)
            cboDepartment.SelectedValue = .Item(8)
            txtHoursRequired.Text = .Item(9)
            txtValue.Text = .Item(10)
            cboStatus.SelectedValue = .Item(11)
            cboReason.SelectedValue = .Item(12)
            chkRiskAssessment.Checked = .Item(13)
            txtProbability.Text = .Item(14)
            chkTaxClaim.Checked = .Item(15)

            If Convert.IsDBNull(.Item(16)) Then

                txtNotes.Text = ""

            Else

                txtNotes.Text = .Item(16)

            End If

            filldsResources(.Item(0))
            tssRecordNumber.Text = "Record " & inc + 1 & " of " & QuotesMaxRows

        End With

Lastly, I am using Framework 3.5

Would anybody be able to tell me what the problem is?

Thank you in advance :D
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
You probably have AutoGenerateColumns set to TRUE on the datagridview in question. As soon as it encounters a data row with more columns than it has in its collection, it will add new columns to display the new data

Simply set YOURDATAGRIDVIEW.AutoGenerateColumns = False
in the form's constructor
 

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
Thank you for your reply cjard. I set the AutoGenerateColumns to false but this still managed to show the ID Column. Incidentally, another column is hidden and this does not keep be showed.
 

posix32

Member
Joined
Apr 20, 2009
Messages
13
Programming Experience
1-3
Absolutely. However, I have gone down the pure code route, so I was thinking that because of this, the program may have some anomolies.

What I thought of trying was using more of the objects supplied with Visual Studio, so setting up an actual data source and bringing that into the datadridview and seeing if the anomolies disappear. Also, what I would need to do is make sure, through this way that any linked tables show the actual data from the other tables rather than just numbers. Whichever way that is done using this method.

Hopefully, this won't take too long as I have a meeting back end of next week to demonstrate the software. Last minute stuff where I work, lol.

Anyway, thank you very much again for your help.
 
Top Bottom