SQL Syntax error in ORDER BY clause.(-2147217900) 
Author Message
 SQL Syntax error in ORDER BY clause.(-2147217900)

I have a data grid that shows 6 fields (see below).  When I try
to sort by clicking on the headers, the results are successful in all
fields except "Level".  It is a numeric field, like "Points", that has
decimal values (ie.  3.4, 5.2, 6.9, 6.1, etc).  What am I doing
wrong?
Thanks for any help,
Chris R. Simmons

Quote:
>>In the general declarations the following objects are diminsioned

_________________________________________
Dim conAR                 As ADODB.Connection
Dim ARSunDn            As ADODB.Command
Dim WithEvents rsAR  As ADODB.Recordset
_________________________________________
Private Sub SelectAll()
   'Set up SQL for all records

   mstrSQL = "Select [Test], [Title], [Author], [Level], [Points], [ISBN]
from ARSunDn"
End Sub
_________________________________________
Private Sub dbgDisplay_HeadClick(ByVal ColIndex As Integer)
   'sorts the record source of the Data control based on which column the
user clicked.

   On Error GoTo HandleErrors:
   mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & _
              dbgDisplay.Columns(ColIndex).DataField & ", Title"

   Debug.Print mstrSQL
   With rsAR
      .Close
      .Open mstrSQL, conAR, adOpenStatic, adLockReadOnly, adCmdText
   End With
   Call FillGrid
   dbgDisplay.Refresh
   Call DisplayRecordCount

dbgDisplay_HeadClick_Exit:
   Exit Sub

HandleErrors:
   Dim strMessage    As String
   Dim errDBErr      As ADODB.Error
   Dim lngResponse   As Long

   strMessage = "Error occured, Because:" & vbCr & vbCr
   If conAR.Errors.Count = 0 Then
      strMessage = strMessage & Err.Description & "(" & Err.Number & ")" &
vbCr
   Else
      For Each errDBErr In conAR.Errors
         strMessage = strMessage & errDBErr.Description & _
         "(" & errDBErr.Number & ")" _
         & vbCr
      Next
   End If
   strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
                "Continue without Retrying (No), or Cancel this Operation?"
   lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database Error...")
   If lngResponse = vbYes Then
      Resume
   ElseIf lngResponse = vbNo Then
      Resume Next
   Else
      Resume dbgDisplay_HeadClick_Exit
     '''''''''' rsAR.Close
   End If
End Sub
___________________________________________________

Quote:
>>When I click on all the data grid headers, all fields work except when
>>   I click on the "Level" field.
>> Resulting values for the Debug.Print mstrSQL (see code above)

SELECT * FROM ARSunDn ORDER BY Test, Title
SELECT * FROM ARSunDn ORDER BY Title, Title
SELECT * FROM ARSunDn ORDER BY Author, Title
SELECT * FROM ARSunDn ORDER BY Points, Title
SELECT * FROM ARSunDn ORDER BY ISBN, Title
Quote:
>>All the above work.  But, the "Level" field produces an error.

SELECT * FROM ARSunDn ORDER BY Level, Title

Quote:
>> The resulting error says the syntax is wrong when using the ORDER BY
operator
>>  with the "Level" field.
>> All other fields seem to find no problems with the same syntax?

Error occured, Because:
Syntax error in ORDER BY clause.(-2147217900)
Quote:
>> Thank you for any help you can afford!



Fri, 05 Dec 2003 07:53:04 GMT  
 SQL Syntax error in ORDER BY clause.(-2147217900)
Level is a reserved word.

Change your query to:

SELECT * FROM ARSunDn ORDER BY [Level], Title



Quote:
> I have a data grid that shows 6 fields (see below).  When I try
> to sort by clicking on the headers, the results are successful in all
> fields except "Level".  It is a numeric field, like "Points", that has
> decimal values (ie.  3.4, 5.2, 6.9, 6.1, etc).  What am I doing
> wrong?
> Thanks for any help,
> Chris R. Simmons

> >>In the general declarations the following objects are diminsioned
> _________________________________________
> Dim conAR                 As ADODB.Connection
> Dim ARSunDn            As ADODB.Command
> Dim WithEvents rsAR  As ADODB.Recordset
> _________________________________________
> Private Sub SelectAll()
>    'Set up SQL for all records

>    mstrSQL = "Select [Test], [Title], [Author], [Level], [Points], [ISBN]
> from ARSunDn"
> End Sub
> _________________________________________
> Private Sub dbgDisplay_HeadClick(ByVal ColIndex As Integer)
>    'sorts the record source of the Data control based on which column the
> user clicked.

>    On Error GoTo HandleErrors:
>    mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & _
>               dbgDisplay.Columns(ColIndex).DataField & ", Title"

>    Debug.Print mstrSQL
>    With rsAR
>       .Close
>       .Open mstrSQL, conAR, adOpenStatic, adLockReadOnly, adCmdText
>    End With
>    Call FillGrid
>    dbgDisplay.Refresh
>    Call DisplayRecordCount

> dbgDisplay_HeadClick_Exit:
>    Exit Sub

> HandleErrors:
>    Dim strMessage    As String
>    Dim errDBErr      As ADODB.Error
>    Dim lngResponse   As Long

>    strMessage = "Error occured, Because:" & vbCr & vbCr
>    If conAR.Errors.Count = 0 Then
>       strMessage = strMessage & Err.Description & "(" & Err.Number & ")" &
> vbCr
>    Else
>       For Each errDBErr In conAR.Errors
>          strMessage = strMessage & errDBErr.Description & _
>          "(" & errDBErr.Number & ")" _
>          & vbCr
>       Next
>    End If
>    strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
>                 "Continue without Retrying (No), or Cancel this
Operation?"
>    lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database Error...")
>    If lngResponse = vbYes Then
>       Resume
>    ElseIf lngResponse = vbNo Then
>       Resume Next
>    Else
>       Resume dbgDisplay_HeadClick_Exit
>      '''''''''' rsAR.Close
>    End If
> End Sub
> ___________________________________________________

