Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SQL Collation Conundrums

Reply
Thread Tools

SQL Collation Conundrums

 
 
Andrew Butchart
Guest
Posts: n/a
 
      03-18-2005
I have had some problems with a web site that I am working on. The
DBA set up all of the tables using the default collation sequence of
SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
returned is "out of order" according to my ASP code. For example, ASP
believes that the string "6\" is greater than "6001001" whereas SQL
returns the "6\" record first.

Is there any way to set the default comparison between string values
in ASP to match the order that SQL is returning, or can I update my
query to give me a collation sequence that ASP will like? Changing
the collation sequence in the database isn't a viable option.

Thanks

Andrew Butchar
http://www.velocityreviews.com/forums/(E-Mail Removed)
 
Reply With Quote
 
 
 
 
John Bell
Guest
Posts: n/a
 
      03-19-2005
Hi

You should be able to use a binary collation to get SQL Server to work like
your ASP

SELECT col1
FROM (
SELECT '6\' COLLATE Latin1_General_BIN AS Col1
UNION ALL SELECT '6001001' ) A
order by col1

To do the opposite in your ASP code I think you would have to write a
function that does character by character comparisons.


John

"Andrew Butchart" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
>I have had some problems with a web site that I am working on. The
> DBA set up all of the tables using the default collation sequence of
> SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
> returned is "out of order" according to my ASP code. For example, ASP
> believes that the string "6\" is greater than "6001001" whereas SQL
> returns the "6\" record first.
>
> Is there any way to set the default comparison between string values
> in ASP to match the order that SQL is returning, or can I update my
> query to give me a collation sequence that ASP will like? Changing
> the collation sequence in the database isn't a viable option.
>
> Thanks
>
> Andrew Butchar
> (E-Mail Removed)



 
Reply With Quote
 
 
 
 
Andrew Butchart
Guest
Posts: n/a
 
      03-20-2005
Dandy - that's it! I just used the COLLATE on my Order By statement
and it's exactly what I needed.

Many thanks John

Andrew Butchart
(E-Mail Removed)

"John Bell" <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> Hi
>
> You should be able to use a binary collation to get SQL Server to work like
> your ASP
>
> SELECT col1
> FROM (
> SELECT '6\' COLLATE Latin1_General_BIN AS Col1
> UNION ALL SELECT '6001001' ) A
> order by col1
>
> To do the opposite in your ASP code I think you would have to write a
> function that does character by character comparisons.
>
>
> John
>
> "Andrew Butchart" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) om...
> >I have had some problems with a web site that I am working on. The
> > DBA set up all of the tables using the default collation sequence of
> > SQL_Latin1_General_CP1_CI_AS HOWEVER, the recordset that is being
> > returned is "out of order" according to my ASP code. For example, ASP
> > believes that the string "6\" is greater than "6001001" whereas SQL
> > returns the "6\" record first.
> >
> > Is there any way to set the default comparison between string values
> > in ASP to match the order that SQL is returning, or can I update my
> > query to give me a collation sequence that ASP will like? Changing
> > the collation sequence in the database isn't a viable option.
> >
> > Thanks
> >
> > Andrew Butchar
> > (E-Mail Removed)

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Collation error in aspnet_regsql.exe Richard Hallgren ASP .Net 0 08-10-2006 07:01 PM
Conundrums of tests Shane NZ Computing 20 08-09-2006 06:21 AM
f*ing collation and asp.net 2.0 security are driving me crazy papaja ASP .Net 0 07-13-2006 08:41 AM
Maximum Character Value and Collation alan@ljubljana.agtrz.com Java 1 08-13-2005 05:06 PM
2 wireless connection conundrums Karl Engel Computer Support 2 08-13-2005 04:44 PM



Advertisments