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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.