> >>When I click on all the data grid headers, all fields work except when
> >>   I click on the "Level" field.

> >> Resulting values for the Debug.Print mstrSQL (see code above)
> SELECT * FROM ARSunDn ORDER BY Test, Title
> SELECT * FROM ARSunDn ORDER BY Title, Title
> SELECT * FROM ARSunDn ORDER BY Author, Title
> SELECT * FROM ARSunDn ORDER BY Points, Title
> SELECT * FROM ARSunDn ORDER BY ISBN, Title
> >>All the above work.  But, the "Level" field produces an error.
> SELECT * FROM ARSunDn ORDER BY Level, Title

> >> The resulting error says the syntax is wrong when using the ORDER BY
> operator
> >>  with the "Level" field.
> >> All other fields seem to find no problems with the same syntax?

> Error occured, Because:
> Syntax error in ORDER BY clause.(-2147217900)

> >> Thank you for any help you can afford!



Fri, 05 Dec 2003 16:30:06 GMT  
 SQL Syntax error in ORDER BY clause.(-2147217900)
Thank you Richard!
    That worked......

    mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & "[Level]" & ", Title"

Chris



Quote:
> Level is a reserved word.

> Change your query to:

> SELECT * FROM ARSunDn ORDER BY [Level], Title



> > I have a data grid that shows 6 fields (see below).  When I try
> > to sort by clicking on the headers, the results are successful in all
> > fields except "Level".  It is a numeric field, like "Points", that has
> > decimal values (ie.  3.4, 5.2, 6.9, 6.1, etc).  What am I doing
> > wrong?
> > Thanks for any help,
> > Chris R. Simmons

> > >>In the general declarations the following objects are diminsioned
> > _________________________________________
> > Dim conAR                 As ADODB.Connection
> > Dim ARSunDn            As ADODB.Command
> > Dim WithEvents rsAR  As ADODB.Recordset
> > _________________________________________
> > Private Sub SelectAll()
> >    'Set up SQL for all records

> >    mstrSQL = "Select [Test], [Title], [Author], [Level], [Points],
[ISBN]
> > from ARSunDn"
> > End Sub
> > _________________________________________
> > Private Sub dbgDisplay_HeadClick(ByVal ColIndex As Integer)
> >    'sorts the record source of the Data control based on which column
the
> > user clicked.

> >    On Error GoTo HandleErrors:
> >    mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & _
> >               dbgDisplay.Columns(ColIndex).DataField & ", Title"

> >    Debug.Print mstrSQL
> >    With rsAR
> >       .Close
> >       .Open mstrSQL, conAR, adOpenStatic, adLockReadOnly, adCmdText
> >    End With
> >    Call FillGrid
> >    dbgDisplay.Refresh
> >    Call DisplayRecordCount

> > dbgDisplay_HeadClick_Exit:
> >    Exit Sub

> > HandleErrors:
> >    Dim strMessage    As String
> >    Dim errDBErr      As ADODB.Error
> >    Dim lngResponse   As Long

> >    strMessage = "Error occured, Because:" & vbCr & vbCr
> >    If conAR.Errors.Count = 0 Then
> >       strMessage = strMessage & Err.Description & "(" & Err.Number & ")"
&
> > vbCr
> >    Else
> >       For Each errDBErr In conAR.Errors
> >          strMessage = strMessage & errDBErr.Description & _
> >          "(" & errDBErr.Number & ")" _
> >          & vbCr
> >       Next
> >    End If
> >    strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
> >                 "Continue without Retrying (No), or Cancel this
> Operation?"
> >    lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database Error...")
> >    If lngResponse = vbYes Then
> >       Resume
> >    ElseIf lngResponse = vbNo Then
> >       Resume Next
> >    Else
> >       Resume dbgDisplay_HeadClick_Exit
> >      '''''''''' rsAR.Close
> >    End If
> > End Sub
> > ___________________________________________________

> > >>When I click on all the data grid headers, all fields work except when
> > >>   I click on the "Level" field.

> > >> Resulting values for the Debug.Print mstrSQL (see code above)
> > SELECT * FROM ARSunDn ORDER BY Test, Title
> > SELECT * FROM ARSunDn ORDER BY Title, Title
> > SELECT * FROM ARSunDn ORDER BY Author, Title
> > SELECT * FROM ARSunDn ORDER BY Points, Title
> > SELECT * FROM ARSunDn ORDER BY ISBN, Title
> > >>All the above work.  But, the "Level" field produces an error.
> > SELECT * FROM ARSunDn ORDER BY Level, Title

> > >> The resulting error says the syntax is wrong when using the ORDER BY
> > operator
> > >>  with the "Level" field.
> > >> All other fields seem to find no problems with the same syntax?

> > Error occured, Because:
> > Syntax error in ORDER BY clause.(-2147217900)

> > >> Thank you for any help you can afford!



Fri, 05 Dec 2003 20:53:43 GMT  
 SQL Syntax error in ORDER BY clause.(-2147217900)
I'm still muddling with SQL expressions...
This is in the same program that is described in the preceding posts.
There seems to be something wrong with this expression, which
when written VB looks like this:

mstrSQL = "SELECT [Test], [Title], [Author], [Level], [Points], [ISBN] " & _
             "FROM ARSunDn WHERE [Level] BETWEEN " & gstrFrom & " AND " & _
              gstrTo & ";"

And when Debug.Print shows the string it looks like this:

SELECT [Test], [Title], [Author], [Level], [Points], [ISBN] FROM ARSunDn
WHERE [Level] BETWEEN 1.0 AND 3.0;

The error message I am getting is this:

Data type mismatch in criteria expression.(-2147217913)

Any help with my SQL problem here is greatly appreciated.
Thanks,
Chris



Quote:
> Thank you Richard!
>     That worked......

>     mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & "[Level]" & ", Title"

> Chris



> > Level is a reserved word.

> > Change your query to:

> > SELECT * FROM ARSunDn ORDER BY [Level], Title



> > > I have a data grid that shows 6 fields (see below).  When I try
> > > to sort by clicking on the headers, the results are successful in all
> > > fields except "Level".  It is a numeric field, like "Points", that has
> > > decimal values (ie.  3.4, 5.2, 6.9, 6.1, etc).  What am I doing
> > > wrong?
> > > Thanks for any help,
> > > Chris R. Simmons

> > > >>In the general declarations the following objects are diminsioned
> > > _________________________________________
> > > Dim conAR                 As ADODB.Connection
> > > Dim ARSunDn            As ADODB.Command
> > > Dim WithEvents rsAR  As ADODB.Recordset
> > > _________________________________________
> > > Private Sub SelectAll()
> > >    'Set up SQL for all records

> > >    mstrSQL = "Select [Test], [Title], [Author], [Level], [Points],
> [ISBN]
> > > from ARSunDn"
> > > End Sub
> > > _________________________________________
> > > Private Sub dbgDisplay_HeadClick(ByVal ColIndex As Integer)
> > >    'sorts the record source of the Data control based on which column
> the
> > > user clicked.

> > >    On Error GoTo HandleErrors:
> > >    mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & _
> > >               dbgDisplay.Columns(ColIndex).DataField & ", Title"

> > >    Debug.Print mstrSQL
> > >    With rsAR
> > >       .Close
> > >       .Open mstrSQL, conAR, adOpenStatic, adLockReadOnly, adCmdText
> > >    End With
> > >    Call FillGrid
> > >    dbgDisplay.Refresh
> > >    Call DisplayRecordCount

> > > dbgDisplay_HeadClick_Exit:
> > >    Exit Sub

> > > HandleErrors:
> > >    Dim strMessage    As String
> > >    Dim errDBErr      As ADODB.Error
> > >    Dim lngResponse   As Long

> > >    strMessage = "Error occured, Because:" & vbCr & vbCr
> > >    If conAR.Errors.Count = 0 Then
> > >       strMessage = strMessage & Err.Description & "(" & Err.Number &
")"
> &
> > > vbCr
> > >    Else
> > >       For Each errDBErr In conAR.Errors
> > >          strMessage = strMessage & errDBErr.Description & _
> > >          "(" & errDBErr.Number & ")" _
> > >          & vbCr
> > >       Next
> > >    End If
> > >    strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
> > >                 "Continue without Retrying (No), or Cancel this
> > Operation?"
> > >    lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database
Error...")
> > >    If lngResponse = vbYes Then
> > >       Resume
> > >    ElseIf lngResponse = vbNo Then
> > >       Resume Next
> > >    Else
> > >       Resume dbgDisplay_HeadClick_Exit
> > >      '''''''''' rsAR.Close
> > >    End If
> > > End Sub
> > > ___________________________________________________

