How Can I Combine Two Tables From Two Comboboxes And Display Result In Datagridview

Question

2.00/5 (3 votes)

See more:

Hi
I have two comboboxes and both comboboxes are being populated with the same tables from sql server.(eg. T1,T2& T3). I want to dynamically choose a table from each combobox and JOIN or Combine the selected tables together with a BUTTON and display the result in a DataGridView. By the way all Tables have the same columns.

This small project is a c# winforms application. Can somebody please help me

Summary
Two comboboxes with tables
Select table dynamically from each combobox
Join/Combine the the selected values with a Button and
Finally display the joined Tables in Datagridview

Posted 7-Oct-14 23:28pm

mikybrain1

Add a Solution

Comments

VC.J 8-Oct-14 5:35am

what you want to do is to show the selected text of combo boxes combine it and then show it in a gridView column ?

mikybrain1 8-Oct-14 7:27am

Yes.
Hi VCJ thankx for your question but i wanna choose each table from each combobox and then click a btn to display the result
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?

mikybrain1 8-Oct-14 10:52am

Yes.
Hi VCJ thankx for your question but i wanna choose each table from each combobox and then click a btn to display the result
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?

ibrahim_ragab 8-Oct-14 5:45am

are you want to merge two table in database and display result in datagrid?

mikybrain1 8-Oct-14 7:32am

Yes. The comboboxes are both fed with the tables and my problem now is how to merge these tables from each combobox and display the union or the merge statement in DataGridview
Something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
Thnx.

ibrahim_ragab 8-Oct-14 7:53am

Suppose first table entries are{one,two,three}
and second table entries {four,five,six}
you want result to be
{one,two,three,four,five,six} ->table entries=6
OR {one four,two five,three six} ->table entries=3

mikybrain1 8-Oct-14 7:59am

I wanna have something like ur first suggestion
{one,two,three,four,five,six} ->table entries=6

mikybrain1 8-Oct-14 10:52am

Yes. The comboboxes are both fed with the tables and my problem now is how to merge these tables from each combobox and display the union or the merge statement in DataGridview
Something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
Thnx.

2 solutions

Solution 2

Did You Expect Like this Result ? just Example i use only SQL and Temp tables for beter Understanding the concepts ........

SQL

Declare @Combobox1 NVARCHAR(30)
Declare @Combobox2 NVARCHAR(30)

SET @Combobox1='@TestTbl1'
SET @Combobox2='@TestTbl2'

Declare @TestTbl1 AS Table (Id Int,Name NVARCHAR(20))

Declare @TestTbl2 AS Table (Id Int,Name NVARCHAR(20))

Declare @TestTbl3 AS Table (Id Int,Name NVARCHAR(20))

INSERT INTO @TestTbl1 (Id,Name) VALUES (1,'A')
INSERT INTO @TestTbl1 (Id,Name) VALUES (2,'B')
INSERT INTO @TestTbl1 (Id,Name) VALUES (3,'C')
INSERT INTO @TestTbl1 (Id,Name) VALUES (4,'D')
INSERT INTO @TestTbl1 (Id,Name) VALUES (5,'E')


INSERT INTO @TestTbl2 (Id,Name) VALUES (6,'F')
INSERT INTO @TestTbl2 (Id,Name) VALUES (7,'G')
INSERT INTO @TestTbl2 (Id,Name) VALUES (8,'H')
INSERT INTO @TestTbl2 (Id,Name) VALUES (9,'I')
INSERT INTO @TestTbl2 (Id,Name) VALUES (10,'J')

INSERT INTO @TestTbl3 (Id,Name) VALUES (11,'K')
INSERT INTO @TestTbl3 (Id,Name) VALUES (12,'L')
INSERT INTO @TestTbl3 (Id,Name) VALUES (13,'M')
INSERT INTO @TestTbl3 (Id,Name) VALUES (14,'N')
INSERT INTO @TestTbl3 (Id,Name) VALUES (15,'O')



IF(@Combobox1='@TestTbl1' AND @Combobox2='@TestTbl2' )

BEGIN

SELECT * FROM @TestTbl1 UNION ALL
SELECT * FROM @TestTbl2



END

ELSE IF(@Combobox1='@TestTbl1' AND @Combobox2='@TestTbl3' )

BEGIN

SELECT * FROM @TestTbl1 UNION ALL
SELECT * FROM @TestTbl3

END

Out Put:

VB

Id  Name
1   A
2   B
3   C
4   D
5   E
6   F
7   G
8   H
9   I
10  J

Posted 8-Oct-14 2:52am

Santhosh Babu Mahimairaj

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Babu Mahimairaj · Accepted Answer · 2014-10-08T00:33:00

Solution 1

try to use dynamic query it will help you

http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

Building Dynamic SQL In a Stored Procedure[^]

Posted 8-Oct-14 0:33am

Santhosh Babu Mahimairaj

Comments

mikybrain1 8-Oct-14 7:35am

Hi Santhosh
Thankx for your sugesstion but i wanna choose each table from each combobox and then click a btn to display the result without a where clause because all columns for the Tables are the same
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?

[no name] 8-Oct-14 8:11am

i could not get this (maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked) colud you please expalin in detail or post snipset regarding this .........

mikybrain1 8-Oct-14 8:20am

Hi. Thatz my code so far which only populates the two comboboxes

/* CREATE A FUNCTION: Code to fill in Combobox..
And have to call the funcktion later in the InitializedComponent */

