Public Class frmSysco
Dim MaxRows As Integer
Dim NewItem As Boolean = False
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataSet
Dim db As New OleDb.OleDbDataAdapter
Dim bs As New BindingSource
Dim bsCat As New BindingSource
Dim daCat As New OleDb.OleDbDataAdapter
Dim dsCat As New DataSet
Dim bsPUnit As New BindingSource
Dim daPUnit As New OleDb.OleDbDataAdapter
Dim dsPUnit As New DataSet
Dim bsUvUM As New BindingSource
Dim daUvUM As New OleDb.OleDbDataAdapter
Dim dsUvUM As New DataSet
Dim sql As String
Private Sub frmSysco_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = D:\Ben's Documents\Work Things\Cost Manangment System\MainDatabase.accdb"
con.Open()
sql = "SELECT * FROM SyscoMasterPriceGuide"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "SyscoPrice")
con.Close()
bs.DataSource = ds.Tables("SyscoPrice")
bs.Sort = "ProductName"
lstName.DataSource = bs
lstName.DisplayMember = "ProductName"
lstName.ValueMember = "ProductCode"
con.Open()
sql = "SELECT * FROM PurchaseCategories"
daCat = New OleDb.OleDbDataAdapter(sql, con)
daCat.Fill(dsCat, "Category")
con.Close()
bsCat.DataSource = dsCat.Tables("Category")
bsCat.Sort = "PurchaseCategory"
cboCategory.DataSource = bsCat
cboCategory.DisplayMember = "PurchaseCategory"
cboCategory.ValueMember = "ID"
con.Open()
sql = "SELECT * FROM PurchaseUnits"
daPUnit = New OleDb.OleDbDataAdapter(sql, con)
daPUnit.Fill(dsPUnit, "Unit")
con.Close()
bsPUnit.DataSource = dsPUnit.Tables("Unit")
bsPUnit.Sort = "Unit"
cboUM.DataSource = bsPUnit
cboUM.DisplayMember = "Unit"
cboUM.ValueMember = "ID"
con.Open()
sql = "SELECT * FROM UniversalUnit"
daUvUM = New OleDb.OleDbDataAdapter(sql, con)
daUvUM.Fill(dsUvUM, "Unit")
con.Close()
bsUvUM.DataSource = dsUvUM.Tables("Unit")
bsUvUM.Sort = "Unit"
cboUnivUM.DataSource = bsUvUM
cboUnivUM.DisplayMember = "Unit"
cboUnivUM.ValueMember = "ID"
MaxRows = ds.Tables("SyscoPrice").Rows.Count
txtPC.Enabled = False
Populate()
End Sub
Private Sub lstName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstName.SelectedIndexChanged
Populate()
End Sub
Private Sub Populate()
Dim dr As DataRow = DirectCast(bs.Current, DataRowView).Row
txtName.Text = dr.Item("ProductName").ToString
txtPC.Text = dr.Item("ProductCode").ToString
cboCategory.SelectedValue = dr.Item("Category")
txtPrice.Text = dr.Item("Price").ToString
cboUM.SelectedValue = dr.Item("UM")
txtUnits.Text = dr.Item("Units").ToString
txtUnitSize.Text = dr.Item("UnitSize").ToString
cboUnivUM.SelectedValue = dr.Item("UniversalUM")
txtUnivPer.Text = dr.Item("UnivUnitsPer").ToString
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If MessageBox.Show("Do you really want to Delete this Record?", _
"Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = DialogResult.No Then
Exit Sub
End If
da.AcceptChangesDuringUpdate = True
Dim cb As New OleDb.OleDbCommandBuilder(da)
bs.RemoveCurrent()
da.Update(ds, "SyscoPrice")
Populate()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
Select Case NewItem
Case False
[COLOR="Red"]'Where I am having trouble[/COLOR]
Dim dr As DataRow = DirectCast(bs.Current, DataRowView).Row
dr.Item("ProductName") = txtName.Text
dr.Item("Category") = cboCategory.SelectedValue
dr.Item("Price") = txtPrice.Text
dr.Item("UM") = cboUM.SelectedValue
dr.Item("Units") = txtUnits.Text
dr.Item("UnitSize") = txtUnitSize.Text
If cboUnivUM.SelectedValue <> Nothing Then
dr.Item("UniversalUM") = cboUnivUM.SelectedValue
End If
dr.Item("UnivUnitsPer") = txtUnivPer.Text
da.Update(ds, "SyscoPrice")
Case True
Dim dsNewRow As DataRow
Dim PC As Integer
PC = txtPC.Text
dsNewRow = ds.Tables("SyscoPrice").NewRow()
dsNewRow.Item("ProductName") = txtName.Text
dsNewRow.Item("ProductCode") = txtPC.Text
dsNewRow.Item("Category") = cboCategory.SelectedValue
dsNewRow.Item("Price") = txtPrice.Text
dsNewRow.Item("UM") = cboUM.SelectedValue
dsNewRow.Item("Units") = txtUnits.Text
dsNewRow.Item("UnitSize") = txtUnitSize.Text
dsNewRow.Item("UniversalUM") = cboUnivUM.SelectedValue
dsNewRow.Item("UnivUnitsPer") = txtUnivPer.Text
ds.Tables("SyscoPrice").Rows.Add(dsNewRow)
da.AcceptChangesDuringUpdate = True
da.Update(ds, "SyscoPrice")
NewItem = False
lstName.Enabled = True
txtPC.Enabled = False
btnDelete.Enabled = True
btnAddNew.Enabled = True
lstName.SelectedValue = PC
End Select
End Sub
Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
If NewItem = True Then
lstName.Enabled = True
txtPC.Enabled = False
btnDelete.Enabled = True
btnAddNew.Enabled = True
NewItem = False
End If
Populate()
End Sub
Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
NewItem = True
lstName.Enabled = False
txtPC.Enabled = True
btnDelete.Enabled = False
btnAddNew.Enabled = False
txtName.Clear()
txtPC.Clear()
cboCategory.SelectedValue = 0
txtPrice.Clear()
cboUM.SelectedValue = 0
txtUnits.Clear()
txtUnitSize.Clear()
cboUnivUM.SelectedValue = 0
txtUnivPer.Clear()
End Sub
End Class
