CODEWITHSUNDEEP
c#sqlsql-serverdatagridview
francydarkcool -
francydarkcool -

Reputation: 85

SQL Query runs only once

I have the following code:

SqlConnection con = new SqlConnection(@"Data Source=NUC\MICROGARDE;Initial Catalog=SQL;Integrated Security=True");
String Query;

for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
            MessageBox.Show(" " + this.dataGridView1.Columns.Count);
            MessageBox.Show(" " + this.dataGridView1.Columns[i].Name + " ");
            MessageBox.Show(" " + this.dataGridView1.SelectedRows[0].Cells[i].Value + " ");

            Query = "insert into [" + this.comboBox1.Text + "] ([" + this.dataGridView1.Columns[i].Name + "]) Values ('" + this.dataGridView1.SelectedRows[0].Cells[i].Value + "') ;";

            SqlCommand cmd = new SqlCommand(Query, con);

            con.Open();

            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(Query, con);

            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            sda.SelectCommand = cmd;

            sda.Fill(dt);

            BindingSource bSource = new BindingSource();
            bSource.DataSource = dt;

            dataGridView1.DataSource = bSource;
        }
        con.Close();

It should insert a specific value into EVERY column of a table (shown in a dataGridView), but after it saves the first value (the value from the first column of the row that we want to insert) it refreshes the table and only that first value gets inserted... I want to insert the whole row

Upvotes: 2

Views: 822

Answers (2)

Sham Sunder
Sham Sunder

Reputation: 102

con.open() should be outside the loop

Upvotes: 0

Paolo
Paolo

Reputation: 2254

the code is behaving correctly as expected.

here is your code:

for each column
 build sql string to take first row, current column and insert in db
 create sql command using the above sql string
 execute the above command
 refresh datagrid
next

the above produces the exact behaviour you are experiencing, this is expected and correct in the sense that the code does exactly what it is told to do.

what your code should be based on your description:

for each row
 build base sql statement
 for each column
  add current value and field name to the base statement
 next
 create sql command
 fill the command with the statement built in the previous cycle
 execute the sql statement
next
refresh datagrid

if you have to insert only one row then the outer foreach is not needed
while performing string concatenation to build the statements take care of input sanitization and datatypes.

Upvotes: 1

Related Questions