Unable to do an update loop in table

Question

0.00/5 (No votes)

See more:

Here my dilema, I'm unable to update my table to modify my ID numbers. I'm trying to do this because sometimes I have to modify the ID number to do a union all with another table so my ID's are a unique number.

This is what I have.

C#

try
           {
               OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strdb + "\\db1.mdb");
               thisConnection.Open();
               OleDbCommand thisCommand = thisConnection.CreateCommand();
               thisCommand.CommandText = "SELECT WPID from table1";
               OleDbDataReader thisReader = thisCommand.ExecuteReader();
               while (thisReader.Read())
               {
                   int StartLoop =50;
                   int StopLoop = 150;
                   int answer = 0;
                       //UPDATE ROUTINE
                   using (OleDbCommand thisUpdateCommand = thisConnection.CreateCommand())
                   {
                       for (int i = StartLoop; i < StopLoop; i++)
                       {
                           answer = answer + i;
                       }
                       thisUpdateCommand.CommandText = "update table1 set WPID = " + answer;
                       thisUpdateCommand.ExecuteNonQuery();
                   }
               }
               thisReader.Close();
               thisConnection.Close();
           }
           catch (Exception ex)
           {
               Console.WriteLine(ex.Message);
           }

I'm trying to do something like an autoincrement without using autoincrement built into Access. I want to control the numbers that go into it from start to finish hence why I'm trying to use a startloop and stoploop numbers.

Does anyone have an idea how I can do an update in a table using sequencial numbers?

Posted 6-Nov-11 8:52am

mzrax

Updated 6-Nov-11 9:08am

Mehdi Gholam

v2

Add a Solution

Comments

Amir Mahfoozi 6-Nov-11 15:09pm

Did you get any error by this way ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bala Selvanayagam · Accepted Answer · 2011-11-06T10:12:00

try,

I have hard coded the starting number as 160 and change accordingly and hope the code is self explanatory

private void button1_Click(object sender, EventArgs e)
       {
           try{

               using (OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DATABASE\\Database1.mdb"))
               {
                   thisConnection.Open();
                   OleDbCommand thisCommand = thisConnection.CreateCommand();

                   OleDbDataAdapter thisAdapter = new OleDbDataAdapter("select ID from WPID", thisConnection);
                   DataSet ods = new DataSet();
                   thisAdapter.Fill(ods);
                   OleDbCommandBuilder thisCommandBuilder = new OleDbCommandBuilder(thisAdapter);

                   for (int i=0; i < ods.Tables[0].Rows.Count;i++)
                   {
                      //160 is my start number and  change accordingly
                      ods.Tables[0].Rows[i]["ID"] = 160 + i;

                   }

                   thisAdapter.Update(ods.Tables[0]);
                   MessageBox.Show("OK");
               }

            }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }
       }

UPDATE -1 Solution added later based on OP's comment and revises the above solution.

private void button3_Click(object sender, EventArgs e)
      {
          try
          {

              using (OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DATABASE\\Database1.mdb"))
              {
                  thisConnection.Open();
                  OleDbCommand thisCommand = thisConnection.CreateCommand();

                  OleDbDataAdapter thisAdapter = new OleDbDataAdapter("select ID,WPID from table1", thisConnection);
                  DataSet ods = new DataSet();
                  thisAdapter.Fill(ods);
                  OleDbCommandBuilder thisCommandBuilder = new OleDbCommandBuilder(thisAdapter);

                  int intStart = 3003;
                  int intEnd = 3010;

                  for (int i = 0; i < ods.Tables[0].Rows.Count; i++)
                  {

                      ods.Tables[0].Rows[i]["WPID"] = intStart;
                      intStart = intStart + 1;
                  }

                  while (intStart <= intEnd)
                  {
                      DataRow x = ods.Tables[0].NewRow();
                      x["WPID"] = intStart;
                      x["ID"] = "Blank" + intStart;
                      ods.Tables[0].Rows.Add(x);
                      intStart = intStart + 1;
                  }

                  thisAdapter.Update(ods.Tables[0]);
                  MessageBox.Show("OK");
              }

          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
      }