Cannot Get to Load Values on Form Load! VB SQL Access

Question

I am trying to get some values on form load, that will be automated as the user shall not type these in. I am doing these in the form of Subroutines so it looks neater and it is easier for me to reuse. Bellow you can have a look at the 5 subs I have, the first one works perfectly and the rest dont! I dont get it because they are based on the same principle!

Sub CustomerNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    conn.Open()

    Dim Rows As Integer 'Used For Customer
    Dim sql As String = "SELECT * FROM Customer"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Customer")
    Rows = ds.Tables("Customer").Rows.Count
    Customer_IDTextBox.Text = Rows
End Sub
Sub OrderNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    conn.Open()

    Dim ds As New DataSet
    Dim sql As String = "SELECT * FROM Order"
    Dim Rows As Integer 'Used for Order Table
    Dim da As OleDb.OleDbDataAdapter
    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds.Tables(0))
    Rows = ds.Tables(0).Rows.Count
    NewOrderID.Text = Rows + 1
    Order_IDTextBox.Text = NewOrderID.Text

End Sub
Sub PizzaNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    conn.Open()

    Dim Rows As Integer 'Used for Pizza Table
    Dim sql As String = "SELECT * FROM Pizza"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Pizza")
    Rows = ds.Tables("Pizza").Rows.Count
    NewPizzaID.Text = Rows + 1
    Pizza_IDTextBox.Text = NewPizzaID.Text

End Sub
Sub DrinksNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    conn.Open()

    Dim Rows As Integer 'Used for Drinks Table
    Dim sql As String = "SELECT * FROM Drinks"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Drinks")
    Rows = ds.Tables("Drinks").Rows.Count
    NewDrinksID.Text = Rows + 1
    Drinks_IDTextBox.Text = NewDrinksID.Text

End Sub
Sub SidesNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    conn.Open()

    Dim Rows As Integer 'Used for Sides Table
    Dim sql As String = "SELECT * FROM Sides"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Sides")
    Rows = ds.Tables("Sides").Rows.Count
    NewSidesID.Text = Rows + 1
    Sides_IDTextBox.Text = NewSidesID.Text

End Sub

And here is the bit that I call on form load for this to execute:

Private Sub frmProducts_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    SetValues()

    CustomerNo()
    OrderNo()
    PizzaNo()
    DrinksNo()
    SidesNo()

End Sub

The set values sub works fine so that is not a problem, it is just setting the values at the beggining. It doesnt show an error or nothing, it is just that the code doesnt seem to work and it does nothing, apart from the first CustomerNo() bit.

Thanks again a lot in advance.

Regards,

Jose

Answer 1

You have several errors going on here: 1) the word "Order" is a reserved word in nearly any DB. So you have to delimit it or the DB will throw an error. 2) in your datasets, you are trying to fill table(0) that you didn't create (and therefore, doesn't exist). Try this code:

'It is always best to put settings like this in a private property.'  
'Better still, learn how to look it up from your project settings (file)'
Private ReadOnly Property ConnString As String
    Get
        return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\Year 2\Unit 17 Project Planning\Workto do\PizzaPalce\Program\DatabasePizzaPalace.accdb"
    End Get
End Property

Sub OrderNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = ConnString
    conn.Open()

    Dim ds As New DataSet
    Dim sql As String = "SELECT * FROM [Order]"
    Dim Rows As Integer 'Used for Order Table'
    Dim da As OleDb.OleDbDataAdapter
    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds)
    Rows = ds.Tables(0).Rows.Count
    NewOrderID.Text = Rows + 1
    Order_IDTextBox.Text = NewOrderID.Text
End Sub

Sub PizzaNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = ConnString
    conn.Open()

    Dim Rows As Integer 'Used for Pizza Table'
    Dim sql As String = "SELECT * FROM Pizza"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Pizza")
    Rows = ds.Tables("Pizza").Rows.Count
    NewPizzaID.Text = Rows + 1
    Pizza_IDTextBox.Text = NewPizzaID.Text
End Sub

Sub DrinksNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = ConnString
    conn.Open()

    Dim Rows As Integer 'Used for Drinks Table'
    Dim sql As String = "SELECT * FROM Drinks"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Drinks")
    Rows = ds.Tables("Drinks").Rows.Count
    NewDrinksID.Text = Rows + 1
    Drinks_IDTextBox.Text = NewDrinksID.Text
End Sub

Sub SidesNo()
    Dim conn As New System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = ConnString
    conn.Open()

    Dim Rows As Integer 'Used for Sides Table'
    Dim sql As String = "SELECT * FROM Sides"
    Dim da As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    da = New OleDb.OleDbDataAdapter(sql, conn)
    da.Fill(ds, "Sides")
    Rows = ds.Tables("Sides").Rows.Count
    NewSidesID.Text = Rows + 1
    Sides_IDTextBox.Text = NewSidesID.Text

End Sub