web application handling some database!!

Question

hi!
i dont know what is wrong in this code. It shows no errors while compilation. but at the run time it  shows an error.

Error: There is no row at position 0.

here is the code:

 

    protected void Page_Load(object sender, EventArgs e)
    {
        cn.ConnectionString ="Data Source=rajan; initial catalog=dailymarket; integrated security=true";
       
         cn.Open ();
         try
         {

             SqlCommand cmd1 = new SqlCommand("select CompanyName from dailyupdate1_1", cn);
            
             da1 = new SqlDataAdapter(cmd1);
             da1.Fill(dt1);
             SqlCommand cmd = new SqlCommand("select CompanyName from dailyupdate1_1", cn);
            
             //SqlDataReader dtr1 = new SqlDataReader();
             SqlDataReader dtr1 = cmd.ExecuteReader();
             try
             {
                 while (dtr1.Read())
                 {

                     SqlCommand cmd2 = new SqlCommand("select companyID from CompanyId where CompanyName='" + dtr1["CompanyName"].ToString() + "' ", cn);
                     cmd2.ExecuteNonQuery();
                     da2 = new SqlDataAdapter(cmd2);
                     da2.Fill(dt2);
                 }
                 // cmd2.ExecuteNonQuery();

                 // da2 = new SqlDataAdapter(cmd2);
                 //da2.Fill(dt2);
             }
             catch
             {
             }
             finally
             {
                 dtr1.Close();
             }
             for (int i = 0; i < dt1.Rows.Count; i++)
             {
                 SqlCommand cmd3 = new SqlCommand("insert into CompanyData(CompanyID)" +
                     "values" +
                     "(" +

                               " '" + dt2.Rows[i]["CompanyID"].ToString().Replace("'", "''") + "', " +
                       ")", cn);
                 cmd3.ExecuteNonQuery();
             }

             SqlCommand cmd4 = new SqlCommand("select NumOfTransaction,PriceMax,PriceMin,PriceClose,TotalShare,Amount,PrevClose,Difference,date from dailyupdate1_1", cn);
            
             SqlDataAdapter da3 = new SqlDataAdapter();
             da3 = new SqlDataAdapter(cmd4);
             da3.Fill(dt3);

             for (int j = 0; j < dt1.Rows.Count; j++)
             {
                 SqlCommand cmd5 = new SqlCommand("insert into CompanyData(NumOfTransaction,PriceMax,PriceMin,PriceClose,TotalShare,Amount,PrevClose,Difference,date)" +
                     "values" +
                     "(" +
                      " '" + dt3.Rows[j]["NumOfTransaction"].ToString() + "', " +
               " '" + dt3.Rows[j]["PriceMax"].ToString() + "', " +
               " '" + dt3.Rows[j]["PriceMin"].ToString() + "', " +
               " '" + dt3.Rows[j]["PriceClose"].ToString() + "', " +
               " '" + dt3.Rows[j]["TotalShare"].ToString() + "', " +
               " '" + dt3.Rows[j]["Amount"].ToString() + "', " +
               " '" + dt3.Rows[j]["PrevClose"].ToString() + "', " +
               " '" + dt3.Rows[j]["Difference"].ToString() + "', " +
               " '" + dt3.Rows[j]["date"].ToString() + "', " +
               ")", cn);
                 cmd5.ExecuteNonQuery();
             }
         }
         catch (Exception ex)
         {
             Response.Write("Error: " + ex.Message);
         }
         finally
         {
             cn.Close();
         }

    }
}
 

 

Answer 1

 for (int i = 0; i < dt1.Rows.Count; i++)
             {
                 SqlCommand cmd3 = new SqlCommand("insert into CompanyData(CompanyID)" +
                     "values" +
                     "(" +
                               " '" + dt2.Rows[i]["CompanyID"].ToString().Replace("'", "''") + "', " +
                       ")", cn);
                 cmd3.ExecuteNonQuery();
             }

//you should loop in dt2. Rows.Count not dt1, you are reading data from dt2.


for (int j = 0; j < dt1.Rows.Count; j++)
             {
                 SqlCommand cmd5 = new SqlCommand("insert into CompanyData(NumOfTransaction,PriceMax,PriceMin,PriceClose,TotalShare,Amount,PrevClose,Difference,date)" +
                     "values" +
                     "(" +
                      " '" + dt3.Rows[j]["NumOfTransaction"].ToString() + "', " +
               " '" + dt3.Rows[j]["PriceMax"].ToString() + "', " +
               " '" + dt3.Rows[j]["PriceMin"].ToString() + "', " +
               " '" + dt3.Rows[j]["PriceClose"].ToString() + "', " +
               " '" + dt3.Rows[j]["TotalShare"].ToString() + "', " +
               " '" + dt3.Rows[j]["Amount"].ToString() + "', " +
               " '" + dt3.Rows[j]["PrevClose"].ToString() + "', " +
               " '" + dt3.Rows[j]["Difference"].ToString() + "', " +
               " '" + dt3.Rows[j]["date"].ToString() + "', " +
               ")", cn);
                 cmd5.ExecuteNonQuery();
             }

