Hi all.

I am trying to execute a query that will pull in the primary key (CustomerID) from one data table into another table from the same database. All data is submitted on a button click event. This is what I have so far:

Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Submit.Click
Dim InsertQuery As String
Dim SelectQuery As String

InsertQuery = "INSERT INTO CustomerDetails (HouseUnitNumber, Street, Suburb, State, PostCode, Country, FirstName, Surname, Email, ContactPhone, CCType, CCName, CCNumber, CCExpiry, CCSecurityNumber) VALUES (@HouseUnitNumber, @Street, @Suburb, @State, @PostCode, @Country, @FirstName, @Surname, @Email, @ContactPhone, @CCType, @CCName, @CCNumber, @CCExpiry, @CCSecurityNumber)"
SelectQuery = "INSERT INTO SalelOrder"
Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, strConnectionString)

cmd = New OleDbCommand(InsertQuery, strConnectionString)
cmd.Parameters.AddWithValue("@HouseUnitNumber", HouseUnitNumber.Text)
cmd.Parameters.AddWithValue("@Street", Street.Text)
cmd.Parameters.AddWithValue("@Suburb", Suburb.Text)
cmd.Parameters.AddWithValue("@State", PostCode.Text)
cmd.Parameters.AddWithValue("@PostCode", State.Text)
cmd.Parameters.AddWithValue("@Country", Country.Text)
cmd.Parameters.AddWithValue("@FirstName", FirstName.Text)
cmd.Parameters.AddWithValue("@Surname", Surname.Text)
cmd.Parameters.AddWithValue("@Email", Email.Text)
cmd.Parameters.AddWithValue("@ContactPhone", Phone.Text)
cmd.Parameters.AddWithValue("@CCType", CCtype.Text)
cmd.Parameters.AddWithValue("@CCName", NameCC.Text)
cmd.Parameters.AddWithValue("@CCNumber", NumberCC.Text)
cmd.Parameters.AddWithValue("@CCExpiry", ExpCC.Text)
cmd.Parameters.AddWithValue("@CCSecurityNumber", SNCC.Text)

strConnectionString.Open()
cmdOLEDB = New OleDbCommand(InsertQuery, strConnectionString)
cmd.ExecuteNonQuery()
strConnectionString.Close()
SaveCartData()

For One table.

Protected Sub SaveCartData()
Dim InsertQuery As String
InsertQuery = "INSERT INTO SalesOrder (Description, Quantity, Price, PostageCost, CustomerID) VALUES (@Description, @Quantity, @Price, @PostageCost)"

Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, strConnectionString)
Dim cart As List(Of CartItem) = ShoppingCart.Instance.Items()
Dim s As String = ""
For i = 0 To cart.Count - 1
cmd = New OleDbCommand(InsertQuery, strConnectionString)
cmd.Parameters.AddWithValue("@Description", cart.Item(i).Description())
cmd.Parameters.AddWithValue("@Quantity", cart.Item(i).Quantity())
cmd.Parameters.AddWithValue("@Price", cart.Item(i).UnitPrice())
cmd.Parameters.AddWithValue("@PostageCost", Label1.Text)

strConnectionString.Open()
cmdOLEDB = New OleDbCommand(InsertQuery, strConnectionString)
cmd.ExecuteNonQuery()
strConnectionString.Close()
Next
End Sub

For the other.

I am using MS Access 2010 and VS2010. Any hints would be great.

Cheers.

Posted 2 years ago
Top