Binding data to custom columns using sql

[DelilahDemented]

I'm learning as I go here, but I'm stuck at the moment. I am trying to dynamically create a datagridview and populate it from a database using SQL. I have created my columns, but I don't know how to assign the data coming out of my sql query to its matching column. Some of the columns will be populated using information from the sql statement, others (not added at the moment) are supposed to be comboboxes, ect, so the user can choose the information needed. I don't even know if I'm on the right track with this. Any help, suggestions, or comments are greatly appreciated!

 Private Sub FloorInfoNeeded()
        Dim CurrentFloor As String = String.Empty

        CurrentFloor = MainMenu.FloorSelected

        Select Case CurrentFloor
            Case "3E"
                LblFloor.Text = "Three East"
                StringNeeded = "SELECT [Patient Account Number], [PV1 Room], [PV1 Financial Class], [PV1 Patient Class], [First Name] + ' ' + MI + ' ' + [Last Name] [PATIENT NAME], [PV1 Attending Phy Lastname], SUBSTRING([PV1 Admit Date/Time], 5, 2) + '/' + SUBSTRING([PV1 Admit Date/Time], 7, 2) + '/' + SUBSTRING([PV1 Admitted/Time], 1, 4), SUBSTRING([PV1 Admit Date/Time], 9, 4), [PV1 Admit Source], [PV1 Discharge Date/Time] FROM ADT WHERE ([PV1 Department] = '030' AND [PV1 Discharge Date/Time] = '') ORDER BY [PV1 Room]"
                AddColumns()
                RunSQL()
        End Select
 End Sub

 Private Sub AddColumns()
        'Declare the columns needed
        Dim ColPatNum As New DataGridViewColumn
        Dim ColRoom As New DataGridViewColumn
        Dim ColFinClass As New DataGridViewColumn
        Dim ColPatName As New DataGridViewColumn
        Dim ColPhysician As New DataGridViewColumn
        Dim ColDRG As New DataGridViewColumn

        'Set ColPatNum Properties
        ColPatNum.Name = "PatNum"
        ColPatNum.ValueType = GetType(Integer)
        ColPatNum.HeaderText = "Pat Num"
        ColPatNum.CellTemplate = New DataGridViewTextBoxCell

        'Set ColRoom Properties
        ColRoom.Name = "Room"
        ColRoom.ValueType = GetType(String)
        ColRoom.HeaderText = "Room"
        ColRoom.CellTemplate = New DataGridViewTextBoxCell

        'Set ColFinClass Properties
        ColFinClass.Name = "FinClass"
        ColFinClass.ValueType = GetType(String)
        ColFinClass.HeaderText = "FC"
        ColFinClass.CellTemplate = New DataGridViewTextBoxCell

        'Set ColPatName Properties
        ColPatName.Name = "PatName"
        ColPatName.ValueType = GetType(String)
        ColPatName.HeaderText = "Pat Name"
        ColPatName.CellTemplate = New DataGridViewTextBoxCell

        'Set ColPhysician Properties
        ColPhysician.Name = "Physician"
        ColPhysician.ValueType = GetType(String)
        ColPhysician.HeaderText = "Physician"
        ColPhysician.CellTemplate = New DataGridViewTextBoxCell

        'Set ColDRG Properties
        ColDRG.Name = "DRG"
        ColDRG.ValueType = GetType(String)
        ColDRG.HeaderText = "DRG"
        ColDRG.CellTemplate = New DataGridViewTextBoxCell

        With DataGridView1
            .Columns.Add(ColPatNum)
            .Columns.Add(ColRoom)
            .Columns.Add(ColFinClass)
            .Columns.Add(ColPatName)
            .Columns.Add(ColPhysician)
            .Columns.Add(ColDRG)
            .MultiSelect = False
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
        End With
 End Sub

 Private Sub RunSQL()
        Dim list As New List(Of String)

        Using conn As New SqlConnection(My.Settings.CPSISQLConnectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand(StringNeeded, conn)
                Dim reader As SqlDataReader = cmd.ExecuteReader

                While reader.Read()

                End While
            Catch ex As Exception

            End Try


        End Using
    End Sub

[G_Waddell]

In Your Add Columns routine add ALL the columns you need, those from the database and the extras.

Next in your RunSQL routine, loop through the result set and add new rows to the datagridview as you go:

dim MyNewRow as DatagridViewRow
While reader.Read
 myNewRow = DataGridView1.Rows.Add
    MyNewRow.Cells(0).Value = reader.Item("Patient Account Number").ToString
    'And so on....
    'For Non DB cells just set the value
    myNewRow.Cells(1).Value = MyNonDBValue
    'if you have a Checkbox column in cell 2 then and wish to check it
    MyNewRow.Cells(2).Value = True
End While

You could always go back and populate any of the extra cells if required.

IMPORTANT
You will not be databinding the grid as you are placing extra columns

If for any reason you wish to run these routines on an already populated grid, you should clear the Columns and Rows first or you will append the new columns or rows to the Existing data.

[DelilahDemented]

I am getting an error in:

MyNewRow = DataGridView1.Rows.Add

Error: Value of type 'Integer' cannot be converted to 'Systems.Windows.Forms.DataGridViewRow'

[G_Waddell]

Hi
Did you set up the columns beforehand?

hmmm must be returning the index of the row - I thought it would just add it. You could try this instead:

While reader.Read
    MyNewRow = DataGridView1.Rows(0).Clone
    'Do Whatever
    DataGridView1.Rows.Add(MyNewRow)
End while

[DelilahDemented]

Thanks for the help! I've got it working.