inserting multiple values from C# into one row in a SQL table

Question

I have a table which i want to store all the outputs into one row seperated by a space(eg "textbox1 textbox2 textbox3". How would i do this? At the moment its trying to put them into different columns. Giving me an error stating im using fewer columns.

            }
            using (SqlCommand cmd = new SqlCommand("INSERT INTO [user] (solution) VALUES (@textbox1, @textbox2, @textbox3)", cn))
            {
                cmd.Parameters.AddWithValue("@textbox1", textBox1.Text);
                cmd.Parameters.AddWithValue("@textbox2", textBox2.Text);
                cmd.Parameters.AddWithValue("@textbox3", textBox3.Text);
                cmd.ExecuteNonQuery(); // or int affected = cmd.ExecuteNonQuery() 
                MessageBox.Show("Success!");

            }
        }

The problem is you are using COMMA (,) in your values set. In SQL query, comma (,) is used to separate variables (parameters) to be used in the query. So remove it and try to concate the textbox values before using in the query. And use the concatenated string value in the query. — vpv
– vpv, Commented Jun 10, 2012 at 6:51
One word of advice: don't do something like this! Sooner or later, you'll have to parse that column again and separate the values from the comma-separated list. Databases should have one cell, one value - don't start putting comma-separated lists of multiple values into a single column - this violates even the first normal form of database design. Just don't do it - find some other way to store this data. What's wrong with storing three values in three separate columns (or rows)? — marc_s
– marc_s, Commented Jun 10, 2012 at 8:26

Kapil Khandelwal · Accepted Answer · 2012-06-10 05:42:03Z

2

Try this:

using (SqlCommand cmd = new SqlCommand("INSERT INTO [user] (solution) VALUES (@text)", cn))
            {
                cmd.Parameters.AddWithValue("@text", textBox1.Text + " " + textBox2.Text + " " + textBox3.Text);

                cmd.ExecuteNonQuery(); // or int affected = cmd.ExecuteNonQuery() 
                MessageBox.Show("Success!");

            }

answered Jun 10, 2012 at 5:42

Kapil Khandelwal

16.2k3 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akash KC · Accepted Answer · 2012-06-10 05:52:05Z

You are inserting data in more column than you have specified so I think you have got the error in that part. So, make some modification in code to remove the error:

   }
            using (SqlCommand cmd = new SqlCommand("INSERT INTO [user] (solution) VALUES (@textbox1+' '+ @textbox2+' '+ @textbox3)", cn))
            {
                cmd.Parameters.AddWithValue("@textbox1", textBox1.Text);
                cmd.Parameters.AddWithValue("@textbox2", textBox2.Text);
                cmd.Parameters.AddWithValue("@textbox3", textBox3.Text);
                cmd.ExecuteNonQuery(); // or int affected = cmd.ExecuteNonQuery() 
                MessageBox.Show("Success!");

        }
    }

Sort of icky to do the concat server-side, IMOHO... I do believe that will work, however... so +1

Collectives™ on Stack Overflow

inserting multiple values from C# into one row in a SQL table

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related