高手帮忙：如何向Access数据库一次插入多行数据。

shengyongwang 2009-06-09 03:06:42

我想向access数据库一次插入多条数据有错误，但是插入sql数据库就没有错误。代码如下
sql数据库：



  SqlConnection con = new SqlConnection("server=.;database=test;uid=sa;pwd=123");

            con.Open();

            string sql="";

            sql += "insert into userinfo (num,name) values(1,'a')";

            sql += "insert into userinfo (num,name) values(2,'b')";

            sql += "insert into userinfo (num,name) values(3,'c')";

            SqlCommand cmd = new SqlCommand(sql, con);

            if (cmd.ExecuteNonQuery()>0)

            {

                MessageBox.Show("OK");

            }

Access数据库：



 OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=d:\\GYMRace.mdb");

            con.Open();

            string sql = "";

            sql += "insert into teaminfo (num,username,isplay) values(1,'a',1)";

            sql += "insert into teaminfo (num,username,isplay) values(2,'b',1)";

            sql += "insert into teaminfo (num,username,isplay) values(3,'c',1)";

            OleDbCommand cmd = new OleDbCommand(sql, con);

            if (cmd.ExecuteNonQuery() > 0)

            {

                MessageBox.Show("OK");

            }

执行第一个没有错误。执行第二个就有错误，说Sql语句的结束位置缺少分号。当我在每条sql语句的结尾加上分号的时候，又说在sql语句结尾之后找到字符，把insert前面加一个空格也不行。是不是不能向Access数据库一次插入多条数据啊，请大家帮我看看，谢谢了。

...全文

1535 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

gdjlc 2009-06-09

打赏
举报

access不支持上面这种用法,可以用事务循环插入多行数据：



 /*=============2009/06/09 测试通过===============*/

 OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:/Inetpub/wwwroot/DotNetArticle/App_Data/Article.mdb"); //这里换成你的路径

        con.Open();  



        OleDbCommand cmd = new OleDbCommand();

        cmd.Connection = con;

        cmd.Transaction = con.BeginTransaction();

        try

        {

            string sql1 = "insert into teaminfo (num,username,isplay) values(1,'a',1)";

            string sql2 = "insert into teaminfo (num,username,isplay) values(2,'b',1)";

            string sql3 = "insert into teaminfo (num,username,isplay) values(3,'c',1)";



            string[] SQLStringList = { sql1, sql2, sql3 };

            for (int n = 0; n < SQLStringList.Length; n++)

            {

                string strsql = SQLStringList[n].ToString();

                if (strsql.Trim().Length > 1)

                {

                    cmd.CommandText = strsql;

                    cmd.ExecuteNonQuery();

                }

            }          

            cmd.Transaction.Commit();  //提交事务

            MessageBox.Show("OK");



        }

        catch (Exception)

        {

            cmd.Transaction.Rollback();

        }

        finally

        {

            con.Close();

        }