How to insert multiple records in *.sdf file

Question

3.50/5 (2 votes)

See more:

i having 5 textboxes for each fields e.g 5 textboxes for serial no,length range and so on...
how do i add records in one "insert" command instead of writing 5 lines of "Insert" Commands...
here i used "IF-Else" statement to check whether the user had selected multiple rows insertion or just a single row..

C#

if (dtHydro2.Checked.Equals(false) || dtHydro3.Checked.Equals(false) || dtHydro4.Checked.Equals(false) || dtHydro5.Checked.Equals(false))
               {
                   cmd.CommandText = "INSERT INTO Requirement_Table (Serial_No,Length_Range,Heat_No_Maker,Heat_No_MSI,No_Of_Parts,Total_Meters,Hydro_Static,Hydro_Static_Test,Bend_Test,Flattening_Test,Flaring_Test,Drift_Test,Crushing_Test,Bend_Test_On_Strip,Flange_test,Heat_Treatment,Non_Destructive_Test,Customer_Order_No,Name_Of_Parts) Values('" + txtSerial1.Text + "','" + txtLength1.Text + "','" + txtMaker1.Text + " " + txtMSL1.Text + " " + txtNo_Of1.Text + "','" + txtTotalMeters1.Text + "','" + txtPSI.Text + "','" + txtHydroStatic1.Text + "','" + txtBendTest1.Text + "','" + txtFlattening1.Text + "','" + txtFlaring1.Text + "','" + txtDrift1.Text + "','" + txtCrushing1.Text + "','" + txtBendStrip1.Text + "','" + txtFlange1.Text + "','" + txtHeatTreatment1.Text + "','" + txtNonDestructive1.Text + "','" + CustomerDetail.customer_order_no +"','"+txtNo.Text + "' )";
                   cmd.ExecuteNonQuery();
               }
               else
               {
                   for (int i = 1; i < 6; i++)
                   {
                       cmd.CommandText = "INSERT INTO Requirement_Table (Serial_No,Length_Range,Heat_No_Maker,Heat_No_MSI,No_Of_Parts,Total_Meters,Hydro_Static,Hydro_Static_Test,Bend_Test,Flattening_Test,Flaring_Test,Drift_Test,Crushing_Test,Bend_Test_On_Strip,Flange_test,Heat_Treatment,Non_Destructive_Test,Customer_Order_No,Name_Of_Parts) Values('" + txtSerial1.Text + "','" + txtLength1.Text + "','" + txtMaker1.Text + " " + txtMSL1.Text + " " + txtNo_Of1.Text + "','" + txtTotalMeters1.Text + "','" + txtPSI.Text + "','" + txtHydroStatic1.Text + "','" + txtBendTest1.Text + "','" + txtFlattening1.Text + "','" + txtFlaring1.Text + "','" + txtDrift1.Text + "','" + txtCrushing1.Text + "','" + txtBendStrip1.Text + "','" + txtFlange1.Text + "','" + txtHeatTreatment1.Text + "','" + txtNonDestructive1.Text + "','" + CustomerDetail.customer_order_no +"','"+txtNo.Text + "' )";
                       cmd.ExecuteNonQuery();
                   }
               }

How do i insert all 5 rows values in database at one click please help...

Posted 13-Jan-14 0:44am

shwetap07

Add a Solution

Comments

Richard MacCutchan 13-Jan-14 6:53am

Start by replacing all that dangerous coding by properly parameterised SQL commands. And if you do not know why it is dangerous, Google for "bobby tables".

shwetap07 13-Jan-14 9:39am

I came to know the problem with my above code that will turns into sql injection... Can you please provide me link for sql parameterized queries tutorial...

Richard MacCutchan 13-Jan-14 11:05am

Look at the MSDN pages or use Google.

CHill60 13-Jan-14 14:57pm

http://www.dotnetperls.com/sqlparameter[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Accepted Answer · 2014-01-14T23:02:00

Solution 1

Quote:
I came to know the problem with my above code that will turns into sql injection... Can you please provide me link for sql parameterized queries tutorial...

Check this article
Hack Proof Your ASP.NET Applications From SQL Injection[^]

Quote:
how do i add records in one "insert" command instead of writing 5 lines of "Insert" Commands

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL[^]
SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor[^]

Posted 14-Jan-14 23:02pm

thatraja

Comments

shwetap07 15-Jan-14 6:49am

