Syntax error (missing operator) in query expression

[SkyCross]

I got an error, "Syntax error (missing operator) in query expression ''Blanco, Familyname='Grace'."
in my code, near ExcecuteQuery();
I am performing Update from MSAccess, I can retreive data but when I used to click my update button, I got error.
anyone can help me?

Heres my code

`private void button1_Click(object sender, EventArgs e)

        Records f1 = new Records();



        string SQLUpdateString;

        if (BlockNo == "")
        {
            SQLUpdateString = "UPDATE Personaldata SET BlockNo ='" + BlockNo.Replace("'", "''") + "', LotNo='" + LotNo + "', Numberofoccupants=" + Numberofoccupants + ", Firstname='" + Firstname + ", Familyname='" + Familyname + ", Dateofbirth='" + Dateofbirth + ", Age='" + Age + ", Placeofbirth='" + Placeofbirth + ", Religion='" + Religion + "' WHERE BlockNo=" + BlockNo+ "";
        }
        else
        {
            SQLUpdateString = "UPDATE Personaldata SET BlockNo ='" + BlockNo.Replace("'", "''") + "', LotNo='" + LotNo + "', Numberofoccupants=" + Numberofoccupants + ", Firstname='" + Firstname + ", Familyname='" + Familyname + ", Dateofbirth='" + Dateofbirth + ", Age='" + Age + ", Placeofbirth='" + Placeofbirth + ", Religion='" + Religion + "' WHERE BlockNo=" + BlockNo + "";
        }




        OleDbCommand SQLCommand = new OleDbCommand();
        SQLCommand.CommandText = SQLUpdateString;
        SQLCommand.Connection = f1.database;
        int response = SQLCommand.ExecuteNonQuery();//error here


            MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            Close();
        }`

what should I do? pls help me

[Ancient Dragon]

you might want to display those two strings or write them to a temp file so that you can see exactly what is being sent to the database.

[Jan-Willem]

Starting with Firstname you code misses the closing-squotes:
", Firstname='" + Firstname + ", Familyname='"
should read
", Firstname='" + Firstname + "', Familyname='"

[SkyCross]

thank you. sir Jan-Willem, it worked now, however, I got a new error and it states
Syntax error (comma) in query expression 'BlockNo=System.Windows.Forms.TextBox, Text: 38'.

Heres my code again

Records f1 = new Records();
              BlockNo=textBox1.ToString() ;
              LotNo = textBox2.ToString();
            Numberofoccupants=textBox3.ToString();
            Firstname = textBox4.ToString();
            Familyname=    textBox5.ToString();
            Dateofbirth = textBox6.ToString();
            Age = textBox7.ToString();
            Bloodtype = textBox8.ToString();
            Placeofbirth = textBox9.ToString();
            Religion = textBox10.ToString();


            string SQLUpdateString;

            if (BlockNo == "")
            {
                SQLUpdateString = "UPDATE Personaldata SET BlockNo ='" + BlockNo.Replace("'", "''") + "', LotNo='" + LotNo + "', Numberofoccupants='" + Numberofoccupants + "', Firstname='" + Firstname + "', Familyname='" + Familyname + "', Dateofbirth='" + Dateofbirth + "', Age='" + Age + "', Placeofbirth='" + Placeofbirth + "', Religion='" + Religion + "' WHERE BlockNo=" + BlockNo+ "";
            }
            else
            {
                SQLUpdateString = "UPDATE Personaldata SET BlockNo ='" + BlockNo.Replace("'", "''") + "', LotNo='" + LotNo + "', Numberofoccupants='" + Numberofoccupants + "', Firstname='" + Firstname + "', Familyname='" + Familyname + "', Dateofbirth='" + Dateofbirth + "', Age='" + Age + "', Placeofbirth='" + Placeofbirth + "', Religion='" + Religion + "' WHERE BlockNo=" + BlockNo + "";
            }




            OleDbCommand SQLCommand = new OleDbCommand();
            SQLCommand.CommandText = SQLUpdateString;
            SQLCommand.Connection = f1.database;
            SQLCommand.ExecuteNonQuery();//error here

Pls help me. thank you

[cgeier]

Check to see if this is causing the error: SET BlockNo ='" + BlockNo.Replace("'", "''") + "'. What data does "BlockNo" contain? And how are you wanting it to be stored in the database?

[SkyCross]

cgeier, can you give me a sample?

[cgeier]

What are you trying to accomplish? Why are you replacing a single quote with two single quotes?

Check your where clause. What is the data type of BlockNo? You have

`+ "' WHERE BlockNo=" + BlockNo + "";

If BlockNo is a string you need to surround it with single quotes.

+ "' WHERE BlockNo= '" + BlockNo + "'";

Also, you should use parameterized queries to avoid SQL injection.

[cgeier]

Try something like the following:

    try
    {
        string SQLUpdateString = string.Empty;

        SQLUpdateString = "update Personaldata set BlockNo = @blockNo, ";
        SQLUpdateString += "LotNo = @lotNo, ";
        SQLUpdateString += "Numberofoccupants = @numOccupants, ";
        SQLUpdateString += "FamilyName = @familyName, ";
        SQLUpdateString += "Dateofbirth = @dateofbirth, Age = @age, ";
        SQLUpdateString += "Placeofbirth = @placeofbirth, ";
        SQLUpdateString += "Religion = @religion ";
        SQLUpdateString += "where BlockNo = @blockNo";

        OleDbCommand SQLCommand = new OleDbCommand();

        SQLCommand.CommandText = SQLUpdateString;

        SQLCommand.Parameters.Add("@blockNo", BlockNo);
        SQLCommand.Parameters.Add("@lotNo", LotNo);

        //         ...

    }//try
    catch (OleDbException ex)
    {
        MessageBox.Show("Error: " + ex.Message);
    }//catch

It will make your code easier to follow and help prevent SQL injection.