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.Ole Db
Public Class Form4
Inherits System.Windows. Forms.Form
Dim dap1 As OleDbDataAdapte r
Dim das1 As DataSet
Dim myData As OleDbDataReader
Sub populate()
Dim conStr As String
Dim cnn1 As OleDb.OleDbConn ection
'connect to access DB
conStr = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=C:\Docum ents and Settings\alan\M y Documents\Visua l Studio
Projects\Functi onTest\test.mdb ;" & _
"Persist Security Info=False"
cnn1 = New OleDbConnection (conStr)
'instantiate a data adapter based on the SQL String
dap1 = New OleDbDataAdapte r("Select order_id, food_code,
food_desc, price,seq_num from order_detail", cnn1)
'************** *************** *************** *************** *************** *************** *************** *************** ***
'------------------------
'Update Command
'------------------------
dap1.UpdateComm and = New OleDbCommand("U pdate order_detail set
food_desc=@food Desc where order_id=@order id and seq_num=@seqnum ",
cnn1)
dap1.UpdateComm and.Parameters. Add("@fooddesc" ,
OleDbType.VarCh ar, 50, "food_desc" )
Dim prm1 As OleDbParameter =
dap1.UpdateComm and.Parameters. Add("@orderid", OleDbType.Numer ic)
prm1.SourceColu mn = "order_id"
Dim prm2 As OleDbParameter =
dap1.UpdateComm and.Parameters. Add("@seqnum", OleDbType.Numer ic)
prm2.SourceColu mn = "seq_num"
prm1.SourceVers ion = DataRowVersion. Original
prm2.SourceVers ion = DataRowVersion. Original
'************** *************** *************** *************** *************** *************** *************** *************** ****
'------------------
'Insert Command'
'-----------------
dap1.InsertComm and = New OleDbCommand("I nsert into
order_detail (order_id, seq_num, food_code, food_desc,price ) values" &
_
"(@orderid,@seq num,@foodcde,@f ooddesc,@price) ", cnn1)
dap1.InsertComm and.Parameters. Add("@fooddesc" ,
OleDbType.VarCh ar, 50, "food_desc" )
Dim prm3 As OleDbParameter =
dap1.InsertComm and.Parameters. Add("@orderid", OleDbType.Numer ic)
prm3.SourceColu mn = "order_id"
Dim prm4 As OleDbParameter =
dap1.InsertComm and.Parameters. Add("@seqnum", OleDbType.Numer ic)
prm4.SourceColu mn = "seq_num"
Dim prm5 As OleDbParameter =
dap1.InsertComm and.Parameters. Add("@foodcode" , OleDbType.Numer ic)
prm5.SourceColu mn = "food_code"
Dim prm6 As OleDbParameter =
dap1.InsertComm and.Parameters. Add("@fooddesc" , OleDbType.VarCh ar)
prm6.SourceColu mn = "food_desc"
Dim prm7 As OleDbParameter =
dap1.InsertComm and.Parameters. Add("@price", OleDbType.Curre ncy)
prm7.SourceColu mn = "price"
prm3.SourceVers ion = DataRowVersion. Original
prm4.SourceVers ion = DataRowVersion. Original
'************** *************** *************** *************** *************** *************** *************** *************** ******
'------------------
'Delete Command
'-----------------
dap1.DeleteComm and = New OleDbCommand("D elete from
prder_detail where order_id=@order id and seq_num=@seqnum ", cnn1)
Dim prm8 As OleDbParameter =
dap1.DeleteComm and.Parameters. Add("@orderid", OleDbType.Numer ic)
prm8.SourceColu mn = "order_id"
Dim prm9 As OleDbParameter =
dap1.DeleteComm and.Parameters. Add("@seqnum", OleDbType.Numer ic)
prm9.SourceColu mn = "seq_num"
prm8.SourceVers ion = DataRowVersion. Original
prm9.SourceVers ion = DataRowVersion. Original
'************** *************** *************** *************** *************** *************** *************** *************** ******
cnn1.Open()
das1 = New DataSet
dap1.Fill(das1, "order_deta il")
cnn1.Close()
End Sub
Private Sub Form4_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
populate()
tbBinding()
displayLV()
End Sub
Private Sub btFirst_Click(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles btFirst.Click
Me.BindingConte xt(das1, "order_detail") .Position =
Me.BindingConte xt(das1, "order_detail") .Position.MinVa lue
End Sub
Private Sub btPrevious_Clic k(ByVal sender As System.Object, ByVal
e As System.EventArg s) Handles btPrevious.Clic k
Me.BindingConte xt(das1, "order_detail") .Position -= 1
End Sub
Private Sub btNext_Click(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btNext.Click
Me.BindingConte xt(das1, "order_detail") .Position += 1
End Sub
Private Sub btLast_Click(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btLast.Click
Me.BindingConte xt(das1, "order_detail") .Position =
Me.BindingConte xt(das1, "order_detail") .Position.MaxVa lue
End Sub
Private Sub btUpdate_Click( ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles btUpdate.Click
If Me.BindingConte xt(das1, "order_detail") .Position = 0 Then
Me.BindingConte xt(das1, "order_detail") .Position -= 1
Else
Me.BindingConte xt(das1, "order_detail") .Position -= 1
Me.BindingConte xt(das1, "order_detail") .Position += 1
End If
dap1.Update(das 1, "order_deta il")
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.SelectComm and.ExecuteRead er
lvCol = New ColumnHeader
lvCol.Text = "Food Code"
lv1.Columns.Add ("Food Code", 150, HorizontalAlign ment.Left)
lvCol = New ColumnHeader
lvCol.Text = "Price $"
lv1.Columns.Add ("Price $", 100, HorizontalAlign ment.Left)
lvCol = Nothing
For i = 0 To Me.BindingConte xt(das1, "order_detail") .Count - 1
Me.BindingConte xt(das1, "order_detail") .Position = i
itm = New ListViewItem
itm.Text = tbFood.Text
itm.SubItems.Ad d(tbPrice.Text)
total += CInt(tbPrice.Te xt)
lv1.Items.Add(i tm)
lbTotal.Text = total
Next i
Me.BindingConte xt(das1, "order_detail") .Position = 0
End Sub
Private Sub btInsert_Click( ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles btInsert.Click
Dim newRow As DataRow = das1.Tables("or der_detail").Ne wRow
newRow("order_i d") = tbOrderId.Text
newRow("seq_num ") = tbSeqNum.Text
newRow("food_co de") = tbFoodCode.Text
newRow("food_de sc") = tbFood.Text
newRow("price") = tbPrice.Text
das1.Tables("or der_detail").Ro ws.Add(newRow)
dap1.Update(das 1, "order_deta il")
tbFood.DataBind ings.Add(New Binding("text", das1,
"order_detail.f ood_desc"))
tbPrice.DataBin dings.Add(New Binding("text", das1,
"order_detail.p rice"))
tbOrderId.DataB indings.Add(New Binding("text", das1,
"order_detail.o rder_id"))
tbSeqNum.DataBi ndings.Add(New Binding("text", das1,
"order_detail.s eq_num"))
tbFoodCode.Data Bindings.Add(Ne w Binding("text", das1,
"order_detail.f ood_code"))
Me.BindingConte xt(das1, "order_detail") .Position =
Me.BindingConte xt(das1, "order_detail") .Position.MaxVa lue
End Sub
Private Sub btDelete_Click( ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles btDelete.Click
Dim dav1 As DataView = New
DataView(das1.T ables("order_de tail"))
dav1.Sort = ("seq_num")
Dim rowIndex As Integer = dav1.Find(tbSeq Num.Text)
das1.Tables("or der_detail").Ro ws(rowIndex).De lete()
dap1.Update(das 1, "order_deta il")
End Sub
Private Sub Clear_Click(ByV al sender As System.Object, ByVal e As
System.EventArg s) Handles Clear.Click
Me.BindingConte xt(das1, "order_detail") .Position =
Me.BindingConte xt(das1, "order_detail") .Position.MaxVa lue
tbFood.DataBind ings.Clear()
tbPrice.DataBin dings.Clear()
tbOrderId.DataB indings.Clear()
tbSeqNum.DataBi ndings.Clear()
tbFoodCode.Data Bindings.Clear( )
tbFood.Text = ""
tbPrice.Text = ""
tbFoodCode.Text = ""
tbSeqNum.Text = CInt(tbSeqNum.T ext) + 1
End Sub
Sub tbBinding()
tbFood.DataBind ings.Add(New Binding("text", das1,
"order_detail.f ood_desc"))
tbPrice.DataBin dings.Add(New Binding("text", das1,
"order_detail.p rice"))
tbOrderId.DataB indings.Add(New Binding("text", das1,
"order_detail.o rder_id"))
tbSeqNum.DataBi ndings.Add(New Binding("text", das1,
"order_detail.s eq_num"))
tbFoodCode.Data Bindings.Add(Ne w Binding("text", das1,
"order_detail.f ood_code"))
tbOrderId.ReadO nly = True
tbSeqNum.ReadOn ly = True
'tbFoodCode.Rea dOnly = True
End Sub
Private Sub btBind_Click(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btBind.Click
tbBinding()
End Sub
End Class
