这看上去有什么不对？

Question

私有int DBUpdate() {

DAL dal = new DAL();
string upd = "UPDATE [RPform] SET [ProjectName] = '@pname', [ProjectCode] = '@pcode', [Country] = @cnt, ";
upd += "[StartDate] = '@startdate', [FinishDate] = '@finishdate', [TotalParticipants] = @totpart, [ArrivalDate] = '@arrivedate', ";
upd += "[AirportTransfer] = @airtran, [AirportDate] = '@airdate', [AirportHours] = @airhour, [AirportMinutes] = @airmin, ";
upd += "[Problems] = '@problems', [FirstDayActivities] = '@fdayact' ";
upd += "WHERE [UserID]=@usid";

    OleDbParameter[] parm = new OleDbParameter[] { 
    new OleDbParameter("@pname",projname.Text),
    new OleDbParameter("@pcode",projcode.Text),
    new OleDbParameter("@cnt",countries.SelectedIndex),
    new OleDbParameter("@startdate",datestart.Text),
    new OleDbParameter("@finishdate",datefinish.Text),
    new OleDbParameter("@totpart",totalparticipants.Text),
    new OleDbParameter("@arrivedate",datearrival.Text),
    new OleDbParameter("@airtran",RadioButtonList1.SelectedValue),
    new OleDbParameter("@airdate",dateairport.Text),
    new OleDbParameter("@airhour",airporthours.SelectedIndex),
    new OleDbParameter("@airmin",airportminutes.SelectedIndex),
    new OleDbParameter("@problems",problems.Value),
    new OleDbParameter("@fdayact",firstday.Value),
    new OleDbParameter("@usid",user.ID)
    };
 return (dal.UpdateRow(upd,false,parm));
}

/它不会导致异常，但返回受影响的0行。当在MS Access中执行相同的查询时，它可以正常工作。所以我想问题就在于参数的处理.但是什么？谢谢

塞尔吉奥:这可以显式设置OleDbTypes吗？

///whatever ...
        new OleDbParameter("@problems",problems.Value),
        new OleDbParameter("@fdayact",firstday.Value),
        new OleDbParameter("@usid",user.ID)
        };
//then telling each one what they will be ... 
        parm[0].OleDbType = OleDbType.VarWChar;
        parm[1].OleDbType = OleDbType.VarWChar;

///
     return (dal.UpdateRow(upd,false,parm));

Answer 1

检查user.ID的值是否被正确设置。

在您的upd命令字符串中，您用引号包围参数，如下所示：

[StartDate] = '@startdate'

删除所有参数中的引号。

Answer 2

对不起，emre，我忽略了OleDb提供商。如果SQL命令语法是针对SQL提供程序的，则它的语法是正确的。

因此，您的命令应该如下所示：

string upd = "UPDATE [RPform] SET [ProjectName] = ?, [ProjectCode] = ?, [Country] = ?, ";
upd += "[StartDate] = ?, [FinishDate] = ?, [TotalParticipants] = ?, [ArrivalDate] = ?, ";
upd += "[AirportTransfer] = ?, [AirportDate] = ?, [AirportHours] = ?, [AirportMinutes] = ?, ";
upd += "[Problems] = ?, [FirstDayActivities] = ? ";
upd += "WHERE [UserID]=?";

然后，您应该添加您的OleDb参数，就像您已经拥有的那样，但是您必须小心地按照它们在SQL命令中出现的顺序添加它们。

还有一件事，但这与您的问题无关:您不应该连接字符串，因为该操作在性能上不是很好。

相反，要很好地布局SQL命令，请尝试如下：

StringBuilder upd = new StringBuilder();
upd.Append("UPDATE [RPform] SET [ProjectName] = ?, [ProjectCode] = ?, [Country] = ?, ");
upd.Append("[StartDate] = ?, [FinishDate] = ?, [TotalParticipants] = ?, [ArrivalDate] = ?, ");
upd.Append("[AirportTransfer] = ?, [AirportDate] = ?, [AirportHours] = ?, [AirportMinutes] = ?, ");
upd.Append("[Problems] = ?, [FirstDayActivities] = ? ");
upd.Append("WHERE [UserID]=?");

然后，要获得命令字符串，只需：

upd.ToString();

希望这能帮到你。

Answer 3

请注意，如果使用多行字符串文字，它将更易读(而且更容易复制/粘贴)，而不是连接。此外，您也不应该将参数名称括在单引号中，即使对于字符串参数也是如此(只对文字使用单引号)。因此，您的原始SQL看起来可能如下所示：

string upd = @"
UPDATE [RPform] SET [ProjectName] = @pname, [ProjectCode] = @pcode, [Country] = @cnt, 
[StartDate] = @startdate, [FinishDate] = @finishdate, [TotalParticipants] = @totpart, [ArrivalDate] = @arrivedate, 
[AirportTransfer] = @airtran, [AirportDate] = @airdate, [AirportHours] = @airhour, [AirportMinutes] = @airmin, 
[Problems] = @problems, [FirstDayActivities] = @fdayact 
WHERE [UserID]=@usid
";

正如其他人所指出的，OleDb不使用命名参数，因此实际上应该使用以下方法，确保将参数按在SQL语句中显示的相同顺序添加到OleDbCommand中：

string upd = @"
UPDATE [RPform] SET [ProjectName] = ?, [ProjectCode] = ?, [Country] = ?, 
[StartDate] = ?, [FinishDate] = ?, [TotalParticipants] = ?, [ArrivalDate] = ?, 
[AirportTransfer] = ?, [AirportDate] = ?, [AirportHours] = ?, [AirportMinutes] = ?, 
[Problems] = ?, [FirstDayActivities] = ? 
WHERE [UserID]=?
";