Link to home
Start Free TrialLog in
Avatar of Simon Cripps
Simon CrippsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Simon Cripps

ASKER

Thanks your points have helped me move forwad a few more steps