Solved

C# multiple inserts into MS SQL DB from single form

Posted on 2014-07-28
6
820 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

777 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