C# code to loop inside loop

Question

I want to retrieve all User_id's next after my entered User_id referencing each other from the table by Reference_id, the code below gives the exact result but it retrieves all User_id's from "2001 to 2005".

I want if i enter the "2002" as User_id from a textbox then it must retrieve from "2003 - 2005"

Table_xyz column User_id have value= 2001, 2002, 2003, 2004, 2005

Table_xyz column Reference_id have value= 2000, 2001, 2002, 2003, 2004

var gCmd = new SqlCommand(@"SELECT User_id FROM Table_xyz", nCon);

SqlDataAdapter Sda = new SqlDataAdapter(gCmd);

DataTable Dt = new DataTable(); Sda.Fill(Dt);

for (int i = 0; i < Dt.Rows.Count; i++)

    string referenceid = Dt.Rows[i]["User_id"].ToString();

    var gCmd1 = new SqlCommand(@"SELECT User_id FROM Table_xyz WHERE

                                 Reference_id = '" + referenceid + "'", nCon);

    SqlDataAdapter Sda1 = new SqlDataAdapter(gCmd1);

    DataTable Dt1 = new DataTable();

    Sda1.Fill(Dt1);

    Response.Write(referenceid);

I tried adding "SELECT User_id FROM Table_xyz WHERE User_id = '2001'" to the first command but it returns only a single value where User_id matched "2001"

Gokhul Varman · Answer

You can get a clear solution of loops if you refer the link:http://csharp.net-informations.com/statements/csharp-for-loop.htm

Ruchi Sharavat · Answer

Okay, so what would be the condition to fetch all next records? How do you want to identify next records if value inserted through textbox is " ricky_global9"? What should be the output then? Do you want to get all the records after the record which have refernceid =' ricky_global9 ' ? If so do you have any identity column in table Table_xyz? If yes than you can get all the records after that id.

Abdur Razique · Answer

Hi @ Ruchi Sharavat first of all thanks for your valuable time for me. And yes what you replied will work perfect based on my question, actually i did a mistake writing my question. My User_id and Reference_id will have alfa numeric values too (like: ricky_global9, mark09, robin_123) And your said "Recursive command" will work perfect only if the the datatype is INT or Convert.ToInt. So the issue still persist for me.

Abdur Razique · Answer

Hi @Ruchi Sharavat first of all thanks for your valuable time for me. And yes what you replied will work perfect based on my question, actually i did a mistake writing my question. My User_id and Reference_id will have alfa numeric values too (like: ricky_global9, mark09, robin_123) And your said "Recursive command" will work perfect only if the the datatype is INT or Convert.ToInt. So the issue still persist for me.

Ruchi Sharavat · Answer

Hi Abdur, Firstly I didn't find any textbox mentioned in your code, i will suppose it as textbox1. Secondly columns for userid and refernceid would be Numeric as these are refencing columns. SO here is code : Int32 referenceid = textbox1.text ; // It will the uersid eg: 2002 var gCmd1 = new SqlCommand ( @"SELECT User_id FROM Table_xyz WHERE Reference_id > " + referenceid + "" , nCon ); SqlDataAdapter Sda1 = new SqlDataAdapter ( gCmd1 ); DataTable Dt1 = new DataTable (); Sda1 . Fill ( Dt1 ); Your output will be :2003,2004,2005