SQL Server:UPDATE不使用C#更新数据库 [英] SQL Server : UPDATE not updating database Using C#
本文介绍了SQL Server:UPDATE不使用C#更新数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个带有Web表单的网页.该表单已预填充了SQL Server数据库中的数据.用户可以进入并编辑任何字段，然后单击底部的保存"按钮以更新数据库中的记录.由于某种原因，当我将表格预先填充数据时，update语句不会更新数据库中的记录.如果我删除了页面加载方法上预先填写表单的代码，则update语句将按预期工作.当我在页面加载方法中用数据预先填充表单时，update语句似乎正在使用记录中已有的相同数据(而不是用户输入的新信息)更新记录.我完全不知道如何解决此问题，因为表单必须预先加载数据，以便用户可以分别编辑表单.
I have a webpage that has a web form on it. The form is pre-populated with data from a SQL Server database. The user can go in and edit any of the fields and click the "Save" button on the bottom to update the record in the database. For some reason, the update statement isnt updating the record in the database when I have the form pre-filled with data. If I remove the code pre-filling the form on the page load method the update statement works as it should. When I have the form pre-filled with data in the page load method, the update statement appears to be updating the record with the same data that it already had in it, not the new information the user entered. I am completely lost on how to fix this issue as the form has to have data preloaded in it so the user can edit the form respectively.
有人可以指出我需要进行的错误或更正吗?我碰到了众所周知的砖墙:(
Can someone point out an error or some correction that I need to make? I've hit the proverbially brick wall :(
在下面，您将找到页面加载方法和保存"按钮单击事件处理程序.
Below you will find the page load method and the save button click event handler.
protected void Page_Load(object sender, EventArgs e)
{
String projectID = Request.QueryString["jobID"];
String reportID = Request.QueryString["reportID"];
string selectStatement = "SELECT * FROM ahu_data WHERE unit_ID = " + reportID;
string sqlConnectionString = "Removed for Security";
using (SqlConnection connection1 = new SqlConnection(sqlConnectionString))
{
SqlCommand selectCommand = new SqlCommand(selectStatement, connection1);
connection1.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
UMTextBox.Text = reader["make"].ToString();
UMOTextBox.Text = reader["model"].ToString();
UTTextBox.Text = reader["type"].ToString();
USITextBox.Text = reader["size"].ToString();
USTextBox.Text = reader["serial"].ToString();
UATextBox.Text = reader["arrangement"].ToString();
UCTextBox.Text = reader["class"].ToString();
UDTextBox.Text = reader["discharge"].ToString();
UMSTextBox.Text = reader["make_sheave"].ToString();
USDTextBox.Text = reader["sheave_diameter"].ToString();
USBTextBox.Text = reader["sheave_bore"].ToString();
UBNTextBox.Text = reader["belts"].ToString();
UBSTextBox.Text = reader["belt_size"].ToString();
UFNTextBox.Text = reader["filters"].ToString();
UFSTextBox.Text = reader["filter_size"].ToString();
TCFMDTextBox.Text = reader["unitTotalCFMDesign"].ToString();
TCFMATextBox.Text = reader["unitTotalCFMActual"].ToString();
RACFMDTextBox.Text = reader["unitReturnAirCFMDesign"].ToString();
RACFMATextBox.Text = reader["unitReturnAirCFMActual"].ToString();
OACFMDTextBox.Text = reader["unitOutsideAirCFMDesign"].ToString();
OACFMATextBox.Text = reader["unitOutsideAirCFMActual"].ToString();
EACFMDTextBox.Text = reader["unitExhaustAirCFMDesign"].ToString();
EACFMATextBox.Text = reader["unitExhaustAirCFMActual"].ToString();
FRPMDTextBox.Text = reader["unitFanRPMDesign"].ToString();
FRPMATextBox.Text = reader["unitFanRPMActual"].ToString();
MRPMDTextBox.Text = reader["unitMotorRPMDesign"].ToString();
MRPMATextBox.Text = reader["unitMotorRPMActual"].ToString();
MVDTextBox.Text = reader["unitMotorVoltsDesign"].ToString();
MVATextBox.Text = reader["unitMotorVoltsActual"].ToString();
MADTextBox.Text = reader["unitMotorAmpsDesign"].ToString();
MAATextBox.Text = reader["unitMotorAmpsActual"].ToString();
MMTextBox.Text = reader["motor_make"].ToString();
MFTextBox.Text = reader["motor_frame"].ToString();
MHPTextBox.Text = reader["motor_hp"].ToString();
MRPMTextBox.Text = reader["motor_rpm"].ToString();
MVTextBox.Text = reader["motor_volts"].ToString();
MPHTextBox.Text = reader["motor_phasehz"].ToString();
MFLATextBox.Text = reader["motor_fl_amps"].ToString();
MSFTextBox.Text = reader["motor_sf"].ToString();
MMSTextBox.Text = reader["motor_make_sheave"].ToString();
MSDTextBox.Text = reader["motor_sheave_diameter"].ToString();
MSBTextBox.Text = reader["motor_sheave_bore"].ToString();
MODTextBox.Text = reader["motor_operating_diameter"].ToString();
MSCDTextBox.Text = reader["motor_sheave_center_distance"].ToString();
TSPDTextBox.Text = reader["motorTotalSPDesign"].ToString();
TSPATextBox.Text = reader["motorTotalSPActual"].ToString();
ESPDTextBox.Text = reader["motorEnteringSPDesign"].ToString();
ESPATextBox.Text = reader["motorEnteringSPActual"].ToString();
SSPDTextBox.Text = reader["motorSuctionSPDesign"].ToString();
SSPATextBox.Text = reader["motorSuctionSPActual"].ToString();
DSPDTextBox.Text = reader["motorDischargeSPDesign"].ToString();
DSPATextBox.Text = reader["motorDischargeSPActual"].ToString();
PCSPDTextBox.Text = reader["motorPreheatCoilSPDesign"].ToString();
PCSPATextBox.Text = reader["motorPreheatCoilSPActual"].ToString();
CCSPDTextBox.Text = reader["motorCoolingCoilSPDesign"].ToString();
CCSPATextBox.Text = reader["motorCoolingCoilSPActual"].ToString();
RCSPDTextBox.Text = reader["motorReheatCoilSPDesign"].ToString();
RCSPATextBox.Text = reader["motorReheatCoilSPActual"].ToString();
FSPDTextBox.Text = reader["motorFilterSPDesign"].ToString();
FSPATextBox.Text = reader["motorFilterSPActual"].ToString();
AFSPDTextBox.Text = reader["motorAfterFilterSPDesign"].ToString();
AFSPATextBox.Text = reader["motorAfterFilterSPActual"].ToString();
WSPDTextBox.Text = reader["motorWheelSPDesign"].ToString();
WSPATextBox.Text = reader["motorWheelSPActual"].ToString();
RemarksTextArea.Text = reader["remarks"].ToString();
}
}
connection1.Close();
}
}
这是保存按钮单击处理程序，用于更新数据库中的记录.
And here is the Save button click handler that updates the record in the database.
protected void SaveReportButton_Click(object sender, EventArgs e)
{
String projectID = Request.QueryString["jobID"];
String reportID = Request.QueryString["reportID"];
string unitMake = UMTextBox.Text;
string unitModel = UMOTextBox.Text;
string unitType = UTTextBox.Text;
string unitSize = USITextBox.Text;
string unitSerial = USTextBox.Text;
string unitArrangement = UATextBox.Text;
string unitClass = UCTextBox.Text;
string unitDischarge = UDTextBox.Text;
string unitMS = UMSTextBox.Text;
string unitSD = USDTextBox.Text;
string unitSB = USBTextBox.Text;
string unitBeltNumber = UBNTextBox.Text;
string unitBeltSize = UBSTextBox.Text;
string unitFilterNumber = UFNTextBox.Text;
string unitFilterSize = UFSTextBox.Text;
string unitTotalCFMDesign = TCFMDTextBox.Text;
string unitTotalCFMActual = TCFMATextBox.Text;
string unitReturnAirCFMDesign = RACFMDTextBox.Text;
string unitReturnAirCFMActual = RACFMATextBox.Text;
string unitOutsideAirCFMDesign = OACFMDTextBox.Text;
string unitOutsideAirCFMActual = OACFMATextBox.Text;
string unitExhaustAirCFMDesign = EACFMDTextBox.Text;
string unitExhaustAirCFMActual = EACFMATextBox.Text;
string unitFanRPMDesign = FRPMDTextBox.Text;
string unitFanRPMActual = FRPMATextBox.Text;
string unitMotorRPMDesign = MRPMDTextBox.Text;
string unitMotorRPMActual = MRPMATextBox.Text;
string unitMotorVoltsDesign = MVDTextBox.Text;
string unitMotorVoltsActual = MVATextBox.Text;
string unitMotorAmpsDesign = MADTextBox.Text;
string unitMotorAmpsActual = MAATextBox.Text;
string motorMake = MMTextBox.Text;
string motorFrame = MFTextBox.Text;
string motorHP = MHPTextBox.Text;
string motorRPM = MRPMTextBox.Text;
string motorVolts = MVTextBox.Text;
string motorPhaseHz = MPHTextBox.Text;
string motorFullLoadAmps = MFLATextBox.Text;
string motorSF = MSFTextBox.Text;
string motorMakeSheave = MMSTextBox.Text;
string motorSheaveDiameter = MSDTextBox.Text;
string motorSheaveBore = MSBTextBox.Text;
string motorOperatingDiameter = MODTextBox.Text;
string motorSheaveCDistance = MSCDTextBox.Text;
string motorTotalSPDesign = TSPDTextBox.Text;
string motorTotalSPActual = TSPATextBox.Text;
string motorEnteringSPDesign = ESPDTextBox.Text;
string motorEnteringSPActual = ESPATextBox.Text;
string motorSuctionSPDesign = SSPDTextBox.Text;
string motorSuctionSPActual = SSPATextBox.Text;
string motorDischargeSPDesign = DSPDTextBox.Text;
string motorDischargeSPActual = DSPATextBox.Text;
string motorPreheatCoilSPDesign = PCSPDTextBox.Text;
string motorPreheatCoilSPActual = PCSPATextBox.Text;
string motorCoolingCoilSPDesign = CCSPDTextBox.Text;
string motorCoolingCoilSPActual = CCSPATextBox.Text;
string motorReheatCoilSPDesign = RCSPDTextBox.Text;
string motorReheatCoilSPActual = RCSPATextBox.Text;
string motorFilterSPDesign = FSPDTextBox.Text;
string motorFilterSPActual = FSPATextBox.Text;
string motorAfterFilterSPDesign = AFSPDTextBox.Text;
string motorAfterFilterSPActual = AFSPATextBox.Text;
string motorWheelSPDesign = WSPDTextBox.Text;
string motorWheelSPActual = WSPATextBox.Text;
string remarks = RemarksTextArea.Text;
string updateStatement = @"UPDATE ahu_data SET make=@UNITMAKE, model=@UNITMODEL, type=@UNITTYPE, size=@UNITSIZE, serial=@UNITSERIAL, arrangement=@UNITARRANGEMENT,
class=@UNITCLASS, discharge=@UNITDISCHARGE, make_sheave=@UNITMS, sheave_diameter=@UNITSD, sheave_bore=@UNITSB,
belts=@UNITBELTNUMBER, belt_size=@UNITBELTSIZE, filters=@UNITFILTERNUMBER, filter_size=@UNITBELTSIZE,
unitTotalCFMDesign=@UNITTOTALCFMDESIGN, unitTotalCFMActual=@UNITTOTALCFMACTUAL, unitReturnAirCFMDesign=@UNITRETURNAIRCFMDESIGN,
unitReturnAirCFMActual=@UNITRETURNAIRCFMACTUAL, unitOutsideAirCFMDesign=@UNITOUTSIDEAIRCFMDESIGN,
unitOutsideAirCFMActual=@UNITOUTSIDEAIRCFMACTUAL, unitExhaustAirCFMDesign=@UNITEXHAUSTAIRCFMDESIGN,
unitExhaustAirCFMActual=@UNITEXHAUSTAIRCFMACTUAL, unitFanRPMDesign=@UNITFANRPMDESIGN,
unitFanRPMActual=@UNITFANRPMACTUAL, unitMotorRPMDesign=@UNITMOTORRPMDESIGN, unitMotorRPMActual=@UNITMOTORRPMACTUAL,
unitMotorVoltsDesign=@UNITMOTORVOLTSDESIGN, unitMotorVoltsActual=@UNITMOTORVOLTSACTUAL, unitMotorAmpsDesign=@UNITMOTORAMPSDESIGN,
unitMotorAmpsActual=@UNITMOTORAMPSACTUAL, motor_make=@MOTORMAKE, motor_frame=@MOTORFRAME, motor_hp=@MOTORHP,
motor_rpm=@MOTORRPM, motor_volts=@MOTORVOLTS, motor_phasehz=@MOTORPHASEHZ, motor_fl_amps=@MOTORFULLLOADAMPS,
motor_sf=@MOTORSF, motor_make_sheave=@MOTORMAKESHEAVE, motor_sheave_diameter=@MOTORSHEAVEDIAMETER,
motor_sheave_bore=@MOTORSHEAVEBORE, motor_operating_diameter=@MOTOROPERATINGDIAMETER, motor_sheave_center_distance=@MOTORSHEAVECDISTANCE,
motorTotalSPDesign=@MOTORTOTALSPDESIGN, motorTotalSPActual=@MOTORTOTALSPACTUAL, motorEnteringSPDesign=@MOTORENTERINGSPDESIGN,
motorEnteringSPActual=@MOTORENTERINGSPACTUAL, motorSuctionSPDesign=@MOTORSUCTIONSPDESIGN, motorSuctionSPActual=@MOTORSUCTIONSPACTUAL,
motorDischargeSPDesign=@MOTORDISCHARGESPDESIGN, motorDischargeSPActual=@MOTORDISCHARGESPACTUAL, motorPreheatCoilSPDesign=@MOTORPREHEATCOILSPDESIGN,
motorPreheatCoilSPActual=@MOTORPREHEATCOILSPACTUAL, motorCoolingCoilSPDesign=@MOTORCOOLINGCOILSPDESIGN, motorCoolingCoilSPActual=@MOTORCOOLINGCOILSPACTUAL,
motorReheatCoilSPDesign=@MOTORREHEATCOILSPDESIGN, motorReheatCoilSPActual=@MOTORREHEATCOILSPACTUAL, motorFilterSPDesign=@MOTORFILTERSPDESIGN,
motorFilterSPActual=@MOTORFILTERSPACTUAL, motorAfterFilterSPDesign=@MOTORAFTERFILTERSPDESIGN, motorAfterFilterSPActual=@MOTORAFTERFILTERSPACTUAL,
motorWheelSPDesign=@MOTORWHEELSPDESIGN, motorWheelSPActual=@MOTORWHEELSPACTUAL, remarks=@REMARKS WHERE unit_ID = " + reportID;
string sqlConnectionString = "Removed for Security";
using (SqlConnection connection1 = new SqlConnection(sqlConnectionString))
{
connection1.Open();
using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection1))
{
updateCommand.Parameters.AddWithValue("@UNITMAKE", unitMake);
updateCommand.Parameters.AddWithValue("@UNITMODEL", unitModel);
updateCommand.Parameters.AddWithValue("@UNITTYPE", unitType);
updateCommand.Parameters.AddWithValue("@UNITSIZE", unitSize);
updateCommand.Parameters.AddWithValue("@UNITSERIAL", unitSerial);
updateCommand.Parameters.AddWithValue("@UNITARRANGEMENT", unitArrangement);
updateCommand.Parameters.AddWithValue("@UNITCLASS", unitClass);
updateCommand.Parameters.AddWithValue("@UNITDISCHARGE", unitDischarge);
updateCommand.Parameters.AddWithValue("@UNITMS", unitMS);
updateCommand.Parameters.AddWithValue("@UNITSD", unitSD);
updateCommand.Parameters.AddWithValue("@UNITSB", unitSB);
updateCommand.Parameters.AddWithValue("@UNITBELTNUMBER", unitBeltNumber);
updateCommand.Parameters.AddWithValue("@UNITBELTSIZE", unitBeltSize);
updateCommand.Parameters.AddWithValue("@UNITFILTERNUMBER", unitFilterNumber);
updateCommand.Parameters.AddWithValue("@UNITFILTERSIZE", unitFilterSize);
updateCommand.Parameters.AddWithValue("@UNITTOTALCFMDESIGN", unitTotalCFMDesign);
updateCommand.Parameters.AddWithValue("@UNITTOTALCFMACTUAL", unitTotalCFMActual);
updateCommand.Parameters.AddWithValue("@UNITRETURNAIRCFMDESIGN", unitReturnAirCFMDesign);
updateCommand.Parameters.AddWithValue("@UNITRETURNAIRCFMACTUAL", unitReturnAirCFMActual);
updateCommand.Parameters.AddWithValue("@UNITOUTSIDEAIRCFMDESIGN", unitOutsideAirCFMDesign);
updateCommand.Parameters.AddWithValue("@UNITOUTSIDEAIRCFMACTUAL", unitOutsideAirCFMActual);
updateCommand.Parameters.AddWithValue("@UNITEXHAUSTAIRCFMDESIGN", unitExhaustAirCFMDesign);
updateCommand.Parameters.AddWithValue("@UNITEXHAUSTAIRCFMACTUAL", unitExhaustAirCFMActual);
updateCommand.Parameters.AddWithValue("@UNITFANRPMDESIGN", unitFanRPMDesign);
updateCommand.Parameters.AddWithValue("@UNITFANRPMACTUAL", unitFanRPMActual);
updateCommand.Parameters.AddWithValue("@UNITMOTORRPMDESIGN", unitMotorRPMDesign);
updateCommand.Parameters.AddWithValue("@UNITMOTORRPMACTUAL", unitMotorRPMActual);
updateCommand.Parameters.AddWithValue("@UNITMOTORVOLTSDESIGN", unitMotorVoltsDesign);
updateCommand.Parameters.AddWithValue("@UNITMOTORVOLTSACTUAL", unitMotorVoltsActual);
updateCommand.Parameters.AddWithValue("@UNITMOTORAMPSDESIGN", unitMotorAmpsDesign);
updateCommand.Parameters.AddWithValue("@UNITMOTORAMPSACTUAL", unitMotorAmpsActual);
updateCommand.Parameters.AddWithValue("@MOTORMAKE", motorMake);
updateCommand.Parameters.AddWithValue("@MOTORFRAME", motorFrame);
updateCommand.Parameters.AddWithValue("@MOTORHP", motorHP);
updateCommand.Parameters.AddWithValue("@MOTORRPM", motorRPM);
updateCommand.Parameters.AddWithValue("@MOTORVOLTS", motorVolts);
updateCommand.Parameters.AddWithValue("@MOTORPHASEHZ", motorPhaseHz);
updateCommand.Parameters.AddWithValue("@MOTORFULLLOADAMPS", motorFullLoadAmps);
updateCommand.Parameters.AddWithValue("@MOTORSF", motorSF);
updateCommand.Parameters.AddWithValue("@MOTORMAKESHEAVE", motorMakeSheave);
updateCommand.Parameters.AddWithValue("@MOTORSHEAVEDIAMETER", motorSheaveDiameter);
updateCommand.Parameters.AddWithValue("@MOTORSHEAVEBORE", motorSheaveBore);
updateCommand.Parameters.AddWithValue("@MOTOROPERATINGDIAMETER", motorOperatingDiameter);
updateCommand.Parameters.AddWithValue("@MOTORSHEAVECDISTANCE", motorSheaveCDistance);
updateCommand.Parameters.AddWithValue("@MOTORTOTALSPDESIGN", motorTotalSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORTOTALSPACTUAL", motorTotalSPActual);
updateCommand.Parameters.AddWithValue("@MOTORENTERINGSPDESIGN", motorEnteringSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORENTERINGSPACTUAL", motorEnteringSPActual);
updateCommand.Parameters.AddWithValue("@MOTORSUCTIONSPDESIGN", motorSuctionSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORSUCTIONSPACTUAL", motorSuctionSPActual);
updateCommand.Parameters.AddWithValue("@MOTORDISCHARGESPDESIGN", motorDischargeSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORDISCHARGESPACTUAL", motorDischargeSPActual);
updateCommand.Parameters.AddWithValue("@MOTORPREHEATCOILSPDESIGN", motorPreheatCoilSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORPREHEATCOILSPACTUAL", motorPreheatCoilSPActual);
updateCommand.Parameters.AddWithValue("@MOTORCOOLINGCOILSPDESIGN", motorCoolingCoilSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORCOOLINGCOILSPACTUAL", motorCoolingCoilSPActual);
updateCommand.Parameters.AddWithValue("@MOTORREHEATCOILSPDESIGN", motorReheatCoilSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORREHEATCOILSPACTUAL", motorReheatCoilSPActual);
updateCommand.Parameters.AddWithValue("@MOTORFILTERSPDESIGN", motorFilterSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORFILTERSPACTUAL", motorFilterSPActual);
updateCommand.Parameters.AddWithValue("@MOTORAFTERFILTERSPDESIGN", motorAfterFilterSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORAFTERFILTERSPACTUAL", motorAfterFilterSPActual);
updateCommand.Parameters.AddWithValue("@MOTORWHEELSPDESIGN", motorWheelSPDesign);
updateCommand.Parameters.AddWithValue("@MOTORWHEELSPACTUAL", motorWheelSPActual);
updateCommand.Parameters.AddWithValue("@REMARKS", remarks);
updateCommand.ExecuteNonQuery();
}
connection1.Close();
}
}
推荐答案
Page_Load避免重新执行填充文本框的代码，则="nofollow"> ASP.NET页面生命周期会导致这种情况.
ASP.NET page lifecycle causes this situation if you don't protect the Page_Load from reexecuting the code that fill your textboxes.
if (!IsPostBack)
{
string selectStatement = "SELECT * FROM ahu_data WHERE unit_ID = " + reportID;
string sqlConnectionString = "Removed for Security";
using (SqlConnection connection1 = new SqlConnection(sqlConnectionString))
{
.... rest of code that pre-fill your fields
页面.IsPostBack 是Page的布尔属性，用于通知您的代码是第一次调用该页面还是由于某些需要在服务器端处理的事件而被调用.
在后一种情况下，您不应再次执行填充文本框的代码，否则，当流程到达您的按钮代码时，您会发现具有原始值的文本框而不是修改后的文本框，因为Page_Load已重置所有内容.
Page.IsPostBack is a boolean property of the Page that informs your code if the page is called for the first time or if it is called as a consequence of some event that need to be processed server-side.
In the latter case you should not execute again the code that fills your textboxes otherwise, when the flow reaches your button code, you will find the textboxes with the original values instead of the modified ones because the Page_Load has resetted everything.
也不要忘记上面关于参数化第一个查询的评论.您已经完成了参数化UPDATE的大部分工作，仅剩下一个参数即可完成.
And do not forget the comment above about parameterizing your first query. You have already done the biggest part parameterizing the UPDATE, just one parameter remains and it is complete.
这篇关于SQL Server:UPDATE不使用C#更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