> > > >>When I click on all the data grid headers, all fields work except
when
> > > >>   I click on the "Level" field.

> > > >> Resulting values for the Debug.Print mstrSQL (see code above)
> > > SELECT * FROM ARSunDn ORDER BY Test, Title
> > > SELECT * FROM ARSunDn ORDER BY Title, Title
> > > SELECT * FROM ARSunDn ORDER BY Author, Title
> > > SELECT * FROM ARSunDn ORDER BY Points, Title
> > > SELECT * FROM ARSunDn ORDER BY ISBN, Title
> > > >>All the above work.  But, the "Level" field produces an error.
> > > SELECT * FROM ARSunDn ORDER BY Level, Title

> > > >> The resulting error says the syntax is wrong when using the ORDER
BY
> > > operator
> > > >>  with the "Level" field.
> > > >> All other fields seem to find no problems with the same syntax?

> > > Error occured, Because:
> > > Syntax error in ORDER BY clause.(-2147217900)

> > > >> Thank you for any help you can afford!



Sat, 13 Dec 2003 09:55:13 GMT  
 SQL Syntax error in ORDER BY clause.(-2147217900)
What is the data type of the Level field?

On Tue, 26 Jun 2001 01:55:13 GMT, "Chris R. Simmons"

Quote:

>I'm still muddling with SQL expressions...
>This is in the same program that is described in the preceding posts.
>There seems to be something wrong with this expression, which
>when written VB looks like this:

>mstrSQL = "SELECT [Test], [Title], [Author], [Level], [Points], [ISBN] " & _
>             "FROM ARSunDn WHERE [Level] BETWEEN " & gstrFrom & " AND " & _
>              gstrTo & ";"

>And when Debug.Print shows the string it looks like this:

>SELECT [Test], [Title], [Author], [Level], [Points], [ISBN] FROM ARSunDn
>WHERE [Level] BETWEEN 1.0 AND 3.0;

>The error message I am getting is this:

>Data type mismatch in criteria expression.(-2147217913)

>Any help with my SQL problem here is greatly appreciated.
>Thanks,
>Chris



>> Thank you Richard!
>>     That worked......

>>     mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & "[Level]" & ", Title"

>> Chris



>> > Level is a reserved word.

>> > Change your query to:

>> > SELECT * FROM ARSunDn ORDER BY [Level], Title



>> > > I have a data grid that shows 6 fields (see below).  When I try
>> > > to sort by clicking on the headers, the results are successful in all
>> > > fields except "Level".  It is a numeric field, like "Points", that has
>> > > decimal values (ie.  3.4, 5.2, 6.9, 6.1, etc).  What am I doing
>> > > wrong?
>> > > Thanks for any help,
>> > > Chris R. Simmons

>> > > >>In the general declarations the following objects are diminsioned
>> > > _________________________________________
>> > > Dim conAR                 As ADODB.Connection
>> > > Dim ARSunDn            As ADODB.Command
>> > > Dim WithEvents rsAR  As ADODB.Recordset
>> > > _________________________________________
>> > > Private Sub SelectAll()
>> > >    'Set up SQL for all records

