A
alan
in my project there are
5 textbox : tbOrderid, tbSeqNum, tbFoodCode, tbFoodDesc and tbPrice
9 button: btInsert, btdelete, btUpdate, btClear, btBind, btFirst,
btPrevious, btNext and btLast
i cannot do the insert and delete function. Can anybody help me? If
you need the program I can email to anyone who want to help me,
thanks!! ^^Alan
and the code is as follow:
Imports System.Data.OleDb
Public Class Form4
Inherits System.Windows.Forms.Form
Dim dap1 As OleDbDataAdapter
Dim das1 As DataSet
Dim myData As OleDbDataReader
Sub populate()
Dim conStr As String
Dim cnn1 As OleDb.OleDbConnection
'connect to access DB
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\alan\My Documents\Visual Studio
Projects\FunctionTest\test.mdb;" & _
"Persist Security Info=False"
cnn1 = New OleDbConnection(conStr)
'instantiate a data adapter based on the SQL String
dap1 = New OleDbDataAdapter("Select order_id, food_code,
food_desc, price,seq_num from order_detail", cnn1)
'**************************************************************************************************************************
'------------------------
'Update Command
'------------------------
dap1.UpdateCommand = New OleDbCommand("Update order_detail set
food_desc=@foodDesc where order_id=@orderid and seq_num=@seqnum",
cnn1)
dap1.UpdateCommand.Parameters.Add("@fooddesc",
OleDbType.VarChar, 50, "food_desc")
Dim prm1 As OleDbParameter =
dap1.UpdateCommand.Parameters.Add("@orderid", OleDbType.Numeric)
prm1.SourceColumn = "order_id"
Dim prm2 As OleDbParameter =
dap1.UpdateCommand.Parameters.Add("@seqnum", OleDbType.Numeric)
prm2.SourceColumn = "seq_num"
prm1.SourceVersion = DataRowVersion.Original
prm2.SourceVersion = DataRowVersion.Original
'***************************************************************************************************************************
'------------------
'Insert Command'
'-----------------
dap1.InsertCommand = New OleDbCommand("Insert into
order_detail (order_id, seq_num, food_code, food_desc,price) values" &
_
"(@orderid,@seqnum,@foodcde,@fooddesc,@price)", cnn1)
dap1.InsertCommand.Parameters.Add("@fooddesc",
OleDbType.VarChar, 50, "food_desc")
Dim prm3 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@orderid", OleDbType.Numeric)
prm3.SourceColumn = "order_id"
Dim prm4 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@seqnum", OleDbType.Numeric)
prm4.SourceColumn = "seq_num"
Dim prm5 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@foodcode", OleDbType.Numeric)
prm5.SourceColumn = "food_code"
Dim prm6 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@fooddesc", OleDbType.VarChar)
prm6.SourceColumn = "food_desc"
Dim prm7 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@price", OleDbType.Currency)
prm7.SourceColumn = "price"
prm3.SourceVersion = DataRowVersion.Original
prm4.SourceVersion = DataRowVersion.Original
'*****************************************************************************************************************************
'------------------
'Delete Command
'-----------------
dap1.DeleteCommand = New OleDbCommand("Delete from
prder_detail where order_id=@orderid and seq_num=@seqnum", cnn1)
Dim prm8 As OleDbParameter =
dap1.DeleteCommand.Parameters.Add("@orderid", OleDbType.Numeric)
prm8.SourceColumn = "order_id"
Dim prm9 As OleDbParameter =
dap1.DeleteCommand.Parameters.Add("@seqnum", OleDbType.Numeric)
prm9.SourceColumn = "seq_num"
prm8.SourceVersion = DataRowVersion.Original
prm9.SourceVersion = DataRowVersion.Original
'*****************************************************************************************************************************
cnn1.Open()
das1 = New DataSet
dap1.Fill(das1, "order_detail")
cnn1.Close()
End Sub
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
populate()
tbBinding()
displayLV()
End Sub
Private Sub btFirst_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btFirst.Click
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MinValue
End Sub
Private Sub btPrevious_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btPrevious.Click
Me.BindingContext(das1, "order_detail").Position -= 1
End Sub
Private Sub btNext_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btNext.Click
Me.BindingContext(das1, "order_detail").Position += 1
End Sub
Private Sub btLast_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btLast.Click
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MaxValue
End Sub
Private Sub btUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btUpdate.Click
If Me.BindingContext(das1, "order_detail").Position = 0 Then
Me.BindingContext(das1, "order_detail").Position -= 1
Else
Me.BindingContext(das1, "order_detail").Position -= 1
Me.BindingContext(das1, "order_detail").Position += 1
End If
dap1.Update(das1, "order_detail")
End Sub
Sub displayLV()
Dim lvCol As ColumnHeader
Dim itm As ListViewItem
Dim sht1 As Short
Dim total As Integer = 0
Dim i As Integer = 0
' myData = dap1.SelectCommand.ExecuteReader
lvCol = New ColumnHeader
lvCol.Text = "Food Code"
lv1.Columns.Add("Food Code", 150, HorizontalAlignment.Left)
lvCol = New ColumnHeader
lvCol.Text = "Price $"
lv1.Columns.Add("Price $", 100, HorizontalAlignment.Left)
lvCol = Nothing
For i = 0 To Me.BindingContext(das1, "order_detail").Count - 1
Me.BindingContext(das1, "order_detail").Position = i
itm = New ListViewItem
itm.Text = tbFood.Text
itm.SubItems.Add(tbPrice.Text)
total += CInt(tbPrice.Text)
lv1.Items.Add(itm)
lbTotal.Text = total
Next i
Me.BindingContext(das1, "order_detail").Position = 0
End Sub
Private Sub btInsert_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btInsert.Click
Dim newRow As DataRow = das1.Tables("order_detail").NewRow
newRow("order_id") = tbOrderId.Text
newRow("seq_num") = tbSeqNum.Text
newRow("food_code") = tbFoodCode.Text
newRow("food_desc") = tbFood.Text
newRow("price") = tbPrice.Text
das1.Tables("order_detail").Rows.Add(newRow)
dap1.Update(das1, "order_detail")
tbFood.DataBindings.Add(New Binding("text", das1,
"order_detail.food_desc"))
tbPrice.DataBindings.Add(New Binding("text", das1,
"order_detail.price"))
tbOrderId.DataBindings.Add(New Binding("text", das1,
"order_detail.order_id"))
tbSeqNum.DataBindings.Add(New Binding("text", das1,
"order_detail.seq_num"))
tbFoodCode.DataBindings.Add(New Binding("text", das1,
"order_detail.food_code"))
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MaxValue
End Sub
Private Sub btDelete_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btDelete.Click
Dim dav1 As DataView = New
DataView(das1.Tables("order_detail"))
dav1.Sort = ("seq_num")
Dim rowIndex As Integer = dav1.Find(tbSeqNum.Text)
das1.Tables("order_detail").Rows(rowIndex).Delete()
dap1.Update(das1, "order_detail")
End Sub
Private Sub Clear_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Clear.Click
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MaxValue
tbFood.DataBindings.Clear()
tbPrice.DataBindings.Clear()
tbOrderId.DataBindings.Clear()
tbSeqNum.DataBindings.Clear()
tbFoodCode.DataBindings.Clear()
tbFood.Text = ""
tbPrice.Text = ""
tbFoodCode.Text = ""
tbSeqNum.Text = CInt(tbSeqNum.Text) + 1
End Sub
Sub tbBinding()
tbFood.DataBindings.Add(New Binding("text", das1,
"order_detail.food_desc"))
tbPrice.DataBindings.Add(New Binding("text", das1,
"order_detail.price"))
tbOrderId.DataBindings.Add(New Binding("text", das1,
"order_detail.order_id"))
tbSeqNum.DataBindings.Add(New Binding("text", das1,
"order_detail.seq_num"))
tbFoodCode.DataBindings.Add(New Binding("text", das1,
"order_detail.food_code"))
tbOrderId.ReadOnly = True
tbSeqNum.ReadOnly = True
'tbFoodCode.ReadOnly = True
End Sub
Private Sub btBind_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btBind.Click
tbBinding()
End Sub
End Class
5 textbox : tbOrderid, tbSeqNum, tbFoodCode, tbFoodDesc and tbPrice
9 button: btInsert, btdelete, btUpdate, btClear, btBind, btFirst,
btPrevious, btNext and btLast
i cannot do the insert and delete function. Can anybody help me? If
you need the program I can email to anyone who want to help me,
thanks!! ^^Alan
and the code is as follow:
Imports System.Data.OleDb
Public Class Form4
Inherits System.Windows.Forms.Form
Dim dap1 As OleDbDataAdapter
Dim das1 As DataSet
Dim myData As OleDbDataReader
Sub populate()
Dim conStr As String
Dim cnn1 As OleDb.OleDbConnection
'connect to access DB
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Documents and Settings\alan\My Documents\Visual Studio
Projects\FunctionTest\test.mdb;" & _
"Persist Security Info=False"
cnn1 = New OleDbConnection(conStr)
'instantiate a data adapter based on the SQL String
dap1 = New OleDbDataAdapter("Select order_id, food_code,
food_desc, price,seq_num from order_detail", cnn1)
'**************************************************************************************************************************
'------------------------
'Update Command
'------------------------
dap1.UpdateCommand = New OleDbCommand("Update order_detail set
food_desc=@foodDesc where order_id=@orderid and seq_num=@seqnum",
cnn1)
dap1.UpdateCommand.Parameters.Add("@fooddesc",
OleDbType.VarChar, 50, "food_desc")
Dim prm1 As OleDbParameter =
dap1.UpdateCommand.Parameters.Add("@orderid", OleDbType.Numeric)
prm1.SourceColumn = "order_id"
Dim prm2 As OleDbParameter =
dap1.UpdateCommand.Parameters.Add("@seqnum", OleDbType.Numeric)
prm2.SourceColumn = "seq_num"
prm1.SourceVersion = DataRowVersion.Original
prm2.SourceVersion = DataRowVersion.Original
'***************************************************************************************************************************
'------------------
'Insert Command'
'-----------------
dap1.InsertCommand = New OleDbCommand("Insert into
order_detail (order_id, seq_num, food_code, food_desc,price) values" &
_
"(@orderid,@seqnum,@foodcde,@fooddesc,@price)", cnn1)
dap1.InsertCommand.Parameters.Add("@fooddesc",
OleDbType.VarChar, 50, "food_desc")
Dim prm3 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@orderid", OleDbType.Numeric)
prm3.SourceColumn = "order_id"
Dim prm4 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@seqnum", OleDbType.Numeric)
prm4.SourceColumn = "seq_num"
Dim prm5 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@foodcode", OleDbType.Numeric)
prm5.SourceColumn = "food_code"
Dim prm6 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@fooddesc", OleDbType.VarChar)
prm6.SourceColumn = "food_desc"
Dim prm7 As OleDbParameter =
dap1.InsertCommand.Parameters.Add("@price", OleDbType.Currency)
prm7.SourceColumn = "price"
prm3.SourceVersion = DataRowVersion.Original
prm4.SourceVersion = DataRowVersion.Original
'*****************************************************************************************************************************
'------------------
'Delete Command
'-----------------
dap1.DeleteCommand = New OleDbCommand("Delete from
prder_detail where order_id=@orderid and seq_num=@seqnum", cnn1)
Dim prm8 As OleDbParameter =
dap1.DeleteCommand.Parameters.Add("@orderid", OleDbType.Numeric)
prm8.SourceColumn = "order_id"
Dim prm9 As OleDbParameter =
dap1.DeleteCommand.Parameters.Add("@seqnum", OleDbType.Numeric)
prm9.SourceColumn = "seq_num"
prm8.SourceVersion = DataRowVersion.Original
prm9.SourceVersion = DataRowVersion.Original
'*****************************************************************************************************************************
cnn1.Open()
das1 = New DataSet
dap1.Fill(das1, "order_detail")
cnn1.Close()
End Sub
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
populate()
tbBinding()
displayLV()
End Sub
Private Sub btFirst_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btFirst.Click
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MinValue
End Sub
Private Sub btPrevious_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btPrevious.Click
Me.BindingContext(das1, "order_detail").Position -= 1
End Sub
Private Sub btNext_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btNext.Click
Me.BindingContext(das1, "order_detail").Position += 1
End Sub
Private Sub btLast_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btLast.Click
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MaxValue
End Sub
Private Sub btUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btUpdate.Click
If Me.BindingContext(das1, "order_detail").Position = 0 Then
Me.BindingContext(das1, "order_detail").Position -= 1
Else
Me.BindingContext(das1, "order_detail").Position -= 1
Me.BindingContext(das1, "order_detail").Position += 1
End If
dap1.Update(das1, "order_detail")
End Sub
Sub displayLV()
Dim lvCol As ColumnHeader
Dim itm As ListViewItem
Dim sht1 As Short
Dim total As Integer = 0
Dim i As Integer = 0
' myData = dap1.SelectCommand.ExecuteReader
lvCol = New ColumnHeader
lvCol.Text = "Food Code"
lv1.Columns.Add("Food Code", 150, HorizontalAlignment.Left)
lvCol = New ColumnHeader
lvCol.Text = "Price $"
lv1.Columns.Add("Price $", 100, HorizontalAlignment.Left)
lvCol = Nothing
For i = 0 To Me.BindingContext(das1, "order_detail").Count - 1
Me.BindingContext(das1, "order_detail").Position = i
itm = New ListViewItem
itm.Text = tbFood.Text
itm.SubItems.Add(tbPrice.Text)
total += CInt(tbPrice.Text)
lv1.Items.Add(itm)
lbTotal.Text = total
Next i
Me.BindingContext(das1, "order_detail").Position = 0
End Sub
Private Sub btInsert_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btInsert.Click
Dim newRow As DataRow = das1.Tables("order_detail").NewRow
newRow("order_id") = tbOrderId.Text
newRow("seq_num") = tbSeqNum.Text
newRow("food_code") = tbFoodCode.Text
newRow("food_desc") = tbFood.Text
newRow("price") = tbPrice.Text
das1.Tables("order_detail").Rows.Add(newRow)
dap1.Update(das1, "order_detail")
tbFood.DataBindings.Add(New Binding("text", das1,
"order_detail.food_desc"))
tbPrice.DataBindings.Add(New Binding("text", das1,
"order_detail.price"))
tbOrderId.DataBindings.Add(New Binding("text", das1,
"order_detail.order_id"))
tbSeqNum.DataBindings.Add(New Binding("text", das1,
"order_detail.seq_num"))
tbFoodCode.DataBindings.Add(New Binding("text", das1,
"order_detail.food_code"))
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MaxValue
End Sub
Private Sub btDelete_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btDelete.Click
Dim dav1 As DataView = New
DataView(das1.Tables("order_detail"))
dav1.Sort = ("seq_num")
Dim rowIndex As Integer = dav1.Find(tbSeqNum.Text)
das1.Tables("order_detail").Rows(rowIndex).Delete()
dap1.Update(das1, "order_detail")
End Sub
Private Sub Clear_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Clear.Click
Me.BindingContext(das1, "order_detail").Position =
Me.BindingContext(das1, "order_detail").Position.MaxValue
tbFood.DataBindings.Clear()
tbPrice.DataBindings.Clear()
tbOrderId.DataBindings.Clear()
tbSeqNum.DataBindings.Clear()
tbFoodCode.DataBindings.Clear()
tbFood.Text = ""
tbPrice.Text = ""
tbFoodCode.Text = ""
tbSeqNum.Text = CInt(tbSeqNum.Text) + 1
End Sub
Sub tbBinding()
tbFood.DataBindings.Add(New Binding("text", das1,
"order_detail.food_desc"))
tbPrice.DataBindings.Add(New Binding("text", das1,
"order_detail.price"))
tbOrderId.DataBindings.Add(New Binding("text", das1,
"order_detail.order_id"))
tbSeqNum.DataBindings.Add(New Binding("text", das1,
"order_detail.seq_num"))
tbFoodCode.DataBindings.Add(New Binding("text", das1,
"order_detail.food_code"))
tbOrderId.ReadOnly = True
tbSeqNum.ReadOnly = True
'tbFoodCode.ReadOnly = True
End Sub
Private Sub btBind_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btBind.Click
tbBinding()
End Sub
End Class