[RESOLVED] Order of columns in datagridview

[bodylojohn]

Hello,

I have a form containing a datagridview and a groupbox containing 2 radiobuttons.
If radiobutton 1 is selected only the order headers are shown.
If radiobutton 2 is selected only the orderlines are shown.
There also is a button called "show". When clicked the dgv gets filled with data.

Here is my problem.

When I first select radiobutton 2 (orderlines) and I press the show button the columns in the DGV are in the right order (as shown in the code "strview").

If I select radiobutton 1 still the columns are in the right order.

HOWEVER...when I now select radiobutton 2 the order of the columns get all messed up.

    If Me.radOrder.Checked = True Then 'ORDERHEADERS
      strView = Nothing
      strView = String.Format("SELECT DISTINCT InkoopVerkoop,Order_ID,OrderNummer,Omschrijving,Order_Datum,VolledigeNaamBestel,Status,NaamRelatie, " & _
                              "OrdNumLev,Geannuleerd,Geleverd,LeverDatum FROM qryORDER ")
    End If

    If Me.radOrderregels.Checked = True Then 'ORDERLINES
      strView = Nothing
      strView = String.Format("SELECT DISTINCT InkoopVerkoop,Order_ID,OrderNummer,Omschrijving,Order_Datum,VolledigeNaamBestel,Artikel,ArtOmschrijving,Aantal,PrijsEenheid, " & _                            "Status,NaamRelatie,OrdNumLev,Geannuleerd,Geleverd,LeverDatum FROM qryORDER ")
    End If

   
    Dim SampleSource As New DataSet
    Dim TableView As DataView
    Dim SampleCommand As New SqlCommand()
    Dim SampleDataAdapter = New SqlDataAdapter()
    SampleCommand.CommandText = strOrder_SQL
    SampleCommand.Connection = connection
    SampleDataAdapter.SelectCommand = SampleCommand
    SampleDataAdapter.Fill(SampleSource)
    TableView = SampleSource.Tables(0).DefaultView

    If Me.radOrder.Checked = True Then 'ORDER HEADERS
      With dgvZoek
        .DataSource = TableView
        .Columns(0).Width = 40
        .Columns(0).HeaderText = "Type"
        .Columns(1).Width = 30
        .Columns(1).HeaderText = "ID"
        .Columns(2).Width = 75
        .Columns(2).HeaderText = "Order"
        .Columns(3).Width = 150
        .Columns(3).HeaderText = "Omschrijving"
        .Columns(4).Width = 100
        .Columns(4).HeaderText = "Order datum"
        .Columns(5).Width = 150
        .Columns(5).HeaderText = "Besteller"
        .Columns(6).Width = 75
        .Columns(6).HeaderText = "Status"
        .Columns(7).Width = 150
        .Columns(7).HeaderText = "Relatie"
        .Columns(8).Width = 75
        .Columns(8).HeaderText = "OrderLev"
        .Columns(9).Width = 75
        .Columns(9).HeaderText = "Geannuleerd"
        .Columns(10).Width = 75
        .Columns(10).HeaderText = "Geleverd"
        .Columns(11).Width = 100
        .Columns(11).HeaderText = "Lever datum"
      End With
    End If

    If Me.radOrderregels.Checked = True Then 'ORDERLINES
      With dgvZoek
        .DataSource = TableView
        .Columns(0).Width = 40
        .Columns(0).HeaderText = "Type"
        .Columns(1).Width = 30
        .Columns(1).HeaderText = "ID"
        .Columns(2).Width = 75
        .Columns(2).HeaderText = "Order"
        .Columns(3).Width = 150
        .Columns(3).HeaderText = "Omschrijving"
        .Columns(4).Width = 100
        .Columns(4).HeaderText = "Order datum"
        .Columns(5).Width = 150
        .Columns(5).HeaderText = "Besteller"
        .Columns(6).Width = 75
        .Columns(6).HeaderText = "Artikel"
        .Columns(7).Width = 150
        .Columns(7).HeaderText = "Artikel omschrijving"
        .Columns(8).Width = 75
        .Columns(8).HeaderText = "Aantal"
        .Columns(9).Width = 75
        .Columns(9).HeaderText = "Prijs/ST"
        .Columns(10).Width = 100
        .Columns(10).HeaderText = "Status"
        .Columns(11).Width = 100
        .Columns(11).HeaderText = "Relatie"
        .Columns(12).Width = 100
        .Columns(12).HeaderText = "OrderLev"
        .Columns(13).Width = 75
        .Columns(13).HeaderText = "Geannuleerd"
        .Columns(14).Width = 75
        .Columns(14).HeaderText = "Geleverd"
        .Columns(15).Width = 100
        .Columns(15).HeaderText = "Lever datum"
      End With
    End If

I hope you guys can point out what I am doing wrong.

