Click here to Skip to main content
15,887,485 members
Search within:


How do I add multiple textbox values to single column in database table
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi,
I have a web page with multiple pair of textboxes - one for coursecode and the other an int courseunit. At the buttom of the page, I have a save button and I want upon clicking of the button, user input in each pair be saved to my courseCode and courseUnit columns of my Courses table in my database for all of my text box pairs. So I'm saying I have about 20 textboxes, 10 for coursecodes and 10 for course units all to be stored at once in just two db table columns.

What I have tried:

C#
protected void SaveBtn_Click(object sender, EventArgs e)
        {
            //save coursecodes and units entered to database
            try
            {

                using (SqlConnection sqlCon = new SqlConnection(connectionString))
                {

                        sqlCon.Open();
                    //string Query = "INSERT INTO Courses (courseCode,courseUnit) VALUES (@courseCode1,@courseCode2,@courseCode3,@courseCode4,@courseCode5,@courseCode6,@courseCode7,@courseCode8,@courseCode9,@courseCode10,@courseCode11,@courseCode12,@courseCode13,@courseCode14,@courseCode15,@courseCode16,@courseUnit1,@courseUnit2,@courseUnit3,@courseUnit4,@courseUnit5,@courseUnit6,@courseUnit7,@courseUnit8,@courseUnit9,@courseUnit10,@courseUnit11,@courseUnit12,@courseUnit13,@courseUnit14,@courseUnit15,@courseUnit16)";
                    string Query = "INSERT INTO Courses (courseCode,courseUnit) VALUES (@courseCode,@courseUnit)";
                    SqlCommand sqlCmd = new SqlCommand(Query, sqlCon);

                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox1")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox2")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox7")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox9")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox13")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox15")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox19")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox21")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox25")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox27")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox31")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox33")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox37")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox39")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox5")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseCode", SqlDbType.NChar).Value = (FindControl("TextBox11")) ?? (object)DBNull.Value;



                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox3")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox4")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox8")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox10")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox14")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox16")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox20")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox22")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox26")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox28")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox32")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox34")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox38")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox40")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox6")) ?? (object)DBNull.Value;
                    sqlCmd.Parameters.AddWithValue("@courseUnit", SqlDbType.Int).Value = (FindControl("TextBox12")) ?? (object)DBNull.Value;


                    //sqlCmd.Parameters.AddWithValue("@courseCode1", SqlDbType.NChar).Value = (FindControl("TextBox1")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode2", SqlDbType.NChar).Value = (FindControl("TextBox2")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode3", SqlDbType.NChar).Value = (FindControl("TextBox7")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode4", SqlDbType.NChar).Value = (FindControl("TextBox9")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode5", SqlDbType.NChar).Value = (FindControl("TextBox13")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode6", SqlDbType.NChar).Value = (FindControl("TextBox15")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode7", SqlDbType.NChar).Value = (FindControl("TextBox19")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode8", SqlDbType.NChar).Value = (FindControl("TextBox21")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode9", SqlDbType.NChar).Value = (FindControl("TextBox25")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode10", SqlDbType.NChar).Value = (FindControl("TextBox27")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode11", SqlDbType.NChar).Value = (FindControl("TextBox31")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode12", SqlDbType.NChar).Value = (FindControl("TextBox33")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode13", SqlDbType.NChar).Value = (FindControl("TextBox37")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode14", SqlDbType.NChar).Value = (FindControl("TextBox39")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode15", SqlDbType.NChar).Value = (FindControl("TextBox5")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseCode16", SqlDbType.NChar).Value = (FindControl("TextBox11")) ?? (object)DBNull.Value;



                    //sqlCmd.Parameters.AddWithValue("@courseUnit1", SqlDbType.Int).Value = (FindControl("TextBox3")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit2", SqlDbType.Int).Value = (FindControl("TextBox4")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit3", SqlDbType.Int).Value = (FindControl("TextBox8")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit4", SqlDbType.Int).Value = (FindControl("TextBox10")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit5", SqlDbType.Int).Value = (FindControl("TextBox14")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit6", SqlDbType.Int).Value = (FindControl("TextBox16")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit7", SqlDbType.Int).Value = (FindControl("TextBox20")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit8", SqlDbType.Int).Value = (FindControl("TextBox22")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit9", SqlDbType.Int).Value = (FindControl("TextBox26")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit10", SqlDbType.Int).Value = (FindControl("TextBox28")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit11", SqlDbType.Int).Value = (FindControl("TextBox32")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit12", SqlDbType.Int).Value = (FindControl("TextBox34")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit13", SqlDbType.Int).Value = (FindControl("TextBox38")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit14", SqlDbType.Int).Value = (FindControl("TextBox40")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit15", SqlDbType.Int).Value = (FindControl("TextBox6")) ?? (object)DBNull.Value;
                    //sqlCmd.Parameters.AddWithValue("@courseUnit16", SqlDbType.Int).Value = (FindControl("TextBox12")) ?? (object)DBNull.Value;



                    //    sqlCmd.Parameters.AddWithValue("@courseCode1", (FindControl("TextBox1")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode2", (FindControl("TextBox2")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode3", (FindControl("TextBox7")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode4", (FindControl("TextBox9")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode5", (FindControl("TextBo13")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode6", (FindControl("TextBox15")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode7", (FindControl("TextBox19")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode8", (FindControl("TextBox21")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode9", (FindControl("TextBox25")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode10", (FindControl("TextBox27")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode11", (FindControl("TextBox31")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode12", (FindControl("TextBox33")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode13", (FindControl("TextBox37")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode14", (FindControl("TextBox39")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode15", (FindControl("TextBox5")));
                    //    sqlCmd.Parameters.AddWithValue("@courseCode16", (FindControl("TextBox11")));



                    //sqlCmd.Parameters.AddWithValue("@courseUnit1", (FindControl("TextBox3")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit2", (FindControl("TextBox4")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit3", (FindControl("TextBox8")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit4", (FindControl("TextBox10")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit5", (FindControl("TextBo14")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit6", (FindControl("TextBox16")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit7", (FindControl("TextBox20")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit8", (FindControl("TextBox22")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit9", (FindControl("TextBox26")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit10", (FindControl("TextBox28")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit11", (FindControl("TextBox32")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit12", (FindControl("TextBox34")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit13", (FindControl("TextBox38")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit14", (FindControl("TextBox40")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit15", (FindControl("TextBox6")));
                    //sqlCmd.Parameters.AddWithValue("@courseUnit16", (FindControl("TextBox12")));
                    sqlCmd.ExecuteNonQuery();
                        lblSuccessMessage.Text = "Courses successfully added";
                        lblErrorMessage.Text = "";

                   
                }
            }
                catch (Exception ex)
            {

                lblSuccessMessage.Text = "";
                lblErrorMessage.Text = ex.Message;
            }


            /*  //use coursecode input to name new columns in gridview
              foreach (var c in GridView1.HeaderRow.Cells)
              {

              }
              */

        }

