Incorrect syntax near Items

Hi,

I am getting a mysterious error message, and it doesnt say which line it referres to, just gives me a stack trace.

Could somone decipher it for me?:

System.Data.SqlClient.SqlException: Incorrect syntax near 'items'.

[SqlException (0x80131904): Incorrect syntax near 'items'.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +190
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +405
 System.Web.UI.WebControls.SqlDataSource.Insert() +13
 detailproview.Button2_Command(Object sender, CommandEventArgs e) +41
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +75
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886

My page code is:

private bool ExecuteUpdate(int quantity)
{
 SqlConnection con = new SqlConnection();
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
 con.Open();
 SqlCommand command = new SqlCommand();
 command.Connection = con;
 TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
 Label labname = (Label)FormView1.FindControl("Label3");
 Label labid = (Label)FormView1.FindControl("Label13");
 command.CommandText = "UPDATE Items SET Quantityavailable = @.qty WHERE productID=@.productID";
 command.Parameters.Add("@.qty", TextBox1.Text);
 command.Parameters.Add("@.productID", labid.Text);
 command.ExecuteNonQuery();
 con.Close();
 return true;
}
 private bool ExecuteInsert(String quantity)
 {
 SqlConnection con = new SqlConnection();
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";
 con.Open();
 SqlCommand command = new SqlCommand();
 command.Connection = con;
 TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
 Label labname = (Label)FormView1.FindControl("Label3");
 Label labid = (Label)FormView1.FindControl("Label13");
 command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@.User)"+
 "INSERT INTO Transactions (Itemid)VALUES (@.productID)"+
 "INSERT INTO Transactions (itemname)VALUES (@.Itemsname)"+
 "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+
 "INSERT INTO Transactions (Qty)VALUES (@.qty)"+
 command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
 command.Parameters.Add("@.Itemsname", labname.Text);
 command.Parameters.Add("@.productID", labid.Text);
 command.Parameters.Add("@.qty", TextBox1.Text);
 command.ExecuteNonQuery();
 con.Close();
 return true;
 }
protected void Button2_Click(object sender, EventArgs e)
{
 TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
 ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}
protected void Button2_Command(object sender, CommandEventArgs e)
 {
 if (e.CommandName == "Update")
 {
 SqlDataSource1.Insert();
 }
 }
}.

Thanks!

Jon 

The error is referring to the SQL syntax you are using in your queries. There is a line in your stack trace that reads "System.Web.UI.WebControls.SqlDataSource.Insert() +13" which tends to suggest that the problem is in your INSERT statement, although looking at your INSERT statement I cannot see where "items" is mentioned. One bit that does confuse me is your Button2_Comand event-hander: why do you checke.CommandName == "Update" and then callSqlDataSource1.Insert() ?

Hope this helps

|||

Hi,

I cant see where items is mentioned either..

I checked update because the buttons command name is update, but I also want it to insert.. (it both updates and inserts).

Where else could the items error be happening??

Thanks,

Jon

|||

I have just noticed that you haveExecuteUpdateandExecuteInsertmethods defined in your code, but you are calling the Insert method of theSqlDataSource1control: what SQL statements have you got defined against SqlDataSource1?

|||

Well the data is on a formview which is attached so Sqdatasource1..

Perhaps I should try to called insert method of execute insert - how would I write this?:

if (e.CommandName == "Update")
{
ExecuteInsert.Insert();

?

Thanks!

Jon

|||You should just be able to write this:

if (e.CommandName =="Update") { ExecuteInsert();}

|||

Hi,

I tried that but got the error:

No overload for method 'ExecuteInsert' takes '0' arguments

Line 86: ExecuteInsert();

??

Thanks,

Jon 

|||

Ah, sorry, I didn't notice that yourExecuteInsertrequired a quantity parameter: just pass the method the quantity you require to be inserted.