Insert multiple records from one table to another table

Hello.

Got a quick problem. I need to post shopping cart items from a temperary table "ShopCart" to a permanent table "tbl_orders". Problem is, I cannot figure out how to iterate through the recordset to post the unique products from "ShopCart" to "tbl_orders". The code below inserts the first product-- but that's it. If there are 6 different products, it will insert the first product 6 times.

Public Sub GetShopCart()
Dim ordernumber = session("ordernumber")
Dim objConn as new SqlConnection(strConn)
Dim strSQL as String
strSQL = "Select * FROM shopcart WHERE ordernumber = " & ordernumber
Dim sda as new SqlDataAdapter(strSQL, objConn)
Dim ds as new system.data.DataSet()
sda.Fill(ds,"dsGetCart")
Dim orderdate = ds.tables(0).rows(0).Item("orderdate")
Dim orderunumber = ds.tables(0).rows(0).Item("ordernumber")
Dim itemnumber = ds.tables(0).rows(0).Item("itemnumber")
Dim itemquantity = ds.tables(0).rows(0).Item("itemquantity")
Dim itemprice = ds.tables(0).rows(0).Item("itemprice")
Dim itemamount = ds.tables(0).rows(0).Item("itemamount")
Dim itemsize = ds.tables(0).rows(0).Item("itemsize")
Dim I as integer
For I = 0 to ds.tables(0).rows.count -1
StoreOrder(ordernumber, orderdate, itemnumber, itemquantity, itemprice, itemamount, itemsize)
Next
End Sub

Public Sub StoreOrder(byval ordernumber as string, byval orderdate as string, byval itemnumber as string, byval itemquantity as integer, byval itemprice as string, byval itemamount as string, byval itemsize as string)
Dim cnn As New SQLConnection(System.Configuration.ConfigurationSettings.AppSettings("slcart"))
Dim InsertCommand As SqlCommand = New SqlCommand()
InsertCommand.Connection = cnn
Dim sql As String
'end sizing chart
sql = "Insert Into tbl_orders (OrderNumber, OrderItem, OrderQuantity, OrderSize, OrderDate, OrderAmount, OrderPrice) Values (" & ordernumber & ", " & itemnumber & ", " & itemquantity & ", '" & itemsize & "'," & orderdate & ", " & itemamount & ", " & itemprice & ")"
response.Write(sql.tostring)
try
InsertCommand.CommandText = sql
cnn.Open()
InsertCommand.ExecuteNonQuery()
cnn.Close()
catch ex as exception
response.Write(ex.tostring)
end try
'cleanup()
End Sub
Any help would be GREATLY appreciated.
Thanks.
0
vantechnet
6/7/2004 8:13:03 PM
asp.net.getting-started 91979 articles. 4 followers. Follow

4 Replies
775 Views

Similar Articles

[PageSpeed] 8

why dont you move this whole process to a stored proc ? you can get everything done in one trip rather than 7 trips as in the case above ??

hth
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
6/7/2004 8:40:52 PM
try this instead:


Dim orderdate
Dim orderunumber
Dim itemnumber
Dim itemquantity
Dim itemprice
Dim itemamount
Dim itemsize
Dim I as integer
For I = 0 to ds.tables(0).rows.count -1
orderdate = ds.tables(0).rows(I).Item("orderdate")
orderunumber = ds.tables(0).rows(I).Item("ordernumber")
itemnumber = ds.tables(0).rows(I).Item("itemnumber")
itemquantity = ds.tables(0).rows(I).Item("itemquantity")
itemprice = ds.tables(0).rows(I).Item("itemprice")
itemamount = ds.tables(0).rows(I).Item("itemamount")
itemsize = ds.tables(0).rows(I).Item("itemsize")
StoreOrder(ordernumber, orderdate, itemnumber, itemquantity, itemprice, itemamount, itemsize)
Next

Mike Banavige
~~~~~~~~~~~~

Need a site code sample in a different language? Try converting it with: http://converter.telerik.com/
0
mbanavige
6/7/2004 8:41:26 PM
and yes...NDINAKAR is absulutely right.  Hitting the DB multiple times like you are is a bit much.  A single call to a stored proc is definately the way to go from a performance standpoint.
Mike Banavige
~~~~~~~~~~~~

