SQL Query

[mancode1007]

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

[Reverend Jim]

In English, what is your query supposed to do and what happens when you run it?

[adam_k]

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.

[Reverend Jim]

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.

[wen_cai]

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"