halo guys, I having problem executing the SQL statement in the visual studio but when in the MySQL it work fine. Here are my code.

   Dim sql As String = "SELECT * FROM (SELECT @row:= @row + 1 AS rowNum, std.* FROM Student std ,(SELECT @row:= 0) r) AS t where rowNum = " & i & ""

Please help.thanks

It would help to know the error or the exact nature of the "problem" you are having and possibly know how you are trying to use this string.

As a rule of thumb when having problems executing a command in a different system, start by verifying the command you are sending. ie debug.print sql instead of executing it (or when you get the error) and copy/paste the output from the immediate pane to your db and make sure your statement is correct.

I was once asked (by the maintainer of an in-house app), why his SQL query ran so slowly. I had a look at it and was appalled to see that the query consisted of almost 20 lines of concatenated text. I asked him what the query was supposed to do and he couldn't tell me.

Which is why I want to know what it is you are trying to do so I can possibly suggest a simpler alternative.

It would be great if you could post the error as with just one line of sql, I have no idea what you are doing wrong.

Since you said that the sql works, I am guessing you did not add value into your parameter in your sql?

Also, since you are using parameters sql, why did you not just sent the i variable as a parameter as well?

Dim sql As String = "SELECT * FROM (SELECT @row:= @row + 1 AS rowNum, std.* FROM Student std ,(SELECT @row:= 0) r) AS t where rowNum = " & **i **& ""

To:

"SELECT * FROM (SELECT @row:= @row + 1 AS rowNum, std.* FROM Student std ,(SELECT @row:= 0) r) AS t where rowNum = @rownNum"

This article has been dead for over six months. Start a new discussion instead.