Starting a project involving Access database

[o2f2]

So I'm attempting this project, but have no idea where or how to start. What I want it to do:

A program for a mail-order company that enables a clerk to enter each catalog number for items the customer wants to order. After entering catalog number, the program looks up the item's price, description, and quantity available. When the clerk enters the quantity desired, the program verifies that the quantity is actually available and calculates the total price for that item and added to the order total. The information for the total order will be displayed on a separate sheet.

Basically, I need help determining how to do the dataset part. I do not have Access but am looking at creating a dataset in VB. But, I have all the information written as code also.

Any advice would be appreciated...I am so lost!!

Thanks!

[dunfiddlin]

You can create an Access type database from scratch easily in VB.Net. Here's an example (a High Score Database for a game) which covers all the main sections needed

If Not My.Computer.FileSystem.FileExists("ScoreDB.mdb") Then ' don't want to create it twice!
            Dim DBCat As New Catalog()
            DBCat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=ScoreDB.mdb;" & _
                    "Jet OLEDB:Engine Type=5")
            'Add table and define fields
            Dim DBCon As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ScoreDB.mdb")
'create table (High_Score) with two columns 
            Dim DBCmd As New OleDb.OleDbCommand("CREATE TABLE [High_Score] ([Name] TEXT(30), [Score] NUMERIC(10))", DBCon) 
            'Open database and complete update
            DBCon.Open()
            DBCmd.ExecuteNonQuery()
            DBCon.Close()
        End If

Once you have the database set up there's hundreds of tutorials available on how to use it so your best bet is to jump in and try things out.

[.paul.]

use a datatable
a dataset is just a collection of datatables...

if you post what you have so far, i'll help you modify it.

but, i'm sure dunfiddlin will give you a VERY helpful answer

[o2f2]

Thanks!! I'm working on setting it all up now, working through a book I've got. But, this is the hardest program I've had to write so far so I'm sure I'll need all the help I can get!

[o2f2]

Okay, so far I have the following lines of code:

Public Class Form1
Dim catno As String() = {"A20gaw", "A21ham", "B38gbh", "B67vba", "G17rgf", "M45pmm", "O17nof", "P27gph", "S49rsc", "W09swi"}
Dim desc As String() = {"Golden Apples – WA", "HoneyCrisp Apples – MN", "Green Banana – HI", "Vaccinium Blueberry - AL",
"Ruby Red Grapefruit – FL","Pulp Mango – MX", "Navel Oranges – FL", "Golden Pineapple – HI", "Red Strawberry – CA",
"Seedless Watermelon – IN"}
Dim price As Single() = {0.83, 3.13, 0.25, 0.1, 1.36, 1.11, 0.84, 2.45, 2.99, 1.7}
Dim quantity As Integer() = {388, 224, 2170, 3000, 605, 315, 823, 319, 800, 277}
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'line of code to load, move, or remove data?
Me.FruitTableAdapter.Fill(Me.FruitDataSet.Fruit)
End Sub

Private Function FruitTableAdapter() As Object
Throw New NotImplementedException
End Function

Private Function FruitDataSet() As Object
Throw New NotImplementedException
End Function

End Class

I've had to add the not implemented exception for some reason, not sure why the database didn't load differently. I will have to upload the copy of the program I am working with online, will this affect the way the program works on a different computer?

[.paul.]

try this:

Public Class Form1

    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim catno As String() = {"A20gaw", "A21ham", "B38gbh", "B67vba", "G17rgf", "M45pmm", "O17nof", "P27gph", "S49rsc", "W09swi"}
        Dim desc As String() = {"Golden Apples – WA", "HoneyCrisp Apples – MN", "Green Banana – HI", "Vaccinium Blueberry - AL", _
        "Ruby Red Grapefruit – FL", "Pulp Mango – MX", "Navel Oranges – FL", "Golden Pineapple – HI", "Red Strawberry – CA", _
        "Seedless Watermelon – IN"}
        Dim price As Single() = {0.83, 3.13, 0.25, 0.1, 1.36, 1.11, 0.84, 2.45, 2.99, 1.7}
        Dim quantity As Integer() = {388, 224, 2170, 3000, 605, 315, 823, 319, 800, 277}

        dt.Columns.Add("catno")
        dt.Columns.Add("desc")
        dt.Columns.Add("price", GetType(Single))
        dt.Columns.Add("quantity", GetType(Integer))

        For x As Integer = 0 To 9
            dt.Rows.Add(catno(x), desc(x), price(x), quantity(x))
        Next
        dt.PrimaryKey = New DataColumn() {dt.Columns(0)}

    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        'if you type a valid catno in TextBox1, you'll get a msgbox showing desc
        'to display the other fields, use the same method, i.e.: row.Item("field name")
        Dim row As DataRow = dt.Rows.Find(TextBox1.Text)
        If row IsNot Nothing Then
            MsgBox(row.Item("desc"))
        End If
    End Sub

