C# multiple inserts into MS SQL DB from single form

I have a user form to plan future loads for delivery in ASP.net / C#.  The user pre-enters the delivery loads for the next day.  Often, multiple loads are to and from the same location.  I am looking for a way for the user to determine how many times the same information from one form entry is entered in to the database.  

Example form entries.

LOADID    Date        PickupLocation     Delivery Location      Commodity           Number of loads    


If the user puts the number 10 for number of loads, the entered DATE, PICKUP, DELIVERY AND COMMODITY will be entered into the database in 10 new rows with unique LOADID's

I am not sure where to start looking for the solution to this.  

Any help is appreciated.
LVL 5
rtayIT DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghu MutalikdesaiSenior Manager in a leading IT Services companyCommented:
Here are the steps:
1. Ensure that LOADID is set as primary key in the table (assuming table name is TblLoads). Also, set Auto Increment property to true
2. Next, get the correct connection string that you will require in the code (refer http://www.connectionstrings.com/sqlconnection/ for exhaustive guide)
3. You would have captured 5 input values from the form: Number of loads (int), Commodity (string), Delivery Location (string), Pickup Location (string) and Date (DateTime). Now you will need to put rest of the code pieces together:
// Include these namespaces above the class definition
using System.Data;
using System.Data.SqlClient;

public class DataLoad
{
    protected void AddLoads()
    {
        // These variables will contain the actual values from Form Input
        int loadCount;
        string pLoc, dLoc, commodity;
        DateTime date;

        // Connection string - please refer to link as discussed before
        string connStr;

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            conn.Open();
            string insertSql = string.Format("INSERT INTO TblLoads Commodity, PickupLocation, DeliveryLocation, TransDate, NumberOfLoads VALUES ({1}, {2}, {3}, {4}, {5})", commodity, pLoc, dLoc, date, loadCount);
            for (int idx = 0; idx < loadCount; idx++)
            {
                using (SqlCommand cmd = new SqlCommand(insertSql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            conn.Close();
        }
    }
}

Open in new window

You might want to put a try / catch block just outside of the cmd.ExecuteQuery() to catch any kind of errors
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rtayIT DirectorAuthor Commented:
Sorry for the lack of information in my post.  I have no problems with saving one line of data to the db.  Looking for suggestions on how to post the same data x number of times based on user input.  

Maybe a do loop using numberoftimes.text for the x number of times to post?
0
Raghu MutalikdesaiSenior Manager in a leading IT Services companyCommented:
Line no. 21 to 27 -- I have used a for loop to continuously execute the same command until "Number of Loads"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
Maybe a do loop using numberoftimes.text for the x number of times to post?
That sounds a tad painful.  Why not add all the rows with a single INSERT statement.
0
Raghu MutalikdesaiSenior Manager in a leading IT Services companyCommented:
If you are using SQL Server 2008, it supports executing multiple INSERT statements as a single query. Here is an example:
INSERT INTO TblLoads (Commodity, PickupLocation, DeliveryLocation, TransDate, NumberOfLoads) VALUES ('Commodity1', 'Location1', '2014-05-01 12:34:00', 3), ('Commodity1', 'Location1', '2014-05-01 12:34:00', 3), ('Commodity1', 'Location1', '2014-05-01 12:34:00', 3);

Open in new window

0
rtayIT DirectorAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.