问题描述:

Hi I am looking for a way to insert all of my GridView Cells or data into a SQL Server Table. I want the columns to match the columns of SQL Server and each row to be added. The rows change all the time. Is this possible?

Here is what I have tried (no errors, just doesn't do anything)

 foreach (GridViewRow GVRow in GridView1.Rows)

{

string PartNumber = string.Empty;

string Qty = string.Empty;

string Price = string.Empty;

string ExtPrice = string.Empty;

PartNumber = GVRow.Cells[1].Text;

Qty = GVRow.Cells[2].Text;

Price = GVRow.Cells[3].Text;

ExtPrice = GVRow.Cells[4].Text;

using (SqlConnection sqlCon5 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))

{

SqlCommand scm = new SqlCommand();

scm.Connection = sqlCon5;

scm.CommandText = @"INSERT INTO OrderDetail (OrderNumber, PartNumber, Qty, Price, ExtPrice) VALUES (@OrderNumber, @PartNumber,@Qty,@Price,@ExtPrice)";

scm.Parameters.AddWithValue("@OrderNumber", TextBox1.Text);

scm.Parameters.AddWithValue("@PartNumber", PartNumber.ToString());

scm.Parameters.AddWithValue("@Qty", Qty.ToString());

scm.Parameters.AddWithValue("@Price", Price.ToString());

scm.Parameters.AddWithValue("@ExtPrice", ExtPrice.ToString());

sqlCon5.Open();

scm.ExecuteNonQuery();

sqlCon5.Close();

}

}

网友答案:

Try using for loop instead.

int rowsCount = GridView1.Rows.Count;
int rowsInserted = 0;

for (int i = 0; i < rowsCount; i++)
{
    StrQuery = @"INSERT INTO OrderDetail (OrderNumber, PartNumber, Qty, Price, ExtPrice) VALUES   (@OrderNumber, @PartNumber,@Qty,@Price,@ExtPrice)";
    scm.CommandText = StrQuery;
    scm.Parameters.AddWithValue("@OrderNumber",GridView1.Rows[i].
                      Cells["columnName"].Value);
    scm.Parameters.AddWithValue("@PartNumber",GridView1.Rows[i].
                      Cells["columnName"].Value);
    scm.Parameters.AddWithValue("@Qty",GridView1.Rows[i].
                      Cells["columnName"].Value);
    scm.Parameters.AddWithValue("@Price",GridView1.Rows[i].
                      Cells["columnName"].Value);
    scm.Parameters.AddWithValue("@ExtPrice",GridView1.Rows[i].
                      Cells["columnName"].Value);

if (scm.ExecuteNonQuery() > 0)
{
    rowsInserted++;
}
scm.Parameters.Clear();
}

//check if all rows inserted
if(rowsCount == rowsInserted)
{
   //Display successfull message
}
else
{
  //Display error message
}

NOTE* replace columnName with the actual column name from GridView1.

相关阅读:
Top