Error in asp.net c# code (mysql database connection)

Question

My code is to update a record if it already exists in database else insert as a new record.

My code is as follows:

protected void Button3_Click(object sender, EventArgs e)
    {

        OdbcConnection MyConnection = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=testcase;User=root;Password=root;Option=3;");
        MyConnection.Open();
        String MyString = "select fil_no,orderdate from temp_save where fil_no=? and orderdate=?";
        OdbcCommand MyCmd = new OdbcCommand(MyString, MyConnection);
        MyCmd.Parameters.AddWithValue("", HiddenField4.Value);
        MyCmd.Parameters.AddWithValue("", TextBox3.Text);
        using (OdbcDataReader MyReader4 = MyCmd.ExecuteReader())
        {
            //**
            if (MyReader4.Read())
            {

                    String MyString1 = "UPDATE temp_save SET order=? where fil_no=? AND orderdate=?";
                    OdbcCommand MyCmd1 = new OdbcCommand(MyString1, MyConnection);
                    MyCmd1.Parameters.AddWithValue("", Editor1.Content.ToString());
                    MyCmd1.Parameters.AddWithValue("", HiddenField1.Value);
                    MyCmd1.Parameters.AddWithValue("", TextBox3.Text);
                    MyCmd1.ExecuteNonQuery();
                }



            else
            {

                // set the SQL string
                String strSQL = "INSERT INTO temp_save (fil_no,order,orderdate) " +
                "VALUES (?,?,?)";

                // Create the Command and set its properties
                OdbcCommand objCmd = new OdbcCommand(strSQL, MyConnection);
                objCmd.Parameters.AddWithValue("", HiddenField4.Value);
                objCmd.Parameters.AddWithValue("", Editor1.Content.ToString());
                objCmd.Parameters.AddWithValue("", TextBox3.Text);

                // execute the command
                objCmd.ExecuteNonQuery();



            }


        }
    }

I am getting the error as:

ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.51-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order,orderdate) VALUES ('04050040272009','&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&' at line 1

The datatype for fields in table temp_save are:

fil_no-->INT(15)( to store a 15 digit number)
order-->LONGTEXT(to store contents from HTMLEditor(ajax control))
orderdate-->DATE(to store date)

Please help me to resolve my error.

Do you aware mysql support insert ... on duplicate key update? — ajreal
– ajreal, Commented Dec 21, 2010 at 7:36

Mr. C · Accepted Answer · 2015-11-04 14:33:12Z

1

order is a reserved word. For a complete list of Reserved Words, please review this document.

You can wrap it in back-ticks i.e. (on my keyboard a back tick is under the ~ key)

INSERT INTO temp_save (fil_no,`order`,orderdate)....

edited Nov 4, 2015 at 14:33

Mr. C

1,7202 gold badges17 silver badges23 bronze badges

answered Dec 21, 2010 at 7:34

Tim Jarvis

18.9k11 gold badges62 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pilgerstorfer Franz · Accepted Answer · 2010-12-21 07:52:06Z

0

i would try brackets in case ... that's the way it works in ms sql server .. probablly the same in mySql

String MyString1 = "UPDATE temp_save SET [order]=? where fill .... ";

answered Dec 21, 2010 at 7:52

Pilgerstorfer Franz

8,3473 gold badges46 silver badges56 bronze badges

Collectives™ on Stack Overflow

Error in asp.net c# code (mysql database connection)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related