Actually, Ben, Fred is right ... maybe I should have replied to him yesterday to give him an "attaboy" when I saw his post. Here's how you would do this:
string ConnString = "server=(local);uid=sa;pwd=";
SqlConnection oConn = new SqlConnection(ConnString);
SqlCommand sc = new SqlCommand("use MyDataBase", oConn);
oConn.Open();
sc.ExecuteNonQuery();
sc.CommandText = "select * from MyTable";
SqlDataAdapter da = new SqlDataAdapter(sc);
DataSet ds = new DataSet();
da.Fill(ds);
oConn.Close();
The trick is to open the connection and "use" your database, then keeping the same connection, do whatever else you need to do.
~~Bonnie
>>You don't need to connect to your database in your connection string, just to your server. You can then connect to the appropriate database using the "USE yourdatabase" command via a SQLEXEC. This way, you can change databases using the same connection.
>
>Actually, as it was stated by Borislav and Bonnie earlier in this thread...one
has to use the database name in the connection string (Initial Catalog=...) otherwise it won't work. It you notice in my original posting, I mentioned that I tried using the "USE
" SQL command and it came back with an error stating that CREATE PROCEDURE has to be the first executing statement in a batch.