How to insert update delete in query string with login page| How to use query string in asp.net |Query Strings in ASP.Net

How to insert update delete in query string with login

Database code :-

How to create database:-

create database query_string

How to use database:-

use query_string

How to create table:-

create table Employee
(
Empid int primary key identity(1,1),
Name varchar(50),
Gender int,
Country int,
State int,
DOB datetime,
Salary float,
Gmail varchar(50),
Password varchar(50),
Cpassword varchar(50),
Hobbies varchar(100),
File_Upload varchar(100),
Inactive int
)

create table Country
(
Cid int primary key identity(1,1),
Cname varchar(50)
)

insert into Country(Cname) values('India'),('USA'),('Nepal'),('Canada')

create proc usp_employee_Country
as
begin
select * from Country
end

How to create Insert procedure:-

create alter proc usp_Employee_insert
@Name varchar(50),
@Email varchar(50),
@Password varchar(50),
@Gender int,
@Country int,
@Hobbies varchar(100)
as
begin
insert into Employee(Name,Email,Password,Gender,Country,Hobbies) values(@Name,@Email,@Password,@Gender,@Country,@Hobbies)
end

select * from Employee

How to create Login Procedure:-

create proc usp_userlogin
@Email varchar(50),
@Password varchar(50)
as
begin
select * from Employee where Email=@Email and Password=@Password
end

How to create get record procedure and join multi pal table...

create proc usp_employee_get
@Empid int
as
begin
select Employee.*, Country.* from Employee inner join Country on Employee.Country=Country.Cid where Empid=@Empid
end

How to create Edit Procedure:-

create proc usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid=@Empid
end

How to create Delete Procedure:-

create proc usp_employee_Delete
@Empid int
as
begin
delete from Employee where Empid=@Empid
end

How to create insert update both procedure e:-

ALTER proc usp_emp_insert
@Name varchar(50),
@Gender int,
@Country int,
@State int,
@DOB datetime,
@Salary float,
@Gmail varchar(50),
@Password varchar(50),
@Cpassword varchar(50),
@Hobbies varchar(100),
@File_Upload varchar(100),
@Isactive int
as
begin
insert into Employee(Name,Gender,Country,State,DOB,Salary,Gmail,Password,Cpassword,Hobbies,File_Upload,Isactive) values(@Name,@Gender,@Country,@State,@DOB,@Salary,@Gmail,@Password,@Cpassword,@Hobbies,@File_Upload,@Isactive)
end

create proc usp_emp_Update
@Empid int,
@Name varchar(50),
@Gender int,
@Country int,
@State int,
@DOB datetime,
@Salary float,
@Gmail varchar(50),
@Password varchar(50),
@Cpassword varchar(50),
@Hobbies varchar(100),
@File_Upload varchar(100),
@Isactive int
as
begin
update Employee set Name=@Name,Gender=@Gender,Country=@Country,State=@State,DOB=@DOB,Salary=@Salary,Gmail=@Gmail,Password=@Password,Cpassword=@Cpassword,Hobbies=@Hobbies,File_Upload=@File_Upload,Isactive=@Isactive where Empid= @Empid
end

Registration.aspx page coding:-

<table>
<tr>
<td>Name:</td>
<td><asp:TextBox ID="Textname" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Gender:</td>
<td><asp:RadioButtonList ID="rblgender" runat="server" RepeatColumns="3">
<asp:ListItem Value="1" Text="Male"></asp:ListItem>
<asp:ListItem Value="2" Text="Female"></asp:ListItem>
<asp:ListItem Value="3" Text="Other"></asp:ListItem>
</asp:RadioButtonList></td>
</tr>

<tr>
<td>Country:</td>
<td><asp:DropDownList ID="ddlcountry" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlcountry_SelectedIndexChanged"></asp:DropDownList></td>
</tr>

<tr>
<td>State:</td>
<td><asp:DropDownList ID="ddlstate" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlstate_SelectedIndexChanged"></asp:DropDownList></td>
</tr>

<tr>
<td>DOB:</td>
<td><asp:TextBox ID="Textdob" runat="server"></asp:TextBox></td>
<td><Ajax:CalendarExtender ID="dob" runat="server" PopupButtonID="Textdob" TargetControlID="Textdob" PopupPosition="BottomRight"></Ajax:CalendarExtender></td>
</tr>

