Amateur SQL formation error

Hi I have the error:

Only assignment, call, increment, decrement, and new object expressions can be used as a statement

Line 29: command.CommandText = "UPDATE Items SET Quantityavailable = '+TextBox1.Text+' + Quantityavailable.ToString()"; "INS...

Refering to my poor coding:

private bool ExecuteUpdate(int quantity)
{
 SqlConnection con = new SqlConnection();
 con.ConnectionString = "CustomString";
 con.Open();
 SqlCommand command = new SqlCommand();
 command.Connection = con;
 command.CommandText = "UPDATE Items SET Quantityavailable = '+TextBox1.Text+' + Quantityavailable.ToString()"; "INSERT Transactions SET Usersname = '<%# System.Web.HttpContext.Current.User.Identity.Name %>'"; "INSERT Transactions SET Itemid = '@.ProID'"; "INSERT Transactions SET itemname = '@.ItemName'"; "INSERT Transactions SET Date = '<%# DateTime.Now %>'";
 command.ExecuteNonQuery();
 con.Close();
}
protected void Button2_Click(object sender, EventArgs e)
{
 TextBox tb = FormView.FindControl("TextBox1") as TextBox;
 ExecuteUpdate( Int32.Parse(tb.Text) );
}

Can someone tell me what I've done wrong?

Thanks,

Jon 

What is Quantityavailable.ToString? I don't see that in the code.

Should iot just be

UPDATE Items SET Quantityavailable = '+TextBox1.Text;

|||

Your update statement doesn't specify which item in the items table you want to update.

You have multiple inserts into the transactions table, and you are using the UPDATE syntax for each.

You have quotation problems.

You can't use the databinding syntax in code <% ... %>.

You have parameters defined (@.ProID, @.ItemName) in the SQL, but never declared or set a value for them (And you have them inside quotes).

Try something like (Sorry, not all that good at C#, but it would look something like this):

command.CommandText = "UPDATE Items SET QuantityAvailable = @.qty WHERE ItemID=@.ItemID; INSERT INTO Transactions(Usersname,ItemID,ItemName,[Date]) VALUES (@.User,@.ItemID,@.ItemName,getdate())";

command.Parameters.Add("@.qty",sqldbtypes.Integer).Value = quantity;

command.Parameters.Add("@.User",sqldbtypes.Nvarchar).Value = System.Web.HttpContext.Current.User.Identity.Name;

command.Parameters.Add("@.ItemID",sqldbtypes.Integer).Value = ?;

command.Parameters.Add("@.ItemName",sqldbtypes.NVarchar).Value= ?;

|||

Hi thanks for your response..

The thing is, I am trying to make one table update (Items - Quantityavailable) with the value typed into the textbox, at the same time as another has data inserted (Transactions).

The data to be inserted is databound to the formview of the page - thats why I didnt define the parameters (@....@....) I thought they would just get lifted from the formview as they are defined within the formview. Is there a way to do this?

I have modified my code to this:

SqlConnection con = new SqlConnection();
con.ConnectionString = "CustomString";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = "UPDATE Items SET Quantityavailable = +TextBox1.Text' ";
"INSERT INTO Transactions(Usersname,ItemID,ItemName,[Date]) VALUES (@.User,@.ItemID,@.ItemName,getdate())";
command.Parameters.Add("@.User",sqldbtypes.Nvarchar).Value = System.Web.HttpContext.Current.User.Identity.Name;
command.Parameters.Add("@.ItemID",sqldbtypes.Integer).Value = ?;
command.Parameters.Add("@.ItemName",sqldbtypes.NVarchar).Value= ?;

command.ExecuteNonQuery();

con.Close();

Thanks,

Jon

|||

Should be

"UPDATE Items SET Quantityavailable = " +TextBox1.Text + ";"

|||

mcp111:

Should be

"UPDATE Items SET Quantityavailable = " +TextBox1.Text + ";"

Rather, I would recommend using parameterized queries to prevent SQL Injection attacks.

"UPDATE Items SET Quantityavailable = @.Qty"