C# multiple inserts into MS SQL DB from single form

Posted on 2014-07-28
Last Modified: 2014-07-30
I have a user form to plan future loads for delivery in / 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.
Question by:rtay
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Accepted Solution

Raghu Mutalikdesai earned 500 total points
ID: 40226086
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 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))
            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))

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

Author Comment

ID: 40226544
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?

Expert Comment

by:Raghu Mutalikdesai
ID: 40226554
Line no. 21 to 27 -- I have used a for loop to continuously execute the same command until "Number of Loads"
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 40228187
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.

Expert Comment

by:Raghu Mutalikdesai
ID: 40228514
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


Author Closing Comment

ID: 40230413
Thank you!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question