Need a site code sample in a different language? Try converting it with: http://converter.telerik.com/
0
mbanavige
6/7/2004 8:44:15 PM
Cool it's working!  Thanks.  The reason I didn't do this in a stored procedure, is because I'm more confused when it comes to sprocs, than I am with VB
0
vantechnet
6/7/2004 9:27:14 PM
Reply:

Similar Artilces:

can I compare one record from one table to another record in another table?
can I compare one record from one table to another record in another table? I need to test if this condition is met. My scenario is: i need to check one table for a value and compare it to a list of records. for example, table 1           columnrecord 1- applesrecord 2- orangesrecord 3- grapes table 2            col1   col2   col3    col4record 1- Null , Null, Null, grapes I need to compare table 2 records with a list of records from table 1. the fru...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

Get records from one table and insert into another one
I make that but I did not work, thanks  SqlCommand cmd2;cmd = new SqlCommand("SELECT CS_ID FROM Tbl_Conselheiros WHERE CS_TIPO IN (5,6,7)", conn); dr = cmd.ExecuteReader();  while (dr.Read()) {cmd2 = new SqlCommand("SP_PRES_GRAVAR", conn); cmd2.CommandType = CommandType.StoredProcedure;SqlParameter id = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);id.Value = Convert.ToInt32(dr[0]); cmd2.Parameters.Add(id);SqlParameter data2 = new SqlParameter("@DATA", SqlDbType.VarChar, 8); data2.Value = FormataData(); cmd2.Parameters.Add(data...

Inserting Multiple Records From A Table Into Another Table
I have a situation where I ran an query in ASP and displayed my data in a table.  Now I want to take that consolidated table and insert it into a different table.  How do I write the SQL query for that?  For the sake of the thread lets say the table is titled guests and the field I want to import are fname, lname and email   I have tried multiple ways and when I do something I get a single new record in the database, but all the fields are blank.   I really appreciate any assistance. you should loop through the consolidated table one row at a time and inse...

Design Question
Hello.I'm wondering what my best options are here....I have one MSSQL2005 table I'll call MSTR_BOL and one called DETAIL_BOL.  The idea is to have one page where a user can enter into MSTR_BOL a header record so to speak.  This header record would have such items as BOL number, Customer number, Carrier and so on.  Then below that (on the form) would be an area to enter DETAIL_BOL items for that MSTR_BOL records.  The link between the two tables would be BOL number.The catch is for a given MSTR_BOL records there are, or can be, multiple MSTR_BOL records.  So, for MSTR...

Getting information from one table and applying it to another table record.G
Hello, I'm creating a website in ASP.NET and C#, My problem is I want to add a new record to a table using data from another table. i.e. Add a book, to set the book category you have to select if from a databound dropdown in another table. All those categories are stored in a different table. I'm not sure how to do that. Any help is appreciated. Thanks a lot!! Gabriel do datasource copy in the server-side or javascript copy in the client-sideRichard Tsuei (Chikun Cui)Contact me via E-mail: ricktsuei@gmail.comWeblog: http://ricktsuei.cnblogs.com+-+-+-+-+-+-+-+-+-+-+-+-+-...

how to order records from one table by records of another table?
i have two tables: subject ~~~~~~~ name number (primary key document ~~~~~~~~ name, number (primary key) subject_number (foreign key) what SQL query will return a table (view) of the subjects, ordered by the number of documents each subject has? subjects with no documents should not be included. Hello, Following is a query: SELECT subject.name, Document.name FROM subject INNER JOIN Document1 ON subject.number = Document.subject_number ORDER BY Document.name Dear, this is not a SQL centric blog. So its may not possible every time to solve SQL relat...

is there an sql statement to insert data to one table and getting the data from another table
Ok I have a table that have general categories with just name and CatNo. Now what I want to know is when I create a client to move all the data in general categories to another table called categories that have the clientid to associate it with the client. I would appreciate for any help what is your question here??Shravan Addaypally MCP...

On Record Inserted in table a, Insert record in table b
 Hi Guys, I have heard that is is possible to write a script (stored procedure) that my MS SQL Server creates a record in table b for every record in table a.e.g.I insert "CustomerNr"  [pk] and "CustomerName" in table a and I want that this "CustomerNr" is now used to create a row in table b (where there are only update queries and no insert queries).is that possible?many thankspolynaux  You can use a trigger in the tablea insert command to insert a new row in  the tablebVikram www.vikramlakhotia.comPlease mark the answer if it helped ...

inserting data from one table to another table..
Hi Guys!  I am stuck on inserting data. Hope you will help me. I have two tables name Customer and Address; Customer(cust_id,name,add_fk);    fk=foreign key,  cust_id and add_id are primary key Address(add_id,street,city,zip); Now, how can I retrieve add_id from Address table and insert into Customer table ( in the add_fk field) using stored procedure with parameters? I am using Oracle 9i.  I would appreciate your help. Thanks! Are you using any of this data in between these actions? (eg: Are they going to reside in your page in a grid, or formview?) ...

Inserting from datas in one table from Multiple tables
Hi guys,I have a problem with my query. What i want to happen is to populate my table EV_NOTIFICATIONDETAILS (Docownerid, CurrentSentDate, LastSentDate, detailsID, GeneralRemarks) using the datas from the two different tables EV_NOTIFICATIONHEADER and EV_DOCDETAILS.I tried to create some a query but im having a error. The problem is once i insert the data from datas to the columns Docownerid, CurrentSentDate, LastSentDate the datas are stored in the database and when i tried to insert the remaining columns TO EV_NOTIFICATIONDETAILS detailsID, GeneralRemarks getting the datas from EV_DOCDETAI...

1 record from one table N records from another table using gridview in single row?
Hi there, I have 2 tables named question(questionid,question) and options(optionid,options) In that questions table I got one record and in options table 3 records. Whenever I query I got 3 records 1)question1 choice1 2)question1 choice2 3)question1 choice3 I would like to use gridview(I would like to edit those values) to display these in a single row. like Question1 ch1 ch2 ch3(one record) . But the problem is I always getting 3 rows. I have seen some example(nested datalist) but they are using only one table, I am using 2 tables. Can any one help me to acheive th...

