Results 1 to 10 of 10

Thread: Inner Join two tables and getting their values

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    Inner Join two tables and getting their values

    here is the code
    I'm getting the field names in the DataGridView but not the values

    Code:
    _string = "select Employer.CompanyName,Employee.Firstname from Employee inner join Employer on Employee.IDEmployer = Employer.EmployerID"
    here is the screenshot.
    Name:  Sql_Join_error.jpg
Views: 1632
Size:  7.3 KB

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Inner Join two tables and getting their values

    Obviously that's not all the relevant code so you might show us the rest. Presumably you are calling Fill on a data adapter. The first step is to check the value returned by that. If it's zero then that means that there are no rows that match your query criteria. If it's not zero then there's some issue with your binding.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    Re: Inner Join two tables and getting their values

    Here is the full code
    Code:
     Public Function DGVload_testsql() As DataView
            Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
            con.Open()
            '_string = "select * from Employee inner join Employer on Employee.IDEmployer = Employer.EmployerID"
            _string = "select Employer.CompanyName,Employee.Firstname from Employee inner join Employer on Employee.IDEmployer = Employer.EmployerID"
            Dim SampleSource As New DataSet
            Dim TableView As DataView
            Try
                Dim SampleCommand As New SqlCommand()
                Dim SampleDataAdapter = New SqlDataAdapter()
                SampleCommand.CommandText = _string
                SampleCommand.Connection = con
                SampleDataAdapter.SelectCommand = SampleCommand
                SampleDataAdapter.Fill(SampleSource)
                TableView = SampleSource.Tables(0).DefaultView
                'Use colors in DataGridView
                Test_dgv_sql.GridColor = Color.Red
                Test_dgv_sql.CellBorderStyle = DataGridViewCellBorderStyle.None
                Test_dgv_sql.BackgroundColor = Color.LightGray
    
                Test_dgv_sql.DefaultCellStyle.SelectionBackColor = Color.Brown
                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Yellow
    
                Test_dgv_sql.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]
    
                Test_dgv_sql.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                Test_dgv_sql.AllowUserToResizeColumns = False
    
                Test_dgv_sql.RowsDefaultCellStyle.BackColor = Color.Bisque
                Test_dgv_sql.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
    
                Test_dgv_sql.ColumnHeadersDefaultCellStyle.BackColor = Color.DarkRed
                Test_dgv_sql.ColumnHeadersDefaultCellStyle.ForeColor = Color.DarkGray
    
                'cells and rows height in DataGridView
                Dim row As DataGridViewRow = Me.Test_dgv_sql.RowTemplate
                row.DefaultCellStyle.BackColor = Color.Bisque
                row.Height = 30
                row.MinimumHeight = 15
    
            Catch ex As Exception
                Throw
            End Try
            Return TableView
        End Function
    End Class
    Code:
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
           
            Test_dgv_sql.DataSource = DGVload_testsql()
           
        End Sub

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Inner Join two tables and getting their values

    Are you sure there are matching records in the db?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    Re: Inner Join two tables and getting their values

    Yes both the tables have there first name matching,with column name Firstname
    Employer table,Fieldname Firstname = Danial
    Employee table,Fieldname Firstname = Danial

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Inner Join two tables and getting their values

    Maybe you could read all of my post instead of just the first sentence.
    Quote Originally Posted by jmcilhinney
    Presumably you are calling Fill on a data adapter. The first step is to check the value returned by that. If it's zero then that means that there are no rows that match your query criteria. If it's not zero then there's some issue with your binding.

  7. #7
    Lively Member
    Join Date
    Apr 2011
    Posts
    75

    Re: Inner Join two tables and getting their values

    Try to break down your problems into simple steps, verifying along the way what works and what doesn't, then you'll find where the problem lies.
    In this case, try a simple sql query and see if that works, before worrying about the join.
    Once the fill works, then you can worry about the SQL.

    And it's easier to play with SQL in Management Studio first, IMHO

  8. #8
    Lively Member
    Join Date
    Apr 2011
    Posts
    75

    Re: Inner Join two tables and getting their values

    (dupe - can't delete)
    (technical error made me re-post the posting, and surprise surprise it had already posted despite a "database error")

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Sep 2012
    Posts
    119

    Re: Inner Join two tables and getting their values

    I have assigned fk to EmpoyerID on the employee table and then the data is there ,but now I wonder weather now the two tables related or not ,If I create employer profile and add three employee how do I know they are related to the employer?

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Inner Join two tables and getting their values

    It should not allow you to enter and employer id in the employee table which is not in the employer table already.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width