-1
string sql = "Update stdrecord set firstname='" + fname + "',lastname='" + lname + "',mobile='" + mob + "',phone='" + phn + "',city='" + city + "',province'" + prov + "'where id='" + id + "'";

error :

System.Data.SqlClient.SqlException: Incorrect syntax

can anybody cor rectify the query ?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
user3268114
  • 1
  • 1
  • 4
    use paramterised queries. – Mitch Wheat Dec 03 '14 at 09:55
  • 4
    Always try your queries in your database manager first. And you should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. – Soner Gönül Dec 03 '14 at 09:56
  • can you eloborate paramterised queries? – user3268114 Dec 03 '14 at 09:56
  • @user3268114 Here is an answer I wrote yesterday demonstrating parameterised queries http://stackoverflow.com/a/27247134/1663001 – DavidG Dec 03 '14 at 09:59
  • @user3268114 check my asnwer this is what you need for param queries. – mybirthname Dec 03 '14 at 10:16

4 Answers4

8

Your missing an equal:

"',province = '" + prov + "' where id='" + id + "'";

And do not build SQL-Queries like this. Please use ADO.Net Parameter.

BendEg
  • 20,098
  • 17
  • 57
  • 131
1

Equal sign is missing:

,province='" + prov + "' where id='" + id + "'";
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Rakesh_HBK
  • 181
  • 3
  • 12
1
string sql = "Update stdrecord set firstname='" + fname + "',lastname='" + lname + "',mobile='" + mob + "',phone='" + phn + "',city='" + city + "',province='" + prov + "'where id='" + id + "'";

You miss = after province and there is no space between prov and where !

Also in this case you are open to SqlInjection, please use SqlCommand.Parameters.

The Query should look like this.

string sql = @"Update stdrecord set firstname=@FName ,lastname=@LastName, mobile=@Mobile, 
               phone=@Phone,city=@City, province=@Province where id=@ID";

This will protect you from SqlInjection and also sql server will cache your query.

To using command Parameters you need to add this code to your SqlCommand

SqlCommand cmd = new SqlCommand(sql, connectionString);

cmd.Parameters.AddWithValue("@FName", fName);
cmd.Parameters.AddWithValue("@LastName", lname );
cmd.Parameters.AddWithValue("@Mobile", mob);
cmd.Parameters.AddWithValue("@Phone", phn);
cmd.Parameters.AddWithValue("@City", city);
cmd.Parameters.AddWithValue("@Province", prov);
cmd.Parameters.AddWithValue("@ID", id);

With this structure you will not have problems like this in future because you will not add + and ' non stop. Also use @ when you build string this give you the possibility to write string on more than one line without using +.

mybirthname
  • 17,949
  • 3
  • 31
  • 55
  • http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – DavidG Dec 03 '14 at 10:37
  • Article which starts this should stop and a lot of might might might. Please stop reference this, I can write everything in my blog. Also if you want to say something like that give prove ! I used AddWithValue from years and 0 problems appear, also it is used with not latin characters and nvarchars and again 0 problems. I write you that because I see you post this many times on my answers ! – mybirthname Dec 03 '14 at 10:49
  • You are free to use it anywhere you wish, I won't downvote you for it, but I also won't upvote an answer with it in. I also used it for a long time and had no problems, but at some point, it might just come along and really bite you in the backside. I hope that doesn't happen for you. Personally I take the advice given by a Microsoft MVP seriously (after checking myself of course) – DavidG Dec 03 '14 at 10:52
  • @DavidG at some point someone can nuke my country, if I don't see prove I'm not going to live in a bunker. I told you give proves with examples that the written article is correct. – mybirthname Dec 03 '14 at 10:54
  • The article speaks for itself and your comparison of nuclear bombs is not relevant. It's about writing solid, reliable, testable code and `AddWithName` *can* have issues. Like I said, feel free to ignore the advice, that is your choice. – DavidG Dec 03 '14 at 10:56
  • Not mentioned in the article I linked is the issue of performance, some good discussion here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e6dd040f-6bf9-4ecb-aeb1-034608472234/sql-parameters-add-vs-addwithvalue?forum=sqldataaccess – DavidG Dec 03 '14 at 11:02
-2

Put a space before Where Clause and equal sign in province column, will get work perfectly

Manu Nair
  • 314
  • 2
  • 7