none
C# Database interaction RRS feed

  • Question

  • I'm trying to interact with an Access database with C#.  I'm trying to insert new records into the database and then view them, I'm just using the Console reading and writing methods, and I can't get it to pull values, then insert values, and then pull the values again with the new values showing up.  Below is the code I have for the method that inserts my values, because I know the code that pulls the values works.

     

    public static void DBUpdate(DataTable DT)

    {

    string strInsertCommand;

    string name;

    Console.WriteLine("Enter Name");

    name = Console.ReadLine();

    string SQL = "SELECT * FROM Test";

    strInsertCommand = "INSERT INTO Test (name) VALUES ('" + name + "')";

    OleDbConnection myConn = new OleDbConnection(strAccessConn);

    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(SQL, strAccessConn);

    myDataAdapter.SelectCommand = new OleDbCommand(strAccessSelect, myConn);

    myDataAdapter.InsertCommand = new OleDbCommand(strInsertCommand);

    myDataAdapter.InsertCommand.Parameters.Add("@name", OleDbType.VarChar, 40, "name");

    myDataAdapter.InsertCommand.Connection = myConn;

    myDataAdapter.InsertCommand.Connection.Open();

    myDataAdapter.Update(DT);

    myDataAdapter.InsertCommand.Connection.Close();

    }

     

    I'm pulling the DT from the method that pulls all of the data from the database.  Any help would be appreciated.

    Wednesday, June 13, 2007 8:14 PM

All replies

  • So... let's see if I understand... it looks like what you are trying to do is update the database with the changes that have been added to the DataTable, and then update the ID of the correct record in the DataTable so that they are both now up to date?

    Well then, first up, I normally have a separate object to communicate with the database, and it would have a persistent copy of the DataSet and DataAdapter.  I'd make the DataSet a public property (with a get anyway) but keep the DataAdapter private.

    I'd set up all of the commands for the DataAdapter in that objects constructor... and btw your doing the whole property thing wrong.  Take a look at the code below.  Not super easy I'll grant

    btw, m_cmdGetIdentity is a OledbCommand that looks like this
    OleDbCommand m_cmdGetIdentity = new OleDbCommand("SELECT MAX(ID) FROM Test");

    Where ID is the name of your ID field in the test table


               
    Code Snippet


                //Add command to the DataAdapter for insertion
                //For the Test Table
                string sqlInsert = "INSERT INTO Test(name, otherColumn) VALUES(?,?)";


    //create the actual commands for the adapter, using the strings above and connection above
                OleDbCommand insertCmd = new OleDbCommand(sqlInsert,conn);


                //Add parameters to the insert command (tell adapter what to replace ?'s with in query)
                 insertCmd.Parameters.Add("name",OleDbType.VarChar,255,"Iname");
    insertCmd.Parameters.Add("otherColumn",OleDbType.BigInt,12,"otherColumn");


    myDataAdapter.InsertCommand = insertCmd;



    //Run when an update occurs
                myDataAdapter.RowUpdated +=new OleDbRowUpdatedEventHandler(myDataAdapter_RowUpdated);



    //Ok now elsewhere in the class

    private void myDataAdapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
            {
                if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert))
                {
                   
                    // Get the Identity column value  Replace ID with the name of your ID field
                    e.Row["ID"] = Int32.Parse(m_cmdGetIdentity.ExecuteScalar().ToString());
                    e.Row.AcceptChanges();

                  
                }
                else
                {
                }
            }




    Good luck,

    -mwalts


    Wednesday, June 13, 2007 8:45 PM