How can I get Text values that appear as numbers sort in 'numeric' order in a datagrid?

I have database columns that are defined as Text. One, labeled PartID sometimes has values such as 1,2,3...etc. The problem is when I want to sort in a d dataGrid, it will  sort like 1,11,12,2,3,32,4...etc. How can I get it to sort as if they were consecutive numbers? The fielf is defined Text because more often than not, the value may be alpha in nature too. Thank you in advance for code examples.
IT_SteveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ajay SharmaCommented:
use this

select convert(int, partId) from demo order by convert(int,partid) asc
0
IT_SteveAuthor Commented:
I apologize for forgetting to include I am using Access for my database.  I have not found a way to make your suggestion work. Is that for stored procedures? I will need a solution that can deal with values such as 1,2,3..,Dave's-40,SJ,... etc.
0
planoczCommented:
The only way to get around it is to format that field to have a leading zero if the number is between 1 and 9.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Ajay SharmaCommented:
use it like this

    Dim ds As New DataSet
    Dim da As SqlDataAdapter
    Dim sql As String
    Dim myConnection As SqlConnection
    Dim ConnStr As String = "ur connection string here"

            myConnection = New SqlConnection(ConnStr)

            sql = "select convert(int, partId), partname,partdesc,allothercolumns from demo order by convert(int,partid) asc"
            da = New SqlDataAdapter(sql, myConnection)
            da.Fill(ds, "demo")

0
IT_SteveAuthor Commented:
Ajaysharmaapjs -
When I try the code you suggest, I get the error: Undefined function 'Convert' in expression. I am using VB.NET 2003. Perhaps Convert is a built in method in 2005? But I was wondering how it willl behave if the partID was 'Steve'sWidget4'? Remember, some partID look numeric, 1,72,4, but many are mixed like 'Steve'sWidget4' or all alpha chars 'NinethParcel'.

Planoz -
I was hoping to avoid the 0 but may be resigned to that. I will wait awhile to see if there are other suggestions.
0
IT_SteveAuthor Commented:
Planoz - Your solution answered my question as asked and works. I was intrigued by Ajaysharmaapjs 's Select CONVERT(... concept but it didn't work for me.  Thank you for your ideas.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.