Problems with displaying and updating my database via VB 10

Question

Yaw_1 0 Newbie Poster

9 Years Ago

Imports System.Data
Imports System.Data.OleDb
Public Class Chemical_Details
    Dim MyConnection As OleDbConnection
    Dim MyDataAdapter As OleDbDataAdapter
    Dim MyCommandBuilder As OleDbCommandBuilder
    Dim MyDataTableChemical As DataTable
    Dim MyDataSet As DataSet


    Private Sub Chemical_Details_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MyConnection = New OleDbConnection 'Creates OleDB connection
        MyConnection.ConnectionString = "Provider= Microsoft.ace.oledb.12.0;Data Source=Stock Database.accdb" 'Provides the data from the ms access database
        MyDataAdapter = New OleDbDataAdapter("SELECT * FROM [Chemical],[SupplierTable],[OrderTable] WHERE Chemical.ChemicalID = OrderTable.ChemicalID AND SupplierTable.SupplierID=OrderTable.SupplierID ", MyConnection.ConnectionString) 'Selects all the items from the specified tables for a particular chemical
        MyCommandBuilder = New OleDbCommandBuilder(MyDataAdapter) 'used to manipulate database to insert, update etc..
        MyConnection.Open()
        MyDataTableChemical = New DataTable
        MyDataAdapter.Fill(MyDataTableChemical) 'Populates the data adapter
        MyConnection.Close()

        ChemicalID_TextBox.Enabled = False
        ChemicalName_TextBox.Enabled = False
        OtherChemicalName_Textbox.Enabled = False
        ChemicalState_ComboBox.Enabled = False
        ChemicalName_TextBox.Enabled = False
        UseQuantity_textbox.Enabled = False
        ChemicalThreshold_TextBox.Enabled = False
        ExpiryDate_DateTimePicker.Enabled = False
        PurchaseDate_DateTimePicker.Enabled = False
        PurchasePrice_TextBox.Enabled = False
        Hazard_ComboBox.Enabled = False

        SupplierID_TextBox.Enabled = False
        SupplierName_TextBox.Enabled = False
        AddressLine1_Textbox.Enabled = False
        AddressLine2_textbox.Enabled = False
        PostCode_textBox.Enabled = False
        TelephoneNo1_Textbox.Enabled = False
        TelephoneNo2_Textbox.Enabled = False
        Delete_Button.Enabled = False
    End Sub
    Private Sub ShowCurrentRecord()
        If MyDataTableChemical.Rows.Count = 0 Then
            MsgBox("No chemical data available on the system. Please Update.", MsgBoxStyle.Critical, "Error!")
        End If
        Exit Sub
        ChemicalID_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("Chemical.ChemicalID").ToString
        ChemicalName_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("ChemicalName").ToString 'Populates the chemical name textbox with a chemical name from the database
        OtherChemicalName_Textbox.Text = MyDataTableChemical.Rows(RowPosition)("ChemicalName(Other)").ToString
        ChemicalState_ComboBox.Text = MyDataTableChemical.Rows(RowPosition)("ChemicalState").ToString
        ChemicalQuantity_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("StockQuantity").ToString
        UseQuantity_textbox.Text = MyDataTableChemical.Rows(RowPosition)("UseQuantity").ToString
        Hazard_ComboBox.Text = MyDataTableChemical.Rows(RowPosition)("HazardName").ToString
        RoomNumber_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("RoomNo").ToString
        CupboardName_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("CupboardName").ToString
        ShelfNumber_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("ShelfNo").ToString
        ChemicalThreshold_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("ThresholdLimit").ToString
        ExpiryDate_DateTimePicker.Value = MyDataTableChemical.Rows(RowPosition)("ExpiryDate").ToString
        PurchaseDate_DateTimePicker.Value = MyDataTableChemical.Rows(RowPosition)("PurchaseDate").ToString
        PurchasePrice_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("PurchasePrice(£)").ToString

        SupplierID_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierTable.SupplierID").ToString
        SupplierName_TextBox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierName").ToString 'Populates the chemical name textbox with a chemical name from the database
        AddressLine1_Textbox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierAddress1").ToString
        AddressLine2_textbox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierCounty").ToString
        City_Textbox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierCity").ToString
        PostCode_textBox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierPostCode").ToString
        TelephoneNo1_Textbox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierTelephone").ToString
        TelephoneNo2_Textbox.Text = MyDataTableChemical.Rows(RowPosition)("SupplierTelephone2").ToString


    End Sub

    Private Sub FirstRecord_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FirstRecord_Button.Click
     RowPosition = 0 
        Call ShowCurrentRecord()
    End Sub

    Private Sub LastRecord_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LastRecord_Button.Click
        If MyDataTableChemical.Rows.Count <> 0 Then
            RowPosition = MyDataTableChemical.Rows.Count - 1
            Call ShowCurrentRecord()
        End If
    End Sub


    Private Sub Next_Buttonn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Next_Buttonn.Click
        If RowPosition <> MyDataTableChemical.Rows.Count - 1 Then
            RowPosition = RowPosition + 1
            Call ShowCurrentRecord()
        Else
            MsgBox("This is the last chemical", MsgBoxStyle.Information, "Notice")
        End If
    End Sub

    Private Sub Previous_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Previous_Button.Click
        If RowPosition <> 0 Then
            RowPosition = RowPosition - 1
            Call ShowCurrentRecord()
        Else
            MsgBox("This is the first chemical", MsgBoxStyle.Information, "Notice")
        End If
    End Sub

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

    Private Sub Edit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Edit.Click
        If Edit.Enabled = True Then
            ChemicalID_TextBox.Enabled = True
            ChemicalName_TextBox.Enabled = True
            OtherChemicalName_Textbox.Enabled = True
            ChemicalState_ComboBox.Enabled = True
            ChemicalQuantity_TextBox.Enabled = True
            UseQuantity_textbox.Enabled = True
            ChemicalThreshold_TextBox.Enabled = True
            ExpiryDate_DateTimePicker.Enabled = True
            PurchaseDate_DateTimePicker.Enabled = True
            PurchasePrice_TextBox.Enabled = True
            Hazard_ComboBox.Enabled = True


            SupplierID_TextBox.Enabled = True
            SupplierName_TextBox.Enabled = True
            AddressLine1_Textbox.Enabled = True
            AddressLine2_textbox.Enabled = True
            PostCode_textBox.Enabled = True
            TelephoneNo1_Textbox.Enabled = True
            TelephoneNo2_Textbox.Enabled = True
            Delete_Button.Enabled = True
        End If
    End Sub


    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If MyDataTableChemical.Rows.Count <> 0 Then
            'makes the requested changes to the fields in the chemical table
            MyDataTableChemical.Rows(RowPosition)("ChemicalID") = ChemicalID_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("ChemicalName") = ChemicalName_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("ChemicalName(Other)") = OtherChemicalName_Textbox.Text
            MyDataTableChemical.Rows(RowPosition)("StockQuantity") = ChemicalQuantity_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("UseQuantity") = UseQuantity_textbox.Text
            MyDataTableChemical.Rows(RowPosition)("HazardName") = Hazard_ComboBox.Text
            MyDataTableChemical.Rows(RowPosition)("ThresholdLimit") = ChemicalThreshold_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("ChemicalState") = ChemicalState_ComboBox.Text
            MyDataTableChemical.Rows(RowPosition)("PurchasePrice(£)") = PurchasePrice_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("ExpiryDate") = ExpiryDate_DateTimePicker.Value
            MyDataTableChemical.Rows(RowPosition)("PurchaseDate") = PurchaseDate_DateTimePicker.Value
            MyDataTableChemical.Rows(RowPosition)("RoomNo") = RoomNumber_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("ShelfNo") = ShelfNumber_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("CupboardName") = CupboardName_TextBox.Text

            'makes the requested changes to the fields in the supplier table
            MyDataTableChemical.Rows(RowPosition)("SupplierID") = SupplierID_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierName") = SupplierName_TextBox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierAddress1") = AddressLine1_Textbox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierCounty") = AddressLine2_textbox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierCity") = City_Textbox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierPostCode") = PostCode_textBox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierTelephone") = TelephoneNo1_Textbox.Text
            MyDataTableChemical.Rows(RowPosition)("SupplierTelephone2") = TelephoneNo2_Textbox.Text

            Try

            Catch ex As Exception
                MyDataAdapter.Update(MyDataTableChemical)
                MsgBox("Save unsuccessful!", MsgBoxStyle.Critical, "Error")
            End Try

            MsgBox(ChemicalName_TextBox.Text & " details have been saved", MsgBoxStyle.Information, "Notice")

        End If
    End Sub


End Class

vb.net

1 Contributor
1 Reply
173 Views
1 Minute Discussion Span
Latest Post 9 Years Ago Latest Post by Yaw_1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Yaw_1 0 Newbie Poster · Answer 1 · 2015-03-21T11:36:39+00:00

When I debug the code above nothing happens!. Any help will be appreciated. Thank you in advance