October 7, 2005 at 2:06 am
Hi, I am currently trying to INSERT selected rows from table Cart into table Orders. Table Cart has columns Id_record(autonumber), Id_title(LongInt), Customer(text), Table Orders has the same columns and also column Order_ID. When I delete this colums Order_ID and insert only values from table cart like this, it works.
queryString = "INSERT INTO [Orders] SELECT * FROM [Cart] WHERE Cart.Customer = @user"
Dim dbCommand as OleDbCommand = new OleDbCommand(queryString, myConnection)
dbCommand.Parameters.Add(New OleDbParameter("@user", OleDbType.Char, 20))
dbCommand.Parameters("@user").Value = user.Identity.Name
Troubles begin, when I try to insert also that randomly generated number into my table Orders. I use this code...
Dim myConnection as OleDbConnection = new OleDbConnection(connectionString)
queryString = "INSERT INTO [Orders] SELECT *, @random as Order_ID FROM [Cart] WHERE Cart.Customer = @user"
Dim dbCommand as OleDbCommand = new OleDbCommand(queryString, myConnection)
dbCommand.Parameters.Add(New OleDbParameter("@user", OleDbType.Char, 20))
dbCommand.Parameters("@user").Value = user.Identity.Name
Randomize()
dbCommand.Parameters.Add(New OleDbParameter("@random", OleDbType.Integer, 20))
dbCommand.Parameters("@random").Value = ((Int32.MaxValue) * Rnd)
I always ger error "Duplicate output destination in Id_record" and have no idea, how to fix it. Could pls somebody help me? Or could u give me some link on some good INSERT tutorial?
October 7, 2005 at 5:51 am
1)Always gives the fields a name instead of using SELECT *
INSERT INTO dbo.ORDERS
(Id_record, Id_title, Customer, Order_ID)
SELECT Id_record, Id_title, Customer, @random
FROM dbo.Cart CART
WHERE Cart.Customer = @user
The query analyzer can script out the column names so you don't have to type it yourself.
2) Id_record in orders is an identity or not?
3)You could wrap this in a stored procedure for performance,safety, flexibility.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply