Select statement query throwing OleDbException error

Hi
I am trying to create a paging system and came across a tutorial that included code for achieving this. However, when I run my query, it throws the quoted error. I have double checked code but cannot see error. Can someone point out my mistake. Thanks

Quote:

---

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

---

Code:

---

Dim Row_Per_Page As Integer = 4
Dim TotRows As Integer = 17
Dim Page_Number As Integer = 2

Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP '" & Row_Per_Page & "' *, Count(*) As '" & TotRows & "' From [Select Top('" & TotRows & "' - (('" & Page_Number & "' - 1) * '" & Row_Per_Page & "'))From Postings Order By [Date] DESC] Order By [Date] ASC", oledbCnn)

---

Rather than just showing us the code to create the SQL statement, it would be far more useful to also show us the SQL statement that is created.

As the issue is related to an SQL statement, you should also tell us which database system you are using (eg: SQL Server Express 2008 R2)

I think the propblem is that you're wrapping numbers in quotes. E.g. TOP '" & Row_Per_Page & "' *. That's not going to produce a valid sql statement. As Si said though, it would be far easier to see the problem if we could see the actual SQL being produced.

Quote:

---

Originally Posted by FunkyDexter

I think the propblem is that you're wrapping numbers in quotes. E.g. TOP '" & Row_Per_Page & "' *. That's not going to produce a valid sql statement. As Si said though, it would be far easier to see the problem if we could see the actual SQL being produced.

---

Here is the updated code I am using along with the sql result. The db is access btw. This statement however, is producing an error of:

Quote:

---

The Microsoft Office Access database engine cannot find the input table or query 'Select Top(17 - ((2 - 1) * 4))From Postings Order By Date DESC'. Make sure it exists and that its name is spelled correctly.

---

Code:

---

Dim myStatement As String = "Select TOP " & Row_Per_Page & " *, Count(*) As " & TotRows & " From [Select Top(" & TotRows & " - ((" & Page_Number & " - 1) * " & Row_Per_Page & "))From Postings Order By Date DESC] Order By Date ASC"
            Debug.Print(myStatement) '<--- This will give you the string that was built in your debug window to review
            Dim oledbCmd As OleDbCommand = New OleDbCommand(myStatement, oledbCnn)

---

sql result

Code:

---

myStatement        "Select TOP 4 *, Count(*) As 17 From [Select Top(17 - ((2 - 1) * 4))From Postings Order By Date DESC] Order By Date ASC"        String

---

Many thanks for your help.

you told it how many to get, but not what to get....




waaaaait... .wait jsut one second...what's with the square brackets in there?


-tg

That's the only thing you're confused by? The square bracket? I had a hard time figuring out what:

TOP 4 *,

did. I don't use * unless it's throwaway code. Still, I see that that would get all columns followed by a Count(), which is an aggregate. Doesn't that require that there be a GROUP BY statement for EVERY column contained within the vague *?

Yeah... I think we can all agree that the query has a lot of issues... I'm just not sure what the OP is really trying to do...


-tg

Quote:

---

Originally Posted by techgnome

Yeah... I think we can all agree that the query has a lot of issues... I'm just not sure what the OP is really trying to do...


-tg

---

What I am trying to do is create a pagination to show so many records at a time and when that number is reached click a next button for the next 100. Now I am a new user to vb.net and got this code from a site that showed pagination as a tutorial. Here is the site: http://www.jertix.org/en/blog/progra...ms-access.html

How can I get this code to work Thanks

Any further help with this problem please. Thanks

Whenever I have a problem with some complicated SQL statement, which is more often than I would like to admit, I find that the best solution is not to solve it in code. Take the DB of your choice, and Access is pretty good for this, and copy the SQL into the query builder, or just build the query in there. In this case, there's the inner query and the outer query. I would take that inner query, move that into Access, and make sure that it is returning what I expect it to. Access likes to clutter up the SQL with lots of extra parentheses, which is annoying, but they don't cause any harm.

Once you have the inner query working, you set that aside (whenever I am working on a query I have Notepad open, as well, so that I can store things in there, which is nice, as errors can often cause the text of the query to reset or clear). Ideally, you would then work on the outer query without dealing with the inner query, but that may be hard to do, in this case, so perhaps the thing to do is to move the whole thing, including the, now tested, inner query.

The reason for doing this is that the query builder in Access will often give you more useful information than you would get by whacking away at code in VB and trapping the resulting exceptions.

We've pretty well covered that the square brackets need to be replaced by normal parentheses. I would suggest writing out the columns rather than EVER using *, though I realize this can be horribly tedious (and I don't always do it myself). The * is a nice shorthand, but even if you really DO need all the columns, this is less efficient than explicitly writing out the columns. If you don't need all the columns, then using * will just result in moving lots of extra stuff. That's fine for a simple program, but not a good habit to form.

In any case, I don't believe that query will run in any form, even with the parentheses changed, but the query editor might give you a more informative message: If you have an aggregate such as a Count or Sum, then you need a Group By statement that includes all the non-aggregated columns. Are those the only problems? Hard to say. The error messages you get on SQL in code are often rather opaque. The query editor will give you more verbose messages, if not necessarily more informative messages.