//you should loop in dt3. Rows.Count not dt1, you are reading data from dt3.

Here is the erros I found. These may couse the exception if dt1 rows count is bigger then dt2 or dt2. I wrote the corrections you need to do on the code above as comments

Answer 2

Thanks a lot Tamer,
   but invalidoperationexception occured that says
"There is already an open DataReader associated with this Command which must be closed first."

What can be done?

regards.

Answer 3

do you call cmd again before closing the DataReader object?

Could you send the latest code and more details about exception. Ex: the line that exception occured.

Answer 4

This is the latest code.

protected

 

void Page_Load(object sender, EventArgs e)

{

cn.ConnectionString =

"Data Source=rajan; initial catalog=dailymarket; integrated security=true";

 

cn.Open ();

 

try

{

 

SqlCommand cmd1 = new SqlCommand("select CompanyName from dailyupdate1_1", cn);

da1 =

new SqlDataAdapter(cmd1);

da1.Fill(dt1);

 

SqlCommand cmd = new SqlCommand("select CompanyName from dailyupdate1_1", cn);

 

SqlDataReader dtr1 = cmd.ExecuteReader();

 

try

{

 

while (dtr1.Read())

{

 

SqlCommand cmd2 = new SqlCommand("select companyID from CompanyId where CompanyName='" + dtr1["CompanyName"].ToString() + "' ", cn);

cmd2.ExecuteNonQuery();

da2 =

new SqlDataAdapter(cmd2);

da2.Fill(dt2);

}

 

 

}

 

catch

{

}

 

finally

{

dtr1.Close();

}

 

for (int i = 0; i < dt2.Rows.Count; i++)

{

 

SqlCommand cmd3 = new SqlCommand("insert into CompanyData(CompanyID)" +

 

"values" +

 

"(" +

 

" '" + dt2.Rows[i]["CompanyID"].ToString().Replace("'", "''") + "', " +

 

")", cn);

cmd3.ExecuteNonQuery();

}

 

SqlCommand cmd4 = new SqlCommand("select NumOfTransaction,PriceMax,PriceMin,PriceClose,TotalShare,Amount,PrevClose,Difference,date from dailyupdate1_1", cn);

 

 

SqlDataAdapter da3 = new SqlDataAdapter();

da3 =

new SqlDataAdapter(cmd4);

da3.Fill(dt3);

 

for (int j = 0; j < dt3.Rows.Count; j++)

{

 

SqlCommand cmd5 = new SqlCommand("insert into CompanyData(NumOfTransaction,PriceMax,PriceMin,PriceClose,TotalShare,Amount,PrevClose,Difference,date)" +

 

"values" +

 

"(" +

 

" '" + dt3.Rows[j]["NumOfTransaction"].ToString() + "', " +

 

" '" + dt3.Rows[j]["PriceMax"].ToString() + "', " +

 

" '" + dt3.Rows[j]["PriceMin"].ToString() + "', " +

 

" '" + dt3.Rows[j]["PriceClose"].ToString() + "', " +

 

" '" + dt3.Rows[j]["TotalShare"].ToString() + "', " +

 

" '" + dt3.Rows[j]["Amount"].ToString() + "', " +

 

" '" + dt3.Rows[j]["PrevClose"].ToString() + "', " +

 

" '" + dt3.Rows[j]["Difference"].ToString() + "', " +

 

" '" + dt3.Rows[j]["date"].ToString() + "', " +

 

")", cn);

cmd5.ExecuteNonQuery();

}

}

 

catch (Exception ex)

{

Response.Write(

"Error: " + ex.Message);

}

 

finally

{

cn.Close();

}

}

}


These are the two exception caused  and caught by two catch function respectively:

{"There is already an open DataReader associated with this Command which must be closed first."}

{"Incorrect syntax near ')'."}

Answer 5

Hi,

 

The exception is caused by the nature of DataReader.

While a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

 

You can get more information from this MSDN document,

http://msdn.microsoft.com/en-us/library/haa3afyz(VS.71).aspx

 

 

So, while a DataReader is open, you can create another connection instance of the command. Sample code (without try and catch),

 

           SqlConnection conn = new SqlConnection(@"Data Source=XXX;Initial Catalog=Student;Integrated Security=True;");

            SqlConnection conn1 = new SqlConnection(@"Data Source=XXX;Initial Catalog=Student;Integrated Security=True;");

            conn.Open();

          

            SqlCommand cmd1 = new SqlCommand("select name from stu",conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd1);

            DataSet ds = new DataSet();

 

            da.Fill(ds);

 

            SqlCommand cmd2 = new SqlCommand("select name from stu",conn);

            SqlDataReader dr = cmd2.ExecuteReader();

 

            conn1.Open();

            while (dr.Read())

            {

                SqlCommand cmd3 = new SqlCommand("select name from employee",conn1);

                cmd3.ExecuteNonQuery();

                SqlDataAdapter da2 = new SqlDataAdapter(cmd3);

                da2.Fill(ds);

            }

 

 

Does this work for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.