Insert data into a table in database C#

Question

I intend to populate an access DB table that has three columns; Entity(text type), Date and Value(double type). I wrote the following code by going through some online links. Although the code runs fine, the table has no data. I am probably missing some part. Any advice?

for (int i = 0; i < model.CDFResults.Count; i++)
{ // connection details to the DB here...
    for (int j = 0; j < model.CDFResults[i].DataPoints.Count; j++)
    {
        OleDbCommand myAccessCommand = new OleDbCommand();
        myAccessCommand.CommandType = CommandType.Text;
        myAccessCommand.CommandText = "INSERT INTO TypeCurves([Entity],[Date],[Value])VALUES(?,?,?)";
        myAccessCommand.Parameters.AddWithValue("@Entity", model.CDFResults[i].catname_db);
        myAccessCommand.Parameters.AddWithValue("@Date", model.CDFResults[i].DataPoints[j].dt);
        myAccessCommand.Parameters.AddWithValue("@Value", model.CDFResults[i].DataPoints[j].CDFVal);


    } // end of FOR(j) loop

} // end of FOR(i) loop

EDIT: Still not working

for (int i = 0; i < model.CDFResults.Count; i++)
{ // connection details to the DB here...
    for (int j = 0; j < model.CDFResults[i].DataPoints.Count; j++)
    {

        OleDbConnection thisConnection = new OleDbConnection(connectionname);
        thisConnection.Open();

        OleDbCommand myAccessCommand = new OleDbCommand();
        myAccessCommand.CommandType = CommandType.Text;
        myAccessCommand.CommandText = "INSERT INTO TypeCurves([Entity],[Date],[Value])VALUES(?,?,?)";
        myAccessCommand.Parameters.AddWithValue("@Entity", model.CDFResults[i].catname_db);
        myAccessCommand.Parameters.AddWithValue("@Date", model.CDFResults[i].DataPoints[j].dt);
        myAccessCommand.Parameters.AddWithValue("@Value", model.CDFResults[i].DataPoints[j].CDFVal);

        myAccessCommand.ExecuteNonQuery();    
    } // end of FOR(j) loop
} // end of FOR(i) loop

Possible duplicate of [Insert Data into database in C#](http://stackoverflow.com/questions/8389803/insert-data-into-database-in-c-sharp) — Mihai-Daniel Virna, Jan 06 '16 at 16:08

user1666620 · Accepted Answer · 2016-01-06T16:33:36.677

4

You need create the connection to the database and execute the query.

using (OleDbConnection connection = new OleDbConnection(connectionString))
{   
    string query = "INSERT INTO TypeCurves([Entity],[Date],[Value])VALUES(@Entity,@Date,@Value)";
    OleDbCommand myAccessCommand = new OleDbCommand(query, connection);
    myAccessCommand.Parameters.AddWithValue("@Entity", model.CDFResults[i].catname_db);
    myAccessCommand.Parameters.AddWithValue("@Date", model.CDFResults[i].DataPoints[j].dt);
    myAccessCommand.Parameters.AddWithValue("@Value", model.CDFResults[i].DataPoints[j].CDFVal);

    connection.Open();
    myAccessCommand.ExecuteNonQuery();
}

connectionString is whatever your connection string is to your database.

In this example you don't need to explicitly close the connection after the query is executed as the connection is wrapped in a using block, and so will be disposed of once it has exited the block.

https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery(v=vs.110).aspx

edited Jan 06 '16 at 16:33

answered Jan 06 '16 at 16:06

user1666620

4,800
18
27

I added it but I get an error saying "ExecuteNonQuery:Connection property has not been initialized." – Modi Jan 06 '16 at 16:10
@user5752879 you need to create a connection to the database. – user1666620 Jan 06 '16 at 16:11
Thanks. I understood the concept too! – Modi Jan 06 '16 at 16:30
@Aron Your downvote is unwarranted. If you check the question edit history (and my answer history), I originally stated that he needed to execute the query. However then it turned out that in addition to not executing the query, the OP wasn't creating a connection in the first place. It doesn't help that the OP kept updating his question, incorporating my original answer into the query as well as his own attempts to create a connection. – user1666620 Jan 06 '16 at 16:35
@user1666620 Okay...my bad...or rather bad question asking etiquette on the OP's side. I'll restore the question properly... – Aron Jan 06 '16 at 16:39

Insert data into a table in database C#

1 Answers1