locked
show all colums from both tables using join RRS feed

  • 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"))

    Saturday, May 4, 2019 6:19 PM

All replies

  • 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?

    Saturday, May 4, 2019 7:26 PM
  • 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

    Sunday, May 5, 2019 5:28 PM
  • 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.

    Monday, May 6, 2019 10:41 AM
  • 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.

    Monday, May 6, 2019 12:15 PM
  • 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

    Wednesday, May 8, 2019 9:45 AM