Solved

C# multiple inserts into MS SQL DB from single form

Posted on 2014-07-28
6
866 Views
Last Modified: 2014-07-30
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.
0
Comment
Question by:rtay
[X]
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
6 Comments
 
LVL 7

Accepted Solution

by:
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 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
 
LVL 5

Author Comment

by:rtay
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?
0
 
LVL 7

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"
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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.
0
 
LVL 7

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

0
 
LVL 5

Author Closing Comment

by:rtay
ID: 40230413
Thank you!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

717 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