Results 1 to 10 of 10

Thread: Select statement query throwing OleDbException error

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    44

    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

    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)

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Select statement query throwing OleDbException error

    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)

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Select statement query throwing OleDbException error

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    44

    Re: Select statement query throwing OleDbException error

    Quote Originally Posted by FunkyDexter View Post
    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:

    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.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Select statement query throwing OleDbException error

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Select statement query throwing OleDbException error

    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 *?
    My usual boring signature: Nothing

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Select statement query throwing OleDbException error

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    44

    Re: Select statement query throwing OleDbException error

    Quote Originally Posted by techgnome View Post
    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

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2013
    Posts
    44

    Re: Select statement query throwing OleDbException error

    Any further help with this problem please. Thanks

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Select statement query throwing OleDbException error

    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.
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width