Thanks in advance!!!

[enrico1982]

Not sure what you mean by orderlines and orderheaders, that might help explain your question a bit.

Also when is the above code occurring? Depending on that, if you're running the above code each time the radio buttons are selected, more and more columns are going to be added each time, which may be part of your problem.

[bodylojohn]

Not sure what you mean by orderlines and orderheaders, that might help explain your question a bit.

Also when is the above code occurring? Depending on that, if you're running the above code each time the radio buttons are selected, more and more columns are going to be added each time, which may be part of your problem.

Thanks for replying...

The code occurs when I hit the Show button. The code then looks wich radiobutton is checked.

Orderheaders and orderlines are just tables I use.

There are no columns added. The order of the columns are just not right.

[enrico1982]

Orderheaders and orderlines are just tables I use.

In both instances you are getting data from the same table?

Regardless, you are not clearing your columns, so when you show you data and add columns for the second time, I imagine you are going to have two sets of columns.

Also I don't see strView being used after you have defined it. How are you selecting your data?

Also..asuming only one radio button can be select at a time, you can save some code by doing:

If Me.radOrder.Checked Then 'ORDERHEADERS
      strView = Nothing
      strView = String.Format("SELECT DISTINCT InkoopVerkoop,Order_ID,OrderNummer,Omschrijving,Order_Datum,VolledigeNaamBestel,Status,NaamRelatie, " & _
                              "OrdNumLev,Geannuleerd,Geleverd,LeverDatum FROM qryORDER ")
Else 'ORDERLINES
      strView = Nothing
      strView = String.Format("SELECT DISTINCT InkoopVerkoop,Order_ID,OrderNummer,Omschrijving,Order_Datum,VolledigeNaamBestel,Artikel,ArtOmschrijving,Aantal,PrijsEenheid, " & _                            "Status,NaamRelatie,OrdNumLev,Geannuleerd,Geleverd,LeverDatum FROM qryORDER ")
End If

Note that you don't need to do radOrder.checked = true, radOrder.checked works the same.

[kareninstructor]

I took at quick look at your SQL, both are in the same order but one has less columns, if that is correct then why not load all columns, set any properties of the columns then rather than reloading data hide/show columns.

In the example below Opt1 would have DataGridView column names to hide in this case if RadioButton2 is checked while when RadioButton1 is checked all columns are shown.

Private Opt1 As String() = {}
Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles RadioButton1.CheckedChanged, RadioButton2.CheckedChanged

    If RadioButton1.Checked Then
        For Each col As DataGridViewColumn In DataGridView1.Columns
            col.Visible = True
        Next
    Else
        For Each Column In Opt1
            DataGridView1.Columns(Column).Visible = False
        Next
    End If
End Sub

[bodylojohn]

To clarify things... Here is the exact procedure.

1. I select the output I want to see in my datagridview via the radiobuttons.

2. I press the show button. This triggers the VulDGV procedure.

