By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,688 Members | 1,936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,688 IT Pros & Developers. It's quick & easy.

Cannot do insert and delete

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Cor
Hi Alan,

I asume this question is answered by Jody in the message above this (with my
small addition)?

Cor
Nov 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.