如果您想了解SQLServer：UPDATE不使用C＃更新数据库的相关知识，那么本文是一篇不可错过的文章，我们将为您提供关于c#–使用SqlDataAdapter更新数据库视图、C＃和SQLServe
如果您想了解SQL Server：UPDATE不使用C＃更新数据库的相关知识，那么本文是一篇不可错过的文章，我们将为您提供关于c# – 使用SqlDataAdapter更新数据库视图、C＃和SQL Server之间的DateTime不匹配、JavaEE.sql.Update更新数据、MySQL更新数据update的有价值的信息。
本文目录一览：- SQL Server：UPDATE不使用C＃更新数据库
- c# – 使用SqlDataAdapter更新数据库视图
- C＃和SQL Server之间的DateTime不匹配
- JavaEE.sql.Update更新数据
- MySQL更新数据update
SQL Server：UPDATE不使用C＃更新数据库
我有一个上面带有Web表单的网页。该表单已预填充了SQL
Server数据库中的数据。用户可以进入并编辑任何字段，然后单击底部的“保存”按钮以更新数据库中的记录。由于某种原因，当我将表格预先填充数据时，update语句不会更新数据库中的记录。如果我删除了页面加载方法上预先填写表单的代码，则update语句将按预期工作。当我在页面加载方法中用数据预先填充表单时，update语句似乎正在使用记录中已有的相同数据（而不是用户输入的新信息）更新记录。我完全不知道如何解决此问题，因为表单必须预先加载数据，以便用户可以分别编辑表单。
有人可以指出我需要做的错误或更正吗？我碰到了众所周知的砖墙:(
在下面，您将找到页面加载方法和保存按钮click事件处理程序。
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(); }}这是保存按钮单击处理程序，用于更新数据库中的记录。
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(); }}答案1
小编典典如果您无法防止ASP.NET页面生命周期Page_Load重新执行填充文本框的代码，则会导致这种情况。
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 fieldsPage.IsPostBack是Page的布尔属性，用于通知您的代码是第一次调用该页面还是由于某些需要在服务器端处理的事件而调用该页面。
在后一种情况下，您不应再次执行填充文本框的代码，否则，当流程到达您的按钮代码时，您将找到具有原始值而不是修改后的值的文本框，因为Page_Load重置了所有内容。
并且不要忘记上面关于参数化您的第一个查询的评论。您已经完成了参数化UPDATE的大部分工作，仅剩下一个参数即可完成。
c# – 使用SqlDataAdapter更新数据库视图
在我的应用程序中,我想将GridControl绑定到DataTable,该DataTable包含来自多个数据库表(使用外键引用)的数据.
我在更新数据方面遇到了问题,因为我在引用多个数据库表的命令上使用了sqlDataAdapter.我收到了错误：
Dynamic sql generation is not supported against multiple base tables
我尝试通过创建一个将多个表组合成一个“表”的视图来处理它,然后我可以将其绑定到GridControl.
插入数据是在视图中使用“而不是”触发器完成的,在该视图中我将适当的数据添加到适当的表中.在普通的t-sql中,插入工作完美无缺.我可以将数据插入到我的视图中,并将其插入到适当的表中.我的sqlDataAdapter应该没有更新问题,因为它正在更新一个“表”,其余的更新由触发器完成.
不幸的是,我仍然收到错误
Dynamic sql generation is not supported against multiple base tables
你知道为什么sqlDataAdapter仍然没有让我更新,甚至认为它现在只更新一个“表” – 视图？
我的代码：
风景：
CREATE view [dbo].[v_TestTypeParameter_grid] as ( select t1.Id,t3.Name,t3.Description,t2.MinValue,t2.MaxValue,t4.Symbol from TestTypes as t1 join TestTypeParameters as t2 on t1.Id = t2.TestType join Parameters as t3 on t2.Parameter = t3.Id left join Units as t4 on t2.Unit = t4.Id )
而不是视图上的触发器：
CREATE TRIGGER [dbo].[tr_TestTypeParameteradded] ON [dbo] [v_TestTypeParameter_grid]
INSTEAD OF INSERT
AS
BEGIN
DECLARE
@TestTypeId int,@ParameterId int,@UnitId int,@ParameterName varchar(100),@MinValue decimal(18,2),@MaxValue decimal(18,@UnitSymbol varchar(100)
SELECT @TestTypeId = Id,@ParameterName = Name,@MinValue = MinValue,@MaxValue = MaxValue,@UnitSymbol = Symbol FROM Inserted;
SELECT @ParameterId = Id FROM Parameters WHERE Name = @ParameterName;
SELECT @UnitId = Id from Units WHERESymbol = @UnitSymbol;
INSERT INTO TestTypeParameters(TestType,Parameter,MinValue,MaxValue,Unit) VALUES(@TestTypeId,@ParameterId,@MinValue,@MaxValue,@UnitId);
END
我的绑定代码：
mvarParameterlistadapter = DBService.GetDataAdapter("select * from v_TestTypeParameter_grid where Id = " + mvarTestTypeId);
mvarParameterTable = new DataTable();
mvarParameterlistadapter.Fill(mvarParameterTable);
gcParameters.DataSource = mvarParameterTable;
gcParameters.RefreshDataSource();
DBService.GetDataAdapter方法：
public static sqlDataAdapter GetDataAdapter(String command,DataTable parameters = null)
{
sqlConnection lvarConnection = GetConnection();
sqlCommand lvarCommand = new sqlCommand(command,lvarConnection);
if (parameters != null && parameters.Rows.Count > 0)
{
foreach (DaTarow lvarRow in parameters.Rows)
{
lvarCommand.Parameters.AddWithValue("@" + lvarRow[0],lvarRow[1]);
}
}
sqlDataAdapter lvaradapter = new sqlDataAdapter(lvarCommand);
sqlCommandBuilder lvarCommandBuilder = new sqlCommandBuilder(lvaradapter);
return lvaradapter;
}
更新代码：
try
{
mvarParameterlistadapter.Update(mvarParameterTable);
}
catch (Exception ex)
{
// Here,I'm getting the error
}
在t-sql中工作的插入：
INSERT INTO v_TestTypeParameter_grid VALUES (2,'Fe','Iron',5.2,7.9,'%')
由于我有几个这样工作的GridControl(使用不同的数据),我试图不为自己生成sqlDataAdapter的select,insert,update和delete命令,而不是那样,我使用sqlCommandBuilder来保持它更简单.
谢谢！
编辑：
(希望)明确表达：
这是我正在研究的GridControl.原谅我的列名,他们是波兰语.
Identyfikator = Id,
纳兹瓦=姓名,
Opis =描述,
最小= MinValue,
Maksimum = MaxValue,
Jednostka =单位
现在,我在GridControl中添加了另一行,它只包含“Nazwa(Name)”和“Opis(Description)”字段.
我正在填写“Identyfikator(Id)”字段,其中包含我想要添加参数的TestType的Id以及其默认值的其他字段.新行自动添加到绑定到GridControl的DataTable中,在用户单击“保存”后,我想使用sqlDataAdapter.Update()方法将这些更改推送到数据库.这是我收到错误的地方：
Dynamic sql generation is not supported against multiple base tables
我希望它能说明我想要实现的目标以及我是如何做到的.
编辑：
我设法找到了解决这个问题的方法.您可以在下面的答案中查看.
解决方法
我终于设法完成了项目的那部分.我也找到了解决问题的方法.
首先,我创建了一个模仿GridControl的视图.它看起来像这样：
CREATE VIEW [dbo].[v_TestTypeParameter_grid]
AS
SELECT t1.Id,t2.Unit as Unit,t3.Id AS ParameterId
FROM
dbo.TestTypes AS t1 INNER JOIN
dbo.TestTypeParameters AS t2 ON t1.Id = t2.TestType INNER JOIN
dbo.Parameters AS t3 ON t2.Parameter = t3.Id
重要的是视图包含使目标表中的行唯一的ID(在本例中为：TestTypeId和ParameterId).
然后,因为sql仍然不允许我插入,更新或删除行
Dynamic sql generation is not supported against multiple base tables
错误.
我通过在视图上创建INSTEAD OF触发器来跳过它(一个用于插入,更新和删除).
我的触发器看起来像这样(是的,我知道它们应该基于它设置)：
插入：
CREATE TRIGGER [dbo].[tr_TestTypeParameteradded] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF INSERT
AS
BEGIN
DECLARE
@TestTypeId int,@UnitId = Unit from Inserted;
SELECT @ParameterId = Id from Parameters where Name = @ParameterName;
insert into TestTypeParameters(TestType,Unit) values (@TestTypeId,@UnitId);
END
更新：
CREATE TRIGGER [dbo].[tr_TestTypeParameterUpdated] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF UPDATE
AS
BEGIN
DECLARE
@TestTypeId int,2)
select * from inserted
SELECT @TestTypeId = Id,@UnitId = Unit,@ParameterId = ParameterId from Inserted;
update TestTypeParameters set MinValue = @MinValue,MaxValue = @MaxValue,Unit = @UnitId where TestType = @TestTypeId and Parameter = @ParameterId;
END
删除：
CREATE TRIGGER [dbo].[tr_TestTypeParameterDeleted] ON [dbo].[v_TestTypeParameter_grid]
INSTEAD OF DELETE
AS
BEGIN
DECLARE
@TestTypeId int,@ParameterId = ParameterId from deleted;
delete from TestTypeParameters where TestType = @TestTypeId and Parameter = @ParameterId;
END
我还必须改变我创建sqlDataAdapters的方式：
sqlCommand lvarInsert =
new sqlCommand(
"insert into v_TestTypeParameter_grid values (@Id,@Name,@Description,@Unit,@ParameterId)",DBService.Connection);
mvarParameterlistadapter.InsertCommand = lvarInsert;
lvarInsert.Parameters.Add("@Id",sqlDbType.Int,5,"Id");
lvarInsert.Parameters.Add("@Name",sqlDbType.NVarChar,"Name");
lvarInsert.Parameters.Add("@Description","Description");
lvarInsert.Parameters.Add("@MinValue",sqlDbType.Decimal,"MinValue");
lvarInsert.Parameters.Add("@MaxValue","MaxValue");
lvarInsert.Parameters.Add("@Unit","Unit");
lvarInsert.Parameters.Add("@ParameterId","ParameterId");
sqlCommand lvarSelect = new sqlCommand("select * from v_TestTypeParameter_grid where Id = " + mvarTestTypeId,DBService.Connection);
mvarParameterlistadapter.SelectCommand = lvarSelect;
sqlCommand lvarUpdate =
new sqlCommand(
"update v_TestTypeParameter_grid set Id = @Id,ParameterId = @ParameterId,Name = @Name,Description = @Description,MinValue = @MinValue,Unit = @Unit where Id = @Id and ParameterId = @ParameterId",DBService.Connection);
mvarParameterlistadapter.UpdateCommand = lvarUpdate;
lvarUpdate.Parameters.Add("@Id","Id");
lvarUpdate.Parameters.Add("@ParameterId","ParameterId");
lvarUpdate.Parameters.Add("@Name","Name");
lvarUpdate.Parameters.Add("@Description","Description");
lvarUpdate.Parameters.Add("@MinValue","MinValue");
lvarUpdate.Parameters.Add("@MaxValue","MaxValue");
lvarUpdate.Parameters.Add("@Unit","Unit");
sqlCommand lvarDelete =new sqlCommand(
"delete from v_TestTypeParameter_grid where Id = @Id and ParameterId = @ParameterId",DBService.Connection);
mvarParameterlistadapter.DeleteCommand = lvarDelete;
lvarDelete.Parameters.Add("@Id","Id");
lvarDelete.Parameters.Add("@ParameterId","ParameterId");
我几乎可以肯定它可以以不同的(更好的)方式完成,但我想分享它,因为它花了我一些时间来修复它,我找不到任何在线解决方案.
如果您对如何改进它有任何想法,请随时发表评论.
请享用！
C＃和SQL Server之间的DateTime不匹配
从字符转换日期和/或时间时转换失败 字符串
问题是您在SQL数据库中插入了一个字符串' '，而不是您指定的DateTime对象。
如果使用DateTime.TryParseExact()
示例：
DateTime fromDate;
string txtStartDate = "22/09/2020 01:27:00";
DateTime.TryParseExact(txtStartDate,"dd/MM/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None,out fromDate);
Console.WriteLine(fromDate.ToString());
现在我们还可以将该变量作为其值插入到SQL语句中。
INTO HomeVisit (LifeplusCaseId,FromDateTime,ToDateTime)
VALUES ('39909F03-2DEF-4682-89CA-0000DCC3E098',fromDate,toDate);
您可以根据自己的要求格式化日期，只需查看以下示例即可。
DateTime oDate = Convert.ToDateTime(xate);
oDate = oDate.ToString("yyyy-MM-dd");
JavaEE.sql.Update更新数据
| UPDATE |
| 语法格式 |
| UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; |
| S1.常用更新数据用法 |
| UPDATE test_master SET pwd = ''admin'' WHERE id = 1000 |
| S2.依赖上一次的值 |
| UPDATE test_master m SET m.id = m.id + 100 WHERE m.bindNo = ''bn001'' |
|
|
|
|
| ref |
| https://www.w3schools.com/sql/sql_update.asp http://www.runoob.com/mysql/mysql-update-query.html |
MySQL更新数据update
在MySQL通过UPDATE语句更新数据表中的数据。在此，我们将就用六中的student学生表
1. UPDATE基本语法
UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];
在该语法中：字段名1、字段名2…用于指定要更新的字段名称；值1、值 2…用于表示字段的新数据；WHERE 条件表达式 是可选的，它用于指定更新数据需要满足的条件
2. UPDATE更新部分数据
示例：将name为tom的记录的age设置为20并将其gender设置为female MySQL命令：
update student set age=20,gender=''female'' where name=''tom'';
mysql安装参考：http://youhua.cuohei.com/
3. UPDATE更新全部数据
示例：将所有记录的age设置为18 MySQL命令：
update student set age=18;
今天的关于SQL Server：UPDATE不使用C＃更新数据库的分享已经结束，谢谢您的关注，如果想了解更多关于c# – 使用SqlDataAdapter更新数据库视图、C＃和SQL Server之间的DateTime不匹配、JavaEE.sql.Update更新数据、MySQL更新数据update的相关知识，请在本站进行查询。
本文标签：
