mardi 5 mai 2015

DataGridView not showing all columns from table

I am currently creating a DataGridView within visual studio. The whole process is working fine and I have no errors. Apart from only one column is shown when executed when hitting 'Start' and is shown within a form.

For example in my process, only the "total cost" column is shown in the "bookingID" row in my DataGridView. I want all three columns to show, as in the code below states. The three rows are "bookingID", "paymentConfirmation" and "totalCost".

Here is the code:

     Imports System.Data.Sql
     Imports System.Data.SqlClient


Public Class Form4
Dim SQL As New SQLControl
Dim sqlCon As New SqlConnection



Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    With DGVData

        .Rows.Clear()
        .ColumnCount = 3


        .Columns(0).HeaderText = "Booking ID"
        .Columns(0).Width = 75
        .Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        .Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter


        .Columns(1).HeaderText = "Payment Confirmation"
        .Columns(1).Width = 100
        .Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter

        .Columns(2).HeaderText = "Total Cost"
        .Columns(2).Width = 100
        .Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter



    End With

    LoadBookingData()
End Sub
Public Sub LoadBookingData()
    Dim loadSQL As String = "SELECT * FROM booking"
    Dim RowsCount As Integer


    If SQL.SQLCon.State = ConnectionState.Closed Then

        SQL.RunQuery(loadSQL)

        RowsCount = SQL.SQLDS.Tables(0).Rows.Count

        MsgBox(RowsCount)
        ' there are records !
        DGVData.Rows.Add(RowsCount)
        For i As Integer = 0 To RowsCount - 1
            With DGVData
                .Rows(i).Cells(0).Value = SQL.SQLDS.Tables(0).Rows(i).Item("bookingID")
                .Rows(i).Cells(0).Value = SQL.SQLDS.Tables(0).Rows(i).Item("paymentConfirmation")
                .Rows(i).Cells(0).Value = SQL.SQLDS.Tables(0).Rows(i).Item("totalCost")
            End With
        Next

    Else

        MsgBox("There is no records", MsgBoxStyle.Critical, "Sorry")
        SQL.SQLDS.Reset()
        SQL.SQLCon.Close()
    End If

        SQL.SQLDA.Fill(SQL.SQLDS, "GettingInfo")
        RowsCount = SQL.SQLDS.Tables("GettingInfo").Rows.Count
        If RowsCount < 1 Then
            MsgBox("There is no records", MsgBoxStyle.Critical, "Sorry")
            SQL.SQLDS.Reset()
            SQL.SQLCon.Close()
        Else
            ' there are records !
            DGVData.Rows.Add(RowsCount)
            For i As Integer = 0 To RowsCount - 1
                With DGVData
                .Rows(i).Cells(0).Value = SQL.SQLDS.Tables("GettingInfo").Rows(i).Item("bookingID")
                .Rows(i).Cells(0).Value = SQL.SQLDS.Tables("GettingInfo").Rows(i).Item("paymentConfirmation")
                .Rows(i).Cells(0).Value = SQL.SQLDS.Tables("GettingInfo").Rows(i).Item("totalCost")
                End With
            Next
        End If
        SQL.SQLDS.Reset()
        SQL.SQLCon.Close()
End Sub

Here is the query within SQLControl:

    Imports System.Data.SqlClient
    Public Class SQLControl
Public SQLCon As New SqlConnection With {.ConnectionString = "Data Source=JENNIFER\DDAP2015;Initial Catalog=zachtravelagency;Integrated Security=True;"}
Public SQLcmd As SqlCommand
Public SQLDA As SqlDataAdapter
Public SQLDS As DataSet



Public Function HasConnection() As Boolean
    Try
        SQLCon.Open()

        SQLCon.Close()
        Return True
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    Return False
End Function

Public Sub RunQuery(Query As String)
    Try
        SQLCon.Open()

        ' CREATE COMMAND
        SQLcmd = New SqlCommand(Query, SQLCon)

        ' FILL DATASET
        SQLDA = New SqlDataAdapter(SQLcmd)
        SQLDS = New DataSet
        SQLDA.Fill(SQLDS)


        SQLCon.Close()
    Catch ex As Exception
        MsgBox(ex.Message)

        ' MAKE SURE CONNECTION IS CLOSED
        If SQLCon.State = ConnectionState.Open Then
            SQLCon.Close()
        End If
    End Try
End Sub

An example in code on how to fix this would be much appreciated. Thanks.

Aucun commentaire:

Enregistrer un commentaire