Home > Forum > ASP.NET > need to create sql procedure . or any other solution is also welcome

need to create sql procedure . or any other solution is also welcome

by celv   on Oct 21, 2014   Category: ASP.NET  |  Views: 1338    |  Points: 5   |  Bronze 
  Reply
need to create sql procedure . or any other solution is also welcome



this is my table structure
CREATE TABLE [dbo].[aten](
[Id] [int] NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Intime] [datetime] NULL,
[Date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Outtime] [datetime] NULL,
[totalhours] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)


CREATE TABLE [dbo].[absentdays](
[id] [int] NULL,
[date] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[status] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)



in the data() i fetch the data in both table aten and absentdays and show it gridview3.and update the status as Present


private void data()
{
try
{
con = new SqlConnection(s);
con.Open();
string qrse = "select id as issd from absentdays where id in (select id from aten where date ='" + TextBox3.Text + "' ) ";
SqlCommand cmd = new SqlCommand(qrse, con);
SqlDataAdapter da = new SqlDataAdapter();
DataSet dssd = new DataSet();
da = new SqlDataAdapter(cmd);
da.Fill(dssd);
GridView3.DataSource = dssd;
GridView3.DataBind();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}


private void absent()
{
con = new SqlConnection(s);
con.Open();
SqlCommand cmd = new SqlCommand();

for (int i = 0; i < GridView3.Rows.Count; i++)
{
for (int j = 0; j < GridView3.Rows.Cells.Count; j++)
{

string qty = "update absentdays set status='" + TextBox5.Text;
qty += "' where id='" + GridView3.Rows.Cells[j].Text;
qty += "' and date='" + TextBox3.Text;
qty += "'";
cmd = new SqlCommand(qty, con);
cmd.ExecuteNonQuery();
}
}
con.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
con = new SqlConnection(s);
con.Open();
Session["Outtime"] = TextBox2.Text;
string qrr = "update aten set Outtime='" + TextBox2.Text;
qrr += "' where Id='" + TextBox1.Text + "' and date='" + TextBox3.Text + "' and intime='" + TextBox4.Text;
qrr += "'";
SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand(qrr, con);
string mm = Convert.ToString(qrr);
if (mm != "")
{
Label4.Text = "Employee Logout Sucessfully";
Label4.ForeColor = System.Drawing.Color.White;
TextBox1.Text = "";

}
cmd.ExecuteNonQuery();
}
catch (Exception)
{

}
con.Close();

}

Bookmark and Share:
 
  User Reply  | Ask a question  |   Reply 
  Re :need to create sql procedure . or any other solution is also welcome   
by Thamil
on Oct 24 2014 12:46AM
Points : 10
Gold 
hi celv,

You are executing select query directly from front end, though you can the create stored procedure and call that stored procedure from C# code behind. Refer the below URL for how to create stored procedure in sql server.

http://msdn.microsoft.com/en-us/library/ms345415.aspx

Below URL, you will get sample for stored procedure and how to call from front end C# code as well.

http://dotnetgallery.com/kb/resource10-How-to-perform-insert-update-delete-and-select-rows-in-ASPNET-gridview-c.aspx


Thanks.

Recent Post

Latest Posts