Nested SQL commands

Hi Experts,
I am looking to insert new records to various tables depending on the connect of an existing table. So I am opening a read of the first table and then doing a While (table1.Read()) , within this while statement I create new records to be put into separate tables.
My issue is that the new tables will not insert as the original connections not closed, but if I close the connection prior to the While statement there is no data to read.
Please could you explain the best practice to do this?
Attached is a code sample of what I am trying to achieve.

string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(connStr);
SqlCommand SQLCmd = new SqlCommand("SPAdsKWParmstoAdd", con);
try
        {   SQLCmd.Connection.Open();
            SqlDataReader AddAds;
            AddAds = SQLCmd.ExecuteReader();
            while (AddAds.Read())
            {
SqlCommand SQLCmd1 = new SqlCommand("SPStatement1", con);
... some code to create data for the new records.....
SQLCmd1.Connection.Open();
SQLCmd1.ExecuteNonQuery();
SQLCmd1.Connection.Dispose();

SqlCommand SQLCmd2 = new SqlCommand("SPStatement2", con);
... some code to create data for the new records.....
SQLCmd2.Connection.Open();
SQLCmd2.ExecuteNonQuery();
SQLCmd2.Connection.Dispose();

}
SQLCmd.Connection.Close();
SQLCmd.Connection.Dispose();
}

Open in new window

Simon CrippsOwnerAsked:
Who is Participating?
 
cyberkiwiCommented:
What you have done there is correct.
The only thing to make sure is the at the SqlCmd produces a static dataset.  Even though the connection is not closed, the query has completed.  Think of SSMS where your query has finished and the connection is available for reuse - sqlcmd1 and sqlcmd2 should be able to work as normal.
0
 
Alfred A.Commented:
0
 
Alfred A.Commented:
Oops.  Check this out for C#.  Previous one is VB.NET

http://support.microsoft.com/kb/308507
0
 
Simon CrippsOwnerAuthor Commented:
Thanks your points have helped me move forwad a few more steps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.