Insert into Access Table Problem!

Results 1 to 6 of 6

Thread: Insert into Access Table Problem!

  1. #1
    Marco Kooreman Guest

    Default Insert into Access Table Problem!

    I have got the following problem:<BR><BR>I have got a table with let&#039s say a 100 items which looks as follows:<BR>orderid item price<BR><BR>for instance record 5, 20 and 90 have the same orderid.<BR><BR>What I want to do now is to update another table with the items with the same orderid. So this table must be filled with 3 records.<BR><BR>I have the following code that only takes the first item and puts it in the other table called boodschappen (the ordervast field is a session field).<BR><BR>How can I create a kind of loop that puts all the 3 records in the table, or is there another solution??????<BR><BR>&#060;%<BR>SQL = "SELECT item From item where orderid = 3"<BR>Set RS = Connection.Execute(SQL)%&#062;<BR>&#060;%item=Serv er.HTMLEncode(rs.Fields("item").Value)<BR>SQL="Ins ert into boodschappen (ordervast,item) VALUES(&#039"&ordervast&"&#039,&#039"&item&"&#039) "<BR>Set RS=Connection.Execute(SQL)%&#062;

  2. #2
    Nathen Grass Guest

    Default RE: Insert into Access Table Problem!

    According to your example you have three records with the same orderid. The SELECT statement you used is selecting records by the orderid so all three records will be brought back by that query. All you have to do is go through the recordset and append the fields and values to your INSERT statement.

  3. #3
    Marco Kooreman Guest

    Default RE: Insert into Access Table Problem!

    HOW?<BR><BR>I have tried everything, it is driving me almost crazy!!<BR><BR>Thanks,<BR><BR>Marco

  4. #4
    Nathen Grass Guest

    Default RE: Insert into Access Table Problem!

    Actually you would have to run separate INSERT statements for each item:<BR><BR>&#060;%<BR>SQL = "SELECT item From item where orderid = 3"<BR>Set RS = Connection.Execute(SQL)<BR><BR>Do while not RS.EOF<BR>&nbsp;item = Server.HTMLEncode(rs.Fields("item").Value)<BR>&nbs p;SQL = "Insert into boodschappen (ordervast,item) "<BR>&nbsp;SQL = SQL & " VALUES(&#039" & ordervast & "&#039,&#039" & item & "&#039)"<BR>&nbsp;Connection.Execute SQL,,adExecuteNoRecords<BR>&nbsp;RS.MoveNext<BR>Lo op<BR>%&#062;<BR><BR>Hope this helps.


  5. #5
    Marco Kooreman Guest

    Default RE: Insert into Access Table Problem!

    Dear Nathan,<BR><BR>I am at home right now. I will try it tomorrow. If it does the trick I will let you know on the messageboeard tomorrow!.<BR><BR>Thanks in advance.<BR><BR>Marco Kooreman<BR>The Netherlands

  6. #6
    Marco Kooreman Guest

    Default THANKS

    Thank you very much, it works perfectly.<BR><BR>BR<BR><BR>Marco Kooreman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •