UPDATE SQL statement error

[sea#]

I'm writing an app to keep results of survey in DB. Using MS Access.
This method is writing answers to DB. Each survey has one question.
Each time an answer arrive to method, I check to which survey it
belongs to. Then I update the relevant corresponding column in DB. The
error I get is:
"Syntax error in UPDATE statement"

Marked bellow the line I discover it by debugger.

public void WriteAnswers(string username, byte SrNo, byte ans)
{
try
{
string sqlwrite;
switch (SrNo)
{
case 1:
sqlwrite = "UPDATE ResultsTbl" +
"WHERE ([UserName]='" + username + "' AND survey1 = " + 0 + ")"+
"SET survey1= " + ans + " " ;

break;

case 2:
sqlwrite = "UPDATE ResultsTbl" +
"SET survey2= " + ans + " " +
"WHERE (UserName='" + username + "' AND survey2 = " + 0 + ")";
break;

case 3:
sqlwrite = "UPDATE ResultsTbl" +
"SET survey3= " + ans + " " +
"WHERE (UserName='" + username + "' AND survey3 = " + 0 + ")";
break;

default:
sqlwrite="No answer was chosen";
break;
}

OleDbCommand cmdWrite = new OleDbCommand();

cmdWrite.Connection = cnSurvey;
cmdWrite.CommandType = CommandType.Text;
cmdWrite.CommandText = sqlwrite;

cnSurvey.Open();

cmdWrite.ExecuteNonQuery(); //error happens here
cnSurvey.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Please help me find the cause of the error.

TIA,

sea#

 

[Arjen]

I think the best way is to write the value of "sqlwrite" somewhere (screen
or debug).

Posible you need to add some spaces.

sqlwrite = "UPDATE ResultsTbl<SPACE>" +
"WHERE ([UserName]='" + username + "' AND survey1 = " + 0 + ")<SPACE>"+
"SET survey1= " + ans + " " ;

Hope this helps,
Arjen

 

[oj]

simple mistake...

case 1:
sqlwrite = "UPDATE ResultsTbl" +
"SET survey1= " + ans + " " +
"WHERE ([UserName]='" + username + "' AND survey1 = " + 0 + ")";

 

[sea#]

Actualy the syntax I sent last time was after I have tried the one oj
sent me, which means the SET comes before WHERE. This didnt do in my
case.

Arjen, what does it mean:
write the value of "sqlwrite" somewhere (screen or debug).
please clarify your words.

sea#

 

[oj]

It didn't work because you didn't have a space between "ResultsTbl" and
"SET".

You could change your code to this:

if (SrNo>=1 & SrNo<=3)
{
sqlwrite = string.format("UPDATE ResultsTbl SET survey{0}={1} WHERE
UserName='{2}' AND survey{0}=0",SrNo,ans,username);
}
else
{
sqlwrite="No answer was chosen";
}

 

[Jon Skeet [C# MVP]]

I'm writing an app to keep results of survey in DB. Using MS Access.
This method is writing answers to DB. Each survey has one question.
Each time an answer arrive to method, I check to which survey it
belongs to. Then I update the relevant corresponding column in DB. The
error I get is:
"Syntax error in UPDATE statement"

<snip>

Aside from the other responses, I'd strongly advise you *not* to
include parameter values directly in your SQL. Instead, use
parameterised SQL statements - that way you don't need to worry about
escaping the values yourself.

See http://www.pobox.com/~skeet/csharp/faq/#db.parameters

 

[Arjen]

write the value of "sqlwrite" somewhere (screen or debug).

You add a value in the "sqlwrite" variable. Output the value to your screen
or debug window.
The you can see how your SQL statement will look like.

Arjen

 

[sea#]

oj, used your code succefuly.
if (SrNo>=1 & SrNo<=3)
{
sqlwrite = string.format("UPDATE ResultsTbl SET survey{0}={1} WHERE
UserName='{2}' AND survey{0}=0",SrNo,ans,username­);
}

Thanx!