How to fix this syntax error

[kiwis]

Dim sqlqry2 As String = "SELECT * FROM RSSFeeds ORDER BY NewsID DESC LIMIT " & RSSReturnValue
            Dim conn2 As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(My.Settings.DatabaseConnectionString)
            Dim cmd2 As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand(sqlqry2, conn2)
            conn2.Open()

My error is "Syntax error in ORDER BY clause."

mysql2 = "SELECT * FROM RSSFeeds ORDER BY NewsID DESC LIMIT 150"


It works on my webserver but not VB how come?

[Pradeep1210]

I think that's mySQL syntax, and I doubt it would work with any other database.
Are you connecting to MySQL?

[keystone_paul]

Its not VB thats telling you its a syntax error its your database provider. Are you referencing the same database?

I'm not a database expert but I've never seen "LIMIT" before - I've always used "TOP", ie

"SELECT TOP 150 * FROM RSSFeeds ORDER BY NewsID DESC"

[gep13]

@paul the OP would appear to be using MySQL as the database backend, and the SQL syntax is different between SQL Server and MySQL. You can see the LIMIT keyword being used here:

http://dev.mysql.com/doc/refman/5.1/en/select.html

@kiwis can you confirm the actual SQL statement that is sent to the server? i.e. what is the value of RSSReturnValue is?

We will need some more information before anyone will be able to help you.

If you are actually connecting to MySQL, you might want to think about using the managed provider for it, you can find a link to it in my signature.

Gary

[keystone_paul]

Ah there you go - my SQL experience is all from either Microsoft (SQL Server, Access) or Ingres - "proper" databases

My initial thought was that the OP might be using two different databases, ie the website is accessing MySQL but the desktop app is accessing a SQL Server database, after all if the syntax of a query is acceptible for a given database from one application I wouldn't have throught that it should fail on the same database connected from another application (even if they are using different libraries for connecting?)

[su ki]

hey kiwis

if its true what @keystone_paul is saying that u r using two different database then you may choose following approach i have used this in one of my project when some clients told us to provide mysql support along with mssql

you may use conditional compilation for it

'on top of the class or module
#if MySql
using MySql.Data;
using MySql.Data.MySqlClient;
#else
using System.Data;
using System.Data.SqlClient;
#endif

' where you want to use 
#if MySql
            MySqlConnection oConn = new MySqlConnection(sMySqlConnectionString);
            MySqlDataAdapter oAdp = new MySqlDataAdapter();
#else
            SqlConnection oConn = new SqlConnection(sSqlConnectionString);
            SqlDataAdapter oAdp = new SqlDataAdapter();
#endif

[gep13]

Hey,

In this situation, the use of conditional compiliation is not really justified, in my opinion,

If, and we don't know if this is the case yet, the OP wants to support multiple databases, then really they should implement a provider architecture using generic database factory objects rather than database specific ones, where the database being used is set in the configuration file for the application.

In addition, the code that you have posted is C#, but this is the VB.Net Forum.

Thanks

Gary

[Pradeep1210]

just as gep said, I would avoid using conditional compilation. Instead a configuration in the configuration file should do the work better.

[su ki]

@Gary , sorry for this actually it was from one of my project which is in c#

now this is in vb.net

#If MySql Then
Imports MySql.Data
Imports MySql.Data.MySqlClient
#Else
Imports MySql.Data
Imports System.Data.SqlClient
#End If

#If MySql Then
            Dim oConn As New MySqlConnection(sMySqlConnectionString)
            Dim oAdp As New MySqlDataAdapter()
#Else
            Dim oConn As New SqlConnection(sSqlConnectionString)
            Dim oAdp As New SqlDataAdapter()
#End If

[techgnome]

OK... why do we think the OP wants to support multiple databases (for which there are better ways than using conditional compiling)...

At any rate, If memory serves, the LIMIT construct takes two parameters, a starting index and a rows count:
LIMIT 0,150 .... start with the first record and return 150 rows...

also, if yo uare connecting to MySQL, you''ll want to get the MySQL .NET Connector, install it, then use the MySQLClient namespace and not OLEDB... which might be part of the problem, as LIMIT isn't part of the SQL standard (yet).

-tg

[gep13]

Hey,

For clarity:

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Gary