OleDbParameter在Access資料庫Insert時引數不對應的怪現象[友情提示]

protected void signup_submit_ServerClick(object sender, ImageClickEventArgs e)
    {
        //入資料庫
        string connStr = EightourConfiguration.DbConnectionString;
        string loginNameValue = LoginName.Value;
        string passwordValue = Password.Value;
        string mobileValue = Mobile.Value;
        string emailValue = Email.Value;
        string sexValue = Sex.SelectedValue;
        int sex = 2;
        if (sexValue == "T")
        {
            sex = 1;
        }
        else if (sexValue == "F")
        {
            sex = 0;
        }
        string chineseNameValue = ChineseName.Value;
        string addressValue = Address.Value;
        string postcodeValue = Postcode.Value;
        string telValue = Tel.Value;

       //注意引數順序
        string updateSql = "insert into MB_BaseInfo ([LoginName],[Password],[ChineseName],[Sex]) values (@Para_LoginName,@Para_Password,@Para_ChineseName,@Para_Sex)";
        OleDbCommand cmd = new OleDbCommand(updateSql);

       //注意引數順序與上面一一對應
        OleDbParameter Para_LoginName = new OleDbParameter("@Para_LoginName", System.Data.OleDb.OleDbType.VarChar, 50);
        Para_LoginName.Value = loginNameValue;
        cmd.Parameters.Add(Para_LoginName);

        OleDbParameter Para_Password = new OleDbParameter("@Para_Password", System.Data.OleDb.OleDbType.VarChar, 50);
        Para_Password.Value = passwordValue;
        cmd.Parameters.Add(Para_Password);

        OleDbParameter Para_ChineseName = new OleDbParameter("@Para_ChineseName", System.Data.OleDb.OleDbType.VarChar, 50);
        Para_ChineseName.Value = chineseNameValue;
        cmd.Parameters.Add(Para_ChineseName);

        OleDbParameter Para_Sex = new OleDbParameter("@Para_Sex", System.Data.OleDb.OleDbType.Integer);
        Para_Sex.Value = sex;
        cmd.Parameters.Add(Para_Sex);
       
        int lastId = -1;

        using (OleDbConnection myConnection = new OleDbConnection(connStr))
            {
                cmd.Connection = myConnection;
                myConnection.Open();   
                OleDbTransaction trans = myConnection.BeginTransaction();
                cmd.Transaction = trans;
                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "select @@identity";
                    lastId = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                    trans.Commit();
                }
                catch (Exception exc)
                {
                    trans.Rollback();
                    throw new Exception(exc.Message);
                }
                finally
                {
                }
            }
            if (lastId == -1) return;

            bool isSuccess = false;

       //注意引數順序
        updateSql = "insert into MB_PersonalInfo ([BaseInfoId],[Tel],[Mobile],[Address],[Postcode],[Email]) values(@Para_BaseInfoId,@Para_Tel,@Para_Mobile,@Para_Address,@Para_Postcode,@Para_Email)";
        cmd = new OleDbCommand(updateSql);

       //注意引數順序與上面並不一一對應
        OleDbParameter Para_BaseInfoId = new OleDbParameter("@Para_BaseInfoId", System.Data.OleDb.OleDbType.Integer);
        Para_BaseInfoId.Value = lastId;
        cmd.Parameters.Add(Para_BaseInfoId);

        OleDbParameter Para_Tel = new OleDbParameter("@Para_Tel", System.Data.OleDb.OleDbType.VarChar, 50);
        Para_Tel.Value = telValue;
        cmd.Parameters.Add(Para_Tel);

//這裡現在是Postcode郵政編碼，上面第三個引數是Mobile，不對應了。
        OleDbParameter Para_Postcode = new OleDbParameter("@Para_Postcode", System.Data.OleDb.OleDbType.VarChar, 50);
        Para_Postcode.Value = postcodeValue;
        cmd.Parameters.Add(Para_Postcode);

        OleDbParameter Para_Mobile = new OleDbParameter("@Para_Mobile", System.Data.OleDb.OleDbType.VarChar, 50);
        Para_Mobile.Value = mobileValue;
        cmd.Parameters.Add(Para_Mobile);

        OleDbParameter Para_Address = new OleDbParameter("@Para_Address", System.Data.OleDb.OleDbType.VarChar,255);
        Para_Address.Value = addressValue;
        cmd.Parameters.Add(Para_Address);

        OleDbParameter Para_Email = new OleDbParameter("@Para_Email", System.Data.OleDb.OleDbType.VarChar, 255);
        Para_Email.Value = emailValue;
        cmd.Parameters.Add(Para_Email);

        using (OleDbConnection myConnection = new OleDbConnection(connStr))
        {
            cmd.Connection = myConnection;
            myConnection.Open();
            OleDbTransaction trans = myConnection.BeginTransaction();
            cmd.Transaction = trans;
            try
            {
                cmd.ExecuteNonQuery();
                trans.Commit();
                isSuccess = true;
            }
            catch (Exception exc)
            {
                trans.Rollback();
                throw new Exception(exc.Message);
            }
            finally
            {
            }
        }
        if (isSuccess)
        {
            lblNotifier.Visible = true;
            lblNotifier.Text = "
你已成功註冊！alert('恭喜您，註冊成功！');";
        }
        else
        {
            lblNotifier.Visible = false;
        }
    }
執行上面程式碼時，結果發現資料庫表中Mobile的值成了Postcode的值，Address的值成了Mobile的值.....亂套了。
如果將上面的相關順序調整一下，變成一一對應，就可以了。

在SQL Server資料庫中就不會涉及此類問題。看來，是ACCESS的侷限。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14601556/viewspace-528473/，如需轉載，請註明出處，否則將追究法律責任。