With C#, how to insert incrementing numbers in MS Access database using a loop?

Question

I am trying to create a windows form with Visual Studio 2012, and working with an MS Access database. This form will programmatically create a database and manually input all data into the fields bases on user input. I can create the database and tables just fine, the primary keys also auto-increment. What I am trying to accomplish is I would like to input a beginning and ending number, say a year for example, and then loop through the range of numbers to input redundant data and the incremented year that would save a ton of typing. I have the loop working and the data inputs into each record through the length of the loop, but the number I need to increment does not increment. How can I get a second field to increment based on user input when there is already an auto-increment primary key? Any insight would be greatly appreciated, my problem method is below.

    private void button3_Click(object sender, EventArgs e)
    {
        char ch = '"';
        string sql = "";
        string tempYear = "";
        int year = Convert.ToInt32(this.textBoxBegYear.Text);
        int endYear = Convert.ToInt32(this.textBoxEndYear.Text);

        try
        {
            OleDbConnection myConnection = new OleDbConnection();
            myConnection.ConnectionString
                   "Provider=Microsoft.ACE.OLEDB.12.0DataSource=" + ch + openFileDialog1.FileName + ch;
            myConnection.Open();

            sql = "INSERT INTO " + this.comboBox1.SelectedItem.ToString() + " (Year) Values (@tempYear)";
            OleDbCommand myCommand = new OleDbCommand(sql, myConnection);

            for (int i = year; i <= endYear; i++)
            {
                tempYear = Convert.ToString(i);
                myCommand.Parameters.Add("@tempYear", OleDbType.Numeric).Value = tempYear;
                myCommand.ExecuteNonQuery();
            }
            myCommand.Connection.Close();
        }
        catch (Exception err)
        {
            MessageBox.Show("Error: " + err.Message.ToString());
        }
    }

I would like the finished output to be as follows but I need to get the number to increment first, the rest works fine...

     Year    ...all other fields
     2000    ...stuff
     2001    ...identical stuff from previous record
     2002    ...identical stuff from first record

Thank You.

nope, just a number I would like to increase with each record. The user inputs the beginning and ending numbers, everything in between in filled in with the data along with the incrementing number. In the example the user would put in 2000 and 2002. — user2305700
– user2305700, Commented Apr 4, 2015 at 19:43

Gord Thompson · Accepted Answer · 2015-04-04 20:00:16Z

2

System.Data.OleDb ignores parameter names, so even though you .Add a parameter with the same name inside the loop you are actually creating a new additional one each time. Each new parameter you add has the new (incremented) value, but those new parameters are never used; only the first one (with the first value) has any effect.

What you need to do is .Add the parameter before entering the loop, and then just update the .Value inside the loop:

sql = "INSERT INTO " + this.comboBox1.SelectedItem.ToString() + " ([Year]) VALUES (?)";
OleDbCommand myCommand = new OleDbCommand(sql, myConnection);
myCommand.Parameters.Add("?", OleDbType.Integer);
for (int i = year; i <= endYear; i++)
{
    myCommand.Parameters[0].Value = i;
    myCommand.ExecuteNonQuery();
}

edited Apr 4, 2015 at 20:00

answered Apr 4, 2015 at 19:52

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2305700 Over a year ago

It also makes sense how when debugging, I could see the incremented value but couldn't find a way to get it into the field.

Collectives™ on Stack Overflow

With C#, how to insert incrementing numbers in MS Access database using a loop?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related