We help IT Professionals succeed at work.
Get Started

Build SQL SELECT query on the fly from three tables ASP.NET - VB

spaceneedlejumper
on
306 Views
Last Modified: 2012-05-06
I am building a job board for the community college I attend.

I need to search three tables simultaneously.

Table 1)  Employer Table - BusinessName, EmpID
Table 2)  Jobs Table - JobID, JobTitle, JobType, DateLastModified, Status
Table 3)  JobType Table - There are about 30 fields here, all with True or False values representing the different degree and certificate programs offered by the college.  I need to check for True values in any fields where the user checks a checkbox during their search.

I'm having trouble getting this third table into the query correctly.  

Here is a screenshot of the search page and the code that builds the SELECT statement, and a copy of the SELECT statement when the user checks "Full Time Permanent" and "Aerospace".

CONTENTS OF STUDENTSSEARCHJOBS.ASPX.VB
*****************************************************************************
 
 
Partial Class StudentSearchJobs
    Inherits System.Web.UI.Page
 
    Protected Sub btn_Search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Search.Click
        Dim TypeCheck As Boolean = False
        Dim JobCommand As String = "SELECT DISTINCT JobID, " & _
                "JobTitle, " & _
                "DateLastModified, " & _
                "Employer.EmpID, " & _
                "Employer.Emp_BusinessName " & _
                "FROM [Jobs] INNER JOIN [Employer] " & _
                "ON Jobs.EmpID = Employer.EmpID " & _
                "WHERE Jobs.Status = 'Active' AND"
        '   Check every checkbox and build the query with all that are checked
        '   For the first four checkboxes set the TypeCheck flag to True the first time we hit one that is checked.
        '   For the last 22, set the FieldCheck flag to True
 
        '   Build the JobCommand
        '   Type of Work Checkboxes
        If CBT_FullTimePermanent.Checked = True Then
            JobCommand += " ([JobType] = 'Full Time Job')"
            TypeCheck = True
        End If
        If CBT_PartTimePermanent.Checked = True Then
            If TypeCheck = True Then
                JobCommand += " OR ([JobType] = 'Part Time Job')"
            Else
                JobCommand += " ([JobType] = 'Part Time Job')"
                TypeCheck = True
            End If
        End If
        If CBT_PaidInternships.Checked = True Then
            If TypeCheck = True Then
                JobCommand += " OR ([JobType] = 'Paid Internship')"
            Else
                JobCommand += " ([JobType] = 'Paid Internship')"
                TypeCheck = True
            End If
        End If
        If CBT_UnpaidInternships.Checked = True Then
            If TypeCheck = True Then
                JobCommand += " OR ([JobType] = 'Un-paid Internship')"
            Else
                JobCommand += " ([JobType] = 'Un-paid Internship')"
                TypeCheck = True
            End If
        End If
 
        '   Field of Study checkboxes
        If CBF_Aerospace.Checked = True Then
            JobCommand += " AND ([JobType.JobClassAerospace = True])"
        End If
 
        JobCommand += " ORDER BY [DateLastModified] DESC, [JobTitle];"
 
        Session("MyQuery") = JobCommand
 
        If TypeCheck = False Then
            lbl_error.Visible = True
            Exit Sub
        Else
            Server.Transfer("StudentSearchResults.aspx")
        End If
 
    End Sub
End Class
 
 
*****************************************************************************
COPY OF THE SELECT STATEMENT
 
"SELECT DISTINCT JobID, JobTitle, DateLastModified, Employer.EmpID, Employer.Emp_BusinessName FROM [Jobs] INNER JOIN [Employer] ON Jobs.EmpID = Employer.EmpID WHERE Jobs.Status = 'Active' AND ([JobType] = 'Full Time Job') AND ([JobType.JobClassAerospace = True]) ORDER BY [DateLastModified] DESC, [JobTitle];"

Open in new window

SearchPage.gif
Comment
Watch Question
Data Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE