Incorrect syntax near [SQL UPDATE COMMAND] > cmd.ExecuteNonQuery()

Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot.

-------------------------------------

public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

using (cn)
{
cn.Open();
return cmd.ExecuteNonQuery() > 1;
}
}

capture the return value to a variable and return that. You are trying to use a shortcut that doesn't exist.

|||

I added a variable that will hold the INTEGER value of the rows updated, but it still generates the same error.
-----------------------------------------
public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

int rowsAffected;

using (cn)
{
cn.Open();
rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected > 1;
}
}

|||

try this

If (rowsAffected >1){

return true;}

else {

return false;}

|||

No luck... It still generates the same error even with the IF condition in place. Here is a copy of the error trace from the browser:

--------------------------------

Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Source Error:

Line 215: {
Line 216: cn.Open();
Line 217: rowsAffected = cmd.ExecuteNonQuery();
Line 218:
Line 219: if (rowsAffected > 1)

|||

post your stored procedure. This is where your problem is.

|||

I don't see the point of using rowsaffected >1

Why don't you just use return rowsAffected?

Isn't that going to give you the count anyway?

|||

Thanks for the quick reply.:

------------------------------

Create procedure UpdateInfoByAccountAndFullName(
@.newaccount varchar(50),
@.newfullname varchar(100),
@.rep varchar(10),
@.zip varchar(10),
@.comments varchar(2000),
@.oldaccount varchar(50),
@.oldfullname varchar(100)
)
as

update Info
Set ACCOUNT = @.newaccount,
FULL_NAME = @.newfullname,
REP = @.rep,
ZIP = @.zip,
COMMENTS = @.comments
where
ACCOUNT = @.oldaccount and
FULL_NAME = @.oldfullname

|||

Duduvi,

Why don't you just use return rowsAffected? Isn't that going to give you the count anyway? > I could use rowsAffected, but I don't really see the need of taking into account how many records where updated. This is why I used the boolean rowsAffected > 1. I just need to know that more than or 1 record(s) were updated.

|||

I don't see a problem. Try removing the return value. Just try executing the query and see if it works then you can eliminate the stored procedure as the problem.

|||

You are right. I dont see the problem neither. This is what freaks me out too. I already tried running the StoredProc and the SQL String from within SQL2005, and it did update the record. The only time it throws an exception is on my .NET code when I do a ExecuteNonQuery() with or without a return value.

|||

HA! I can't believe i missed it. you forgot to specify that your command is a stored procedure using the commandtype enum.

|||

Yep you're right. I forgot to specify the command type. Thanks again.