[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can't Order a SQL Table in DEC

Posted on 2011-04-27
42
Medium Priority
?
369 Views
Last Modified: 2012-06-27
Hello, I need to order a SQL table, and to do that i use the following command
   Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()

But that query doesn't order my the table.

What should I do?

BTW I am using vb.net 2010

0
Comment
Question by:kachro
  • 19
  • 10
  • 8
  • +3
42 Comments
 
LVL 1

Expert Comment

by:myaspnet
ID: 35473787
mr kachro
try this query on sql server program
write this script SELECT * FROM master ORDER BY cycletime DESC and press F5
if successful on sql server you must have successfull in your code


myaspnet
0
 
LVL 3

Author Comment

by:kachro
ID: 35473795
yes....I forgot to mention...if I do this query at the sql server it works just fine...the problem is at my code.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35473836
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 3

Author Comment

by:kachro
ID: 35473855
Actually no, I loaded up the data from an excel file, (that's my input file, can't touch it), and I need to sort it in sql.
0
 
LVL 1

Expert Comment

by:myaspnet
ID: 35473881
excel to sql server use bulkcopy  
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\example0.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
        Using connection As New OleDbConnection(excelConnectionString)
            Dim command As New OleDbCommand("Select * FROM [Sheet1$]", connection)
            connection.Open()
            Using dr As DbDataReader = command.ExecuteReader()
                Dim sqlConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=db1;Integrated Security=True"
                Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
                    bulkCopy.DestinationTableName = "std1"
                    bulkCopy.WriteToServer(dr)
                End Using
                dr.Close()
            End Using
        End Using
        MsgBox("Data Inserted Successfully", MsgBoxStyle.Information)
    End Sub

Open in new window

0
 
LVL 3

Author Comment

by:kachro
ID: 35473891
ok, but, what part of the code tells the data to be ordered decreasingly?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35473926
dear kachro

after you get your data into sql, than its easy to sort by any column.

you dont need to sort while transferring the data, just get it raw...
0
 
LVL 3

Author Comment

by:kachro
ID: 35473936
AmmarR...strange as it sounds...I can't....I write these sentences
   Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()


but if I look at the table afterwards the data is still unsorted
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1000 total points
ID: 35473948
did you mean to say you want to store the data in sorted order in Table ?

As per your code you will get it sorted in that sqlreader only
it will not sorted physicaaly in table
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35473957
if you are using the code above to insert data from Excel to SQL, the data wont go sorted

you will always need to use the Order by clause if you read the table..
...

i think i am missing something here.. could you please explain the whole process and where are you using this code for
0
 
LVL 3

Author Comment

by:kachro
ID: 35473958
I want to sort it in the table
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474020
What is the type of column?
0
 
LVL 3

Author Comment

by:kachro
ID: 35474024
I want to order it by a floating number
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474029
What is the column type of cycletime? Is it float? What is the resultset looking like? Do you have a screenshot?
0
 
LVL 3

Author Comment

by:kachro
ID: 35474046
tiempociclo=cycletime ...the first row should be the highest time.
27-04-2011-6-54-39.gif
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35474108
Looks like the grid control is set up to sort nombrecodiqo ascending, then you can query with whatever order, the grid order overrides that.

Bye, Olaf.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474168
My third attempt: What is the column type of cycletime?
0
 
LVL 3

Author Comment

by:kachro
ID: 35474223
Floating
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474261
Float? The data does not look like float. What happens if you change it to Integer? Show a screenshot of table open in design mode.
0
 
LVL 3

Author Comment

by:kachro
ID: 35474324
I've tried with all types of data.

How do I open the table in design mode?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35474341
you dont need to open it in design mode

just run this script

ALTER TABLE [tablename]
ALTER COLUMN [columnname] int
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474345
Are you using Sql Server management studio? Or Server explorer in VS?
0
 
LVL 3

Author Comment

by:kachro
ID: 35474404
sql server management studio
0
 
LVL 3

Author Comment

by:kachro
ID: 35474410
sorry AmmarR, I don't know how to run a script....my knowledge is on the IT Support, Im new at programming.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474447
In SQL Server Management Studio, right click on the database name on left hand side and click on New Query. Then paste your query and execute it (execute button on toolbar). See what it brings back. Show us a screenshot.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35474449
if you are using sql server management studio

you can right click on the table and click design, to be able to change the type of the column.

or

if you notice at the top right corner of sql server management studio, you will find [New Query] button.

you can click on the database you want and then click on the button and keep the script i mentioned above.

you need to make sure your column is using the right datatype for better sorting...

because usually when you transfer data from Excel to SQL. all columns will be varchar(255) by default, and that could be the reason your table is not sorting
Design.png
0
 
LVL 3

Author Comment

by:kachro
ID: 35474464
Here's the screenshot of the table's design
design.gif
0
 
LVL 3

Author Comment

by:kachro
ID: 35474474
BTW, the data type of the column 'timecycle' must be floating because there are values like 3262,5 in it.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35474483
can you try this script from sql mngt studio , using [New Query]

SELECT * FROM master ORDER BY cycletime DESC

just tell us does it sort or not....
0
 
LVL 3

Author Comment

by:kachro
ID: 35474503
It sorts ok
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474509
Then the problem is on .NET side. How are you using the resultset?
0
 
LVL 3

Author Comment

by:kachro
ID: 35474520
Don't know what is a resultset.
 I just do this
   Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()

And then look at my table and the data is not sorted
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35474543
Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()

this script will return some where, do you store it in a grid or what control

because a datareader or a dataadapter doesnt have a sorting option.

you need to sort it in the control you are using...
0
 
LVL 3

Author Comment

by:kachro
ID: 35474548
Well, I am kind of understanding where my error is...now, could you please type what sentences should I write so I can end up with a sorted table?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35474577
it depends where you write the sentence

if in sql management studio, you know.

but if in a grid check the links i mentioned above, if some were else specify it and will show you how
0
 
LVL 3

Author Comment

by:kachro
ID: 35474584
I am using visual basic .net (VStudio 2010)
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35474723
The problem is not in the query or the database, it's in the GUI element you use (in your screenshot 27-04-2011-6-54-39.gif) , it's not showing data 1:1 as it arrives in your variable "answer". The control does not display the data in the order it is in "answer", it also sorts the data (on the first column). Find out how to turn that off or use another control to display the query result.

Bye, Olaf.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474862
>And then look at my table and the data is not sorted

Ah so you are expecting the table in the DB to be sorted by running that select? That is not going to happen. You dont need to sort the table in the DB. Sort it when displaying to the user in a control like grid.
0
 
LVL 3

Author Comment

by:kachro
ID: 35475000
It is float, as I said before, there are values like 290.6
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 35480909
Dear kachro:

as mentioned by the team above, you cant order inside the table. you can order in the GUI, that will display the data.

but since you are using Visual studio to view the table, look at the screen shots below to show you how to sort the table using VS.net

1. Open your table and click on the [Show Criteria Pane] icon in the top menu
 Show Criteria Pane
2. Chose a column and a sort order and click on the [!] execute icon
 Sort & Execute

if you dont see these icons in the top menu, make sure the Query designer is selected, by right clicking on the top menu and selecting it, check image below
Check-step.png
0
 
LVL 3

Author Comment

by:kachro
ID: 35480921
Thank you AmmarR for your superb answer but I think I mistook. What I really need is to have the table ordered in DESC, but not only for my viewing, but actually stored in an ordered way.....

Did I make myself clear?
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 1000 total points
ID: 35481038
Dear kachro:

yeah i think you made your self clear enough.

but what you are asking for cannot be done.

yet there is a work around like using a clustered index

http://stackoverflow.com/questions/4595229/keep-sql-server-table-sorted-inside-database
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/81dc5d09-a6b0-4be3-9dc6-e5fde0171365

but to sort the data inside the table, no it cannot be done
check this post

[how to sort actual table in sql server]
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/e61d7172-49c2-461d-af4c-6e1281c84842

hope its clear now...

Regards
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question