Click here to Skip to main content
15,117,719 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two database first I am searching all custId from database1 and for all that custId I need to retrive their complete infirmation from database 2.

As custid will have more than one value ( say 10 custid I have and I need all customer info from database 2.

Here is my C# code:
C#
using System.Data.SqlClient;

    // .....

    string queryString = "Select custID from Table1"; 
    String TempCustID;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        
        while (reader.Read())
        {
          if (!reader.IsDBNull(0))
          {
             TempCustID = reader[0].ToString();
              
            // Here I need to connect to other database and use TempCustID 
            // variable to search in a Table in DB2
            // And display values in datagridview
            
          }
        }
        reader.Close();
    }
Posted
Updated 22-Sep-15 23:58pm
v3
Comments
F-ES Sitecore 23-Sep-15 5:17am
   
Use the same kind of code....you're already connecting to a database and getting data, so it's the same thing. You need a second connection string and connection, and you construct a select query from the second table where the id is the one read from the first. Google "ado.net paramterized queries" for examples on how to do this.
Member 12005342 23-Sep-15 6:02am
   
I tried constructing query but its giving me error

Here is my code for that
String strsql = "select * from customer where custID = " + TempCustID; //connecting database

DataTable customer = new Datable();
da1.Fill(Customer); // here I am getting error "no value given for one or more required parameters"
Datagridview1.data source = Customer;

Member 12005342 23-Sep-15 19:27pm
   
I have two database In database1 I am having table1 (custid,productid,customername )
Database2 : table2 (productname,productid, pice, mfg)

I want to display all the products purchased by particular custid


Here is my code :
C#

using System.Data.SqlClient;

// .....

string queryString = "Select custID from Table1 where custId ="+ textbox1.text;
String TempCustID;

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
if (!reader.IsDBNull(0))
{
TempCustID = reader[0].ToString();

String stringprovider = "@database connection string ";
Sqlconnection con2 = new sqlconnection(stringprovider);
Con2.open();
Cmd2.connection=con2;
Cmd2.commandType = new commandType.text;
Cmd2.commandText = "select * from Table2 where Productid = @productid";
Cmd2.parameters.addwithvalue("@productid",TempCustID);
}
}
reader.Close();
Dataset Ds = new dataset();
Oledbdataadaptaer da1 = new oledbdataadapter(cmd2);
Datatable Table2 = new Data table();
Da1.fill(table2);
Datagridview2.source = table2;


}


In this I am getting only the first product details of that customer its not displaying all the products at a time.
Please suggest.
Sergey Kizyan 25-Sep-15 10:52am
   
I don't see you execture reading second time. You need to have another ExecuteReader adn reader Close pair
Member 12005342 25-Sep-15 14:59pm
   
Its reading all the values but not displaying .. Even after using execute reader I am getting the same output only last value is getting displayed
Wendelius 28-Sep-15 16:36pm
   
Are the tables really located in different databases or do you have two tables in the same database?

1 solution

Usually join statement[^] is used in such of situations.

SQL
Select t1.custID, t2.TempCustId
From [dbo1].Table1 AS t1 INNER JOIN [dbo2].Table1 AS t2 ON t1.CustId = t2.TempCustId


Above query returns recordset where CustId field is equal to TempCustId field. For further information, please see: Visual Representation of SQL Joins[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900