C# problem Access database , filed sorting .

Question

2.33/5 (2 votes)

See more:

in access database, he id field i want to sort it again, such as if the id filed was 1,22,30 .
i want make it 1,2,3
this the code .

C#

OleDbConnection h5 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\IJC Database.accdb");
            OleDbConnection h6 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\users\\IJC Database.accdb");
            OleDbCommand cmd5;
            OleDbCommand cmd6;
            h5.Open();
            cmd5 = new OleDbCommand("select count(ID) from ijc"+";",h5);
            int countID = (int)cmd5.ExecuteScalar();
            h5.Close();
            h6.Open();
            for (int i = 1; i <= countID; i++)
            {
                cmd6 = new OleDbCommand("update ijc set ID='"+i+"';",h6);
                cmd6.ExecuteNonQuery();
            
            }
            h6.Close();

but how can i move to the next id...

Posted 24-Jun-13 8:42am

loai_maane

Updated 24-Jun-13 8:48am

v3

Add a Solution

Comments

Mike Meinz 24-Jun-13 14:48pm

1. Each time you execute the UPDATE statement, you are changing all rows in the table. Usually there is a WHERE clause on an UPDATE statement to specify which row(s) should be changed.

2. You do not have a SELECT statement to select rows. Your SELECT statement selects an aggregate function value. A single value does not need to be sorted.

3. Rows are not kept sorted in the database. Rows are sorted after they are retrieved in a SELECT statement before they are returned to your application.

4. You do not need two OleDbCommand variables. You can execute both your SELECT and UPDATE statement using the same OleDbCommand variable.

[no name] 24-Jun-13 14:49pm

What do you mean "move to the next id"? All you are doing is setting all of the IDs in the table to the value of "i" in a loop. At the very least, you need to figure out what a WHERE clause does.

loai_maane 24-Jun-13 14:54pm

i mean ... in my database when i add data the id simply is 1,2,3....right??
but when i remove one row such as row number 2 the id be wrong so i try to write a code to make the id filed again 1,2,3...
i do not know how can explain, but i hope you understand me.

Mike Meinz 24-Jun-13 14:56pm

Why would you do that?

loai_maane 24-Jun-13 15:00pm

because when i finish my project, i want to print a report include that data in the access database

Mike Meinz 24-Jun-13 15:01pm

See Solution 1. It is not a best practice to re-number all rows because you removed a row.

3 solutions

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)

Ron Beyer · Answer 1 · 2013-06-24T08:56:00

You may have to clarify what you are really trying to do. If you are using the ID as a sorting column and are trying to adjust the ID's, then you are really going against what the ID column is used for.

Look at it this way, if you have another table in the database (call it Table2) that references (call it Table1) by ID, and you update the ID's in Table 1 but not the ones in Table2, then you've broken every relationship in the two tables.

If you only want to use the ID to sort the results, your select query should look something like this:

SQL

SELECT * FROM ijc ORDER BY ID

Which will order by ID. The last thing you should ever do in a database is re-order ID's, id's are just a number, don't put too much emphasis on the order.

Bernhard Hiller · Answer 2 · 2013-06-24T21:14:00

Solution 2

Do you want to "re-use" ID values which got free by e.g. deleting some rows from the database? That's a useless task! The ID is an automatically generated value, its only purpose is to identify a row. Filling up the gaps between the values won't alter peformance of your database.

Posted 24-Jun-13 21:14pm

Bernhard Hiller

TD7 STUDIOS · Answer 3 · 2015-08-10T00:54:00

Solution 3

I am sure it will be working on it.

string query = "select * from " + "TableName" + " Order by" + " ID";

Posted 10-Aug-15 0:54am

TD7 STUDIOS