<tr>
<td>Salary:</td>
<td><asp:TextBox ID="Textsalary" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Gmail:</td>
<td><asp:TextBox ID="Textgmail" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Password:</td>
<td><asp:TextBox ID="Textpassword" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Cpassword:</td>
<td><asp:TextBox ID="Textcpassword" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Hobbies:</td>
<td><asp:CheckBoxList ID="cblhobbies" runat="server" RepeatColumns="3">
<asp:ListItem Value="1" Text="Football"></asp:ListItem>
<asp:ListItem Value="2" Text="Cricket"></asp:ListItem>
<asp:ListItem Value="3" Text="Music"></asp:ListItem>
<asp:ListItem Value="4" Text="Dancing"></asp:ListItem>
<asp:ListItem Value="5" Text="Yoga"></asp:ListItem>
<asp:ListItem Value="6" Text="Study"></asp:ListItem>
</asp:CheckBoxList></td>
</tr>

<tr>
<td>File_Upload</td>
<td><asp:FileUpload ID="Textfn" runat="server" /></td>
</tr>

<tr>
<td>Isactive</td>
<td><asp:CheckBox ID="checkboxs" runat="server" /> </td>
</tr>

<tr>
<td></td>
<td><asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" /></td>
</tr>
</table>

Registration.aspx .CS page code:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace Query_string
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Fill_country();
ddlstate.Items.Insert(0, new ListItem("--select--", "0"));
}
if (Request.QueryString["Reg"] != null && Request.QueryString["Reg"].ToString() != "")
{
if (!IsPostBack)
{
Fill_edit();
}
}
}
public void Fill_edit()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_employee_edit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid", Request.QueryString["Reg"].ToString());
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
Textname.Text = ds.Tables[0].Rows[0]["Name"].ToString();
rblgender.SelectedValue = ds.Tables[0].Rows[0]["Gender"].ToString();
ddlcountry.SelectedValue = ds.Tables[0].Rows[0]["Country"].ToString();
Fill_state(ddlcountry.SelectedValue);
ddlstate.SelectedValue = ds.Tables[0].Rows[0]["State"].ToString();
Textdob.Text = ds.Tables[0].Rows[0]["DOB"].ToString();
Textsalary.Text = ds.Tables[0].Rows[0]["Salary"].ToString();
Textgmail.Text = ds.Tables[0].Rows[0]["Gmail"].ToString();
Textpassword.Text = ds.Tables[0].Rows[0]["Password"].ToString();
Textcpassword.Text = ds.Tables[0].Rows[0]["Cpassword"].ToString();

string[] arg = ds.Tables[0].Rows[0]["Hobbies"].ToString().Split(',');
cblhobbies.ClearSelection();

for (int i = 0; i < cblhobbies.Items.Count; i++)
{
for (int J = 0; J < arg.Length; J++)
{
if (cblhobbies.Items[i].Text == arg[J])
{
cblhobbies.Items[i].Selected = true;
break;
}
}
}

ViewState["Fn"] = ds.Tables[0].Rows[0]["File_Upload"].ToString();

if (ds.Tables[0].Rows[0]["Isactive"].ToString() == "1")
{
checkboxs.Checked = true;
}
else
{
checkboxs.Checked = false;
}
btnsave.Text = "Update";
}
else
{
//con.Open();
//SqlCommand cmd = new SqlCommand("usp_employee_update")
//con.Close();
}
}
public void Fill_country()
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_Country_get", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlcountry.DataValueField = "Cid";
ddlcountry.DataTextField = "Cname";
ddlcountry.DataSource = ds;
ddlcountry.DataBind();
ddlcountry.Items.Insert(0, new ListItem("--select--", "0"));
}
}

public void Fill_state(string Cid)
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_state_get", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Cid", Cid);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlstate.DataValueField = "Sid";
ddlstate.DataTextField = "Sname";
ddlstate.DataSource = ds;
ddlstate.DataBind();
ddlstate.Items.Insert(0, new ListItem("--select--", "0"));
}
}
public void clear()
{
Textname.Text = "";
rblgender.SelectedValue = "0";
ddlcountry.SelectedValue = "0";
ddlstate.SelectedValue = "0";
Textdob.Text = "";
Textsalary.Text = "";
Textgmail.Text = "";
Textpassword.Text = "";
Textcpassword.Text = "";
cblhobbies.ClearSelection();
checkboxs.Checked = false;
}

protected void ddlcountry_SelectedIndexChanged(object sender, EventArgs e)
{
Fill_state(ddlcountry.SelectedValue);
}

protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
{
checkboxs.Checked = true;
}
protected void btnsave_Click(object sender, EventArgs e)
{
string HOB = "";
for (int i = 0; i < cblhobbies.Items.Count; i++ )
{
if (cblhobbies.Items[i].Selected == true)
{
HOB += cblhobbies.Items[i].Text + ',';
}
}
HOB = HOB.TrimEnd();

string FN = "";
FN= Path.GetFileName(Textfn.PostedFile.FileName);
Textfn.SaveAs(Server.MapPath("File_Uploads"+"\\"+FN));
if (btnsave.Text == "Save")
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_emp_insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", Textname.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@State", ddlstate.SelectedValue);
cmd.Parameters.AddWithValue("@DOB", Textdob.Text);
cmd.Parameters.AddWithValue("@Salary", Textsalary.Text);
cmd.Parameters.AddWithValue("@Gmail", Textgmail.Text);
cmd.Parameters.AddWithValue("@Password", Textpassword.Text);
cmd.Parameters.AddWithValue("@Cpassword", Textcpassword.Text);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
cmd.Parameters.AddWithValue("@File_Upload", FN);
cmd.Parameters.AddWithValue("@Isactive", checkboxs.Checked == true ? 1 : 0);
cmd.ExecuteNonQuery();
Response.Redirect("user_login.aspx");
con.Close();
clear();
}
else
{
FN = Path.GetFileName(Textfn.PostedFile.FileName);
con.Open();
SqlCommand cmd = new SqlCommand("usp_emp_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empid",Request.QueryString["Reg"].ToString());
cmd.Parameters.AddWithValue("@Name", Textname.Text);
cmd.Parameters.AddWithValue("@Gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@Country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@State", ddlstate.SelectedValue);
cmd.Parameters.AddWithValue("@DOB", Textdob.Text);
cmd.Parameters.AddWithValue("@Salary", Textsalary.Text);
cmd.Parameters.AddWithValue("@Gmail", Textgmail.Text);
cmd.Parameters.AddWithValue("@Password", Textpassword.Text);
cmd.Parameters.AddWithValue("@Cpassword", Textcpassword.Text);
cmd.Parameters.AddWithValue("@Hobbies", HOB);
if (FN != "")
{
cmd.Parameters.AddWithValue("@File_Upload", FN);
File.Delete(Server.MapPath("File_Uploads" + "\\" + ViewState["Fn"]));
Textfn.SaveAs(Server.MapPath("File_uploads" + "\\" + FN));
}
else
{
cmd.Parameters.AddWithValue("@File_Upload", ViewState["Fn"]);
}
cmd.Parameters.AddWithValue("@Isactive", checkboxs.Checked == true ? 1 : 0);
cmd.ExecuteNonQuery();
btnsave.Text = "Save";
con.Close();
clear();
}
Response.Redirect("user_display.aspx");
}
}
}

Data base in Record:-

Userlogin.aspx coding:-

<table>
<tr>
<td>Gmail:</td>
<td><asp:TextBox ID="textgmail" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td>Password:</td>
<td><asp:TextBox ID="Textpassword" runat="server"></asp:TextBox></td>
</tr>

<tr>
<td></td>
<td><asp:Button ID="btnlogin" Text="Login" runat="server" OnClick="btnlogin_Click" /></td>
</tr>
</table>

Userlogin.aspx .CS page code:-

User_display.aspx page code:-

<table>
<tr>
<td></td>
<td><asp:GridView ID="grd" runat="server" AutoGenerateColumns="false" OnRowCommand="grd_RowCommand">
<Columns>
<asp:TemplateField HeaderText="Emp Name">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Gender">
<ItemTemplate>
<%#Eval("Gender").ToString()=="1"?"Male":Eval("Gender").ToString()=="1"?"Female":"Other" %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<%#Eval("Cname") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="State">
<ItemTemplate>
<%#Eval("Sname") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Date of Birth">
<ItemTemplate>
<%#Eval("DOB") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Salary">
<ItemTemplate>
<%#Eval("Salary") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Gmail">
<ItemTemplate>
<%#Eval("Gmail") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Hobbies">
<ItemTemplate>
<%#Eval("Hobbies") %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField>
<ItemTemplate>
<asp:Image ID="img" runat="server" ImageUrl='<%#Eval("File_Upload","~/File_Uploads/{0}") %>' Width="60px" Height="50px" />
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Status">
<ItemTemplate>
<%#Eval("Isactive").ToString()=="1" ? "Active" :"Inactive" %>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:LinkButton ID="lnkbtndel" runat="server" Text="Delete" CommandName="DEL" CommandArgument='<%#Eval("Empid") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Edit">
<ItemTemplate>
<asp:LinkButton ID="lnkbtnedit" runat="server" Text="Edit" CommandName="EDT" CommandArgument='<%#Eval("Empid") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView></td>
</tr>
</table>
</div>
</form>
</body>

</html>

Userdisplay.aspx .CS page code:-