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
Yaw_1 0 Newbie Poster
Yaw_1 0 Newbie Poster
When I debug the code above nothing happens!. Any help will be appreciated. Thank you in advance
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.