C# 调用MySql存储过程,报错Column 'CheckingAccountID' cannot be null。输入数据没有出现NULL啊,奇怪,而且AccountType也不是Checking,完全不知道为什么会运行到这里然后报错
private void button6_Click(object sender, EventArgs e)
{
//插入账户信息
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand cmd = new MySqlCommand("OpenAccount", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
cmd.Parameters.Add("@AccountID", MySqlDbType.VarChar, 20, textBox60.Text);
cmd.Parameters.Add("@Balance", MySqlDbType.Float, 8, textBox16.Text);
cmd.Parameters.Add("@BrName", MySqlDbType.VarChar, 10, textBox13.Text);
cmd.Parameters.Add("@AccountType", MySqlDbType.VarChar, 20, comboBox1.Text);
cmd.Parameters.Add("@Interest", MySqlDbType.Float, 8, textBox14.Text);
cmd.Parameters.Add("@CurrencyType", MySqlDbType.VarChar, 10, textBox11.Text);
cmd.Parameters.Add("@Overdraft", MySqlDbType.Float, 8, textBox9.Text);
cmd.Parameters.Add("@ID1", MySqlDbType.VarChar, 18, textBox17.Text);
cmd.Parameters.Add("@ID2", MySqlDbType.VarChar, 18, textBox24.Text);
cmd.Parameters.Add("@ID3", MySqlDbType.VarChar, 18, textBox30.Text);
cmd.Parameters.Add("@ID4", MySqlDbType.VarChar, 18, textBox31.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("Success to insert!");
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
存储过程代码如下:
Drop Procedure IF EXISTS OpenAccount;
Delimiter //
Create Procedure OpenAccount(IN AccountID varchar(20), IN Balance float, IN BrName varchar(10),
IN AccountType varchar(20), IN Interest float, IN CurrencyType varchar(10),
IN Overdraft float, IN ID1 varchar(18), IN ID2 varchar(18), IN ID3 varchar(18), IN ID4 varchar(18))
BEGIN
Declare s int default 0;
Declare count int default 0;
Start Transaction;
IF AccountType = '储蓄账户' THEN # SavingAccount
Select COUNT(*)
From SavingManagement
Where BranchName = BrName and (ClientID = ID1 or ClientID = ID2 or ClientID = ID3 or ClientID = ID4)
Into count;
IF count <> 0 THEN
SET s = 2; # The Client have had a saving account in this branch.
ELSE
/* Insert into Saving */
Insert Into Saving
Values(AccountID, Balance, curdate(), curdate(), Interest, CurrencyType);
/* Insert into SavingManagement and SavingStatistics */
Insert Into SavingManagement
Values(ID1, BrName, AccountID);
Insert Into SavingStatistics
Values(year(curdate()), month(curdate()), BrName, ID1, AccountID, Balance);
IF ID2 <> '' THEN
Insert Into SavingManagement
Values(ID2, BrName, AccountID);
Insert Into SavingStatistics
Values(year(curdate()), month(curdate()), BrName, ID2, AccountID, Balance);
END IF;
IF ID3 <> '' THEN
Insert Into SavingManagement
Values(ID3, BrName, AccountID);
Insert Into SavingStatistics
Values(year(curdate()), month(curdate()), BrName, ID3, AccountID, Balance);
END IF;
IF ID4 <> '' THEN
Insert Into SavingManagement
Values(ID4, BrName, AccountID);
Insert Into SavingStatistics
Values(year(curdate()), month(curdate()), BrName, ID4, AccountID, Balance);
END IF;
END IF;
ELSE # CheckingAccount
Select COUNT(*)
From CheckingManagement
Where BranchName = BrName and (ClientID = ID1 or ClientID = ID2 or ClientID = ID3 or ClientID = ID4)
Into count;
IF count <> 0 THEN
SET s = 2;
ELSE
/* Insert into Checking */
Insert Into Checking
Values(AccountID, Balance, curdate(), curdate(), Overdraft);
/* Insert into CheckingManagement */
Insert Into CheckingManagement
Values(ID1, BrName, AccountID);
IF ID2 <> '' THEN
Insert Into SavingManagement
Values(ID2, BrName, AccountID);
END IF;
IF ID3 <> '' THEN
Insert Into SavingManagement
Values(ID3, BrName, AccountID);
END IF;
IF ID4 <> '' THEN
Insert Into SavingManagement
Values(ID4, BrName, AccountID);
END IF;
END IF;
END IF;
IF s = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END //
Delimiter ;