How to get next row value

Question

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

Sam Hobbs · Answer

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 Horvat · Answer

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 Rafeeq · Answer

iam edited pls help me

Zoran Horvat · Answer

What do you mean by next row value? Can you be more specific with criteria to select rows? Zoran