• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

SQL Query Data Join Multiple Tables

Dear Experts-Exchange,

Please see the SQL tables below and SQL/VB.NET query syntax:

tblDepartments                              

DeptID  |   Department_Name
1                     DeptA
2                     DeptB
3                     DeptC

tblEmployee

EmplID | LName |  FName  | DeptID
5              Fish         Paul            2
9              Mark       Jill               3
12            Lois         Phil             1

tblUsers

UserID     EmplID    Password    Admin
pfish            5              lalala            0
jmark           9              vvvvvv         2
plois            12             tttttt             1

Now, the SQL query syntax in vb.net code:

strSQL = "SELECT empl.EmplID as [EmployeeID], " & _
                "empl.LName as [LastName], " & _
                "empl.FName as [FirstName], " & _
                "empl.Department as [Department], " & _
                "user.UserID as [UserID], " & _
                "user.Password as [Password], " & _
                "user.Administrator as [Administrator] " & _
                "FROM employee as empl left outer join users as user on " & _
                "empl.emplid=user.emploid where empl.EmplID = '" & e.CommandArgument & "'"


It’s working good, but I am trying to add the tbldepartments to be joined with other tables in SQL syntax

strSQL = "SELECT empl.EmplID as [EmployeeID], " & _
                "empl.LName as [LastName], " & _
                "empl.FName as [FirstName], " & _
                "empl.Department as [Department], " & _
                "user.UserID as [UserID], " & _
                "user.Password as [Password], " & _
                "user.Administrator as [Administrator], " & _
                “dept.DeptID as [DeptID]                
                “dept.Department_Name as [DeptName] “ & _
                "FROM employee as empl left outer join users as user INNER JOIN departments as dept on " & _
                "empl.emplid=user.emplid where empl.EmplID = '" & e.CommandArgument & "'"

strCnn = System.Configuration.ConfigurationManager.AppSettings("ABC")
            cnn = New SqlConnection(strCnn)

            cmd = New SqlCommand(strSQL, cnn)
            cnn.Open()

            dr = cmd.ExecuteReader(CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)

            Do While dr.Read
                If dr("EmplID") Is DBNull.Value Then
                    Me.LblEmplID.Text = String.Empty
                Else
                    Me.LblEmplID.Text = dr("EmplID")
                End If
                If dr("LName") Is DBNull.Value Then
                    Me.TxtLName.Text = String.Empty
                Else
                    Me.TxtLName.Text = dr("LName")
                End If
                If dr("FName") Is DBNull.Value Then
                    Me.TxtFname.Text = String.Empty
                Else
                    Me.TxtFname.Text = dr("FName")
                End If
                If dr("UserID") Is DBNull.Value Then
                    Me.TxtUserID.Text = String.Empty
                Else
                    Me.TxtUserID.Text = dr("UserID")
                End If
                If dr("Password") Is DBNull.Value Then
                    Me.TxtUpdPassword.Text = String.Empty
                Else
                    Me.TxtUpdPassword.Text = dr("Password")
                End If

                If dr("Department_Name") Is DBNull.Value Then
                    Dim li As New ListItem(dr("Department_Name"))
                    li.Value = dr("Deptid")
                    Me.ddlDep.Items.Add(li)
                End If
            Loop
dr.Close()
        End If

This SQL Syntax is not working.  What am I doing wrong?  Your attention and help is much appreciative. Thanks.


0
slb2008
Asked:
slb2008
  • 4
  • 2
1 Solution
 
srikanthreddyn143Commented:
Join was not proper with department

SELECT empl.EmplID as [EmployeeID], " & _
                "empl.LName as [LastName], " & _
                "empl.FName as [FirstName], " & _
                "empl.Department as [Department], " & _
                "user.UserID as [UserID], " & _
                "user.Password as [Password], " & _
                "user.Administrator as [Administrator], " & _
                “dept.DeptID as [DeptID]                
                “dept.Department_Name as [DeptName] “ & _
                "FROM employee as empl left outer join users as useron " & _
                "empl.emplid=user.emplid  INNER JOIN departments as dept  on dept.DeptID= empl.DeptID where empl.EmplID = '" & e.CommandArgument & "'"
0
 
slb2008Author Commented:
srikanthereddy, Thanks for your quick response.
I am going to test the sql syntax you sent.  I'll let you know soon.  thanks.
0
 
slb2008Author Commented:
I am back to tell you the SQL Syntax works very well, but doesn't display the data on department field using VB.NET :
strCnn = System.Configuration.ConfigurationManager.AppSettings("ABC")
            cnn = New SqlConnection(strCnn)

            cmd = New SqlCommand(strSQL, cnn)
            cnn.Open()

            dr = cmd.ExecuteReader(CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)

            Do While dr.Read
                If dr("EmplID") Is DBNull.Value Then
                    Me.LblEmplID.Text = String.Empty
                Else
                    Me.LblEmplID.Text = dr("EmplID")
                End If
                If dr("LName") Is DBNull.Value Then
                    Me.TxtLName.Text = String.Empty
                Else
                    Me.TxtLName.Text = dr("LName")
                End If
                If dr("FName") Is DBNull.Value Then
                    Me.TxtFname.Text = String.Empty
                Else
                    Me.TxtFname.Text = dr("FName")
                End If
                If dr("UserID") Is DBNull.Value Then
                    Me.TxtUserID.Text = String.Empty
                Else
                    Me.TxtUserID.Text = dr("UserID")
                End If
                If dr("Password") Is DBNull.Value Then
                    Me.TxtUpdPassword.Text = String.Empty
                Else
                    Me.TxtUpdPassword.Text = dr("Password")
                End If

   HERE IS NOT WORKING--->             If dr("Department_Name ") Is DBNull.Value Then
                    Dim li As New ListItem(dr("Department_Name"))
                    li.Value = dr("Deptid")
                    Me.ddlDep.Items.Add(li)   <----
Display an error :  Object reference not set to an instance of an object....
                End If
            Loop
dr.Close()
        End If
 
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
srikanthreddyn143Commented:
You should use DeptName rather than Department_Name
0
 
slb2008Author Commented:
That's right.  
Well you helped me to solved my little problem.
The SQL Syntax was great.  You will take 500 points.  Thanks so much for your help.
slb2008
0
 
slb2008Author Commented:
helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now