locked
SELECT query is working fine, but INSERT is not.... RRS feed

  • Question

  • User-70711634 posted

    I have been sitting with this for hours now and cannot find a solution.  I have a Access 2007 database and am reading information from it into a dataset ("ds").  That works fine, but I am unable to use an INSERT query to add a new row to the "User" table.  It is using the same connection as the SELECT and when I copy my CommandText into Access, it appends the row without any problem.  I have tried two methods (one being Updating the dataset and the other by creating a command and just calling it with ExecuteNonQuery()) but nothing has worked.  When I tried the dataset method, the dataset had the new row listed when using the debugger, but it still wouldn't appear in the database.  Could it be a security issue?  I am running windows 7 and VS2008.  Below is some of the code related to the problem:

     

    OleDbConnection conn;

    string connection = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=team12.accdb;" + @"Persist Security Info=False;";

    conn = new OleDbConnection(connection);

    conn.Open();

    string command = @"INSERT INTO [User] ([Username],[Pass],[Type],[Title],[Initials],[Surname],[TelNo],[CellNo],[Address],[Suburb],[City],[AreaCode],[DOB],[Email],[Gender],[HighestQualification],[YearOfHQ],[Question],[Answer],[Active]) VALUES('" +txtUserN.Text +"','" + txtPassword.Text + "','" + staffStud + "','" + txtTitle.Text + "','" + txtInitials.Text + "','" + txtSurname.Text + "','" + txtTel.Text + "','" + txtCell.Text + "','" + txtAddress.Text + "','" + txtSuburb.Text +"','" + txtCity.Text + "'," + txtCode.Text + ",#" + dtpickDOB.Value.ToString("yyyy-MM-dd") + "#,'" + txtEmail.Text + "','" + MaleFemale + "','" + cboQualification.Text + "'," + cboYearOb.Text + ",'" + cboQuestion.Text + "','" + txtAnswer.Text + "',false)";

     

    OleDbCommand insertCommand = new OleDbCommand(command, conn);

    insertCommand.ExecuteNonQuery();

    conn.Close();

    Thank you so much for your help!!  I really am stuck and it is quite urgent!

    Thank

    Saturday, July 17, 2010 8:40 PM

Answers

  • User-70711634 posted

     I found the problem.  The database was being copied to the debug folder and the changes being commited to the database was overwritten every time I ran the program.  Thank you for your assistance.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 18, 2010 3:46 AM

All replies

  • User579345625 posted

    Have you checked if the insert query that you are building is running successfully on database directly?

    If Insert query itself is failing on database then one cant expect it to from application.

    Saturday, July 17, 2010 10:13 PM
  • User-70711634 posted

    Thank you for the reply.  I have tested it directly in the database and the query works correctly.   It does not work when I run it from VS, however.

    Sunday, July 18, 2010 2:50 AM
  • User-70711634 posted

     I found the problem.  The database was being copied to the debug folder and the changes being commited to the database was overwritten every time I ran the program.  Thank you for your assistance.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 18, 2010 3:46 AM
  • User-1199946673 posted

    I found the problem
     

    Good. Now I suggest you start reading this, otherwise there will come another time you'll be sitting for hours wondering why a command doesn't work when you enter for example a single quote....

    Sunday, July 18, 2010 8:35 AM