none
Update Query problem RRS feed

  • Question

  • Hello all,

     

    I have a problem with my update query for an access database.

     

    Basically, the query should look like this:

    Update [table] set [column]='This is the 'text' that needs to be entered' Where ID=1

     

    When running an update like this the program will throw a syntax exception because of the ' contained in the string value.  I need these ' to be there as this column is going to contain a VBScript.

     

    If I wasn't clear enough, please let me know.

    Wednesday, June 27, 2007 2:52 PM

Answers

  • I deduced the answer while looking at http://msdn2.microsoft.com/en-us/library/ms177523.aspx  The problem was that I was entering the string value directly into update query.

     

    To accomplish this, I insted used the following syntax in my code:  update [table] set [column]=@parameter where id=[x]

     

    Then I created a parameter like this:

     

    OleDBParameter p1 = new OleDBParameter("@parameter", [string]);

     

    then I added the parameter to the command and all went swimmingly.

     

    This is finished code:

     

     

               

    Code Snippet

                s = c3.Text;

                o1.CommandText = "Update links set Program=@p1 Where ID=" + c2.SelectedValue;

                OleDbParameter p = new OleDbParameter("@p1",s);

                o1.Parameters.Add(p);

                DialogResult r;

                r = MessageBox.Show("You are attempting to update thedatabase with a new script\n\nThis can not be undone!\n\nAre you sure you want to do this?", "Update Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);

                if (r == DialogResult.Yes)

                {

                    try

                    {

                        o1.Connection.Open();

                        o1.ExecuteNonQuery();

                        MessageBox.Show("Selected script has been updated", "Update Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }

                    catch (OleDbException ex)

                    {

                        MessageBox.Show("Could not complete script update", "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    }

                    finally

                    {

                        o1.Connection.Close();

                    }

                }

                else

                {

                    MessageBox.Show("User cancelled update", "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                }

            }

     

     

    Wednesday, June 27, 2007 4:16 PM

All replies

  • I deduced the answer while looking at http://msdn2.microsoft.com/en-us/library/ms177523.aspx  The problem was that I was entering the string value directly into update query.

     

    To accomplish this, I insted used the following syntax in my code:  update [table] set [column]=@parameter where id=[x]

     

    Then I created a parameter like this:

     

    OleDBParameter p1 = new OleDBParameter("@parameter", [string]);

     

    then I added the parameter to the command and all went swimmingly.

     

    This is finished code:

     

     

               

    Code Snippet

                s = c3.Text;

                o1.CommandText = "Update links set Program=@p1 Where ID=" + c2.SelectedValue;

                OleDbParameter p = new OleDbParameter("@p1",s);

                o1.Parameters.Add(p);

                DialogResult r;

                r = MessageBox.Show("You are attempting to update thedatabase with a new script\n\nThis can not be undone!\n\nAre you sure you want to do this?", "Update Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);

                if (r == DialogResult.Yes)

                {

                    try

                    {

                        o1.Connection.Open();

                        o1.ExecuteNonQuery();

                        MessageBox.Show("Selected script has been updated", "Update Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }

                    catch (OleDbException ex)

                    {

                        MessageBox.Show("Could not complete script update", "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    }

                    finally

                    {

                        o1.Connection.Close();

                    }

                }

                else

                {

                    MessageBox.Show("User cancelled update", "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                }

            }

     

     

    Wednesday, June 27, 2007 4:16 PM
  •  

    for ex

    dim st as string

    st="update emp set sal=" & trim(txtsal.text) & " where eno=" & trim(txteno.text)

    cmd=new oledbcommand(st,connectionobjname)

    cmd.executenonquery

    con.close

    plz check it once

    Friday, June 29, 2007 8:07 AM