In my code, you'll see that I've taken several similar approaches but all resulted in error
Posted
Updated 8-Oct-17 1:54am
Add a Solution

2 solutions

store all the course code and course unit in list of keyvalue pair and iterate them to insert into DB

string Query = "INSERT INTO Courses (courseCode,courseUnit) VALUES (@courseCode,@courseUnit)";
           SqlCommand sqlCmd = new SqlCommand(Query, sqlCon);
           List<KeyValuePair<object, object>> lst = new System.Collections.Generic.List<System.Collections.Generic.KeyValuePair<object, object>>();
           lst.Add( new System.Collections.Generic.KeyValuePair<object,object> ((FindControl("TextBox1")) ?? (object)DBNull.Value ,  (FindControl("TextBox3")) ?? (object)DBNull.Value));
           lst.Add(new System.Collections.Generic.KeyValuePair<object, object>((FindControl("TextBox2")) ?? (object)DBNull.Value, (FindControl("TextBox4")) ?? (object)DBNull.Value));
           foreach (var item in lst)
           {
               sqlCmd.Parameters.AddWithValue("@courseCode", item.Key);
               sqlCmd.Parameters.AddWithValue("@courseUnit", item.Value);
               sqlCmd.ExecuteNonQuery();
           }
           lblSuccessMessage.Text = "Courses successfully added";
 
Share this answer
 
Posted
Comments
Member 13331557 24-Sep-17 11:42am    
Th approach seems to have worked as I know get a different error -

Cannot insert the value NULL into column 'courseCode', table 'ClassDB.dbo.Courses'; column does not allow nulls. INSERT fails. The statement has been terminated.

