CODEWITHSUNDEEP
c#sql
Karthikeyan P
Karthikeyan P

Reputation: 1267

Search data from two different tables that two tables in different databases

I have two tables in different SQL Server databases.

I want to display all the products purchased by particular custid.

My code:

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 it's not displaying all the products at a time.

Upvotes: 0

Views: 1457

Answers (3)

japzdivino
japzdivino

Reputation: 1746

Change your select statement. join the two tables using INNER JOIN with their productid

SELECT * 
FROM Table2 
INNER JOIN Table1 ON TABLE2.productid = Table1.productid 
WHERE Table2.productid = @productid

That will display all records in two tables, if you want to select specific tables, just remove (*) and replace with the column names you want.

SELECT 
    Table1.customername, Table2.productname, Table2.productid, 
    Table2.pice, Table2.mfg 
FROM 
    Table2 
INNER JOIN 
    Table1 ON TABLE2.productid = Table1.productid 
WHERE 
    Table2.productid = @productid

Hope my sample code above helps you. :)

Upvotes: 1

Rakesh
Rakesh

Reputation: 228

You are selecting custID from Table1... Instead of that select productid from Table 1 depending upon customerID

Modify your first query as :

string queryString = "Select productid from Table1 where custId ="+ textbox1.text;

also command is executing outside the loop...

Upvotes: 0

Karthikeyan P
Karthikeyan P

Reputation: 1267

Separate this into two methods. First will fetch the customer IDs from database one based on the condition. Then pass those customer IDs to second method and fetch the product details. You may create list of customer IDs from first method and build SQL IN clause in second method. Refer Building SQL “where in” statement from list of strings in one line?

Upvotes: 1

Related Questions