Problem Updating Access DB for project PLLEEASSE HELP

Speedway

Hey,
ive a project that im working on for college.its a cd enquiry system and if im taking a customer order their details and order details are typed in various text boxes and combo boxes and then im trying to write the order to the tblOrders access table.Im having some probs tho.no errors appear but there is no new addition to the table.there are already 4 records in the table and i want to add this as a new record after these

here's my code :

Public Class frmCustomerOrder
Inherits System.Windows.Forms.Form

'declares connection, data adapter and dataset with sql to take all records from the database
Dim con2 As New OleDb.OleDbConnection("Provider = Microsoft.jet.oledb.4.0;Data Source = \2nd Year\IS2215\Project\IS2215 Project\IS2215 Project.mdb")
Dim daOrder As New OleDb.OleDbDataAdapter("Select * from tblOrders", con2)
Dim dsOrder As New DataSet()

'sub procedure to fill the customer and order
'details into the form
Public Sub FillOrder()
Dim drOrder As DataRow
drOrder = dsOrder.Tables("tblOrders").Rows.Find(cboOrderNo.SelectedItem)
txtName.Text = drOrder.Item("Name")
txtAddress.Text = drOrder.Item("Address")
txtPhone.Text = drOrder.Item("Phone_No")
txtEmail.Text = drOrder.Item("Email")
cboProdType.Text = drOrder.Item("Ordered_Product_Type")
txtProdName.Text = drOrder.Item("Ordered_Product_Name")

End Sub

Public Sub ViewOrder()
'enables the specified controls when the
'button is pressed
cboOrderNo.Enabled = True
txtName.Enabled = True
txtAddress.Enabled = True
txtPhone.Enabled = True
txtEmail.Enabled = True
txtProdName.Enabled = True
cboProdType.Enabled = True
txtName.ReadOnly = True
txtAddress.ReadOnly = True
txtPhone.ReadOnly = True
txtEmail.ReadOnly = True
txtProdName.ReadOnly = True


'clears the combo box
cboOrderNo.Items.Clear()
'clears the dataset
dsOrder.Clear()

'fills the data adapter with the Order Table contents
daOrder.FillSchema(dsOrder, SchemaType.Source, "tblOrders")
daOrder.Fill(dsOrder, "tblOrders")

'declarations for loop
Dim i As Integer
Dim strCurrentOrd As String

'loop to count the rows in the orders table and add the
'order numbers to the combo box
For i = 1 To dsOrder.Tables("tblOrders").Rows.Count
strCurrentOrd = dsOrder.Tables("tblOrders").Rows(i - 1).Item("Order_No")
cboOrderNo.Items.Add(strCurrentOrd)
Next


'sets combo box at the first item
cboOrderNo.SelectedIndex = 0

'calls fillorder sub procedure
FillOrder()
End Sub


Private Sub btnCloseForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseForm.Click
'closes the current form when the button is pressed
Close()

End Sub

Private Sub frmCustomerOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'disables the specified controls on form load
cboOrderNo.Enabled = False
txtName.Enabled = False
txtAddress.Enabled = False
txtPhone.Enabled = False
txtEmail.Enabled = False
txtProdName.Enabled = False
cboProdType.Enabled = False
btnSave.Enabled = False


End Sub


Private Sub btnViewPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewPrev.Click

ViewOrder()

End Sub

Private Sub btnAddOrd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddOrd.Click

'clears/hides/shows/enables/disables various form controls
cboOrderNo.Visible = False
lblOrdNo.Visible = True
txtName.Enabled = True
txtName.Clear()
txtAddress.Enabled = True
txtAddress.Clear()
txtPhone.Enabled = True
txtPhone.Clear()
txtEmail.Enabled = True
txtEmail.Clear()
txtProdName.Enabled = True
txtProdName.Clear()
cboProdType.Enabled = True
cboProdType.Text = ""
txtName.ReadOnly = False
txtAddress.ReadOnly = False
txtPhone.ReadOnly = False
txtEmail.ReadOnly = False
txtProdName.ReadOnly = False
btnAddOrd.Enabled = False
btnViewPrev.Enabled = False
btnSave.Enabled = True

'integer declarations for the row count loop
Dim i As Integer
Dim intNextID As Integer

'clears the dataset
dsOrder.Clear()

'fills the data adapter with the Order Table contents
daOrder.FillSchema(dsOrder, SchemaType.Source, "tblOrders")
daOrder.Fill(dsOrder, "tblOrders")

