Simon Cripps
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.
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();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks your points have helped me move forwad a few more steps
http://support.microsoft.com/kb/308055