C# 操作mysql insert语句 总是提示 输入字符串的格式不正确


如上图所示，是调试出错的提示//原先读取到idtxt中的字符串是gbk编码的，mysql数据库用的是utf8编码，通过下面的语句转换为utf8                    System.Text.Encoding gbk, utf8;
                    //gb2312   
                    gbk = System.Text.Encoding.GetEncoding("gbk");
                    //utf-8   
                    utf8 = System.Text.Encoding.GetEncoding("utf-8");
                    byte[] gb;
                    gb = gbk.GetBytes(idtxt);
                    gb = System.Text.Encoding.Convert(gbk, utf8, gb);
                    MessageBox.Show("测试提示4.0:UTF8编码的身份证信息\n" + gb);
                    string buffer = utf8.GetString(gb);
                    MessageBox.Show("测试提示4.2:UTF8编码的身份证信息\n" + buffer);
                    string[] idInfor_Utf8 = buffer.Split('|');
从 测试提示4.1 可以判断转换完的数据时正常的下面是 数据库操作的代码                     conn.Open();
                    MySqlParameter p_name, p_idNum, p_sex, p_nation, p_swiTime;
                    MySqlCommand com = new MySqlCommand();
                    com.Connection = conn;
                    com.CommandText = "USE VsInfo;";
                    int i = com.ExecuteNonQuery();
                    if (i == 0)
                    {
                        MessageBox.Show("测试提示6：切换为使用数据库vsinfo");
                    }
                    //com.CommandText = "INSERT INTO vsinfo(name,idNum,sex,nation,swiTime)VALUES('张三','54232519930525101X','男','汉','201508120955');";
                    com.CommandText = "INSERT INTO vsinfo(name,idNum,sex,nation,swiTime)VALUES(?name,?idNum,?sex,?nation,?swiTime);";
                    p_name = com.Parameters.AddWithValue("?name", MySqlDbType.VarChar);
                    p_idNum = com.Parameters.AddWithValue("?idNum", MySqlDbType.VarChar);
                    p_sex = com.Parameters.AddWithValue("?sex", MySqlDbType.VarChar);
                    p_nation = com.Parameters.AddWithValue("?nation", MySqlDbType.VarChar);
                    p_swiTime = com.Parameters.AddWithValue("?swiTime", MySqlDbType.VarChar);
                    com.Prepare();
                    p_name.Value = idInfor_Utf8[0];
                    p_idNum.Value = idInfor_Utf8[5];
                    p_sex.Value = idInfor_Utf8[1];
                    p_nation.Value = idInfor_Utf8[2];
                    p_swiTime.Value = idInfor_Utf8[8];
                    MessageBox.Show("测试提示7:即将存入数据库的字符串\n" + p_name.Value + "\n" + p_idNum.Value + "\n" 
                       + p_sex.Value + "\n" + p_nation.Value + "\n" + p_swiTime.Value + "\n");
                    try { com.ExecuteNonQuery();}
                    catch (MySqlException ae)
                    {
                        MessageBox.Show(ae.Message.ToString());
                    }
                    conn.Close();直到 执行 测试提示7赋给 p_xxx.Value的值都是正确的，但是执行com.ExecuteNonQuery()时，就会报错，输入的字符串格式不正确，已经卡了两天了，不知道哪里出了问题，跪求帮忙啊~

解决方案 »

  1.   

    补充：如果使用这句
    com.CommandText = "INSERT INTO vsinfo(name,idNum,sex,nation,swiTime)VALUES('张三','54232519930525101X','男','汉','201508120955');";
    直接insert  而不用 参数的话，插入是正常执行的，换用参数方式插入就出错了~
      

  2.   

    你show 出这个看下参数赋值后，是不是正确的。
    MessageBox.Show(com.CommandText);
      

  3.   


    Show的结果是这个样子的，感觉是并没有把值赋给那几个参数？
      

  4.   

    这么写试试
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string sql = "USE VsInfo; INSERT INTO vsinfo(name,idNum,sex,nation,swiTime)VALUES(?name,?idNum,?sex,?nation,?swiTime);";
                    MySqlCommand com = new MySqlCommand(sql, conn);
                    com.Parameters.Add("name", MySqlDbType.VarChar);
                    com.Parameters["name"].Value = idInfor_Utf8[0];
                    com.Parameters.Add("idNum", MySqlDbType.VarChar);
                    com.Parameters["idNum"].Value = idInfor_Utf8[5];
                    com.Parameters.Add("sex", MySqlDbType.VarChar);
                    com.Parameters["sex"].Value = idInfor_Utf8[1];
                    com.Parameters.Add("nation", MySqlDbType.VarChar);
                    com.Parameters["nation"].Value = idInfor_Utf8[2];
                    com.Parameters.Add("swiTime", MySqlDbType.VarChar);
                    com.Parameters["swiTime"].Value = idInfor_Utf8[8];
                    try 
                    { 
                        com.ExecuteNonQuery();
                    }
                    catch (MySqlException ae)
                    {
                        MessageBox.Show(ae.Message.ToString());
                    }
                    conn.Close();