Private Sub VulDGV()
  Dim strView As String = Nothing

  If Me.radOrder.Checked = True Then
    strView = Nothing
    strView = String.Format("SELECT DISTINCT InkoopVerkoop,Order_ID,OrderNummer,Omschrijving,Order_Datum,VolledigeNaamBestel,Status,NaamRelatie, " & _
                            "OrdNumLev,Geannuleerd,Geleverd,LeverDatum FROM qryORDER ")
  End If

  If Me.radOrderregels.Checked = True Then
    strView = Nothing
    strView = String.Format("SELECT DISTINCT InkoopVerkoop,Order_ID,OrderNummer,Omschrijving,Order_Datum,VolledigeNaamBestel,Artikel,ArtOmschrijving,Aantal,PrijsEenheid, " & _
                            "Status,NaamRelatie,OrdNumLev,Geannuleerd,Geleverd,LeverDatum FROM qryORDER ")
  End If

  Dim strOrder_SQL As String = String.Format("{0}" & _
        "WHERE 1=1", strView)


  Dim SampleSource As New DataSet
  Dim TableView As DataView

  Dim SampleCommand As New SqlCommand()
  With SampleCommand.Parameters
    .AddWithValue("@OrderDatumVan", Me.dtpOrderDatumVan.Value.Date)
    .AddWithValue("@OrderDatumTot", Me.dtpOrderDatumTot.Value.Date)
    .AddWithValue("@DatumAnnulerenVan", Me.dtpDatumAnnulerenVan.Value.Date)
    .AddWithValue("@DatumAnnulerenTot", Me.dtpDatumAnnulerenTot.Value.Date)
    .AddWithValue("@VrijDatumVeld01Van", Me.dtpVrijeDatum01Van.Value.Date)
    .AddWithValue("@VrijDatumVeld01Tot", Me.dtpVrijeDatum01Tot.Value.Date)
    .AddWithValue("@VrijDatumVeld02Van", Me.dtpVrijeDatum02Van.Value.Date)
    .AddWithValue("@VrijDatumVeld02Tot", Me.dtpVrijeDatum02Tot.Value.Date)
    .AddWithValue("@VrijDatumVeld03Van", Me.dtpVrijeDatum03Van.Value.Date)
    .AddWithValue("@VrijDatumVeld03Tot", Me.dtpVrijeDatum03Tot.Value.Date)
    .AddWithValue("@VrijDatumVeld04Van", Me.dtpVrijeDatum04Van.Value.Date)
    .AddWithValue("@VrijDatumVeld04Tot", Me.dtpVrijeDatum04Tot.Value.Date)
    .AddWithValue("@VrijDatumVeld05Van", Me.dtpVrijeDatum05Van.Value.Date)
    .AddWithValue("@VrijDatumVeld05Tot", Me.dtpVrijeDatum05Tot.Value.Date)

  End With
  Dim SampleDataAdapter = New SqlDataAdapter()
  SampleCommand.CommandText = strOrder_SQL
  SampleCommand.Connection = connection
  SampleDataAdapter.SelectCommand = SampleCommand
  SampleDataAdapter.Fill(SampleSource)
  TableView = SampleSource.Tables(0).DefaultView

  If Me.radOrder.Checked = True Then
    With dgvZoek
      .DataSource = TableView
      .Columns(0).Width = 40
      .Columns(0).HeaderText = "Type"
      .Columns(1).Width = 30
      .Columns(1).HeaderText = "ID"
      .Columns(2).Width = 75
      .Columns(2).HeaderText = "Order"
      .Columns(3).Width = 150
      .Columns(3).HeaderText = "Omschrijving"
      .Columns(4).Width = 100
      .Columns(4).HeaderText = "Order datum"
      .Columns(5).Width = 150
      .Columns(5).HeaderText = "Besteller"
      .Columns(6).Width = 75
      .Columns(6).HeaderText = "Status"
      .Columns(7).Width = 150
      .Columns(7).HeaderText = "Relatie"
      .Columns(8).Width = 75
      .Columns(8).HeaderText = "OrderLev"
      .Columns(9).Width = 75
      .Columns(9).HeaderText = "Geannuleerd"
      .Columns(10).Width = 75
      .Columns(10).HeaderText = "Geleverd"
      .Columns(11).Width = 100
      .Columns(11).HeaderText = "Lever datum"
    End With
  End If

  If Me.radOrderregels.Checked = True Then
    With dgvZoek
      .DataSource = TableView
      .Columns(0).Width = 40
      .Columns(0).HeaderText = "Type"
      .Columns(1).Width = 30
      .Columns(1).HeaderText = "ID"
      .Columns(2).Width = 75
      .Columns(2).HeaderText = "Order"
      .Columns(3).Width = 150
      .Columns(3).HeaderText = "Omschrijving"
      .Columns(4).Width = 100
      .Columns(4).HeaderText = "Order datum"
      .Columns(5).Width = 150
      .Columns(5).HeaderText = "Besteller"
      .Columns(6).Width = 75
      .Columns(6).HeaderText = "Artikel"
      .Columns(7).Width = 150
      .Columns(7).HeaderText = "Artikel omschrijving"
      .Columns(8).Width = 75
      .Columns(8).HeaderText = "Aantal"
      .Columns(9).Width = 75
      .Columns(9).HeaderText = "Prijs/ST"
      .Columns(10).Width = 100
      .Columns(10).HeaderText = "Status"
      .Columns(11).Width = 100
      .Columns(11).HeaderText = "Relatie"
      .Columns(12).Width = 100
      .Columns(12).HeaderText = "OrderLev"
      .Columns(13).Width = 75
      .Columns(13).HeaderText = "Geannuleerd"
      .Columns(14).Width = 75
      .Columns(14).HeaderText = "Geleverd"
      .Columns(15).Width = 100
      .Columns(15).HeaderText = "Lever datum"
    End With
  End If
End Sub

I hope this helps to explain my problem better.

[kareninstructor]

Hello again,

You can still use the logic I provided. The difference would be to load all the data then if the user selected the option with less columns hide them otherwise do nothing. My thought here is that the user wants to see the data no matter what the view of the data is.

[bodylojohn]

Hello again,

You can still use the logic I provided. The difference would be to load all the data then if the user selected the option with less columns hide them otherwise do nothing. My thought here is that the user wants to see the data no matter what the view of the data is.

Dear Kevin,

Thanks for the reply.

The thing is the 2 queries are different. So I need to use the 2 seperate queries.

BTW: what value do I have to assign to Private Opt1 As Integer() = {}
Between {}

Thanks again.

[bodylojohn]

I added

.DataSource = Nothing

to my code and now it works perfect.

Thanks everybody.