how to update a record or value in sqlserver 2005 using asp.net. [Resolved]

Posted by Siddu1281 under ASP.NET on 9/7/2012 | Points: 10 | Views : 1879 | Status : [Member] | Replies : 8
how to update a record or value in sqlserver 2005 using asp.net.
my problem is if iam having a number 20 in my database, now iwant to subtract 5 and update that record into 15.

my DATABASE Leaverecord
Id-------LeaveRemaining
1-------------20
2-------------18
3-------------18

the code which i developed was not correct actually, so please help me in ExecuteUpdate() method. i want to know how to pass the value into that method

public partial class Applying : System.Web.UI.Page
{
String connectionString = ConfigurationManager.ConnectionStrings["SQLDbconnection"].ToString();
SqlConnection con;

public Applying()
{
con = new SqlConnection(connectionString);
}
protected void Page_Load(object sender, EventArgs e)
{
tbStartdate_CalendarExtender.StartDate = DateTime.Today;
tbEndDate_CalendarExtender.StartDate = DateTime.Today.AddDays(1);
}

public void btnOk_Click(object sender, EventArgs e)
{
DateTime StartDate1 = DateTime.ParseExact(tbStartdate.Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
DateTime EndDate1 = DateTime.ParseExact(tbEnddate.Text, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);

TimeSpan diffResult = EndDate1.Subtract(StartDate1);
int NoOfDays = Convert.ToInt32(diffResult.Days);

ExecuteUpdate(NoOfDays);

}
}
private void ExecuteUpdate() // help me in this code
{

con.Open();
SqlCommand cmd1 = new SqlCommand();
string sql_update="UPDATE LeaveRecord SET LeavesRemaining WHERE Id=' "+tbID.Text+" ' ";
cmd1.CommandType = CommandType.Text;
cmd1.ExecuteNonQuery();
con.Close();
}






Responses

Posted by: Vasanthmvp on: 9/7/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi, hope this clears your problem. You can use Stored Procedure instead to reduce the code, to make it simpler i choose this way.

.aspx Page:


<asp:TextBox ID="tbID" runat="server" />
<asp:Button ID="btn" runat="server" Text="Update" OnClick="btnOk_Click" />

.aspx.cs:

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)
{
}
public void btnOk_Click(object sender, EventArgs e)
{
DateTime StartDate1 = DateTime.Parse(DateTime.Today.ToShortDateString());
DateTime EndDate1 = DateTime.Parse(DateTime.Today.AddDays(1).ToShortDateString());
TimeSpan diffResult = EndDate1.Subtract(StartDate1);
int NoOfDays = Convert.ToInt32(diffResult.Days);
ExecuteUpdate(NoOfDays);
}
private void ExecuteUpdate(int NoofDays) // help me in this code
{
int attendeddays;
using (SqlConnection conn = new SqlConnection(_connStr))
{
string sqlselect = "select DaysAttended From EmployeeTable where EmployeeID ="+int.Parse(tbID.Text);
// Here i used a table EmployeeTable with EmployeeID and DaysAttended as columns.

using (SqlCommand cmd = new SqlCommand(sqlselect, conn))
{
conn.Open();
attendeddays = (int)cmd.ExecuteScalar();
conn.Close();
}
int actualdays = attendeddays - NoofDays;
string sqlupdate = "Update EmployeeTable Set DaysAttended ="+actualdays+"where EmployeeID ="+int.Parse(tbID.Text);
using (SqlCommand cmd = new SqlCommand(sqlupdate, conn))
{
conn.Open();
object obj = cmd.ExecuteNonQuery();
conn.Close();
if (obj != null)
{
Response.Write("Succesully Updated");
}
}
}
}


Regards,

Awesome Coding !! :)

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vijay.K on: 9/7/2012 [Member] Starter | Points: 25

Up
0
Down
con.open();
Sql command cmd = new Sqlcommand("update table_name set name='"+Textbox1.text+"',salary='"+textbox2.text+"' where eno='"+textBox3.text+"'",con);
cmd.Executenonquery();
Response.write("<script>alert('Updated Successfully')</script>");
con.close();

vijay.k

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vijay.K on: 9/7/2012 [Member] Starter | Points: 25

Up
0
Down
update table_name set ename='vijay',age=10 where eid=10

vijay.k

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Siddu1281 on: 9/7/2012 [Member] Starter | Points: 25

Up
0
Down
hello vijay

i want to pass variable NoOfDays into the Executeupdate() method how it can be made..?

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 9/7/2012 [Member] Starter | Points: 25

Up
0
Down
private void ExecuteUpdate(int NoofDays) // I believe no of days is the above passed parameter (diff. of start date & end date)
{
con.Open();
string sql_update="UPDATE LeaveRecord SET DaysonLeave ="+NoofDays+"WHERE Id=' "+tbID.Text+" ' ";
// IF your EmplD is of type int in the database. then write Where Id = "+int.Parse(tbID.Text);
// here LeaveRecord is tablename, DaysonLeave is the assumed columns name to be updated, tbID.text is the employee id.
using(SqlCommand cmd1 = new SqlCommand(sql_update,con))
{
obj = cmd1.ExecuteNonQuery();
con.Close();
if(obj!=null)
{
response.write("Record Updated Succesfully");
}

}
}

Awesome Coding !! :)

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 9/7/2012 [Member] Starter | Points: 25

Up
0
Down
Hi, if you want to more clarity on ADO.NET CRUD (create read Update Delete) operations, please refer the following link:

http://www.dotnetfunda.com/articles/article1619-how-to-perform-edit-update-and-delete-operation-in-gridview-.aspx

Awesome Coding !! :)

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 9/7/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
Try this

private void ExecuteUpdate(int NoOfDays) // Add Parameters here
{
con.Open();
SqlCommand cmd1 = new SqlCommand();
string sql_update="UPDATE LeaveRecord SET LeavesRemaining=' "+NoOfDays+" ' WHERE Id=' "+tbID.Text+" ' ";
cmd1.CommandType = CommandType.Text;
cmd1.ExecuteNonQuery();
con.Close();
}


Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Siddu1281 on: 9/7/2012 [Member] Starter | Points: 25

Up
0
Down
thank you vasanth.

Siddu1281, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response