Updating values from a while loop

emma oj

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Text;
public partial class Nampak_AddFal : System.Web.UI.Page
{
string selectSQL, selectSQL1, selectSQL5;
string updateSQL, updateSQL1, updateSQL5, updateSQL6, updateSQL2;
SqlCommand cmd = new SqlCommand();
SqlCommand cmd1 = new SqlCommand();
SqlCommand cmd2 = new SqlCommand();
SqlCommand cmd3 = new SqlCommand();
SqlCommand cmd5 = new SqlCommand();
SqlCommand cmd6 = new SqlCommand();
SqlCommand cmd9 = new SqlCommand();
SqlConnection dbConn = new SqlConnection();
SqlConnection dbConn1 = new SqlConnection();
SqlConnection dbConn2 = new SqlConnection();
SqlConnection dbConn3 = new SqlConnection();
SqlConnection dbConn4 = new SqlConnection();
SqlConnection dbConn5 = new SqlConnection();
SqlConnection dbConn6 = new SqlConnection();
SqlDataReader dr, dr1, dr2, dr3, dr5, dr8;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void SetSS_Click(object sender, EventArgs e)
{
Session["YY"] = DropDownList1.SelectedItem.Text;
int Q = Convert.ToInt32(TextBox1.Text);
selectSQL = "select id, RTicket from Staff WHERE Jobtype ='Contract'";
dbConn.ConnectionString = "Data Source=LAPTOP\\SQLEXPRESS; Initial Catalog=Namh;Integrated Security=True;";
cmd.Connection = dbConn;
cmd.CommandText = selectSQL;
cmd.CommandType = CommandType.Text;
try
{
dbConn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
Session["FT"] = dr["Rticket"].ToString();
int W = Convert.ToInt32(Session["FT"]);
int S = W + Q;
string P = S.ToString();
Session["PP"] = S.ToString();
updateSQL1 = "update Staff set Rticket='" + Session["PP"].ToString() + "' where Jobtype ='Contract' ";
// Response.Write(updateSQL1);
dbConn1.ConnectionString = "Data Source=LAPTOP\\SQLEXPRESS; Initial Catalog=Namh;Integrated Security=True;";
cmd1.Connection = dbConn1;
cmd1.CommandText = updateSQL1;
cmd1.CommandType = CommandType.Text;
try
{
dbConn1.Open();
int updated1 = cmd1.ExecuteNonQuery();
if (updated1 == 1)
{
Response.Write("well done");
}
else
{
Response.Write("double");
}
}
catch (Exception err)
{
Label6.Text = "Error Logging in 2 ";
Label6.Text += err.ToString();
}
finally
{
dbConn1.Close();
}
}
dr.Close();
}
catch (Exception err)
{
Label6.Text = "Error Logging in 2 ";
Label6.Text += err.ToString();
}
finally
{
dbConn.Close();
}
}
}

my problem is that i am able to read values before adding a value and lastly updating it in the database e.g

20
34
56
67

but it updates all the values to same values in the database e.g

98
98
98
98

instead of say int Q =4

24
38
60
71
in the database ,please help

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
There must be 4 records in table Staff where JobType = 'Contract'. While updating you are not identifying a single record with a unique key such as Id. That's the reason all 4 records are getting updated.

While updating, the WHERE clause in your UPDATE query should be able to identify a single record.

Hope that clarifies.
Should you have any more questions, please do let us know.
Nitin Sontakke8y ago
You can write to me on nitin dot n dot sontakke at gmail dot com.
emma oj8y ago
Hello Nitin Sontakke,

What is your email address.

Emmanuel
emma oj8y ago
Hello Nitin Sontakke,

Please i used his code to set session timeout to 66.6 minutes,but its not working.

Please help.

Emmanuel
Nitin Sontakke8y ago
Glad to be of help!

Please make note of few more things about your code.

1/ There is no need to declare so many connection and command objects, you can just declare one and use it again and again.

2/ DO NOT keep on copying and pasting the connection string all over your code. What will happen if you will need to change it later? Keep connection string in place like app.config / web.config and pick it from there.

3/ You SHOULD NOT have any db related code in your UI page at all. Try to write separate classes for separate task. This way the code gets small / easy to maintain / easy to read / modular, etc.

If you need any help, please do let us know.
emma oj8y ago
Thank you
Nitin Sontakke
it worked

Updating values from a while loop

Nitin Sontakke