Inserting Data Into Two Tables (Getting ID from Table 1 and inserting into Table 2)
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.  Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)  This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!  Insert Table1(Title,Description,Link,Whatever)Values(@title,@descri...

Copy Records From one table to another table with same structure
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem 1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right? 2) these two table have One Prinary Key column with IDENTITY feature. Any Help Greatly appriciated.Regards.Nothing Is Quite Impossible !!! Personally, i found DTS is the best way you just need to set&nbs...

Web resources about - Insert multiple records from one table to another table - asp.net.getting-started

Multiple sequence alignment - Wikipedia, the free encyclopedia
A multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...

Girl, 5, stabbed multiple times outside apartment building in Ballarat; man arrested
A five-year-old girl is in a critical condition after being stabbed multiple times in Ballarat.

Multiple probes in case of missing former Olympian and funds' seller
At least three investigations are underway in the case of an investment dealer and former Canadian Olympic rower who has gone missing from Victoria. ...

Tim Hortons Abruptly Shuts Multiple U.S. Locations, Taking Customers, Staff By Surprise 76
Managers and employees at Tim Hortons locations in New York and Maine say they were let go with virtually no notice this week, amid a round of ...

iOS 9 How-To: Quickly delete multiple images in Messages to free up space without losing your conversations ...
... actually gets stored twice on the device: within Photos and within Messages. In this How-To article, I’m going to discuss how to delete multiple ...

CNN Reporter Describes Multiple Explosions During Overnight Raid
Around 1:30 am ET, CNN’s Atika Shubert was reporting from the Paris suburb of Saint Denis where police were conducting an overnight raid tied ...

Ten killed in multiple bombings, armed attacks around Baghdad
Ten killed in multiple bombings, armed attacks around Baghdad

ROEV lets you use multiple charging networks with one account
Filed under: Green , Green Automakers , Electric Blink, ChargePoint, and EVgo are partnering up with ROEV, a new association that will let you ...

Kate Hudson dumped Nick Jonas because she ‘wants to pursue multiple people’
... But not for long, The source also said the Bride Wars star wants to get into the dating scene again. And soon. ‘Kate wants to pursue multiple ...

Manage multiple Google marketing accounts easily
... you work with any partners, vendors, agencies, or clients, you probably even have Google logins for them. The uncomfortable reality of multiple ...

Resources last updated: 11/26/2015 8:25:37 AM