Field not sorting in ascending order

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance

That's because your field is not numeric, if it were numeric, you would expect 61,100,200

but, with text, it looks at MP with a '1' and sees it first, then, MP with a '2' and sees it next - - naturally 6 is after 2 - - but that's the reason.

|||

It is possible to get text to behave a bit like numbers.

If you had stored this instead, it would sort correctly:

MP061, MP100, MP200

This example presumes that the numerical component will always be no more than 3 characters and all 3 postiions are identified for each record (even if they are zero). In general, it's better not to try to sort alpha-numeric data in a numerical sort order.

|||If all of your records have a 2-character prefix in that column, andthere are only numeric characters that follow, you can use thisapproach to solve your sorting problem:

SELECT
someColumns
FROM
someTable
ORDER BY
CAST(SUBSTRING(mpColumn,3,99) AS integer),
mpColumn

Fatal System Error

Whats happening:

Error#: 40002
Error Item: 01000: [Microsoft...][ODBC...][SQL...]The Statement has
been terminated. > GETID 'INSERT INTO Table(Column1, Column2, Column 3,
Column4, Column5, Column6) VALUES
("","Value1","Value2","Value3","Value4","Value5")'

Error Message: 01000: Microsoft...][ODBC...][SQL...]The statement has
been terminated.

Active Form: frmForm
Form Name:

--
GETID Stored Procedure:
--

CREATE PROCEDURE GETID @.strsql text AS

PRINT cast(@.strsql as varchar(4000))

EXEC(" "
+@.strsql + " select @.@.identity as id")

GO
--

Anyone know what could be causing it? The database has changed - the
frontend code hasn't, so it has to be a database problem. What
happened was a few tables became incorrect so we restored them from a
backup. Everything else seems to work, apart from adding a new record
to this table.
You can edit current records etc..just not add.

Thanks in advance,[posted and mailed, please reply in news]

(lbillson@.netexperts.co.uk) writes:
> Whats happening:
> Error#: 40002
> Error Item: 01000: [Microsoft...][ODBC...][SQL...]The Statement has
> been terminated. > GETID 'INSERT INTO Table(Column1, Column2, Column 3,
> Column4, Column5, Column6) VALUES
> ("","Value1","Value2","Value3","Value4","Value5")'
> Error Message: 01000: Microsoft...][ODBC...][SQL...]The statement has
> been terminated.
>...
> Anyone know what could be causing it? The database has changed - the
> frontend code hasn't, so it has to be a database problem. What
> happened was a few tables became incorrect so we restored them from a
> backup. Everything else seems to work, apart from adding a new record
> to this table.

It appears that one error message has been lost somewhere. "The statement
has been terminated" is an accompanying message to the real error message.

I don't know what sort of client code you are using, but it seems that
the error handling needs a review. When working with SQL Server it's
essential to get all error message. And particularly the first error
message.

As for what have happened, it seems that the definition of the table has
changed, so that the statement is no longer correct. May be there is one
more column which is not nullable. You could also be running into a
key violation. Or, since you had restored some tables from backup, maybe
you are inserting a non-valid foreign key.

Your best bet is probably to run the query from Query Analyzer.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp