Hi Firuz,
Please refer the below code.
SQL
CREATE PROCEDURE SEARCHFRUIT
@Name VARCHAR(20) = NULL
AS
BEGIN
DECLARE @Fruit AS TABLE(Id INT IDENTITY PRIMARY KEY,IdPeople INT,Fruit VARCHAR(20),Vegitable VARCHAR(20),Oil VARCHAR(20))
INSERT INTO @Fruit VALUES(1,'Apple','Tomato','Cotton oil')
INSERT INTO @Fruit VALUES(1,'Orange','Potato','Flax oil')
INSERT INTO @Fruit VALUES(1,'Cherry','Cucumber','Sesame oil')
INSERT INTO @Fruit VALUES(2,'Apple','Potato','Flax oil')
INSERT INTO @Fruit VALUES(2,'Cherry','Cucumber','Sesame oil')
INSERT INTO @Fruit VALUES(3,'Orange','Tomato','Cotton oil')
SELECT f.IdPeople,f.Fruit,f.Vegitable,Oil
FROM INFO i
JOIN @Fruit f ON f.IdPeople = i.Id
WHERE i.Name = @Name
END
HTML
<div>
<asp:Panel ID="Panel1" runat="server" Width="720px" BackColor="#CCCCCC" HorizontalAlign="Center"
CssClass="panel">
<div class="LabelContainer">
<asp:Label ID="Label5" runat="server" Font-Bold="True" Font-Size="X-Large" Height="25px"
Text="Registration population"></asp:Label>
</div>
<div class="SecondLabelContainer">
<asp:Label ID="Label8" runat="server"></asp:Label>
</div>
<div class="IdContainer">
<asp:Label ID="Label1" runat="server" Text="Id"></asp:Label>
<asp:TextBox ID="ID" runat="server" Width="50"></asp:TextBox>
</div>
<div class="FirstNameContainer">
<asp:Label ID="Label3" runat="server" Text="FirstName"></asp:Label>
<asp:TextBox ID="FirstName" runat="server" Width="120"></asp:TextBox>
</div>
<div class="NameContainer">
<asp:Label ID="Label2" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="Name" runat="server" Width="120"></asp:TextBox>
</div>
<div class="LastNameContainer">
<asp:Label ID="Label4" runat="server" Text="LastName"></asp:Label>
<asp:TextBox ID="LastName" runat="server" Width="120"></asp:TextBox>
</div>
<div class="BirthdayContainer">
<asp:Label ID="Label6" runat="server" Text="Birthday"></asp:Label>
<asp:TextBox ID="Birthday" runat="server" Width="120"></asp:TextBox>
</div>
<div class="AdresContainer">
<asp:Label ID="Label7" runat="server" Text="Adress"></asp:Label>
<asp:TextBox ID="Adress" runat="server" Width="120"></asp:TextBox>
</div>
<div class="ButtonContainer">
<asp:Button ID="btnAdd" runat="server" Text="Add" Visible="false" />
<asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
<asp:Button ID="btnNext" runat="server" Text="Next" OnClick="btnNext_Click" />
<asp:Button ID="btnPrevious" runat="server" Text="Previous" OnClick="btnPrevious_Click" />
<asp:Button ID="btnFirst" runat="server" Text="First" Visible="false" />
<asp:Button ID="btnLast" runat="server" Text="Last" Visible="false" />
</div>
</asp:Panel>
<asp:Panel ID="Panel2" runat="server" CssClass="pnl" BackColor="#CCFFCC" Width="720"
Height="200">
<div class="IdtContainer">
<asp:Label ID="Label9" runat="server" Text="Id"></asp:Label>
<asp:TextBox ID="IDTS" runat="server" Width="40px"></asp:TextBox>
</div>
<div class="FruitContainer">
<asp:Label ID="Label10" runat="server" Text="Fruit"></asp:Label>
<asp:TextBox ID="txtFruit" runat="server" Width="108px"></asp:TextBox>
</div>
<div class="VegContainer">
<asp:Label ID="Label11" runat="server" Text="Vegitable"></asp:Label>
<asp:TextBox ID="txtVegitable" runat="server" Width="108px"></asp:TextBox>
</div>
<div class="OilContainer">
<asp:Label ID="Label12" runat="server" Text="Oil"></asp:Label>
<asp:TextBox ID="txtOil" runat="server" Width="108px"></asp:TextBox>
</div>
<div class="BtnContainer">
<asp:Button ID="btNext" runat="server" Text="Next" OnClick="btNext_Click" />
<asp:Button ID="btPrevious" runat="server" Text="Previous" OnClick="btPrevious_Click" />
<asp:Button ID="btFirst" runat="server" Text="First" Visible="false" />
<asp:Button ID="btLast" runat="server" Text="Last" Visible="false" />
</div>
</asp:Panel>
</div>
Code
public static int infoIndex = 0;
public static int fruitIndex = 0;
string str = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
HttpContext.Current.Session["ArrayPos"] = null;
HttpContext.Current.Session["ArrayFruit"] = null;
}
else
{
btnNext.Enabled = false;
btnPrevious.Enabled = false;
btnFirst.Enabled = false;
btnLast.Enabled = false;
btNext.Enabled = false;
btPrevious.Enabled = false;
}
}
public ArrayList GetInfo(string Name)
{
ArrayList list = null;
if (HttpContext.Current.Session["ArrayPos"] == null)
{
list = new ArrayList();
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SEARCHINFO";
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Connection = con;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
list.Add(string.Join(";", rdr["Id"], rdr["FirstName"], rdr["Name"], rdr["LastName"], rdr["Birthday"], rdr["Adress"]));
}
HttpContext.Current.Session["ArrayPos"] = list;
}
else
{
list = HttpContext.Current.Session["ArrayPos"] as ArrayList;
}
return list;
}
protected void btnSearch_Click(object sender, EventArgs e)
{
if (Name.Text == "")
{
Label8.Text = "TextBox name is empty!";
Name.Focus();
}
else
{
Label8.Text = "";
ArrayList dt = GetInfo(Name.Text);
infoIndex = 0;
if (dt.Count > 0)
{
string[] pos = dt[infoIndex].ToString().Split(';');
ID.Text = pos[0].ToString();
FirstName.Text = pos[1].ToString();
Name.Text = pos[2].ToString();
LastName.Text = pos[3].ToString();
Birthday.Text = pos[4].ToString();
Adress.Text = pos[5].ToString();
ArrayList fruit = GetFruit(Name.Text.Trim());
fruitIndex = 0;
if (fruit.Count > 0)
{
string[] pos1 = fruit[fruitIndex].ToString().Split(';');
IDTS.Text = pos1[0].ToString();
txtFruit.Text = pos1[1].ToString();
txtVegitable.Text = pos1[2].ToString();
txtOil.Text = pos1[3].ToString();
if (fruit.Count == 1)
{
btNext.Enabled = false;
btPrevious.Enabled = false;
}
else
{
btNext.Enabled = true;
btPrevious.Enabled = true;
}
}
}
else
{
Label8.Text = "People not saved in database!";
}
}
btnNext.Enabled = true;
btnPrevious.Enabled = true;
btnFirst.Enabled = true;
btnLast.Enabled = true;
}
protected void btnNext_Click(object sender, EventArgs e)
{
ArrayList dt = GetInfo(Name.Text);
btnPrevious.Enabled = true;
infoIndex = infoIndex + 1;
if (infoIndex < dt.Count)
{
string[] pos = dt[infoIndex].ToString().Split(';');
ID.Text = pos[0].ToString();
FirstName.Text = pos[1].ToString();
Name.Text = pos[2].ToString();
LastName.Text = pos[3].ToString();
Birthday.Text = pos[4].ToString();
Adress.Text = pos[5].ToString();
if (infoIndex == dt.Count - 1)
{
btnNext.Enabled = false;
}
else
{
btnNext.Enabled = true;
}
}
}
protected void btnPrevious_Click(object sender, EventArgs e)
{
ArrayList dt = GetInfo(Name.Text);
btnNext.Enabled = true;
infoIndex = infoIndex - 1;
if (infoIndex < dt.Count)
{
string[] pos = dt[infoIndex].ToString().Split(';');
ID.Text = pos[0].ToString();
FirstName.Text = pos[1].ToString();
Name.Text = pos[2].ToString();
LastName.Text = pos[3].ToString();
Birthday.Text = pos[4].ToString();
Adress.Text = pos[5].ToString();
if (infoIndex == 0)
{
btnPrevious.Enabled = false;
}
else
{
btnPrevious.Enabled = true;
}
}
}
public ArrayList GetFruit(string name)
{
ArrayList list = null;
if (HttpContext.Current.Session["ArrayFruit"] == null)
{
list = new ArrayList();
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "SEARCHFRUIT";
cmd.Parameters.AddWithValue("@Name", name);
cmd.Connection = con;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
list.Add(string.Join(";", rdr["IdPeople"], rdr["Fruit"], rdr["Vegitable"], rdr["Oil"]));
}
HttpContext.Current.Session["ArrayFruit"] = list;
}
else
{
list = HttpContext.Current.Session["ArrayFruit"] as ArrayList;
}
return list;
}
protected void btNext_Click(object sender, EventArgs e)
{
ArrayList d = GetFruit(Name.Text.Trim());
btPrevious.Enabled = true;
fruitIndex = fruitIndex + 1;
if (fruitIndex < d.Count)
{
string[] pos = d[fruitIndex].ToString().Split(';');
IDTS.Text = pos[0].ToString();
txtFruit.Text = pos[1].ToString();
txtVegitable.Text = pos[2].ToString();
txtOil.Text = pos[3].ToString();
if (fruitIndex == d.Count - 1)
{
btNext.Enabled = false;
}
else
{
btNext.Enabled = true;
}
}
}
protected void btPrevious_Click(object sender, EventArgs e)
{
ArrayList d = GetFruit(Name.Text.Trim());
btNext.Enabled = true;
fruitIndex = fruitIndex - 1;
if (fruitIndex < d.Count)
{
string[] pos = d[fruitIndex].ToString().Split(';');
IDTS.Text = pos[0].ToString();
txtFruit.Text = pos[1].ToString();
txtVegitable.Text = pos[2].ToString();
txtOil.Text = pos[3].ToString();
if (fruitIndex == 0)
{
btPrevious.Enabled = false;
}
else
{
btPrevious.Enabled = true;
}
}
}
Screenshot
