Trouble with INSERT command, inserting random value into the Table

• Dryden

  Newbie

  Points: 1

  More actions

  October 7, 2005 at 2:06 am

  #169133

  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?

• Jo Pattyn

  SSC-Dedicated

  Points: 32421

  More actions

  October 7, 2005 at 5:51 am

  #595817

  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.