private void FillCombobox1()
{
// Sql Connection and statement
string CS = (@"Data Source=local;Initial Catalog=DB;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// Statement
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{
// Open connection
con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox1.DataSource = dt;

//Display dbo Tables in cb
comboBox1.DisplayMember = "TABLE_NAME";
comboBox1.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox1.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}

/* CREATE a 2. FUNCTION: Code to fill in Combobox. I can always write my own function.
And have to call the funcktion later in the InitializedComponent */

private void FillCombobox2()
{
// Sql Connection and statement
string CS = (@"Data Source=local;Initial Catalog=DB;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// Statement
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{
// Open connection
con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox2.DataSource = dt;

//Display dbo Tables in cb
comboBox2.DisplayMember = "TABLE_NAME";
comboBox2.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox2.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}

// Populate dgv with the two comboboxes
private void button7_Click(object sender, EventArgs e)
{
// Sql Server Connection and statement
string CS = (@"Data Source=local;Initial Catalog=DB;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// IF STATEMENT & Sql Server Connection and TSQL Statement
if (comboBox1.SelectedValue != null && comboBox2.SelectedValue != null)
{

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT * FROM " + comboBox1.SelectedValue UNION ALL "SELECT * FROM" +comboBox2.SelectedValue);

// adapter is return the result in Datagridview
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

try
{
// Open connection
con.Open();

// Save the result

[no name] 8-Oct-14 8:48am

okay fine your code is correct where you struck ?

mikybrain1 8-Oct-14 9:47am

wow that looks good but i wanna have something like this:
i want to choose dynamically from each combobox. i wanna try and picture it for you once more. Unforturnately i cant send u a screenshot of my winform but here is an illustration

cb1 cb2
[t1,t2,t3,...] [t1,t2,t3,t5,t7...] ->the two comboboxes

[t3] ->selectedItem in cb1 [t7] ->selectedItem in cb2

[btnUnion] ->if this button is clicked,

Datagridview
[ ] Contains the uion results
[ ]

This means i can change the cb values dynamically and get the union results
i can choose

[t3] [t4]
[t1] [t10]
[t4] [t8]
and so on
Hope u get me now

[no name] 8-Oct-14 9:44am

Hi Dude thisis your Excepetation ?
(Execute the code in MSsql)
Declare @Combobox1 NVARCHAR(30)
Declare @Combobox2 NVARCHAR(30)

SET @Combobox1='@TestTbl1'
SET @Combobox2='@TestTbl2'

Declare @TestTbl1 AS Table (Id Int,Name NVARCHAR(20))

Declare @TestTbl2 AS Table (Id Int,Name NVARCHAR(20))

Declare @TestTbl3 AS Table (Id Int,Name NVARCHAR(20))

INSERT INTO @TestTbl1 (Id,Name) VALUES (1,'A')
INSERT INTO @TestTbl1 (Id,Name) VALUES (2,'B')
INSERT INTO @TestTbl1 (Id,Name) VALUES (3,'C')
INSERT INTO @TestTbl1 (Id,Name) VALUES (4,'D')
INSERT INTO @TestTbl1 (Id,Name) VALUES (5,'E')

INSERT INTO @TestTbl2 (Id,Name) VALUES (6,'F')
INSERT INTO @TestTbl2 (Id,Name) VALUES (7,'G')
INSERT INTO @TestTbl2 (Id,Name) VALUES (8,'H')
INSERT INTO @TestTbl2 (Id,Name) VALUES (9,'I')
INSERT INTO @TestTbl2 (Id,Name) VALUES (10,'J')

INSERT INTO @TestTbl3 (Id,Name) VALUES (11,'K')
INSERT INTO @TestTbl3 (Id,Name) VALUES (12,'L')
INSERT INTO @TestTbl3 (Id,Name) VALUES (13,'M')
INSERT INTO @TestTbl3 (Id,Name) VALUES (14,'N')
INSERT INTO @TestTbl3 (Id,Name) VALUES (15,'O')

IF(@Combobox1='@TestTbl1' AND @Combobox2='@TestTbl2' )

BEGIN

SELECT * FROM @TestTbl1 UNION ALL
SELECT * FROM @TestTbl2

END

ELSE IF(@Combobox1='@TestTbl1' AND @Combobox2='@TestTbl3' )

BEGIN

SELECT * FROM @TestTbl1 UNION ALL
SELECT * FROM @TestTbl3

END

mikybrain1 15-Oct-14 6:13am

Hi
Am getting problem retrieving information from two tables with exact the same colums.

The tables are:
Year 2009
projNr | location | Amount
A.023 East 10000€
A.023 East 2000€
A.023 East 5000€
C.100 East 8000€
Z.253 East 25000€

Year 2010
projNr | location | Amount
A.023 East 500€
A.023 East 500€
C.100 East 1000€
Z.253 East 5000€

Now i wanna find out the Amount from Year 2009 and 2010
based on projNr A.023 or maybe dynamically.

That what i query but it kept repeating the results:

SELECT a.proNr, a.Amount, a.location, b.Amount
FROM Year 2009 AS a
INNER JOIN Year 2010 AS b
ON a.projNr = b.projNr
WHERE a.projNr = 'A.023' AND b.projNr = 'A.023'

Can someone please help me? Instead of 5 records it repeating the records till 12 rows

[no name] 8-Oct-14 9:59am

your Code is correct duede

chnage this alnoe it well work

cmd.CommandText = ("SELECT * FROM " + comboBox1.SelectedValue + "UNION ALL SELECT * FROM" +comboBox2.SelectedValue);

mikybrain1 8-Oct-14 10:25am

Thank you bro ;)

Am now on the road. Will check it out when i get home.
Its was very nice of you helping me out. wish u a nice evening