sql server 2008 offset keyword not working.

Dear All,

Below query is not working in sql server 2008. OFFSET keyword not available in this sql version. I want to get next 10 record every time on button click.

select * from users order by userid asc OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

kindly suggest if there is another option for this senario.

Thank you in advance.

Hey try this

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;
namespace DataSync
{
public partial class FrmTest : Form
{
public FrmTest()
{
InitializeComponent();
}
int offSetVal = 0;
int nxtOffSetVal = 10;
DataTable dtData = new DataTable();
private void FrmTest_Load(object sender, EventArgs e)
{
string strQuery = "SELECT * FROM TBLEMPLOYEE";
SqlConnection sqlCon = new SqlConnection("your connection string");
SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dtData);
DataTable dtSendData = dtData.Clone();
DataRow[] drRow = dtData.AsEnumerable().Skip(offSetVal).Take(nxtOffSetVal).ToArray();
for (int i = 0; i < drRow.Length; i++)
dtSendData.ImportRow(drRow[i]);
dataGridView1.DataSource = dtSendData;
offSetVal = offSetVal + 10;
}
private void btnNext_Click(object sender, EventArgs e)
{
DataTable dtSendData = dtData.Clone();
DataRow[] drRow = dtData.AsEnumerable().Skip(offSetVal).Take(nxtOffSetVal).ToArray();
for (int i = 0; i < drRow.Length; i++)
dtSendData.ImportRow(drRow[i]);
dataGridView1.DataSource = dtSendData;
offSetVal = offSetVal + 10;
}
private void btnClear_Click(object sender, EventArgs e)
{
offSetVal = 0;
}
}
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaimin Shethiya6y ago
Hello Mayur,

In SQL OFFSET keyword is supported by after SQL 2012 so you can updated you SQL Servce or USE BETWEEN Statement for fetching a data.

Thanks
Amit Mohanty7y ago
Hey try this
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;

namespace DataSync
{
    public partial class FrmTest : Form
    {
        public FrmTest()
        {
            InitializeComponent();
        }

        int offSetVal = 0;
        int nxtOffSetVal = 10;
        DataTable dtData = new DataTable();

        private void FrmTest_Load(object sender, EventArgs e)
        {
            string strQuery = "SELECT * FROM TBLEMPLOYEE";
            SqlConnection sqlCon = new SqlConnection("your connection string");
            SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            sqlDa.Fill(dtData);

            DataTable dtSendData = dtData.Clone();
            DataRow[] drRow = dtData.AsEnumerable().Skip(offSetVal).Take(nxtOffSetVal).ToArray();
            for (int i = 0; i < drRow.Length; i++)
                dtSendData.ImportRow(drRow[i]);
            dataGridView1.DataSource = dtSendData;

            offSetVal = offSetVal + 10;
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            DataTable dtSendData = dtData.Clone();
            DataRow[] drRow = dtData.AsEnumerable().Skip(offSetVal).Take(nxtOffSetVal).ToArray();
            for (int i = 0; i < drRow.Length; i++)
                dtSendData.ImportRow(drRow[i]);
            dataGridView1.DataSource = dtSendData;

            offSetVal = offSetVal + 10;
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            offSetVal = 0;
        }
    }
}