@thatraja I'm getting message as "there was an error parsing query [token line number = 1 ,token line offset 341,token error = , ]
SqlCeConnection conn = new SqlCeConnection(connString);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Requirement_Table (Customer_Order_No,Serial_No,Length_Range,Heat_No_Maker,Heat_No_MSI,Name_Of_Parts,No_Of_Parts,Total_Meters,Hydro_Static,Hydro_Static_Test,Date_Of_Hydro,Date_Of_Manufacture,Bend_Test,Flattening_Test,Flaring_Test,Drift_Test,Crushing_Test,Bend_Test_On_Strip,Flange_Test,Heat_Treatment,Non_Destructive) " +
"Select (" + CustomerDetail.customer_order_no + ",'" + txtSerial1.Text + "','" + txtLength1.Text + "',' " + txtMaker1.Text + "','" + txtMSL1.Text + "','" + txtNo.Text + "','" + txtNo_Of1.Text + "','" + txtTotalMeters1.Text + "','" + txtPSI.Text + "','" + txtHydroStatic1.Text + "','" + dtHydro1.Text + "','" + dtManufacture1.Text + "','" + txtBendTest1.Text + "','" + txtFlattening1.Text + "','" + txtFlaring1.Text + "','" + txtDrift1.Text + "','" + txtCrushing1.Text + "','" + txtBendStrip1.Text + "','" + txtFlange1.Text + "','" + txtHeatTreatment1.Text + "','" + txtNonDestructive1.Text + "' )" +
" UNION ALL " +
"Select (" + CustomerDetail.customer_order_no + ",'" + txtSerial2.Text + "','" + txtLength2.Text + "',' " + txtMaker2.Text + "','" + txtMSL2.Text + "','" + txtNo.Text + "','" + txtNo_Of2.Text + "','" + txtTotalMeters2.Text + "','" + txtPSI.Text + "','" + txtHydroStatic2.Text + "','" + dtHydro2.Text + "','" + dtManufacture2.Text + "','" + txtBendTest2.Text + "','" + txtFlattening2.Text + "','" + txtFlaring2.Text + "','" + txtDrift2.Text + "','" + txtCrushing2.Text + "','" + txtBendStrip2.Text + "','" + txtFlange2.Text + "','" + txtHeatTreatment2.Text + "','" + txtNonDestructive2.Text + "' )" +
" UNION ALL " +
"Select (" + CustomerDetail.customer_order_no + ",'" + txtSerial3.Text + "','" + txtLength3.Text + "',' " + txtMaker3.Text + "','" + txtMSL3.Text + "','" + txtNo.Text + "','" + txtNo_Of3.Text + "','" + txtTotalMeters3.Text + "','" + txtPSI.Text + "','" + txtHydroStatic3.Text + "','" + dtHydro3.Text + "','" + dtManufacture3.Text + "','" + txtBendTest3.Text + "','" + txtFlattening3.Text + "','" + txtFlaring3.Text + "','" + txtDrift3.Text + "','" + txtCrushing3.Text + "','" + txtBendStrip3.Text + "','" + txtFlange3.Text + "','" + txtHeatTreatment3.Text + "','" + txtNonDestructive3.Text + "' )" +
" UNION ALL " +
"Select (" + CustomerDetail.customer_order_no + ",'" + txtSerial4.Text + "','" + txtLength4.Text + "',' " + txtMaker4.Text + "','" + txtMSL4.Text + "','" + txtNo.Text + "','" + txtNo_Of4.Text + "','" + txtTotalMeters4.Text + "','" + txtPSI.Text + "','" + txtHydroStatic4.Text + "','" + dtHydro4.Text + "','" + dtManufacture4.Text + "','" + txtBendTest4.Text + "','" + txtFlattening4.Text + "','" + txtFlaring4.Text + "','" + txtDrift4.Text + "','" + txtCrushing4.Text + "','" + txtBendStrip4.Text + "','" + txtFlange4.Text + "','" + txtHeatTreatment4.Text + "','" + txtNonDestructive4.Text + "' )" +
" UNION ALL " +
"Select (" + CustomerDetail.customer_order_no + ",'" + txtSerial5.Text + "','" + txtLength5.Text + "',' " + txtMaker5.Text + "','" + txtMSL5.Text + "','" + txtNo.Text + "','" + txtNo_Of5.Text + "','" + txtTotalMeters5.Text + "','" + txtPSI.Text + "','" + txtHydroStatic5.Text + "','" + dtHydro5.Text + "','" + dtManufacture5.Text + "','" + txtBendTest5.Text + "','" + txtFlattening5.Text + "','" + txtFlaring5.Text + "','" + txtDrift5.Text + "','" + txtCrushing5.Text + "','" + txtBendStrip5.Text + "','" + txtFlange5.Text + "','" + txtHeatTreatment5.Text + "','" + txtNonDestructive5.Text + "' )";

thatraja 15-Jan-14 7:10am

First, assign the sql string to some variable. And assign that variable to cmd.CommandText. While debugging, get the string value from that variable. Execute that string value(query) in SQL.

For errors, do Google search.

shwetap07 15-Jan-14 7:16am

@thatraja there was mistake within query itself i.e. "Select (" that was actually supposed to be"Select 'xyz',.." and i replaced it. now its working fine :P thanks for help :)

thatraja 15-Jan-14 7:20am

Welcome. You have posted the query in comment, it looks difficult(in comment) without any formatting. So hereafter, update your question with such details. That'll give you quick answers.

shwetap07 15-Jan-14 7:22am

@thatraja .... OKAY.Thanks I'll keep in mind :)