locked
problem in updating records using access RRS feed

  • Question

  • User1252303175 posted

    in this page administrator can update the records of users.he will 1st enter user's id to search the record of a particular user and then all the stored data about user like FullName, MobileNumber, CreationDate, CNIC appears in the respective textboxes.we have made two different attempts but none succeeded.
    In attempt1, i thought of updating "registration" table with respect to the user's id.
    In attempt2, I then thought of first deleting the record and then writing the new record for the same id.
    The error in both attempts is mentioned below.
    ERROR: "Syntax error (missing operator) in query expression "

    Coding behind the "update" button is as follows.

    attempt1:
    // string query4 = "UPDATE registration SET FullName,MobileNumber,CreationDate,CNIC='" + FullName.Text + "','" + MobileNumber.Text + "','" + CreationDate.Text + "','" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";

    attempt2:
    //string query4 = "delete FullName,MobileNumber,CreationDate,CNIC from registration where Id ='" + id_textbox.Text + "'";
            string query4 = "delete from registration where Id = '" + id_textbox.Text + "' ";
            conn.delete(query4);

            string query5 = "INSERT into registration (FullName,MobileNumber,CreationDate,CNIC,Id) values (''" + FullName.Text + "','" + MobileNumber.Text + "','" + CreationDate.Text + "','" + CNIC.Text + "','" + id_textbox.Text + "')";
            conn.insert(query5);

            Response.Write("<script>alert('Record Updated')</script>");

    Monday, June 14, 2010 5:44 AM

Answers

  • User-925286913 posted

    string query4 = "UPDATE registration SET FullName = '" + FullName.Text + "',MobileNumber = '" + MobileNumber.Text + "',CreationDate = '" + CreationDate.Text + "',CNIC='" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";

    I think we need to enclose date fields in # for ms access.

    So query is:

    string query4 = "UPDATE registration SET FullName = '" + FullName.Text + "',MobileNumber = '" + MobileNumber.Text + "',CreationDate = #" + CreationDate.Text + "#,CNIC='" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";

    Also, never use dynamic queries. Use parameterized queries only.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 14, 2010 7:53 AM
  • User1710623990 posted

    Yes chintanpshah you are absolute right.

    But the problem is we dont know what that field data type ariez88 is using (Haven't mentioned anything about the table structure)... Is it realy a true sense date field or a text field storing date as string in access .

    But yes for date fields we need to use '#'

    Laughing



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 14, 2010 8:25 AM

All replies

  • User1710623990 posted

    string query4 = "UPDATE registration SET FullName = '" + FullName.Text + "',MobileNumber = '" + MobileNumber.Text + "',CreationDate = '" + CreationDate.Text + "',CNIC='" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";

    Hope that helps Cool


    Monday, June 14, 2010 7:29 AM
  • User-1360095595 posted

    Delete: http://www.w3schools.com/sql/sql_delete.asp

    Update: http://www.w3schools.com/sql/sql_update.asp

    Also, consider using parameterized queries. 

    Monday, June 14, 2010 7:37 AM
  • User-925286913 posted

    string query4 = "UPDATE registration SET FullName = '" + FullName.Text + "',MobileNumber = '" + MobileNumber.Text + "',CreationDate = '" + CreationDate.Text + "',CNIC='" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";

    I think we need to enclose date fields in # for ms access.

    So query is:

    string query4 = "UPDATE registration SET FullName = '" + FullName.Text + "',MobileNumber = '" + MobileNumber.Text + "',CreationDate = #" + CreationDate.Text + "#,CNIC='" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";

    Also, never use dynamic queries. Use parameterized queries only.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 14, 2010 7:53 AM
  • User165950705 posted

    Your syntax is wrong check this:

    string query4 = "UPDATE registration SET FullName = '" + FullName.Text + "',MobileNumber = '" + MobileNumber.Text + "',CreationDate = '" + CreationDate.Text + "',CNIC='" + CNIC.Text + "' where Id='" + id_textbox.Text + "'";


    Use parameterized queries to avoid the SQL Injection :

    Check Below link :

    http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET



    Monday, June 14, 2010 7:54 AM
  • User1710623990 posted

    Yes chintanpshah you are absolute right.

    But the problem is we dont know what that field data type ariez88 is using (Haven't mentioned anything about the table structure)... Is it realy a true sense date field or a text field storing date as string in access .

    But yes for date fields we need to use '#'

    Laughing



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 14, 2010 8:25 AM
  • User1252303175 posted

    Thankyou all very much. The problem has been resolved. Just had to add  # sign |:) 

    Tuesday, June 15, 2010 12:10 AM
  • User-1199946673 posted

    The problem has been resolved
     

    For now it is resolved. But sooner or later you will encounter more problems when you're keep going on like this. Almost in every answer people point out that you should start using parameterized queries. Perhaps it is a good idea to start reading the articles they refer to, so you'll understand the advantages of using them.

    Tuesday, June 15, 2010 1:04 AM