Question How to insert customer details into Orders table

Keys

New member
Joined
May 4, 2012
Messages
1
Programming Experience
Beginner
Hey there. I've got 3 hours left to finish a uni project. It's a basic Access database driven e-commerce site. I have a home page, a products page, an orders page, an order confirm page, a shopping cart page and a view current orders page. The site uses an Access database with three tables. A Customer table, with all of the customer details, (FirstName, LastName, EmailAdd, CardNo, CardEx, SortCode, DeliveryAdd, Postcode)
A Products table, with all the product information, (ProductID, ProductName, Price, ProductType, Images, ProductDescription).
And an Orders table which contains CustomerID and ProductID.
I'm trying to create an INSERT statement on the orders page so that when the customer inserts their details and presses the submit button the customers table will have a new record inserted. I also want this to create an entry in the orders table and redirect the client to the order confirm page which will display the details of the order.
Here is my code which runs when the submit button is clicked on the order form.

Protected Sub btnAddRecord_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim strFirstName As String
Dim strLastName As String
Dim strEmailAdd As String
Dim intCardNo As String
Dim strCardEx As String
Dim intSortCode As String
Dim strDeliveryAdd As String
Dim strPostCode As String
Dim intProductID As Integer
strFirstName = tbxFirstName.Text
strLastName = tbxLastName.Text
strEmailAdd = tbxEmailAdd.Text
intCardNo = tbxCardNo.Text
strCardEx = tbxCardEx.Text
intSortCode = tbxSortCode.Text
strDeliveryAdd = tbxDeliveryAdd.Text
strPostCode = tbxPostcode.Text
intProductID = ddlProduct.SelectedValue

Dim strDatabaseNameAndLocation As String
strDatabaseNameAndLocation = Server.MapPath("KingToots.mdb")
Dim strSQLCommand As String
strSQLCommand = "INSERT INTO Customer(FirstName, LastName, EmailAdd, CardNo, CardEx, SortCode, DeliveryAdd, Postcode) " & _
"Values ('" & strFirstName & "', '" & strLastName & "', '" & strEmailAdd & "', '" & intCardNo & "', '" & strCardEx & "', '" & intSortCode & "', '" & strDeliveryAdd & "', '" & strPostCode & "');"
Dim objOleDbConnection As System.Data.OleDb.OleDbConnection
objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
objOleDbConnection.Open()
Dim objOleDbCommand As System.Data.OleDb.OleDbCommand
objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
objOleDbCommand.ExecuteNonQuery()
objOleDbConnection.Close()
strSQLCommand = "INSERT INTO Orders(ProductID) " & "Values ('" & intProductID & "');"
objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
objOleDbConnection.Open()
objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
objOleDbCommand.ExecuteNonQuery()
objOleDbConnection.Close()
strSQLCommand = "SELECT Customer.* FROM Customer ORDER BY Customer.CustomerID DESC;"
objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
objOleDbConnection.Open()
objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
Dim objOleDbDataReader As System.Data.OleDb.OleDbDataReader
objOleDbDataReader = objOleDbCommand.ExecuteReader()
Dim datDataTable As System.Data.DataTable
datDataTable = New System.Data.DataTable()
datDataTable.Load(objOleDbDataReader)
objOleDbConnection.Close()
tbxFirstName.Text = ""
tbxLastName.Text = ""
tbxEmailAdd.Text = ""
tbxCardNo.Text = ""
tbxCardEx.Text = ""
tbxSortCode.Text = ""
tbxDeliveryAdd.Text = ""
tbxPostcode.Text = ""

End Sub

I'm getting an overflow error when I submit the form. Can anyone see what the problem is? Thanks in advance.
 
Top Bottom