Hi,
I have 3 short(ish) questions, If someone could help I'd be very grateful..
This is the situation - I have a formview which contains a button, which on clicking should insert a row into one table, and update a column value of another. I will provide the code at the end.
1. On clicking, I get the error:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.
it doesnt say where, but the stack trace is:
[SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'.]
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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1089
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +643
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78
System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1151
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +429
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +88
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +86
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
2. When updating, at the moment it will update with a new quantity provided in the text box, but id like the quantity in the textbox subtracted from the current column value - how would this be done?
3. For inserting, i was wondering if i need a command in the button_click to activate the insert command, similar to the one i have one for the update. I have tried but i cant seem to get the syntax right...
Code:
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) );
}
Huge thanks for anyone who can help shed some light!!
Thanks again,
Jon
1. Debug your application and see the query which is being executed by the SqlDataSource's update event. There might be a space missing in the query. The error is self explanatory - there is syntax error. Try to run the query in the query analyzer.
2.
' After successful updationdim qty asInteger =Integer(QuantityTB.TextBox)dim available asInteger =Integer(AvailableTB.TextBox)' Update the available text boxAvailableTB.TextBox = available - qty
3.
if e.CommandName ="insert" then SqlDataSource1.Insert()end if
|||
Hi, thanks for your response!
1. I had tried debugging but it didn't detect any errors or warnings.. is this what you mean or am i missing the point?
2. I meant to update the column in the database table - it currently updates with just the number that is written in the textbox, but id like it to take away the number written in the textbox from the number that is in their already - do you mean use this code to create a textbox, then update the table with this new value?
3. Is that VB? Do you have it in C# if it is? Or if it isn't, where shall I put it? it doesn't like the syntax in button2_click...
Thanks alot for your help!!!
Cheers,
Jon
|||
1. When I meant debug, put a breakpoint after the query has been formed and now check the value of the query. Copy this to the query analyzer and see if it runs fine. If it does then your query is fine, problem lies somewhere else. If it does not, watch out the error thrown by query analyzer. In your original post, you had written that you were getting error message. Did that error disappear?
2. When you insert the ordered quantity, I thought you would deduct the quantity from the stock. I would suggest, whenever you want to insert a ordered product, then it should be like one transaction.
a. Check if the ordered quantity is not bigger than the available stock.
b. Only add the record, if the above condition is true. If the transaction went through OK, rebind the grid, so that it will show new values.
c. Rebinding of the gridview is good practice, because there can be more than a single user attempting to fill out the order.
No, you do not need to create a new textbox. I was just showing how to update the UI.
3. It is in VB, but simple to convert to C#. It ideally would be in the command event handler of the Insert button.
void InsertCommandBtn_Command(Object sender, CommandEventArgs e) {if (e.CommandName =="insert") { SqlDataSource1.Insert(); }}|||
Hi cheers for help.
Your code seems to work fine. Im getting a different error message now, and need help updating the table - Ill put it to the forum.
Thanks again!
Jon
