none
sqlcommand retreiving info from 2 databases RRS feed

  • Question


  • Hi Everybody,

        I encountered a nice issue today, and on a look throw Google i can't find anything to help me, or maybe i don't search for the right words. I will explain what i am trying to obtain in my code, and if you can help me in any way (even some useful links) i will be thankful.

        First of all I am working in VS2005, in ASP language. On a page I have a button which is checking some info in my database; attached is the code behind the click event for the button.
        The problem is that in my sqlcomman d i am using a select (in the select the tables contain the db_name.schema_name.table_name) which retrieves info from 2 databases , and i have only one connection, to one database. How is the procedure to handle this kind of request, how do i make 2 connections to the databases?


    protected void btn_CheckproductsID_Click(object sender, EventArgs e)
        {
            string productsID;
           
            productsID = drpList_productsID.SelectedValue.ToString();
        
            if (drpList_productsID.SelectedValue.ToString() == "all")
            {
                productsID = "13','235','333','213','261,'112','312";
            }
          
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=HOME\\SQLEXPRESS;Initial Catalog=STORE;Integrated Security=True");
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand("use store use retail select
    a.product,b.clientID from store.dbo.products as a inner join retail.retail_config.clients as b on a.product=b.product where b.payment='"+ txt_nrSEQ.Text.ToString() + "'", conn);                                                                                                                    
                SqlDataReader reader = sqlCommand.ExecuteReader();

                dtGridCategory.DataSource = reader;
                dtGridCategory.DataBind();
                conn.Close();
            }
            catch (Exception ex)
            {
                WebMsgBox.Show(ex.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }      
        }
    Friday, April 9, 2010 10:41 AM

Answers

  • If both databases located on same server and you have permissions to accees both of them that you should be able to do this by specifying database name in your query using [database].[schema].object format. You do not need to use "use databasename" statements in this case. Do you receive any error? Did you try to execute same SQL statement in SQL Server Management Studio?

     

     


    Val Mazur (MVP) http://www.xporttools.net
    Friday, April 9, 2010 10:48 AM
    Moderator

All replies

  • If both databases located on same server and you have permissions to accees both of them that you should be able to do this by specifying database name in your query using [database].[schema].object format. You do not need to use "use databasename" statements in this case. Do you receive any error? Did you try to execute same SQL statement in SQL Server Management Studio?

     

     


    Val Mazur (MVP) http://www.xporttools.net
    Friday, April 9, 2010 10:48 AM
    Moderator
  • Yes, u are right. I copied the select from SSMS into the code, but after that i made some restores in my test database, and did not work anymore the query. Problem solved.

     

    Thank you :).

    Friday, April 9, 2010 1:19 PM