Filling DataGridView from DataSet issue

Question

I'm having trouble fill up my DataGridView. I have three bits of code which are underlined blue:

1. 'SqlDataAdapter'- It says "Type 'SqlDataAdapter' is not defined"
2. 'dgv'- It says "'dgv' is not declared"
3. 'SQLCon' - It says "'SQLCon' is not declared"

The code within my form:

    Public Class Form4
Dim SQL As New SQLControl

Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    With DGVData
        SQL.SQLDS = Nothing
        .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
Private Sub LoadBookingData()
    Dim loadSQL As String = "SELECT * FROM booking"
    Dim LoadAdapter As New SqlDataAdapter
    Dim LoadDataSet As New DataSet
    Dim RowsCount As Integer

    dgv.Rows.Clear()

    If SQLCon.State = ConnectionState.Closed Then
        SQLCon.open()
        LoadAdapter.fill(LoadDataSet, "GettingInfo").
        RowsCount = LoadDataSet.Tables("GettingInfo").Rows.Count
        If RowsCount < 1 Then
            MsgBox("There is no records", MsgBoxStyle.Critical, "Sorry")
            LoadDataSet.Reset()
            Con.Close()
        Else
            ' there are records !
            dvg.Rows.Add(RowsCount)
            For i As Integer = 0 To RowsCount - 1
                With dvg
                    .Rows(1).Cells(0).Value = LoadDataSet.Tables("GettingInfo").Rows(i).Item("bookingID")
                    .Rows(1).Cells(0).Value = LoadDataSet.Tables("GettingInfo").Rows(i).Item("paymentConfirmation")
                    .Rows(1).Cells(0).Value = LoadDataSet.Tables("GettingInfo").Rows(i).Item("totalCost")
                End With
            Next
        End If
        LoadDataSet.Reset()
        Con.Close()

    Else
        ' the connection is already open 
        LoadAdapter.fill(LoadDataSet, "GettingInfo").
       RowsCount = LoadDataSet.Tables("GettingInfo").Rows.Count
        If RowsCount < 1 Then
            MsgBox("There is no records", MsgBoxStyle.Critical, "Sorry")
            LoadDataSet.Reset()
            Con.Close()
        Else
            ' there are records !
            dvg.Rows.Add(RowsCount)
            For i As Integer = 0 To RowsCount - 1
                With dvg
                    .Rows(1).Cells(0).Value = LoadDataSet.Tables("GettingInfo").Rows(i).Item("bookingID")
                    .Rows(1).Cells(0).Value = LoadDataSet.Tables("GettingInfo").Rows(i).Item("paymentConfirmation")
                    .Rows(1).Cells(0).Value = LoadDataSet.Tables("GettingInfo").Rows(i).Item("totalCost")
                End With
            Next
        End If
        LoadDataSet.Reset()
        Con.Close()
    End If
End Sub

My 'SQLControl.vb' which i think something has to do with it :

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

Can someone highlight my errors please?

Answer 1

There are a couple of things.

1. You need to import System.Data.SqlClient into your Form to use the following

   Dim LoadAdapter As New SqlDataAdapter

2. You are using dgv.Rows.Clear() in one place and then you are seen using dvg.Rows.Add(RowsCount) down the line. Why are you using dgv and dvg to refer the grid? What is the correct name of the Grid?

3. As you are using the SQLCon variable which is a Private member of SQLControl class, you need to
   a. First make it Public if you want to use it in Form
   b. use SQL.SQLCon when you are referring to the variable. Like

   If SQL.SQLCon.State = ConnectionState.Closed Then

4. Why do you even need this SQL.SQLDS = Nothing if you are not using the SQLDS variable?

5. Why do you need Dim LoadAdapter As New SqlDataAdapter and Dim LoadDataSet As New DataSet as you already have a SqlDataAdapter and DataSet declared in SQLCOntrol class? You can simply use SQL. SQLDA and SQL.SQLDS instead of LoadAdapter and LoadDataSet.

6. You have an extra . after the SQL.SQLDA.Fill(SQL.SQLDS, "GettingInfo"). Remove the extra . and try.
7. Also, I don't think the name of the table inside your DataSet will be GettingInfo. You might want to use RowsCount = LoadDataSet.Tables("booking").Rows.Count

There will be a lot of errors and you have a long way to go before this code starts working. You will find a ton of tutorials in the net, like this one, teaching what you are trying to achieve.