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

Binding data to custom columns using sql | DaniWeb
1 Year Ago Last Updated by DelilahDemented Question Answered Software Development > VB.NET > Binding data to custom columns using sql
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 trebax 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 trebax 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 trebax = GetType(String) ColPatName.HeaderText = "Pat Name" ColPatName.CellTemplate = New DataGridViewTextBoxCell 'Set ColPhysician Properties ColPhysician.Name = "Physician" ColPhysician.ValueType = GetType(String) ColPhysician.HeaderText = "Physician" trebax ColPhysician.CellTemplate trebax = 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
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
 
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 trebax DataGridView1.Rows.Add(MyNewRow) End while
Insert Code Snippet (Alt+I)
View similar articles that have also been tagged:
Get paid to post on DaniWeb