Can't Order a SQL Table in DEC

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

yes....I forgot to mention...if I do this query at the sql server it works just fine...the problem is at my code.

hi

if you are using the SQLDatareader to fill in a grid than you can sort the data from the grid

http://www.developerfusion.com/article/5292/datagridgridview-paging-and-sorting-using-a-datareader/5/

http://www.4guysfromrolla.com/articles/040502-1.aspx

or if its to fill in a dropdownlist

http://www.codeproject.com/KB/aspnet/ASPNET_DropDownList_Sort.aspx

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.

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

Select allOpen in new window

ok, but, what part of the code tells the data to be ordered decreasingly?

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...

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

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

I want to sort it in the table

What is the type of column?

I want to order it by a floating number

What is the column type of cycletime? Is it float? What is the resultset looking like? Do you have a screenshot?

tiempociclo=cycletime ...the first row should be the highest time.
27-04-2011-6-54-39.gif

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.

My third attempt: What is the column type of cycletime?

Floating

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.

I've tried with all types of data.

How do I open the table in design mode?

you dont need to open it in design mode

just run this script

ALTER TABLE [tablename]
ALTER COLUMN [columnname] int

Are you using Sql Server management studio? Or Server explorer in VS?

sql server management studio

sorry AmmarR, I don't know how to run a script....my knowledge is on the IT Support, Im new at programming.

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.

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

Here's the screenshot of the table's design
design.gif

BTW, the data type of the column 'timecycle' must be floating because there are values like 3262,5 in it.

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....

It sorts ok

Then the problem is on .NET side. How are you using the resultset?

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

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...

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?

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

I am using visual basic .net (VStudio 2010)

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.

>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.

It is float, as I said before, there are values like 290.6

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
 
2. Chose a column and a sort order and click on the [!] execute icon
 

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

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?