locked
C# - How to limit rows from database RRS feed

  • Question

  • How to limit returned rows from database if the limit value is depending on the user?
    For example I want to view only n rows
    where n value is from textbox

    I tried this

     Connection.open();
            OleDbCommand c = new OleDbCommand("select top" + textbox1.Text + " * from listofbooks where title like '%' + @d+'%' or author like '%' + @d + '%' or publishingcompany like '%' + @d + '%' order by id", Connection.connection);
            c.Parameters.AddWithValue("@d", txtSearchbook.Text);
            OleDbDataReader r = c.ExecuteReader();

    And even this

     Connection.open();
            OleDbCommand c = new OleDbCommand("select top"+10+" * from listofbooks where title like '%' + @d+'%' or author like '%' + @d + '%' or publishingcompany like '%' + @d + '%' order by id", Connection.connection);
            c.Parameters.AddWithValue("@d", txtSearchbook.Text);
            OleDbDataReader r = c.ExecuteReader();

    Both didn't work.

    How to do this?
    Thank you in advance.
    Sunday, May 18, 2014 5:35 AM

Answers

  • One thing I notice is a missing blank space after the keyword "top".

    Also, you are using OleDb but you did not say which type of database you are connecting to. You should be aware that not all of them support the TOP clause. For instance, it works with SQL Server, but it doesn't work on Oracle.

    • Marked as answer by neverrMynd Sunday, May 18, 2014 8:53 AM
    Sunday, May 18, 2014 7:56 AM

All replies

  • One thing I notice is a missing blank space after the keyword "top".

    Also, you are using OleDb but you did not say which type of database you are connecting to. You should be aware that not all of them support the TOP clause. For instance, it works with SQL Server, but it doesn't work on Oracle.

    • Marked as answer by neverrMynd Sunday, May 18, 2014 8:53 AM
    Sunday, May 18, 2014 7:56 AM
  • It could easily be done if using Linq and the Take() function.

    http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    You seem to be using the Publishing Company database, a tutorial database.

    The below tutorial is using the Publishing Company database too.

    http://www.vbforums.com/showthread.php?540421-Tutorial-An-Introduction-to-the-ADO-NET-Entity-Framework

    The above link is for VB, but the C# link is there at the top of the page too. But the C# link has lost the screen shots for the Windows forms to be created, but the VB version has the form screen shots.

    Sunday, May 18, 2014 8:43 AM
  • thank you
    now it's working.
    I just added a space after 'top' keyword.
    Sunday, May 18, 2014 8:53 AM