数据库数据未更新

Question

我试图在数据库中更新我的详细信息。下面是我写的代码。

我面临的问题是，数据库中的细节没有更新，而不是程序运行成功。

有什么问题吗?

private void btn_Update_Click(object sender, EventArgs e)
{
sqlConn.ConnectionString = "Server = " + server + ";" + "user Id = " + username + ";" + "password  = " + password + ";" + "database = " + database;
sqlConn.Open();
try
{
MySqlCommand sqlCmd=new MySqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandText = "Update `monthly_expense`.`exp_mast` set `Unique ID`= '@Unique ID', `First Name`=  '@First Name',`Last Name`= '@Last Name',`Address`= '@Address',`Contact Number`= '@Contact Number',`Alternate Contact Number`= '@Alt Number',`Gender`= '@gen' Where(`Unique ID`= '@Unique ID')";
if (rb_male.Checked == true)
{
gen = "Male";
}
else
{
gen = "Female";
}
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Parameters.AddWithValue("@Unique ID", txt_id.Text);
sqlCmd.Parameters.AddWithValue("@First Name", txt_fstn.Text);
sqlCmd.Parameters.AddWithValue("@Last Name", txt_lstn.Text);
sqlCmd.Parameters.AddWithValue("@Address", txt_loc.Text);
sqlCmd.Parameters.AddWithValue("@Contact Number", txt_cont.Text);
sqlCmd.Parameters.AddWithValue("@Alt Number", txt_altcont.Text);
sqlCmd.Parameters.AddWithValue("@gen", gen);
sqlCmd.ExecuteNonQuery();
sqlConn.Close();
MessageBox.Show("Done");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
txt_fstn.Clear();
txt_lstn.Clear();
txt_loc.Clear();
txt_cont.Clear();
txt_altcont.Clear();
if (rb_male.Checked)
{
rb_male.Checked = false;
}
else
{
rb_Female.Checked = false;
}
txt_id.Clear();
}
}

Answer 1

SQL中的参数名不能用引号括起来，也不能使用空格。这是错误的:

set `Unique ID`= '@Unique ID'

它使它看起来像将Unique ID字段设置为字符串值@Unique ID。你想要这样:

set `Unique ID`= @UniqueID

当然，也要为新的参数名适当地更新AddWithValue()行。

同样，sqlConn.Close()调用应该在finally块中。

Answer 2

试试这个:

sqlCmd.CommandText = "Update `monthly_expense`.`exp_mast` set `Unique_ID`= @Unique_ID, `First_Name`=  @First_Name,`Last_Name`= @Last_Name,`Address`= @Address,`Contact_Number`= @Contact_Number,`Alternate_Contact_Number`= @Alt_Number,`Gender`= @gen Where(`Unique_ID`= @Unique_ID);COMMIT;";

check字段name ..名称或参数名称

中没有空格。扩大回答:

这是我的真实代码，类似于你的，工作良好:

private void linkSaveProfile_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
using var conn = new MySqlConnection(DbConnectionString);
try
{
conn.Open();
var sql = "UPDATE users_profile SET " +
"first_name = @pfirst_name," +
"mid_name = @pmid_name," +
"last_name = @plast_name," +
"gender = @pgender," +
"education = @peducation," +
"country = @pcountry," +
"city = @pcity," +
"state = @pstate," +
"country_key = @pcountry_key," +
"mobile = @pmobile," +
"birth_year = @pbirth_year," +
"about = @pabout," +
"description = @pdescription," +
"is_public_description = @pis_public_description," +
"website = @pwebsite," +
"facebook = @pfacebook," +
"linkedin = @plinkedin," +
"skype = @pskype," +
"twitter = @ptwitter," +
"channel_name = @pchannel_name," +
"channel_lang = @pchannel_lang," +
"exper_year = @pexper_year," +
"exper_forex = @pexper_forex," +
"exper_stocks = @pexper_stocks," +
"exper_cfd = @pexper_cfd " +
"WHERE user_id = @puser_id;COMMIT; ";
var cmd = new MySqlCommand(sql, conn);
_ = cmd.Parameters.AddWithValue("@puser_id", UserId);
_ = cmd.Parameters.AddWithValue("@pfirst_name", textFirstName.Text);
_ = cmd.Parameters.AddWithValue("@pmid_name", textMidName.Text);
_ = cmd.Parameters.AddWithValue("@plast_name", textLastName.Text);
_ = cmd.Parameters.AddWithValue("@pgender", radioMale.Checked ? "male" : "female");
_ = cmd.Parameters.AddWithValue("@peducation", textEducation.Text);
_ = cmd.Parameters.AddWithValue("@pcountry", comboCountry.Text);
_ = cmd.Parameters.AddWithValue("@pcity", textCity.Text);
_ = cmd.Parameters.AddWithValue("@pstate", textState.Text);
_ = cmd.Parameters.AddWithValue("@pcountry_key", textCountryKey.Text);
_ = cmd.Parameters.AddWithValue("@pmobile", textMobile.Text);
_ = cmd.Parameters.AddWithValue("@pbirth_year", textBirthYear.Value);
_ = cmd.Parameters.AddWithValue("@pabout", textAbout.Text);
_ = cmd.Parameters.AddWithValue("@pdescription", textDescription.Text);
_ = cmd.Parameters.AddWithValue("@pis_public_description", checkPublicDescription.Checked);
_ = cmd.Parameters.AddWithValue("@pwebsite", textWebsite.Text);
_ = cmd.Parameters.AddWithValue("@pfacebook", textFacebook.Text);
_ = cmd.Parameters.AddWithValue("@plinkedin", textLinkedin.Text);
_ = cmd.Parameters.AddWithValue("@pskype", textSkype.Text);
_ = cmd.Parameters.AddWithValue("@ptwitter", textTwitter.Text);
_ = cmd.Parameters.AddWithValue("@pchannel_name", textChannelName.Text);
_ = cmd.Parameters.AddWithValue("@pchannel_lang", textChannelLang.Text);
_ = cmd.Parameters.AddWithValue("@pexper_year", textExperianceYear.Value);
_ = cmd.Parameters.AddWithValue("@pexper_forex", checkForex.Checked);
_ = cmd.Parameters.AddWithValue("@pexper_stocks", checkStocks.Checked);
_ = cmd.Parameters.AddWithValue("@pexper_cfd", checkCFD.Checked);

cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), @"Error");
}
conn.Close();
conn.Dispose();
}

可能是一些DB引擎允许的空间。在这一点上我错了。

我使用COMMIT;在查询的最后，以确保我的更新完成。无论服务器设置。