show all colums from both tables using join

Question

User-2024981698 posted

Environment  : vs2010, Sql SErver 2008 R2, Vb.net

Sir

I have to tables having exactly same structure.  I create a inner join.  I want to show column 1 from table1 and colum1 from table 2. How to show this in ASP.net. Below is code sample.  which shows column from table a only,  how to show column from table b.

Dim SqlDataSource1 As New SqlDataSource()
Dim SQL As String

Dim connectionString As String
Dim connection As SqlConnection
connectionString = ConfigurationManager.ConnectionStrings("accountsConnectionString").ToString
connection = New SqlConnection(connectionString)
SQL = "select a.*,b.* from center19b a left join center19 b on a.centcode =b.centcode where a.centcode=" & txtcentcode.Text & " "

Try
connection.Open()
Dim cmd As New SqlCommand(SQL, connection)
Dim reader As SqlDataReader = cmd.ExecuteReader
centdetails.Visible = True
While reader.Read()
'ListBox1.Items.Add(reader.Item(0) & " " & reader.Item(1))
lblcentcode.Text = Session("centcode")
lblcentname.Text = reader.Item("centname") ''Name
lblIFSC.Text = IIf(String.IsNullOrEmpty(reader.Item("IFSC")), "", reader.Item("IFSC"))
lblacno.Text = IIf(String.IsNullOrEmpty(reader.Item("ACNO")), "", reader.Item("ACNO"))

Answer 1

User1120430333 posted

SQL = "select a.*,b.* from center19b a left join center19 b on a.centcode =b.centcode where a.centcode=" & txtcentcode.Text & " "

The T-SQL code being formulated that is using string concatenation with a Textbox control of user input leaves the solution wide open to a SQL Injection attack. You should  be using parmterized T-SQL  to prevent the SQL Injection attack 

https://www.dotnettricks.com/learn/sqlserver/sql-injection-attacks

https://stackoverflow.com/questions/11139791/how-to-use-parameters-in-an-sql-command-in-vb

Secondly, you're going to have to pick-out a columnnames on the' a.' between 'b'. and give the columnnames to be selected and using an alias on the colomnames between the two tables if all columnames are the same so that you can address them in the reader.

https://www.w3schools.com/sql/sql_alias.asp

a.IFSC as IFSC-a,  b.IFSC as IFSC-b

Capiche?

Answer 2

User-2024981698 posted

Thanks

I know that this may be a cause of sql injection attack.  Here I wrote to explain only.  as the column is read only in  the real application and we can can use parameter here also.

Using alias is there to  mention column name but I have many columns in table I thought that somebody may have any other solution .

Thanks

Answer 3

User1120430333 posted

Thanks

I know that this may be a cause of sql injection attack.  Here I wrote to explain only.  as the column is read only in  the real application and we can can use parameter here also.

Using alias is there to  mention column name but I have many columns in table I thought that somebody may have any other solution .

Thanks

I doubt that there is another solution other than using ADO.NET Entity Framework and Linq Join with projection.

Answer 4

User753101303 posted

Hi,

I would try perhaps "center19.IFSC" or "b.IFSC" to see if it works. According to http://www.mikepope.com/blog/AddComment.aspx?blogid=1718 it may work (or it could depend on the provider ?)

At worst list all columns and use aliases to have for example a_IFSC and b_IFSC.

Answer 5

User283571144 posted

Hi bhushan,

According to the possible solution mentioned by DA924, I tried to do it with linq and found that it can prevent all the columns from being operated when you write sql statement.

But for the data returned by linq, if you want to get the values of one or more columns, you still have to get them one by one.

Below is my example:

        public void GetData()
        {
            DataClasses1DataContext dc = new DataClasses1DataContext();
            var leftJoinSql = from r in dc.Tests.Where(c => c.Id == 1)
                              join ui in dc.TestCopies
                              on r.Id equals ui.Id
                              into users
                              from ur in users.DefaultIfEmpty()
                              select new
                             {
                                  column_A = r,//all columns in Tests table
                                  column_B = ur//all columns in TestCopies table
                              };
            foreach (var item in leftJoinSql)
            {
                TextBox1.Text += item.column_A.Name + " ";
                TextBox2.Text += item.column_B == null ? "" : item.column_B.Name + " ";
                TextBox3.Text += item.column_A.Id + " ";
                TextBox4.Text += item.column_B == null ? "" : item.column_B.Id + " ";
                TextBox5.Text += item.column_A.Gender + " ";
                TextBox6.Text += item.column_B == null ? "" : item.column_B.Gender + " ";
            }
        }

Result:

Best Regards,

Brando