Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello everyone,

i am working with the web application in which i want to make changes in database based on some condition.my code is as below..

C#
protected void Page_Load(object sender, EventArgs e)
        {

           
            if (DateTime.Now.ToString("dd") == "1")
            {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["leave"].ConnectionString;
            conn.Open();
            SqlCommand cmd = new SqlCommand("select count(*) from leave_bal", conn);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {

                int l = dr.GetInt32(0);
                dr.Close();

                for (int i = 0; i < l; i++)
                {
                    SqlCommand cmd2 = new SqlCommand("select name from Leave_bal", conn);
                    SqlDataReader dr2 = cmd2.ExecuteReader();
                    if (dr2.Read())
                    {

                        string temp = dr2.GetString(i);
                        dr2.Close();
                        DateTime daten = DateTime.Now;
                        SqlCommand cmd_gdate = new SqlCommand("select jdate from emp_details where name='" + temp + "'", conn);
                        SqlDataReader dr3 = cmd_gdate.ExecuteReader();
                        if (dr3.Read())
                        {

                            DateTime datej = Convert.ToDateTime(dr3.GetValue(0));
                            dr3.Close();

                            System.TimeSpan span = daten - datej;
                            int diff = (int)span.TotalDays;
                            if (diff >= 365)
                            {
                                SqlCommand acc = new SqlCommand("update leave_bal set cl=(cl+1.5) where name='" + temp + "'", conn);
                                acc.ExecuteNonQuery();
                            }
                            else
                            {
                                SqlCommand acc = new SqlCommand("update leave_bal set cl=(cl+1) where name='" + temp + "'", conn);
                                acc.ExecuteNonQuery();
                            }
                        }

                    }
                }


            }

           }
        }

in the above code,first i am checking if today's date is 1st,if so i am using for loop which iterates for the total records in the database. each time i am getting employee name an changing the value 'cl' in table leave_bal accordingly.the problem is that,it works for the first iteration of for loop,that is for first record in leave_bal table,but when it comes to second iteration,it gives me the error that Index was outside the bounds of the array on the line highlighted in my code.how to accomplish this?any help would be greatly appriciated.
Posted
Updated 3-Mar-13 18:40pm
v2

Odd way of updating the database!

I would have written this into a stored proc, but that is just the way I work.

Using c# I would do the following.

Use a Datatable instead of a reader and get the records back into the aplication

Select Name from Table

then use a foreach and loop through the datatable

C#
foreach(datarow dr in datatable.rows)
{
//Update the database using the column indexer from the row dr["Name"]
}
 
Share this answer
 
Comments
Thanks7872 4-Mar-13 1:30am    
i framed it my way using your suggession....its awesome.....thanks for your quick reply and support...it works fine now........thanks alot.....
Mycroft Holmes 4-Mar-13 4:07am    
Excellent, always good to know the help is appreciated.
I'm guessing the error is at

SQL
string temp = dr2.GetString(i);



Here you are using i, which is the count of the rows returned, as an index into the collection of columns in the data row you are dealing with.

I suspect you want


SQL
string temp = dr2.GetString(XXXXX);


where XXXXX is the index to the column you want
 
Share this answer
 
Comments
Thanks7872 4-Mar-13 1:07am    
i is not the count returned,it is just the variable for for loop.I am using i such that,for first iteration,dr2.gerstring(0) that is first record from leave_bal,for second iteration,dr2.getstring(1),that is second record from leave_bal,is there any mistake in this concept?the way you suggested,i am making changes that dr2.getstring("name"),but its not working...any help?
_Maxxx_ 4-Mar-13 1:40am    
Yes - but the point is that i is a row counter and you were using it as a column index.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900