I get the same error even when the column is set to accept nulls
Karthik_Mahalingam 24-Sep-17 22:34pm    
add null check condition 

if(item.key!= null && item.value != null){
sqlCmd.Parameters.AddWithValue("@courseCode", item.Key);
               sqlCmd.Parameters.AddWithValue("@courseUnit", item.Value);
}
               sqlCmd.ExecuteNonQuery();


Note:but you will have to check why the value is null at the first place..
Member 13331557 25-Sep-17 14:15pm    
' Note:but you will have to check why the value is null at the first place.. '
Any ideas? cus' only 'null' values are adding to my database for all textboxes
Karthik_Mahalingam 25-Sep-17 23:05pm    
you need to validate in the UI and also debug the function FindControl() and see where it is getting null
my final code

protected void SaveBtn_Click(object sender, EventArgs e)
   {
       //save coursecodes and units entered to database
       try
       {
           using (SqlConnection sqlCon = new SqlConnection(connectionString))
           {

               sqlCon.Open();
               string Query = ("INSERT INTO Courses (courseCode,courseUnit) VALUES (@courseCode,@courseUnit)");
               SqlCommand sqlCmd = new SqlCommand(Query, sqlCon);

               //working single pair
               //sqlCmd.Parameters.AddWithValue("@courseCode", courseCodeTB.Text);
               //sqlCmd.Parameters.AddWithValue("@courseUnit", courseUnitTB.Text);
               //sqlCmd.ExecuteNonQuery();
               //lblSuccessMessage.Text = "Courses successfully added";
               //lblErrorMessage.Text = "";



               List<KeyValuePair<object, object>> lst = new System.Collections.Generic.List<System.Collections.Generic.KeyValuePair<object, object>>();

               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox1.Text ?? (object)DBNull.Value, TextBox3.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox2.Text ?? (object)DBNull.Value, TextBox4.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox7.Text ?? (object)DBNull.Value, TextBox8.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox9.Text ?? (object)DBNull.Value, TextBox10.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox13.Text ?? (object)DBNull.Value, TextBox14.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox15.Text ?? (object)DBNull.Value, TextBox16.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox19.Text ?? (object)DBNull.Value, TextBox20.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox21.Text ?? (object)DBNull.Value, TextBox22.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox25.Text ?? (object)DBNull.Value, TextBox26.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox27.Text ?? (object)DBNull.Value, TextBox28.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox31.Text ?? (object)DBNull.Value, TextBox32.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox33.Text ?? (object)DBNull.Value, TextBox34.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox37.Text ?? (object)DBNull.Value, TextBox38.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox39.Text ?? (object)DBNull.Value, TextBox40.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox5.Text ?? (object)DBNull.Value, TextBox6.Text ?? (object)DBNull.Value));
               lst.Add(new System.Collections.Generic.KeyValuePair<object, object>(TextBox11.Text ?? (object)DBNull.Value, TextBox12.Text ?? (object)DBNull.Value));


               foreach (var item in lst)
               {
                   if (item.Key != null && item.Value != null)
                   {
                       sqlCmd.Parameters.Clear();
                       sqlCmd.Parameters.AddWithValue("@courseCode", item.Key);
                       sqlCmd.Parameters.AddWithValue("@courseUnit", item.Value);
                   }
                   sqlCmd.ExecuteNonQuery();
               }
               lblSuccessMessage.Text = "Courses successfully added";



           }
       }
           catch (Exception ex)
       {

           lblSuccessMessage.Text = "";
           lblErrorMessage.Text = ex.Message;
       }


       /*  //use coursecode input to name new columns in gridview
         foreach (var c in GridView1.HeaderRow.Cells)
         {

         }
         */

   }
 
Share this answer
 
Posted
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
how to store multiple textbox values in a single column of a database?
How to search all columns of a table with single value in textbox, VB.NET
How do I change ms SQL server identity column seed and increment values of multiple tables
How to insert multiple textboxes in a table row by row with button click
Bind Multiple value from database in single column
Acceptting multiple values in single column
Add multiple values to a single index
How to add functions when checking table values
Insert into a single temporary table values from multiple queries
Referencing multiple keys into a single column in MYSQL table

Advertise
Privacy
Cookies
Terms of Use
Last Updated 8 Oct 2017
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900