There was a problem: Incorrect syntax near the keywoard 'as'

Question

Error:

Incorrect syntax near the keyboard 'as'.

Code:

private void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            string str_connection = "Data Source = MSSQLServer058; Initial Catalog = CarRental; Integrated Security = True";
            string MyUpd = "Update [dbo].[Booking] as t1, Car as t2, Customer as t3 " +
                           "set t1.[CustomerID] = '" + lblCustomerID.Text + "'," +
                           "t1.[VIN] = '" + lblVIN.Text + "'," +
                                "t3.[DriverLicNo] = '" + DriverLicNotxt.Text + "'" +
                           "t2.[Make] = '" + txtMake.Text + "'" +

            MessageBox.Show("Save Complete!", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
}

Add some explanation on what you are doing, what is going wrong and what is the expected result. — Cray, Jun 21 '19 at 07:48
You ***need*** to parametrise your queries! What you have there is a huge security hole and you need to fill it in ASAP. — Thom A, Jun 21 '19 at 07:52

score 2 · Answer 1 · answered Jun 21 '19 at 07:50

Looks like you are trying to update multiple tables in one go - this is not possible.

Change your SQL to do multiple updates, if you need to, encapsulate in a transaction:

BEGIN TRAN
UPDATE [dbo].[Booking] set ...
UPDATE Car ...
UPDATE Customer ...
COMMIT TRAN

Thom A · Answer 2 · 2019-06-21T11:56:59.553

I always make this caveat when providing a C# answer: I am not a C# developer. I really have very little experience with it. What I did is what I always do when showing someone how to parametrise a query, I check the documentation:

private void btnUpdate_Click(object sender, EventArgs e)
{
    string str_connection = "Data Source = MSSQLServer058; Initial Catalog = CarRental; Integrated Security = True";

    using (SqlConnection conn = new SqlConnection(str_connection))
    {

        //Do your queries really have no WHERE?
        string MyUpd = "UPDATE dbo.Booking SET CustomerID = @CustomerID, VIN = @VIN; " +
                        "UPDATE dbo.Car SET Make = @Make; " + 
                        "UPDATE dbo.Customer SET DriverLicNo = @LicNo;";

        using (SqlCommand comm = new SqlCommand(MyUpd,conn))
        {

            comm.Parameters.Add("@CustomerID",SqlDbType.Int).Value = lblCustomerID.Text; //Guessed data type
            comm.Parameters.Add("@VIN",SqlDbType.VarChar,50).Value = lblVIN.Text; //Guessed data type
            comm.Parameters.Add("@Make",SqlDbType.VarChar,50).Value = txtMake.Text; //Guessed data type
            comm.Parameters.Add("@DriverLicNo",SqlDbType.VarChar,50).Value = DriverLicNotxt.Text; //Guessed data type

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
                MessageBox.Show("Save Complete!", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch
            {
                /* Your Error handling */
            }
        }
    }
}

For those who definitely have more experience with C#, if anything is wrong please do leave a comment and I'll be happy to fix (or submit an edit). Hopefully, at least, this gets the OP on the right track though.

you should use using for SqlCommand as well as SqlConnection so it gets disposed. Also, you're missing a line. `comm.CommandText = MyUpd;` — Andy Nichols, Jun 21 '19 at 08:12
Yes, also I edited my comment that the command text isn't being set. I've also just noticed `new SqlCommand(comm,conn)` must be wrong as comm is the variable you're setting up. — Andy Nichols, Jun 21 '19 at 08:17
Yeah, just changed it to `SqlCommand comm = new SqlCommand(MyUpd,conn)`. Is that right, @AndyNichols? — Thom A, Jun 21 '19 at 08:18

There was a problem: Incorrect syntax near the keywoard 'as'

2 Answers2