'loops through the dataset counting the rows
For i = 1 To dsOrder.Tables("tblOrders").Rows.Count
intNextID = dsOrder.Tables("tblOrders").Rows(i - 1).Item("Order_No")

Next
'adds one to the last counted order_id
'and shows it in the label as the new order number
lblOrdNo.Text = intNextID + 1



End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'declares integers to handle errors
Dim intError As Integer
intError = 0

'case statements to check for validity
Select Case txtName.Text
Case ""
intError = 1
End Select

Select Case txtAddress.Text
Case ""
intError = 1
End Select

Select Case txtPhone.Text
Case ""
intError = 1
End Select

Select Case txtEmail.Text
Case ""
intError = 1
End Select

Select Case txtProdName.Text
Case ""
intError = 1
End Select

Select Case cboProdType.Text
Case ""
intError = 1
End Select

'if the error number is 1 then a prompt appears
'requesting that all fields are completed
Dim response As MsgBoxResult

If intError = 1 Then

MsgBox("Please complete all fields", MsgBoxStyle.Exclamation, "Incomplete Fields")

Else

response = MsgBox("Are you sure all information is correct and you wish to save this order?", MsgBoxStyle.OKCancel)

If response = MsgBoxResult.OK Then
' table name
Dim dtOrder As DataTable = dsOrder.Tables("tblOrders")
Dim drSaveOrder As DataRow = dtOrder.NewRow()

' field name
drSaveOrder("Order_No") = lblOrderNo.Text
drSaveOrder("Name") = txtName.Text
drSaveOrder("Address") = txtAddress.Text
drSaveOrder("Phone_No") = txtPhone.Text
drSaveOrder("Email") = txtEmail.Text
drSaveOrder("Ordered_Product_Name") = txtProdName.Text
drSaveOrder("Ordered_Product_Type") = cboProdType.Text


lblOrdNo.Visible = False
cboOrderNo.Visible = True


ViewOrder()

Else
Exit Sub

End If


End If


End Sub
End Class


please give me a hand if u can because i CANT figure out the problem and im kinda new to vb.net

cheers

causal

No-one else has replied (probably coz the post is soooo long), so I'll throw something ur way.
Now, I ain't a .net person and honestly I haven't read your code. Could this post be any worse

Anyway, one thing you could check (given that you get no error message and your upated data isn't in the database) is whether or not you have/need a COMMIT in your code.
(If you don't already know 'COMMIT' is a SQL command to write the updated to the database)

hth,
causAl

Speedway

ya i know its WAAY too long, just thought it wud be better to put it all up.thanks for the help casual i'll have another look at it

Seth Brundle

1. have you write access to the database?
2. use a proper SQL INSERT Statement rather than the method above.

amen

when using the DataRow object is there an update/commit method ?

Gizzard

When referencing any field with and _ in it you you should reference field_name as [field_name]

also access is ****e, it would be much easier to use MySQL or even MsSQL

ressem

After adding the new row to the table you should persist it by calling the update method of the DataAdapter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatacommondataadapterclassupdatetopic.asp

stevenmu

Good idea posting all the code, it does make for a long post but in this case anyway it's going to save a lot of time.

It's a while since I've looked at .Net but I can see straight away that you're missing the code to actually update the database. I think there's basically two ways you can do this at this point.

1. You can add the datarow you've built into the dataset (I think it's something along the lines of dataset.datatable.add(datarow) ). You then call the update method of the dataset to commit the changes back to the database. You'll need to have an SQL command assigned to the dataset's updatecommand property for this to work (if you use visual studio to build your dataset it'll do this automagically, I can't remember how to do it manually but it should be easy enough to find around the net).

2. Build up an SQL insert string with the info you've collected and execute it directly against the database. Again I can't remember exactly but I think something like

Dim SQLInsertCmdString as String
Dim SQLInsertCmd as SQLCommand

SQLInsertCmdString = "insert into tblOrders (name,address) values (""" & txtName.Text & """,""" & txtAddress.Text & """)"
SQLInsertCmd.CommandString = SQLInsertCmdString
SQLInsertCmd.Connection = con2
SQLInsertCmd.ExecuteNonQuery

is along the lines of what you're looking for. I'm pretty sure there's some mistakes in the code there, but you get the idea.

.Net purists would probably say the 1st way is technically more correct, and they're probably right but the second way is nice and quick and easy. The only problem with it is building up the insert string, it's an absolute bitch getting all the "s right.

Speedway

cheers everyone thanks for the help i altered a few things and got it writing really appreciate it.