Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – Incorrect syntax near s Sql .c#

private void btnSubmit_Click(object sender, EventArgs e)
{
try
{

    if (EmpIdTB.Text == "" || Empname.Text == "" || EmpPhone.Text == "")
    {
        MessageBox.Show("Missing Information");
    }
    else
    {
        conn.Open();
        string query = "UPDATE EMPTB SET EmpId='" + EmpIdTB.Text + "', EmpName='" + Empname.Text + "', EmpAdd='" + Addresstb.Text + "', EmpDep='" + DepartmentCB.Text.ToString() + "', EmpDob='" + dateTimePicker1.Value.Date + "', EmpPhone='" + EmpPhone.Text + "', EmpGen='" + GenderCB.SelectedItem + "', EmpEdu='" + educationTb.SelectedItem + "'";
        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        MessageBox.Show("Employee Updated Sucessfully !");
        DisplayAccounts();

    }

    }catch(Exception ex) 
{
    MessageBox.Show(ex.Message);
}
finally
{
    conn.Close();
}

}
if i run this code in my visual studio and try to update an current value it just pop up a message INCORRENT SYNTAX NEAR S UNCLOSED QUATATION MARK

I tried every thing in my knowledge but it dosent help

Tags:

3

Answers


  1. 0

    The problem: "UPDATE EMPTB SET EmpId=’" + EmpIdTB.Text + "’, EmpName=’"

    " and ‘ is not allowed in a string
    To use single or double quote, pls refer here: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/strings/#string-escape-sequences

    Refer to this question: Incorrect syntax near 's'. Unclosed quotation mark after the character string

    Login or Signup to reply.
  2. 0

    Say that EmpIdTB.Text contains a single quote like hello'hello. Then this C# code:

    string query = "UPDATE EMPTB SET EmpId='" + EmpIdTB.Text + "', EmpName='"

    Would produce this SQL:

    UPDATE EMPTB SET EmpId='hello'hello', EmpName='...

    This causes SQL Server to raise an exception about unclosed quotation marks. You can avoid this by using parameters instead of adding strings.

    One way to debug your SQL is by having it pop up as a message:

    string query = ...;
MessageBox.Show(query);

    And as Littlefoot commented, most update statements do better with a where clause.

    Login or Signup to reply.
  3. 0

    Consider first writing your UPDATE statement in SSMS first. For each value in the UPDATE use the DECLARE variable and set a value followed by running the statement. Once satisfied take the statement and add it to your code (see below).

    The code which follows does not attempt to match your SQL, its an example to follow and that you can if desire take code from the Operations class below and place the code in a form yet as you get better at coding it will become clear there are two choices to consider.

    The code which follows shows the basics of using parameters were as others commented without using parameters can lead to uses with strings with unmatched apostrophes.

    Also shown, using a class for separating data operations from the user interface (e.g. a Windows Form).

    Next, a class, Customers is used to pass data to the update method, advantages, keeps parameters to a minimum and can be used later for performing validation which you can look into.

    In the class Operations, Update method, the return type indicates success/failure and if failure the actual exception raised.

    public class Customer
{
    public int Identifier { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public int? ContactTypeIdentifier { get; set; }
    public int? GenderIdentifier { get; set; }
    public override string ToString() => CompanyName;
}
// Goes in its own file
public class Operations
{
    public static (bool success, Exception exception) UpdateCustomer(Customer customer)
    {
        /*
         * Create the following in SQL-Server Management Studio and test it
         */
        var statement = 
            @"UPDATE Customer SET CompanyName = @CompanyName, 
              ContactName = @ContactName,
              ContactTypeIdentifier = @ContactTypeIdentifier,
              GenderIdentifier = @GenderIdentifier 
            WHERE Identifier = @Identifier";

        try
        {
            using (var cn = new SqlConnection("Your connection string"))
            {
                using (var cmd = new SqlCommand { Connection = cn, CommandText = statement })
                {
                    cmd.Parameters.Add("@CompanyName", SqlDbType.NChar).Value = customer.CompanyName;
                    cmd.Parameters.Add("@ContactName", SqlDbType.NChar).Value = customer.ContactName;
                    cmd.Parameters.Add("@ContactTypeIdentifier", SqlDbType.Int).Value = customer.ContactTypeIdentifier;
                    cmd.Parameters.Add("@GenderIdentifier", SqlDbType.Int).Value = customer.GenderIdentifier;
                    cmd.Parameters.Add("@Identifier", SqlDbType.Int).Value = customer.Identifier;

                    cn.Open();

                    return (cmd.ExecuteNonQuery() == 1, null);
                }
            }
        }
        catch (Exception localException)
        {
            // consider writing this to a log file
            return (false, localException);
        }
    }
}

    In your form, populate an instance of Customers (in your case perhaps Employee class), invoke the Update method and parse return values which is known as deconstructing.

    var (success, exception) = Operations.UpdateCustomer(customer);
if (success)
{
    // customer updated
}
else
{
    // indicate updated failed
    // use exception variable in a message box
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search