Problem in Displaying Data From Database (MSSQL)

Question

4.00/5 (1 vote)

See more:

i am entering data in data base but when i saw the table in Sql management studio stored data is not in proper order as entered:

if i enter:
1
2
3
4
5

result in table will be:
1
2
5
4
3

Please help me

Posted 15-May-11 4:57am

Shagun Bansal

Add a Solution

Comments

Wonde Tadesse 15-May-11 11:02am

Can you describe the table columns and their associated data type ?

Shagun Bansal 15-May-11 11:29am

3 colums
heading | subheading | date

all are varchar(50)

Wonde Tadesse 15-May-11 11:51am

Does the table has Primary Key ?

Fabio V Silva 15-May-11 11:33am

Why do you want the data in the database to be in the order you entered it?

Shagun Bansal 15-May-11 11:37am

i want to show these 5 entries in the list box or Gridview in order of in which the are entered.

4 solutions

Solution 3

It does not really matter how your data is stored in the table. Make sure that when you retrieve data in your grid / report etc. for display, you sort as required.

Posted 15-May-11 5:44am

Abhinav S

Comments

Albin Abel 15-May-11 13:00pm

Right, for various reasons the order we insert won't reflect in the physical order in the database. 5

Abhinav S 16-May-11 0:38am

Thanks Albin.

Wonde Tadesse 15-May-11 20:00pm

Sometimes ordering may not gave the required output. Please look my answer.

Solution 5

Well, After looking the datatype of the columns(varchar(50)), I suspect the data may contain a white space character. So even if you order them correctly, as long a record contains a white space, the query result won't be as expected. Actually it is valid. However, it might not be what you expected. So double check the data contain a white space character. If so try to remove it. In addition to this, it is better to give appropriate data type for your columns, which must be relevant to your input data. Looking your table columns, If I were you, I will create the table as follows.

SQL

CREATE TABLE Header -- You can give your own name
(heading int,
subheading int, -- Assuming that subheading is a foreign key.
date datetime)
)

I hope this helps you well.

Posted 15-May-11 13:58pm

Wonde Tadesse

Comments

Albin Abel 16-May-11 5:08am

String sorted by the matching from left to right (in usual cases). So white space at the tail end will not have a significant effect when considering the content. White space at the beginning has an impact while ordering.Agreed. But why this should be worried when inserting the data. While inserting database not going to worry about ordering. Ordering occurs when we demand i.e querying. I think OP says the database doesn't follow the order in the same way he added the data.

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)

Fabio V Silva · Accepted Answer · 2011-05-15T05:44:00

Solution 2

If you want to show data in the order it was entered I don't believe you can rely on the order SQL Server saves it. When you do the SELECT statement to retrieve the data you can use ORDER BY to order it by whatever column(s) you need.

More info here[^]

Posted 15-May-11 5:44am

Fabio V Silva

Comments

Albin Abel 15-May-11 13:01pm

Order By is useful for OP. 5

Wonde Tadesse 15-May-11 20:00pm

Sometimes ordering may not gave the required output. Please look my answer.

Albin Abel · Accepted Answer · 2011-05-15T06:59:00

Solution 4

Don't expect the order you insert and the physical order in the database should be same. Imagine a pile of 100 books. You have made it in an order. What if you removed 25 books in between and again added 25 books over the stack. If you think space is important then what you do? Put the books in the gap or above the pile. Then there is no matter what order the books will be present.

If you worry about query performance then use appropriate indexes.

Good luck

Posted 15-May-11 6:59am

Albin Abel

Comments

Wonde Tadesse 15-May-11 20:00pm

Sometimes ordering may not gave the required output. Please look my answer.

Abhinav S 16-May-11 0:39am

Good comment. 5.

Wonde Tadesse 16-May-11 8:59am

What if the OP add a space in front of the number? Do you think the order will be the same. Not at all. In fact, the way the OP get the output will definitely happen.So sorting doesn't matter here.Let say the OP enters data 1,2,5 preceded by two space and data 4 preceded by one space and lastly data 3 without space.Then what will be the output ?

1
2
5
4
3

Whether ordered or not the result will be affected by the front space.So he has to be careful about what kind of data entered into the database.
What if he want to display
1
2
3
4
5

How he will do it ? How come querying with order resolve the problem.