C# winform datagridview向SQL数据库中添加多行数据

我想将datagridview中的多行数据添加到SQL数据库中,但是总是显示“添加失败”,而且添加后查看数据库,只有最后一行数据库加进去了。
求解答,我的头快秃了......(ㄒoㄒ)
我们没有学过多行数据的,我只会单行添加,在网上找了很多资料也没有办法。
上学期我们老师讲的C#简直就是一坨shit,全靠自学,现在又来布置作业,枯了

dim_2是我的数据表

private void 保存转换后坐标ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //连接数据库
            string connStr = @"Data Source=8RLL4E2HQZ2PO3V;Initial Catalog=Internship;Integrated Security=True";

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            conn.Open();
            //创建SQL命令
            SqlCommand cmd = new SqlCommand();
            for (int i = 0; i < Pnum; i++)
            {
                string sql = "insert into dim_2 values(" + this.dataGridView2.Rows[i].Cells[0].Value.ToString() + "," +
                this.dataGridView2.Rows[i].Cells[1].Value + "," + this.dataGridView2.Rows[i].Cells[2].Value + "," +
                this.dataGridView2.Rows[i].Cells[3].Value + "," + this.dataGridView2.Rows[i].Cells[4].Value + "," +
                this.dataGridView2.Rows[i].Cells[5].Value + "," + this.dataGridView2.Rows[i].Cells[6].Value + ")";
                cmd.CommandText = sql;
                cmd.Connection = conn;
            }

            int row = cmd.ExecuteNonQuery();

            conn.Close();
            conn.Dispose();

            if (row == 3)
                MessageBox.Show("添加成功!");

            else
                MessageBox.Show("添加失败!");

        }

3个回答

代码有两个地方有问题,for循环最终输出的sql是一条记录的脚本,row是执行的影响行数,row==3改成row>0即可

可以for循环构造insert into批量插入语句,
语法类似:INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...),
 (value1,value2,value3,…),
 ...
 (value1,value2,value3,…);

伪代码参考如下:
string sql="insert into dim_2 values";
for (int i = 0; i < Pnum; i++)
            {
                sql += "(" + this.dataGridView2.Rows[i].Cells[0].Value.ToString() + "," +
                this.dataGridView2.Rows[i].Cells[1].Value + "," + this.dataGridView2.Rows[i].Cells[2].Value + "," +
                this.dataGridView2.Rows[i].Cells[3].Value + "," + this.dataGridView2.Rows[i].Cells[4].Value + "," +
                this.dataGridView2.Rows[i].Cells[5].Value + "," + this.dataGridView2.Rows[i].Cells[6].Value + “),”;

            }
      sql=sql.Substring(0,sql.Length-1);//移除最后的,
      cmd.CommandText = sql;
      cmd.Connection = conn;
      conn.Close();
      conn.Dispose();

            if (row >0)
                MessageBox.Show("添加成功!");
            else
                MessageBox.Show("添加失败!");
weixin_45796800
weixin_45796800 非常感谢!我明白了!!
大约一个月之前 回复

if (row == 3)
为什么等于3?你只有一个insert into

你是不是想这样写:

private void 保存转换后坐标ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //连接数据库
            string connStr = @"Data Source=8RLL4E2HQZ2PO3V;Initial Catalog=Internship;Integrated Security=True";

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            conn.Open();
            //创建SQL命令
            SqlCommand cmd = new SqlCommand();
                        int rows = 0;
            for (int i = 0; i < Pnum; i++)
            {
                string sql = "insert into dim_2 values(" + this.dataGridView2.Rows[i].Cells[0].Value.ToString() + "," +
                this.dataGridView2.Rows[i].Cells[1].Value + "," + this.dataGridView2.Rows[i].Cells[2].Value + "," +
                this.dataGridView2.Rows[i].Cells[3].Value + "," + this.dataGridView2.Rows[i].Cells[4].Value + "," +
                this.dataGridView2.Rows[i].Cells[5].Value + "," + this.dataGridView2.Rows[i].Cells[6].Value + ")";
                cmd.CommandText = sql;
                cmd.Connection = conn;
                                int row = cmd.ExecuteNonQuery();//每次都调用否则前面的2个sql不执行
                                rows += row;
            }

            int row = cmd.ExecuteNonQuery();

            conn.Close();
            conn.Dispose();

            if (rows == 3)
                MessageBox.Show("添加成功!");

            else
                MessageBox.Show("添加失败!");

        }
weixin_45796800
weixin_45796800 太感谢了!我明白了,已经修改好了!!
大约一个月之前 回复
qq_23126581
渊渟无迹静涛君 赞同这个答案。
大约一个月之前 回复
大概的意思就是,每执行一次for循环就要添加一次数据,而添加数据的代码是cmd.ExecuteNonQuery()这一句,而你没有吧这句代码放进for循环,导致的结果就是你....
weixin_45796800
weixin_45796800 好嘞!我晓得了,之前不懂ExecuteNonQuery这个语句,非常感谢~
大约一个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问