End Class

[dunfiddlin]

but, i'm sure dunfiddlin will give you a VERY helpful answer

What? Wot did I miss this time?

[o2f2]

Code works well!! Thanks a lot!

I'm sure I'll have more questions but I'm making good progress now!

[o2f2]

So right now I've just about completed the program, but I've encountered quite a problem in that the program will only work for one item in the database. For instance, if you want to buy one orange and one apple it will not calculate the order. Additionally, I want to be able to display all items desired for order in a list box on a second form. Any ideas?

Thanks!

Public Class Form1
    Dim dt As New DataTable
    Dim tax As Decimal
    Dim cost As Decimal
    Dim exship As Integer
    Dim ship As Decimal
    Dim total As Decimal

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim catno As String() = {"A20gaw", "A21ham", "B38gbh", "B67vba", "G17rgf", "M45pmm", "O17nof", "P27gph", "S49rsc", "W09swi"}
        Dim desc As String() = {"Golden Apples – WA", "HoneyCrisp Apples – MN", "Green Banana – HI", "Vaccinium Blueberry - AL", _
        "Ruby Red Grapefruit – FL", "Pulp Mango – MX", "Navel Oranges – FL", "Golden Pineapple – HI", "Red Strawberry – CA", _
        "Seedless Watermelon – IN"}
        Dim price As Single() = {0.83, 3.13, 0.25, 0.1, 1.36, 1.11, 0.84, 2.45, 2.99, 1.7}
        Dim quantity As Integer() = {388, 224, 2170, 3000, 605, 315, 823, 319, 800, 277}

        dt.Columns.Add("catno")
        dt.Columns.Add("desc")
        dt.Columns.Add("price", GetType(Single))
        dt.Columns.Add("quantity", GetType(Integer))

        For x As Integer = 0 To 9
            dt.Rows.Add(catno(x), desc(x), price(x), quantity(x))
        Next
        dt.PrimaryKey = New DataColumn() {dt.Columns(0)}

    End Sub

  

    Private Sub FruitBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FruitBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.FruitBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.FruitDataSet)

    End Sub

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

        Me.FruitTableAdapter.Fill(Me.FruitDataSet.Fruit)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If TextBox1.Text > QuantityTextBox.Text Then
            MsgBox("You have entered a quantity that is too high. Please request fewer fruit.")
        Else
            Dim result As Integer = QuantityTextBox.Text - TextBox1.Text
            QuantityTextBox.Text = result.ToString()
        End If



        If CheckBox1.Checked Then
            tax = (cost * 0.0825)
        Else
            tax = 0
        End If

        If CheckBox2.Checked Then
            exship = 5
        Else
            exship = 0
        End If

        cost = Unit_PriceTextBox.Text * TextBox1.Text

        If cost <= 100 Then
            ship = 4.9
        Else
            If cost > 100 & cost <= 250 Then
                ship = 3.49
            Else
                If cost > 250 & cost <= 500 Then
                    ship = 2.29
                Else
                    If cost > 500 Then
                        ship = 0
                    End If
                End If
            End If

        End If

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Application.Exit()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Hide()
        Form2.Show()

        Form2.TextBox1.Text = DescriptionTextBox.Text
        Form2.TextBox2.Text = TextBox1.Text
        Form2.TextBox3.Text = cost
        Form2.TextBox4.Text = tax
        Form2.TextBox5.Text = ship
        Form2.TextBox6.Text = cost + ship + tax + exship

    End Sub
End Class