>Hi all,
> i use vb.net dataset and visual foxpro to database. I want to use sql to order by convert int.
>If use sql server i can SELECT * FROM apos ORDER BY CONVERT(int, scode).
>if visual FoxPro i also can SELECT Cast(sCode as int) as s, bcode, bcode2, descript, unit, cdescript, uprice FROM apos order by s
>
>But in vb.net dataset selectCommand i use SELECT Cast(sCode as int) as s, bcode, bcode2, descript, unit, cdescript, uprice FROM apos order by s, it will error say Error in list of function arguments: 'AS' not recognized. Unable to parse query text.
>
>How can i do? Thank you ~~~

Here is a method I use in my Data class. The IsNull() method negotiates with SQL Server and VFP backends. So, it adjusts accordingly to the syntax being returned. For VFP, other than the fact that is uses NVL to avoid returning nulls, this is pretty close to what you are looking for. In my code, I use I instead of Int. I do not know if that makes a difference.

        ' Generate a IsNull proper syntax based on the backend
        ' expC1 Alias
        ' expC2 Field
        ' expC3 If expC2 is an alias defined with AS as a second name, the real name should be defined here
        Public Function IsNull(ByVal tcAlias As String, ByVal tcField As String, _
         Optional ByVal tcAlias2 As String = "") As String
            Dim lcAlias As String = ""
            Dim lcString As String = ""
            Dim lcType As String = ""
            Dim lnDecimal As Integer = 0
            Dim lnLength As Integer = 0
            Dim loData As Framework.Data = Nothing

            ' Get the value
            lcAlias = tcAlias

            ' If expC2 is an alias defined with AS as a second name
            If tcAlias2.Length > 0 Then
                lcAlias = tcAlias2
            End If

            ' If we run under the framework
            If oApp.lFramework Then

                ' If the table does not exist
                If oApp.Tables(lcAlias) Is Nothing Then
                    ErrorSetup(, "The table " + lcAlias + " does not exist. This is in IsNull() for the field " + tcField + ".")
                    Return False
                End If

                ' If the field does not exist
                If oApp.Tables(lcAlias).Fields(tcField) Is Nothing Then
                    ErrorSetup(, "The field " + lcAlias + "." + tcField + " does not exist. This is in IsNull().")
                    Return False
                End If

                lcType = oApp.Tables(lcAlias).Fields(tcField).Type
                lnLength = oApp.Tables(lcAlias).Fields(tcField).Length
                lnDecimal = oApp.Tables(lcAlias).Fields(tcField).NumberOfDecimal
            Else

                ' Get the proper definition as per the current scope
                If oProcess Is Nothing Then
                    loData = New Framework.Data(oApp)
                Else
                    loData = New Framework.Data(oProcess)
                End If

                loData.nConnectionString = nConnectionString

                ' Get the schema in a datatable
                If Not loData.GetSchema(lcAlias) Then
                    Return False
                End If

                ' Get the field type
                lcType = loData.GetColumnProperty(tcField, "DataType")

                ' Get the field size
                lnLength = loData.GetColumnProperty(tcField, "ColumnSize")

                ' Number of decimals
                lnDecimal = loData.GetColumnProperty(tcField, "NumericScale")

            End If

            ' Get the proper definition as per the current scope
            If oProcess Is Nothing Then
                nBackend = oApp.aConnection(nConnectionString, 4)
            Else
                nBackend = oProcess.aConnection(nConnectionString, 4)
            End If

            ' Different backends handle it differently
            Select Case nBackend

                ' Visual FoxPro
                Case 1

                    ' Type
                    Select Case lcType

                        ' Integer
                        Case "I"
                            lcString = "CAST(NVL(" + tcAlias + "." + tcField + ", 0) AS I)"

                            ' Character
                        Case "C"
                            lcString = "CAST(NVL(" + tcAlias + "." + tcField + ", """") AS C(" + lnLength.ToString + "))"

                            ' Date
                        Case "D"
                            lcString = "CAST(NVL(" + tcAlias + "." + tcField + ", CTOD(""  /  /  "")) AS D)"

                            ' Date time
                        Case "T"
                            lcString = "CAST(NVL(" + tcAlias + "." + tcField + ", CTOT(""  /  /   00:00:00"")) AS T)"

                            ' Numeric
                        Case "N"
                            lcString = "CAST(NVL(" + tcAlias + "." + tcField + ", 0) AS N(" + _
                             lnLength.ToString + "," + lnDecimal.ToString + "))"

                    End Select

                    ' SQL Server
                Case 2

                    ' Type
                    Select Case lcType

                        ' Integer
                        Case "I"
                            lcString = "COALESCE(" + tcAlias + "." + tcField + ",0)"

                            ' Character
                        Case "C"
                            lcString = "COALESCE(" + tcAlias + "." + tcField + ",'')"

                            ' Date
                        Case "D", "T"
                            lcString = "COALESCE(" + tcAlias + "." + tcField + ",'1899-12-30 00:00:00')"

                            ' Numeric
                        Case "N"
                            lcString = "COALESCE(" + tcAlias + "." + tcField + ",0)"

                    End Select

            End Select

            Return lcString
        End Function