Next record from sql server

[kanuri1]

I'm retrieving immediate next record from database table using empcd(employeecode) [for which code is given below].

I wanna retrieve "next record" details from SQL SERVER 2005 with a Button Click Event but not using "EMPLOYEE ID". (SqlClient.SqlConnection)

i want to access database table nextrecord directly using either query or Logic.

Protected Sub next_rec_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles next_rec.Click
        Dim cnt As String
        Dim dr As SqlDataReader

        cn.Open()
        Dim com As New SqlCommand("select empcd from employeepersonneldetails where empcd='" & empcd.Text & "'", cn)
        dr = com.ExecuteReader()
        Dim ds As DataSet

        If dr.HasRows Then
            dr.Read()
            'empcd.Text = dr("empcd").ToString
            cnt = dr(0).ToString + 1

        End If
        cn.Close()

        cn.Open()
        
Dim com1 As New SqlCommand("select empcd from employeepersonneldetails where empcd='" & cnt & "'", cn)
        dr = com1.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            empcd.Text = dr("empcd").ToString
            ''MsgBox(cnt)
        End If

        cn.Close()
    End Sub

[kvprajapati]

>i want to access database table nextrecord directly using either query or Logic.

Use session.

Sub Page_load()
     If Not IsPostBack Then
        ....
        Dim adp as new SqlDataAdapter("select * from tab1","connection_string_here")
       Dim dt as new DataTable
       adp.Fill(dt)

       Session("dt")=dt
    End If
 End Sub

[SolutionExpert]

Hi,

The code

cnt = dr(0).ToString + 1 will change the EmployeeCode .

Not sure the employee code is based on sequence numbers or in series . You can use the existing code provided cnt and next employeecode is same.

You can also try dr.NextResult provide you return multiple records.
make sure previously open sqldatareader is closed before using the other.

Thanks
VJ

[reach_yousuf]

What adatapost is given is an optimum solution,
Load all data in datatable and and use datareader for navigation