How to get next row value

Abdu Rafeeq

15y3.2k

During selection how to find next row value .

Ex:
Some selected rows given beloW
SlNo Bil No Date Product Touch Amout Wt
1 BT-60 01/08/2011 Chain 91.60 200.00 5.000
2 BT-61 01/08/2011 Necklase 91.60 100.00 6.000

I want only any column value ex:

Bil No
BT-61

I want select a second row value duing in first row

pls help me

sir

in select query can select next row value

means tthat

during first selection got
1 BT-60 01/08/2011 Chain 91.60 200.00 5.000

at this time i want one more filed value which is next selection column value

NextBIllNO
BT-61

Lastly i want this type of answer

SlNo Bil No Date Product Touch Amout Wt NextBIllNO
1 BT-60 01/08/2011 Chain 91.60 200.00 5.000 BT-61

4 Replies

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

Sam Hobbs15y ago
I did this type of thing very often in my COBOL days. The simplified pseudo-code for the solution is:

NextData = Read();
while (More)
{
CurrentData = NextData;
NextData = Read();
// process data in CurrentData; the next record is in NextData
}
// process the last data in NextData

A complete sample would be:

using System;
using System.Data.SqlClient;

namespace _134409_how_to_get_next_row_value
{
class Program
{
static void Main(string[] args)
{
String cs = Properties.Settings.Default.ConnectionStringSetting;
using (SqlConnection Connection = new SqlConnection(cs))
{
try { Connection.Open(); }
catch (Exception ex)
{
Console.WriteLine("Database not opened; error: {0}", ex);
return;
}
SqlDataReader dr = null;
try
{
String q = Properties.Settings.Default.QuerySetting;
SqlCommand Command = new SqlCommand(q, Connection);
dr = Command.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine("Command error: {0}", ex);
return;
}
Data NextData;
Data CurrentData = null;
if (!dr.Read())
{
Console.WriteLine("No data");
}
NextData = new Data(dr.GetInt32(0), dr.GetString(1));
while (dr.Read())
{
CurrentData = NextData;
NextData = new Data(dr.GetInt32(0), dr.GetString(1));
Console.WriteLine("{0} {1} {2}", CurrentData.Key, CurrentData.Value, NextData.Key);
}
Console.WriteLine("{0} {1} [none]", NextData.Key, NextData.Value);
}
}
}

class Data
{
public int Key;
public string Value;
public Data(int Key, string Value)
{
this.Key = Key;
this.Value = Value;
}
}
}
Zoran Horvat15y ago
So you want to iterate through the recordset and to preview next value in each step. This can be done in SQL but in a very inefficient way. Better way is to do it in code and I will give you principal code which you can specialize to your case.

Idea is to keep two records in memory at all times (unless there is only one record remaining). In every step you have next record in memory along with current record.

Record[] rec = new Record[2]; // Record is class/structure which contains all fields from the result set
bool[] loaded = new bool[2];

DataReader reader = cmd.ExecuteReader(); // This fetches the recordset

loaded[0] = reader.Read(); // loaded[0] indicates whether there is a row fetched
if (loaded[0])
        rec[0] = FillRecord(reader); // FillRecord is your function which creates new object based on contents of the data row

loaded[1] = reader.Read(); // loaded[1] indicates whether there is a second row fetched
if (loaded[1])
        rec[1] = FillRecord(reader);

// Now you iterate through the recordset
while (loaded[0])
{

        rec[0].NextBill = null;
        if (loaded[1])
                rec[0].NextBill = rec[1].Bill;        // Here you copy the field which you want to preview

        // Now do whatever you want to do with current record, and that is object located in rec[0]
        ...

        // Now advance to next record
        rec[0] = rec[1];
        loaded[0] = loaded[1];

        if (loaded[0])
        {
                loaded[1] = reader.Read();
                if (loaded[1])
                        rec[1] = FillRecord(reader);
        }

}
Abdu Rafeeq15y ago
iam edited ,pls help me
Zoran Horvat15y ago
What do you mean by next row value? Can you be more specific with criteria to select rows?

Zoran