Can Someone explain meaning of this exception? Connection String, Table Name, query syntax are correct

Question

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO tbl_AStatus (Asset Status,Remarks) VALUES (@a, @b)", Login.sqlConn);
cmd.Parameters.AddWithValue("@a", txtAssetStatus.Text);
cmd.Parameters.AddWithValue("@b", txtRemarks.Text);
int a = cmd.ExecuteNonQuery();
MessageBox.Show(a.ToString());

Exception shown:

There was an error parsing the query. [ Token line number = 1,Token line offset = 32,Token in error = Status ]

bad idea to have space in column name `[Asset Status]` if you really have it. — bansi, Nov 27 '14 at 07:28
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Nov 27 '14 at 07:42

score 4 · Accepted Answer · edited May 23 '17 at 12:12

4

If your table name or column names includes white space, you need to use them with square brackets like [Asset Status]. But this is not recommended. Would be better changing your column name to something else if you can.

Read: Database, Table and Column Naming Conventions?

Also use using statement to dispose your database connections and objects.

using(SqlCeCommand cmd = Login.sqlConn.CreateCommand())
{
   cmd.CommandText = "INSERT INTO tbl_AStatus ([Asset Status],Remarks) VALUES (@a, @b)";
   cmd.Parameters.Add("@a", SqlDbType.NVarChar).Value = txtAssetStatus.Text;
   cmd.Parameters.Add("@b", SqlDbType.NVarChar).Value = txtRemarks.Text;
   // I assume your column types are NVarChar
   Login.sqlConn.Open();
   int a = cmd.ExecuteNonQuery();
   MessageBox.Show(a.ToString());
}

And don't use AddWithValue method. It may generate unexpected results sometimes. Use SqlParameterCollection.Add method and it's overloads.

Read: Can we stop using AddWithValue() already?

edited May 23 '17 at 12:12

Community

1
1

answered Nov 27 '14 at 07:26

Soner Gönül

97,193
102
206
364

Thank you Soner for help. Now it is showing result of ExecuteNonQuery() as 1 but no change in database – Shreyas Nov 27 '14 at 07:29
1

@Shreyas Are you sure? If `ExecuteNonQuery` returns `1`, it **has** to insert to your database. Did you try this query in your database manager? It successfully inserts in there? – Soner Gönül Nov 27 '14 at 07:35
@Shreyas _Well_, do you use local database file for this? Try to connect it on Visual Studio's Sql Server Object explorer menu and try this query on your local file. – Soner Gönül Nov 27 '14 at 07:48
1

thank you so much. It is working fine now, it was because of slow machine. Thank you so much. It was much needed. Suggestions Welocme!! – Shreyas Nov 27 '14 at 07:53

Can Someone explain meaning of this exception? Connection String, Table Name, query syntax are correct

1 Answers1