>> > >    mstrSQL = "Select [Test], [Title], [Author], [Level], [Points],
>> [ISBN]
>> > > from ARSunDn"
>> > > End Sub
>> > > _________________________________________
>> > > Private Sub dbgDisplay_HeadClick(ByVal ColIndex As Integer)
>> > >    'sorts the record source of the Data control based on which column
>> the
>> > > user clicked.

>> > >    On Error GoTo HandleErrors:
>> > >    mstrSQL = "SELECT * FROM ARSunDn ORDER BY " & _
>> > >               dbgDisplay.Columns(ColIndex).DataField & ", Title"

>> > >    Debug.Print mstrSQL
>> > >    With rsAR
>> > >       .Close
>> > >       .Open mstrSQL, conAR, adOpenStatic, adLockReadOnly, adCmdText
>> > >    End With
>> > >    Call FillGrid
>> > >    dbgDisplay.Refresh
>> > >    Call DisplayRecordCount

>> > > dbgDisplay_HeadClick_Exit:
>> > >    Exit Sub

>> > > HandleErrors:
>> > >    Dim strMessage    As String
>> > >    Dim errDBErr      As ADODB.Error
>> > >    Dim lngResponse   As Long

>> > >    strMessage = "Error occured, Because:" & vbCr & vbCr
>> > >    If conAR.Errors.Count = 0 Then
>> > >       strMessage = strMessage & Err.Description & "(" & Err.Number &
>")"
>> &
>> > > vbCr
>> > >    Else
>> > >       For Each errDBErr In conAR.Errors
>> > >          strMessage = strMessage & errDBErr.Description & _
>> > >          "(" & errDBErr.Number & ")" _
>> > >          & vbCr
>> > >       Next
>> > >    End If
>> > >    strMessage = strMessage & vbCr & "Do you want to Retry (Yes)," & _
>> > >                 "Continue without Retrying (No), or Cancel this
>> > Operation?"
>> > >    lngResponse = MsgBox(strMessage, vbYesNoCancel, "Database
>Error...")
>> > >    If lngResponse = vbYes Then
>> > >       Resume
>> > >    ElseIf lngResponse = vbNo Then
>> > >       Resume Next
>> > >    Else
>> > >       Resume dbgDisplay_HeadClick_Exit
>> > >      '''''''''' rsAR.Close
>> > >    End If
>> > > End Sub
>> > > ___________________________________________________

>> > > >>When I click on all the data grid headers, all fields work except
>when
>> > > >>   I click on the "Level" field.

>> > > >> Resulting values for the Debug.Print mstrSQL (see code above)
>> > > SELECT * FROM ARSunDn ORDER BY Test, Title
>> > > SELECT * FROM ARSunDn ORDER BY Title, Title
>> > > SELECT * FROM ARSunDn ORDER BY Author, Title
>> > > SELECT * FROM ARSunDn ORDER BY Points, Title
>> > > SELECT * FROM ARSunDn ORDER BY ISBN, Title
>> > > >>All the above work.  But, the "Level" field produces an error.
>> > > SELECT * FROM ARSunDn ORDER BY Level, Title

>> > > >> The resulting error says the syntax is wrong when using the ORDER
>BY
>> > > operator
>> > > >>  with the "Level" field.
>> > > >> All other fields seem to find no problems with the same syntax?

>> > > Error occured, Because:
>> > > Syntax error in ORDER BY clause.(-2147217900)

>> > > >> Thank you for any help you can afford!



Mon, 15 Dec 2003 09:34:15 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Error-2147217900, Syntax error in FROM clause

2. -2147217900 Error: Syntax error in INSERT INTO statement

3. Error 3131 Syntax error in FROM clause - using SQL accessing external ODBC problem

4. Error 3131 Syntax error in FROM clause - using SQL accessing external ODBC problem

5. Run-time error -2147217900 (80040e14) "Syntax error in FROM clause"

6. SQL Syntax error in FROM Clause...but WHY?

7. FROM clause syntax error with sensible SQL query to Excel database

8. error -2147217900 instead of error -2147217873 from SQLserver

9. runtime error -2147217900

10. Runtime Error 2147217900 ODBC with Access

11. 2147217900 error message

12. Run-time error -2147217900

 

 
Powered by phpBB® Forum Software