How to two table bind in one table

Question

0.00/5 (No votes)

See more:

How to concadenate two tables into single table ?

Table 1

Name    city
Raja_1    Newyork_1
Guna_2    claifornia_2
Guna_3    claifornia_3

Table 2

DoorNo
1
2
3
4
5
7

Table 3 (expected Result)

Name    city               DoorNo
Raja_1    Newyork_1           1
Guna_2    claifornia_2        2
Guna_3    claifornia_3        3
                              4
                              5
                              6
                              7

I am using

dt1= DataTable
dt2= DataTable
dt3= DataTable

Repeater1.DataSource = dt3;
Repeater1.DataBind();

What I have tried:

C#

string QueryStringData = "SELECT Name,city from Table1;
                    QueryStringData += "SELECT DoorNo from Table2;
                    
using (SqlCommand com_Find = new SqlCommand(QueryStringData, con_Find))
{
   com_Find.CommandType = CommandType.Text;
   con_Find.Open();
   SqlDataAdapter con_Find_adpr = new SqlDataAdapter(com_Find);
   DataSet connection_ds_con_ds = new DataSet();
   con_Find_adpr.Fill(connection_ds_con_ds);
   DataTable dt1 = new DataTable();
   DataTable dt2 = new DataTable();
   DataTable dt3 = new DataTable();
   dt1 = connection_ds_con_ds.Tables[0];
   dt2 = connection_ds_con_ds.Tables[1];
                    
   if (dt1.Rows.Count == dt2.Rows.Count)
      for (int i = 0; i < dt1.Rows.Count; i++)
         dt3.Rows.Add(dt1.Rows[i]["Name"], dt1.Rows[i]["city"], dt2.Rows[i]["DoorNo"]);
   Repeater1.DataSource = dt3;
   Repeater1.DataBind();
}

Posted 4-Oct-19 21:53pm

Member 11185569

Updated 7-Oct-19 1:57am

Nelek

v2

Add a Solution

2 solutions

Add a Solution

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

OriginalGriff · Answer 1 · 2019-10-04T22:38:00

That's a silly requirement - the two data tables are unrelated, which means there is nothing to "tie" Raja_1 from one table with the "DoorNo" from the other table.

Which means that you need to look more closely at the source data - yoru database - and identify the "common factor" in Table1 and Table2, then instead of doing two SELECT commands to return two sets of unrelated data, you would use a JOIN query to return a single combined Data set: SQL Joins[^]

Any other way to do it does not guarantee that the same query(s) executed at slightly different times will give you the same results - and that's bad news!
Remember, unless you specify an order in which to return data as part of your query ("SELECT ... ORDER BY criteria") SQL is at liberty to return rows in any order it finds suitable - and that can change due to circumstances beyond your control. It may not happen, but it can - and then the info presented to your users changes, which can have drastic results.

Maciej Los · Answer 2 · 2019-10-07T01:57:00

Quote:
How to concadenate two tables into single table ?
...

C#
string QueryStringData = "SELECT Name,city from Table1;
                    QueryStringData += "SELECT DoorNo from Table2";

You CAN'T "concatenate" (join) tables by concatenating strings!

I completely agree with OriginalGriff's statements. There's no relationship between your tables! So, you need to define relationship between them to be able to get data from both tables. At this moment, you can workaround this by using below query (NOT recommended):

SQL

DECLARE @table1 TABLE ([Name] VARCHAR(50),City VARCHAR(50))
INSERT INTO @table1 ([Name], City)
VALUES('Raja_1', 'Newyork_1'),
('Guna_2', 'claifornia_2'),
('Guna_3', 'claifornia_3')

DECLARE @table2 TABLE(DoorNo INT)
INSERT INTO @table2 (DoorNo)
VALUES(1), (2), (3), 
(4), (5), (7)

SELECT t1.*, t2.*
FROM @table1 t1 
	INNER JOIN @table2 t2 ON CONVERT(INT, RIGHT(t1.Name, 1)) = t2.DoorNo

Result:

Name	City			DoorNo
Raja_1	Newyork_1		1
Guna_2	claifornia_2	2
Guna_3	claifornia_3	3

For further details, please see:
Joins (SQL Server) - SQL Server | Microsoft Docs[^]
Visual Representation of SQL Joins[^]

How to two table bind in one table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to two table bind in one table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us