SQL Server, SQL Server Express, and SqlDataSource Control

Re: Nested Stored Proecedure

bedubs — Fri, 29 Aug 2008 21:22:56 GMT

I found out the answer I need to add

MultipleActiveResultSets=True

Re: Nested Stored Proecedure

bedubs — Fri, 29 Aug 2008 16:00:20 GMT

YES Thank you jameswright for the quick response. That is exactly what i need. I think i might have something wrong on my syntax because when i execute my script i receive an error : There is already an open DataReader associated with this Command which must be closed first.

Re: Nested Stored Proecedure

jameswright — Fri, 29 Aug 2008 15:36:40 GMT

Actually, you're still connected to the database inside your while loop. So, you would just need to construct the new command you need and and use cmd.ExecuteNonQuery().

while(reader.Read())
{
string val = //Get val from API.

SqlCommand update = new SqlCommand("UPDATE MyTable ... ");
update.ExecuteNonQuery();
}

..close up connection. That on the right track?

Nested Stored Proecedure

bedubs — Fri, 29 Aug 2008 15:27:16 GMT

I have a pretty basic connection to a database, My question is nested in the While Statement
C#.NET
MSSQL 2005
Data.SqlClient

        String strCon = "Server=...";
        SqlConnection conn = new SqlConnection(strCon);
        SqlDataReader rdr = null;

        conn.Open();

        SqlCommand cmd = new SqlCommand("select intID, email from cventcontacts WHERE URL is null",conn);

        rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
           	// I want to get information from a 3rd party API based on email from rdr[1] then UPDATE the record in my database
	// I think i can figure out the api side of it, but how do i reconnect to the database and update the record?
        }
        rdr.Close();
        conn.Close();
        emails.Text = sb.ToString();

Thanks in advance