怎样把datagridview内的数据批量写入数据库(access) 怎样把datagridview内的数据批量写入数据库? 谢谢各位,急用! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 将DataSet和DataGridView 进行数据绑定。 循环datagridview中的每一行,保存到数据库。 主要是我写了一个程序 if (this.dgvInfo.DataSource != null) { DialogResult result = MessageBox.Show("确定将此次盘库数据保存至数据库?(存储前请务必核对准确)", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question); if (result == DialogResult.OK) { for (int i = 0; i < dgvInfo.Rows.Count ; i++) { string cmdInsert = String.Empty; cmdInsert = "INSERT INTO 成品盘库表(盘库日期,产品名称,包装规格,期初(吨),生产(吨),其他(吨),槽车(吨),公路(吨),自提(吨),转出(吨),期末(吨))"; cmdInsert += "VALUES('" + this.ThisCheckTime.Value.ToString("yyyy-MM-dd") + "'"; cmdInsert += ",'" + this.dgvInfo.Rows[i].Cells[0].Value + "'"; cmdInsert += ",'" + this.dgvInfo.Rows[i].Cells[1].Value + "'"; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[2].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[3].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[4].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[5].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[6].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[7].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[8].Value + ""; cmdInsert += "," + this.dgvInfo.Rows[i].Cells[9].Value + ")"; G_SqlClass.GetExecute(cmdInsert); } } } else { MessageBox.Show("未生成盘库记录!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } }能够把数据写入数据库,可是总是报错INSERT INTO语句的语法错误,有哪位高手给指导一下,谢谢,万分感谢! 不用这么复杂增删改可以一块搞定 GridView里面的数据行 sqldataadapter.update()不可以么? 先填充到 Dataset 再批量UPDATA 昨天看了一个Ado.net高级应用的视频是这样解决的 告诉你吧,你的values那没有加空格。注意拼凑语句时候的错误,最好别用这种拼凑的方法,在字段比较多的时候用传值的方法比较合适。 SqlCommand InsertCommand = sqlConn.CreateCommand();for(int i=0;i<datagridview.Rows.count;i++){ ... InsertCommand.CommandText = "insert into ... InsertCommand.ExecuteNonQuery();}sqlConn.Close(); sql 插入中文应有: "... ,N'" + 含中文值的变量 + "," + ...access 没弄过, 不知道有没有这个问题 各位高手,我用的是access数据库,而且datagridview里的是虚表,是三个数据表连接查询得到的。for循环速度有些慢,能够把数据写入数据库,可是总是报错INSERT INTO语句的语法错误。请帮忙给指点一二。 For i As Integer = 0 To DataGridView.RowCount-1 sSQL = "INSERT INTO t_Stock (StockNo) VALUES ('" & DataGridView.Rows(i).Cells(0).Value& "')"Next 加了空格也是报错还是说,INSERT INTO语句的语法错误 string ConnectionString = "server=.;database=pubs;uid=sa;pwd=123"; SqlConnection GetCon() { return new SqlConnection(ConnectionString); } public bool HandData(DataTable t) { SqlConnection con = null; try { con = GetCon(); con.Open(); SqlDataAdapter dt = GetSqlDataAdapter(con,t); return Convert.ToBoolean(dt.Update(t)); } catch (Exception ex) { Console.Write(ex.Message); return false; } finally { if (con != null) con.Close(); } } // SqlDataAdapter GetSqlDataAdapter(SqlConnection con, DataTable d) { SqlDataAdapter dt = new SqlDataAdapter(); string sqlInsert = "insert into " + d.TableName + "("; string sqlUpdate = "update " + d.TableName + " set "; string sqlDelete = "delete " + d.TableName + " where "; foreach (DataColumn dc in d.Columns) { sqlInsert += dc.ColumnName + ","; sqlUpdate +=dc.ColumnName + "=@" + dc.ColumnName + ","; } sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1) + ")values("; sqlUpdate = sqlUpdate.Substring(0, sqlUpdate.Length - 1) + " where "; foreach (DataColumn dc in d.Columns) { sqlInsert += "@"+dc.ColumnName + ","; } sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1) + ")"; dt.InsertCommand = new SqlCommand(sqlInsert, con); dt.UpdateCommand = new SqlCommand(sqlUpdate, con); dt.DeleteCommand = new SqlCommand(sqlDelete, con); foreach (DataColumn dc in d.Columns) { SqlParameter sp = new SqlParameter("@" + dc.ColumnName, dc.DataType); sp.SourceColumn = dc.ColumnName; dt.InsertCommand.Parameters.Add(sp); sp = new SqlParameter("@" + dc.ColumnName, dc.DataType); sp.SourceColumn = dc.ColumnName; dt.UpdateCommand.Parameters.Add(sp); } DataTable dk = new DataTable();//sp_pkeys tblUser foreach (DataRow rw in dk.Rows) { sqlUpdate += rw[3].ToString().Trim() + "=@" + rw[3].ToString().Trim() + " and "; sqlDelete += rw[3].ToString().Trim() + "=@" + rw[3].ToString().Trim() + " and "; SqlParameter sp = new SqlParameter("@" + rw[3].ToString().Trim(), SqlDbType.VarChar); sp.SourceColumn = rw[3].ToString().Trim(); dt.DeleteCommand.Parameters.Add(sp); } sqlUpdate = sqlUpdate.Substring(0, sqlUpdate.Length - 4); sqlDelete = sqlDelete.Substring(0, sqlDelete.Length - 4); dt.UpdateCommand.CommandText = sqlUpdate; dt.DeleteCommand.CommandText = sqlDelete; return dt; } string[] test ={ "22", "1", "A" }; string[] arr ={ "22", "1", "A" }; string[] ok ={ "22", "1", "A" }; String SqlStr = "select * from test"; //新建内存表dt DataTable dt = new DataTable("test"); //建立sql适配器 SqlDataAdapter da = new SqlDataAdapter(SqlStr, con); da.Fill(dt); //增加数组里的数据 for (int i = 0; i < arr.Length; i++) { DataRow row = dt.NewRow(); row["test"] = test[i]; row["arr"] = arr[i]; row["ok"] = ok[i]; dt.Rows.Add(row); //显示当前行的状态 Console.Write(row[0].ToString()); } SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da); //建立sql数据集 并且更新数据库 DataSet ds = new DataSet(); ds.Tables.Add(dt); int val = da.Update(ds, "test"); ds.AcceptChanges(); 接16楼 把GridView数据源转换成datatableDataTable dt=GridView1.DataSource as DataTable;传递到方法 insert into报错时,多半是没到数据库先执行,这个语句本身写的就有问题。你做好还是选复制到数据库中先运行下。这是一种好习惯,会避免很多不必要的错误 谢谢大家,问题解决了,问题出在for (int i = 0; i < dgvInfo.Rows.Count ; i++)应该是for (int i = 0; i < dgvInfo.Rows.Count-1 ; i++)多循环了一条. 一个字符串的截取 关于json反序列化 怎么去掉"¥",但保留千分位的逗号 『20分』C# 怎么通过代码修改Access数据库日期类型字段的格式? C#2005里的即时窗口里的字符串如何换行? TextBox的问题(急) 请教字符串的位操作问题(急急急) &&&&&&&&50分就问一个问题,Url参数传递问题?如何判断?%%%%%%%%% 我在msdn找到一个例子,将窗口分为两部分,如果我打算将窗口分为四部分,如何做呢? 怎样把网络流转换与内存流??如何把接收到的图片网络流或图片字节装入Picture中!! 简单的正则表达式,送分~ 抛出异常 不支持给定路径的格式 求解
{
DialogResult result = MessageBox.Show("确定将此次盘库数据保存至数据库?(存储前请务必核对准确)", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
for (int i = 0; i < dgvInfo.Rows.Count ; i++)
{
string cmdInsert = String.Empty;
cmdInsert = "INSERT INTO 成品盘库表(盘库日期,产品名称,包装规格,期初(吨),生产(吨),其他(吨),槽车(吨),公路(吨),自提(吨),转出(吨),期末(吨))";
cmdInsert += "VALUES('" + this.ThisCheckTime.Value.ToString("yyyy-MM-dd") + "'";
cmdInsert += ",'" + this.dgvInfo.Rows[i].Cells[0].Value + "'";
cmdInsert += ",'" + this.dgvInfo.Rows[i].Cells[1].Value + "'";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[2].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[3].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[4].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[5].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[6].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[7].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[8].Value + "";
cmdInsert += "," + this.dgvInfo.Rows[i].Cells[9].Value + ")";
G_SqlClass.GetExecute(cmdInsert);
}
}
}
else
{
MessageBox.Show("未生成盘库记录!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}能够把数据写入数据库,可是总是报错INSERT INTO语句的语法错误,有哪位高手给指导一下,谢谢,万分感谢!
增删改可以一块搞定 GridView里面的数据行
不可以么?
注意拼凑语句时候的错误,最好别用这种拼凑的方法,在字段比较多的时候用传值的方法比较合适。
SqlCommand InsertCommand = sqlConn.CreateCommand();
for(int i=0;i<datagridview.Rows.count;i++)
{
...
InsertCommand.CommandText = "insert into ...
InsertCommand.ExecuteNonQuery();
}
sqlConn.Close();
for循环速度有些慢,能够把数据写入数据库,可是总是报错INSERT INTO语句的语法错误。
请帮忙给指点一二。
sSQL = "INSERT INTO t_Stock (StockNo) VALUES ('" & DataGridView.Rows(i).Cells(0).Value& "')"
Next
加了空格也是报错还是说,INSERT INTO语句的语法错误
SqlConnection GetCon()
{
return new SqlConnection(ConnectionString);
}
public bool HandData(DataTable t)
{
SqlConnection con = null;
try
{
con = GetCon();
con.Open();
SqlDataAdapter dt = GetSqlDataAdapter(con,t); return Convert.ToBoolean(dt.Update(t));
}
catch (Exception ex)
{
Console.Write(ex.Message);
return false;
}
finally
{
if (con != null) con.Close();
}
} //
SqlDataAdapter GetSqlDataAdapter(SqlConnection con, DataTable d)
{
SqlDataAdapter dt = new SqlDataAdapter();
string sqlInsert = "insert into " + d.TableName + "(";
string sqlUpdate = "update " + d.TableName + " set ";
string sqlDelete = "delete " + d.TableName + " where "; foreach (DataColumn dc in d.Columns)
{
sqlInsert += dc.ColumnName + ",";
sqlUpdate +=dc.ColumnName + "=@" + dc.ColumnName + ",";
}
sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1) + ")values(";
sqlUpdate = sqlUpdate.Substring(0, sqlUpdate.Length - 1) + " where "; foreach (DataColumn dc in d.Columns)
{
sqlInsert += "@"+dc.ColumnName + ",";
}
sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1) + ")";
dt.InsertCommand = new SqlCommand(sqlInsert, con);
dt.UpdateCommand = new SqlCommand(sqlUpdate, con);
dt.DeleteCommand = new SqlCommand(sqlDelete, con); foreach (DataColumn dc in d.Columns)
{
SqlParameter sp = new SqlParameter("@" + dc.ColumnName, dc.DataType);
sp.SourceColumn = dc.ColumnName;
dt.InsertCommand.Parameters.Add(sp); sp = new SqlParameter("@" + dc.ColumnName, dc.DataType);
sp.SourceColumn = dc.ColumnName;
dt.UpdateCommand.Parameters.Add(sp);
} DataTable dk = new DataTable();//sp_pkeys tblUser foreach (DataRow rw in dk.Rows)
{
sqlUpdate += rw[3].ToString().Trim() + "=@" + rw[3].ToString().Trim() + " and ";
sqlDelete += rw[3].ToString().Trim() + "=@" + rw[3].ToString().Trim() + " and "; SqlParameter sp = new SqlParameter("@" + rw[3].ToString().Trim(), SqlDbType.VarChar);
sp.SourceColumn = rw[3].ToString().Trim();
dt.DeleteCommand.Parameters.Add(sp);
} sqlUpdate = sqlUpdate.Substring(0, sqlUpdate.Length - 4);
sqlDelete = sqlDelete.Substring(0, sqlDelete.Length - 4); dt.UpdateCommand.CommandText = sqlUpdate;
dt.DeleteCommand.CommandText = sqlDelete; return dt; }
string[] arr ={ "22", "1", "A" };
string[] ok ={ "22", "1", "A" }; String SqlStr = "select * from test"; //新建内存表dt
DataTable dt = new DataTable("test"); //建立sql适配器 SqlDataAdapter da = new SqlDataAdapter(SqlStr, con); da.Fill(dt); //增加数组里的数据
for (int i = 0; i < arr.Length; i++)
{
DataRow row = dt.NewRow(); row["test"] = test[i];
row["arr"] = arr[i];
row["ok"] = ok[i]; dt.Rows.Add(row); //显示当前行的状态 Console.Write(row[0].ToString());
} SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da); //建立sql数据集 并且更新数据库
DataSet ds = new DataSet(); ds.Tables.Add(dt); int val = da.Update(ds, "test"); ds.AcceptChanges();
DataTable dt=GridView1.DataSource as DataTable;传递到方法
你做好还是选复制到数据库中先运行下。这是一种好习惯,会避免很多不必要的
错误
多循环了一条.