473,215 Members | 1,306 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,215 software developers and data experts.

Cannot do insert and delete

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
1 1475
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Simon | last post by:
Hi I am trying to produce an update trigger. I understand the concept of delete and insert triggers without a problem. Unfortuantely, the update triggers do not have particularly simple...
8
by: Kragen Sitaker | last post by:
ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index We've been getting this error in our application every once in a while --- typically once an hour to once a day,...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
0
by: Mohammad Ali via DotNetMonster.com | last post by:
hi guys, I have a problem with my datagrid. My form is set up so I have two textboxes an add button and an editable datagrid. The datagrid is bound to a datatable which is in my cache.When I...
0
by: Luqman | last post by:
I have copied the following code from Internet, and copied it to a file named : test.aspx, and copied that file to c:\Inetpub\wwwroot Directory. When I type on my Internet explorer:...
4
by: =?Utf-8?B?UmljaA==?= | last post by:
On a form - I have a datagridview which is docked to the entire form. The datagridview allows users to Delete and/or Add Rows. On the Form_Load event I Fill the datagridview source table with a...
0
by: yukijocelyn | last post by:
I have experienced a problem here while doing a form for accessing datadbase using MS Access. I'm using the AccessDataSource control, and using Gridview to pull the data from the database. I am able...
2
by: ahmedlasheen | last post by:
hello every body when iam trying to delete the last record in DGV this erro appears "Input string was not in a correct format." this error appears only when i am trying to delete the last record....
1
by: skanemupp | last post by:
in this program when using the "c"-button it deletes the last token entered. i want to delete the token after the mousecursor. lets say the string is: 12*(81**.5+12) and i put the cursor between...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.