Clever Geek Handbook

What's bad about it?

private 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));
}

/// It does not throw an exception, but it returns 0 rows. When the same query is executed from MS Access it works great. Hence my guess is that the problem is with the parameter handling ... but what? Thanks you

Sergio: is it ok to set OleDbTypes explicitly?

///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));
0


source to share


7 replies


Check if the user.ID value is set correctly .

On the upd command line, you surround the options with quotes, for example:



[StartDate] = '@startdate'

Remove those quotes in all of your parameters.

+6


source


Sorry emre , I didn't pay attention to the OleDb provider. Your SQL command syntax is correct as long as it targets the SQL provider.

So your command should look like this:

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

Then, you must add your OleDb parameters just like you do, but you must be careful to add them in the same order as they appear in your SQL command.

One more thing, but this is not related to your problem: you shouldn't be concatenating strings because this operation is not very good from a performance standpoint.



Instead, to lay out your SQL command nicely, try this:

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]=?");

Then, to get your command line, you just need to:

upd.ToString();

Hope this can help you.

+3


source


Just to comment on this, not concatenate, it's more readable (and easier to copy / paste) if you are using a multi-line string literal. Also, you should not include parameter names in single quotes, even for string parameters (only use single quotes for literals). So your original SQL might look something like this:

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
";

As others have pointed out, OleDb doesn't use named parameters, so you should actually use the following, ensuring that you add the parameters to your OleDbCommand in the same order they appear in the SQL statement:

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


source


If you set the parameter type in every new OleDbParameter, you won't need to put single quotes in your sql, making the statement less prone to typing errors.

+2


source


Don't use StringBuilder in this case. Use verbatim (@) lines with well-formatted SQL code:

var updateStatement = @"
    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";

But I must point out that + = will not be optimized ! Simple + will run at compile time. for example

string s = "A" + "B" + C";

will lead to the fact that "ABC" does not reach perfect However

string s = "A" + variable + C";

will not be optimized.

+2


source


By the way, you probably want to use StringBuilder instead of string to create upd. Every time you use + =, you discard your old line, creating a new one.

Try

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

And to use it, just

upd.ToString();
+1


source


Aight! I decided to listen to it with Deltron 3030 .. it always works :)

This OleDbParameter object sets its oledbtype internally, according to the type I passed (as an object for the ctor). So I passed in an integer coming from a TextBox (client side checked, but still text, so it fails). and also bool one was radobutton.Value .. which sends True, but still text, so it takes as a string and encapsulates it in single quotes ... But I can't see what names these parameters should give as they are unnamed. ..

Sorry, I hate reading long articles, instead I am using one of the great virtues of a programmer who is lazy. So I am asking here :)

Thank you all for your time and effort .. I wish you all the best

0


source






More articles:


All Articles