Solved

C# multiple inserts into MS SQL DB from single form

Posted on 2014-07-28
6
798 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
Comment Utility
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
Comment Utility
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
Comment Utility
Line no. 21 to 27 -- I have used a for loop to continuously execute the same command until "Number of Loads"
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now