StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Dynamically build your SQL, like in the following:</p> <pre><code>Private Sub LoadData() 'Initialize first grdResults.DataSource = Nothing grdResults.DataBind() grdResults.Visible = False grdResultsRequestedBy.DataSource = Nothing grdResultsRequestedBy.DataBind() grdResultsRequestedBy.Visible = False grdResultsCallDate.DataSource = Nothing grdResultsCallDate.DataBind() grdResultsCallDate.Visible = False btnExportToExcel.Visible = False lblNoneFound.Visible = True ' We have to include null values when no value for a given field is provided. ' The like search "%" does not return rows with null values!! Dim strShiftType As String If (ddlShiftType.SelectedValue = "<- All ->") Then strShiftType = "" Else strShiftType = " and sr.Shift_Type_ID = " & "'" & ddlShiftType.SelectedValue & "' " End If ' Only allow good dates ' If one of Start or End is populated the other one will get the same value assigned. ' This avoids unnecessary entry if you deal with a range of one day only! If txtShiftDateRangeStart.Text <> "" And txtShiftDateRangeEnd.Text = "" Then txtShiftDateRangeEnd.Text = txtShiftDateRangeStart.Text End If If txtShiftDateRangeStart.Text = "" And txtShiftDateRangeEnd.Text <> "" Then txtShiftDateRangeStart.Text = txtShiftDateRangeEnd.Text End If Dim strShiftDate As String If txtShiftDateRangeStart.Text <> "" And txtShiftDateRangeEnd.Text <> "" And IsDate(txtShiftDateRangeStart.Text) And IsDate(txtShiftDateRangeEnd.Text) Then strShiftDate = " and (sr.shift_date between " & "'" & txtShiftDateRangeStart.Text & "' and '" & txtShiftDateRangeEnd.Text & "')" Else strShiftDate = "" txtShiftDateRangeStart.Text = "" txtShiftDateRangeEnd.Text = "" End If Dim strRequestedBy As String If (ddlRequestedBy.SelectedValue = "<- All ->") Then strRequestedBy = "" Else strRequestedBy = " and bi.Requested_By = " & "'" & ddlRequestedBy.SelectedValue & "' " End If Dim txtCallNumberlike As String If (txtCallNumber.Text & "%" = "%") Then txtCallNumberlike = "" Else txtCallNumberlike = " and (bi.Call_Number LIKE @CallNumber) " End If ' Only allow good dates ' If one of Start or End is populated the other one will get the same value assigned. ' This avoids unnecessary entry if you deal with a range of one day only! If txtCallDateRangeStart.Text <> "" And txtCallDateRangeEnd.Text = "" Then txtCallDateRangeEnd.Text = txtCallDateRangeStart.Text End If If txtCallDateRangeStart.Text = "" And txtCallDateRangeEnd.Text <> "" Then txtCallDateRangeStart.Text = txtCallDateRangeEnd.Text End If Dim strCallDate As String If txtCallDateRangeStart.Text <> "" And txtCallDateRangeEnd.Text <> "" And IsDate(txtCallDateRangeStart.Text) And IsDate(txtCallDateRangeEnd.Text) Then strCallDate = " and (cast(bi.Call_date as datetime) between " & "'" & txtCallDateRangeStart.Text & "' and '" & txtCallDateRangeEnd.Text & "')" Else strCallDate = "" txtCallDateRangeStart.Text = "" txtCallDateRangeEnd.Text = "" End If Dim strDispatcher As String If (ddlDispatcher.SelectedValue = "<- All ->") Then strDispatcher = "" Else strDispatcher = " and bi.Dispatcher_ID = " & "'" & ddlDispatcher.SelectedValue & "' " End If Dim strEmployeeCompletingBillingInquiry As String If (ddlEmployeeCompletingBillingInquiry.SelectedValue = "<- All ->") Then strEmployeeCompletingBillingInquiry = "" Else strEmployeeCompletingBillingInquiry = " and bi.Employee_Completing_Billing_Inquiry_ID = " & "'" & ddlEmployeeCompletingBillingInquiry.SelectedValue & "' " End If ' Only allow good dates Dim strWhenCreated As String ' If one of Start or End is populated the other one will get the same value assigned. ' This avoids unnecessary entry if you deal with a range of one day only! If txtWhenCreatedRangeStart.Text <> "" And txtWhenCreatedRangeEnd.Text = "" Then txtWhenCreatedRangeEnd.Text = txtWhenCreatedRangeStart.Text End If If txtWhenCreatedRangeStart.Text = "" And txtWhenCreatedRangeEnd.Text <> "" Then txtWhenCreatedRangeStart.Text = txtWhenCreatedRangeEnd.Text End If If txtWhenCreatedRangeStart.Text <> "" And txtWhenCreatedRangeEnd.Text <> "" And IsDate(txtWhenCreatedRangeStart.Text) And IsDate(txtWhenCreatedRangeEnd.Text) Then strWhenCreated = " and (cast(convert(char(10), bi.When_Created, 101) as datetime) between " & "'" & txtWhenCreatedRangeStart.Text & "' and '" & txtWhenCreatedRangeEnd.Text & "')" Else strWhenCreated = "" txtWhenCreatedRangeStart.Text = "" txtWhenCreatedRangeEnd.Text = "" End If Dim strWhoCreated As String If (ddlWhoCreated.SelectedValue = "<- All ->") Then strWhoCreated = "" Else strWhoCreated = " and bi.Who_Created = " & "'" & ddlWhoCreated.SelectedValue & "' " End If ' Only allow good dates ' If one of Start or End is populated the other one will get the same value assigned. ' This avoids unnecessary entry if you deal with a range of one day only! If txtWhenUpdatedRangeStart.Text <> "" And txtWhenUpdatedRangeEnd.Text = "" Then txtWhenUpdatedRangeEnd.Text = txtWhenUpdatedRangeStart.Text End If If txtWhenUpdatedRangeStart.Text = "" And txtWhenUpdatedRangeEnd.Text <> "" Then txtWhenUpdatedRangeStart.Text = txtWhenUpdatedRangeEnd.Text End If Dim strWhenUpdated As String If txtWhenUpdatedRangeStart.Text <> "" And txtWhenUpdatedRangeEnd.Text <> "" And IsDate(txtWhenUpdatedRangeStart.Text) And IsDate(txtWhenUpdatedRangeEnd.Text) Then strWhenUpdated = " and (cast(convert(char(10), bi.When_Updated, 101) as datetime) between " & "'" & txtWhenUpdatedRangeStart.Text & "' and '" & txtWhenUpdatedRangeEnd.Text & "')" Else strWhenUpdated = "" txtWhenUpdatedRangeStart.Text = "" txtWhenUpdatedRangeEnd.Text = "" End If Dim strWhoUpdated As String If (ddlWhoUpdated.SelectedValue = "<- All ->") Then strWhoUpdated = "" Else strWhoUpdated = " and bi.Who_Updated = " & "'" & ddlWhoUpdated.SelectedValue & "' " End If Dim strIsDeleted As String If (ddlIsDeleted.SelectedValue = "<- All ->") Then strIsDeleted = "" Else If ddlIsDeleted.SelectedValue = "False" Then strIsDeleted = " and (bi.IsDeleted = 'F' or bi.IsDeleted is null)" Else strIsDeleted = " and bi.IsDeleted = 'T'" End If End If ' Only allow good dates ' If one of Start or End is populated the other one will get the same value assigned. ' This avoids unnecessary entry if you deal with a range of one day only! If txtWhenDeletedRangeStart.Text <> "" And txtWhenDeletedRangeEnd.Text = "" Then txtWhenDeletedRangeEnd.Text = txtWhenDeletedRangeStart.Text End If If txtWhenDeletedRangeStart.Text = "" And txtWhenDeletedRangeEnd.Text <> "" Then txtWhenDeletedRangeStart.Text = txtWhenDeletedRangeEnd.Text End If Dim strWhenDeleted As String If txtWhenDeletedRangeStart.Text <> "" And txtWhenDeletedRangeEnd.Text <> "" And IsDate(txtWhenDeletedRangeStart.Text) And IsDate(txtWhenDeletedRangeEnd.Text) Then strWhenDeleted = " and (cast(convert(char(10), bi.When_Deleted, 101) as datetime) between " & "'" & txtWhenDeletedRangeStart.Text & "' and '" & txtWhenDeletedRangeEnd.Text & "')" Else strWhenDeleted = "" txtWhenDeletedRangeStart.Text = "" txtWhenDeletedRangeEnd.Text = "" End If Dim strWhoDeleted As String If (ddlWhoDeleted.SelectedValue = "<- All ->") Then strWhoDeleted = "" Else strWhoDeleted = " and bi.Who_Deleted = " & "'" & ddlWhoDeleted.SelectedValue & "' " End If Dim Parameters_Group_In As String Dim Parameters_Effective_Date_In As String Parameters_Group_In = "REQUESTEDBY" Parameters_Effective_Date_In = Now() Dim SelectString As String = "" Dim Error_Message As String = "" Error_Message = BuildSelectString(SelectString) Dim strCommand As String = "" If ViewState("Submit") = "Submit" Then strCommand += " select " & SelectString strCommand += " from " strCommand += " ( " strCommand += " select convert(Char(10), sr.shift_date, 101) as ShiftDate, " strCommand += " case when st.Shift_Desc is null then '' else st.Shift_Desc end + ' / ' + case when st.Shift_Start_Time is null then '' else st.Shift_Start_Time end + ' / ' + case when st.Shift_End_Time is null then '' else st.Shift_End_Time end + ' / ' + cast(st.Shift_Type_ID as varchar) as ShiftType, " strCommand += " ParamRequestedBy.ParamRequestedByDesc as RequestedBy, " strCommand += " bi.Call_Number as CallNumber, " strCommand += " convert(Char(10), bi.Call_Date, 101) as CallDate, " strCommand += " case when dispatcher.Last_Name is null then '' else dispatcher.Last_Name end + ', ' + case when dispatcher.First_Name is null then '' else dispatcher.First_Name end + ' / ' + cast(dispatcher.Employee_ID as varchar) as Dispatcher, " strCommand += " (Select case when Employee.Last_Name is null then '' else Employee.Last_Name end + ', ' + case when Employee.First_Name is null then '' else Employee.First_Name end + ' / ' + cast(Employee.Employee_ID as varchar) from Employee where Employee.Employee_ID = bi.Dispatcher2_ID) as Dispatcher2, " strCommand += " (Select case when Employee.Last_Name is null then '' else Employee.Last_Name end + ', ' + case when Employee.First_Name is null then '' else Employee.First_Name end + ' / ' + cast(Employee.Employee_ID as varchar) from Employee where Employee.Employee_ID = bi.Dispatcher3_ID) as Dispatcher3, " strCommand += " bi.comments as Comments, " strCommand += " case when completing.Last_Name is null then '' else completing.Last_Name end + ', ' + case when completing.First_Name is null then '' else completing.First_Name end + ' / ' + cast(completing.Employee_ID as varchar) as EmployeeCompletingBillingInquiry, " strCommand += " bi.When_Created as WhenCreated, " strCommand += " case when Who_Created.Last_Name is null then '' else Who_Created.Last_Name end + ', ' + case when Who_Created.First_Name is null then '' else Who_Created.First_Name end + ' / ' + cast(Who_Created.Employee_ID as varchar) as WhoCreated, " strCommand += " bi.When_Updated as WhenUpdated, " strCommand += " case when Who_Updated.Last_Name is null then '' else Who_Updated.Last_Name end + ', ' + case when Who_Updated.First_Name is null then '' else Who_Updated.First_Name end + ' / ' + cast(Who_Updated.Employee_ID as varchar) as WhoUpdated, " strCommand += " case when bi.IsDeleted = 'F' or bi.IsDeleted is null then 'False' else 'True' end as IsDeleted, " strCommand += " bi.When_Deleted as WhenDeleted, " strCommand += " case when Who_Deleted.Last_Name is null then '' else Who_Deleted.Last_Name end + ', ' + case when Who_Deleted.First_Name is null then '' else Who_Deleted.First_Name end + ' / ' + cast(Who_Deleted.Employee_ID as varchar) as WhoDeleted " strCommand += " from billing_inquiries bi " strCommand += " inner join Shift_Report sr on bi.Shift_Report_ID = sr.Shift_Report_ID " strCommand += " inner join Shift_Types st on sr.Shift_Type_ID = st.Shift_Type_ID " strCommand += " inner join Employee dispatcher on bi.Dispatcher_ID = dispatcher.employee_id " strCommand += " inner join Employee completing on bi.Employee_Completing_Billing_Inquiry_ID = completing.employee_id " strCommand += " left outer join Employee Who_Created on bi.Who_Created = Who_Created.Employee_ID " strCommand += " left outer join Employee Who_Updated on bi.Who_Updated = Who_Updated.Employee_ID " strCommand += " left outer join Employee Who_Deleted on bi.Who_Deleted = Who_Deleted.Employee_ID " strCommand += " inner join ( " strCommand += " select Parameters_ID as ParamRequestedByID, Parameters_Value as ParamRequestedByDesc " strCommand += " from Parameters_Details pd1, Parameters_Header ph " strCommand += " where ph.parameters_group = pd1.parameters_group " strCommand += " And pd1.parameters_group = @Parameters_Group " strCommand += " And pd1.parameters_effective_date = " strCommand += " (select max(pd2.parameters_effective_date) " strCommand += " from parameters_details pd2 " strCommand += " where pd2.parameters_group = pd1.parameters_group " strCommand += " and pd2.parameters_id = pd1.parameters_id " strCommand += " and pd2.parameters_effective_date <= @Parameters_Effective_Date) " strCommand += " ) ParamRequestedBy on bi.Requested_By = ParamRequestedBy.ParamRequestedByID " strCommand += " where 1=1 " strCommand += strShiftType strCommand += strShiftDate strCommand += strRequestedBy strCommand += txtCallNumberlike strCommand += strCallDate strCommand += strDispatcher strCommand += strEmployeeCompletingBillingInquiry strCommand += strWhenCreated strCommand += strWhoCreated strCommand += strWhenUpdated strCommand += strWhoUpdated strCommand += strIsDeleted strCommand += strWhenDeleted strCommand += strWhoDeleted strCommand += " ) as Details " End If If ViewState("Submit") = "SubmitRequestedBy" Then strCommand += "select ParamRequestedBy.ParamRequestedByDesc as RequestedBy, " strCommand += " count(*) as Count " strCommand += " from billing_inquiries bi " strCommand += " inner join Shift_Report sr on bi.Shift_Report_ID = sr.Shift_Report_ID " strCommand += " inner join Shift_Types st on sr.Shift_Type_ID = st.Shift_Type_ID " strCommand += " inner join Employee dispatcher on bi.Dispatcher_ID = dispatcher.employee_id " strCommand += " inner join Employee completing on bi.Employee_Completing_Billing_Inquiry_ID = completing.employee_id " strCommand += " left outer join Employee Who_Created on bi.Who_Created = Who_Created.Employee_ID " strCommand += " left outer join Employee Who_Updated on bi.Who_Updated = Who_Updated.Employee_ID " strCommand += " left outer join Employee Who_Deleted on bi.Who_Deleted = Who_Deleted.Employee_ID " strCommand += " inner join ( " strCommand += " select Parameters_ID as ParamRequestedByID, Parameters_Value as ParamRequestedByDesc " strCommand += " from Parameters_Details pd1, Parameters_Header ph " strCommand += " where ph.parameters_group = pd1.parameters_group " strCommand += " And pd1.parameters_group = @Parameters_Group " strCommand += " And pd1.parameters_effective_date = " strCommand += " (select max(pd2.parameters_effective_date) " strCommand += " from parameters_details pd2 " strCommand += " where pd2.parameters_group = pd1.parameters_group " strCommand += " and pd2.parameters_id = pd1.parameters_id " strCommand += " and pd2.parameters_effective_date <= @Parameters_Effective_Date) " strCommand += " ) ParamRequestedBy on bi.Requested_By = ParamRequestedBy.ParamRequestedByID " strCommand += " where 1=1 " strCommand += strShiftType strCommand += strShiftDate strCommand += strRequestedBy strCommand += txtCallNumberlike strCommand += strCallDate strCommand += strDispatcher strCommand += strEmployeeCompletingBillingInquiry strCommand += strWhenCreated strCommand += strWhoCreated strCommand += strWhenUpdated strCommand += strWhoUpdated strCommand += strIsDeleted strCommand += strWhenDeleted strCommand += strWhoDeleted strCommand += " group by ParamRequestedBy.ParamRequestedByDesc " strCommand += " order by ParamRequestedBy.ParamRequestedByDesc " End If If ViewState("Submit") = "SubmitCallDate" Then strCommand += "select DATENAME(year, Call_Date) + ' ' + case when len(cast(month(Call_Date) as varchar)) = 1 then '0' + cast(month(Call_Date) as varchar) else cast(month(Call_Date) as varchar) end as YearMonth, " strCommand += " count(*) as Count " strCommand += " from billing_inquiries bi " strCommand += " inner join Shift_Report sr on bi.Shift_Report_ID = sr.Shift_Report_ID " strCommand += " inner join Shift_Types st on sr.Shift_Type_ID = st.Shift_Type_ID " strCommand += " inner join Employee dispatcher on bi.Dispatcher_ID = dispatcher.employee_id " strCommand += " inner join Employee completing on bi.Employee_Completing_Billing_Inquiry_ID = completing.employee_id " strCommand += " left outer join Employee Who_Created on bi.Who_Created = Who_Created.Employee_ID " strCommand += " left outer join Employee Who_Updated on bi.Who_Updated = Who_Updated.Employee_ID " strCommand += " left outer join Employee Who_Deleted on bi.Who_Deleted = Who_Deleted.Employee_ID " strCommand += " inner join ( " strCommand += " select Parameters_ID as ParamRequestedByID, Parameters_Value as ParamRequestedByDesc " strCommand += " from Parameters_Details pd1, Parameters_Header ph " strCommand += " where ph.parameters_group = pd1.parameters_group " strCommand += " And pd1.parameters_group = @Parameters_Group " strCommand += " And pd1.parameters_effective_date = " strCommand += " (select max(pd2.parameters_effective_date) " strCommand += " from parameters_details pd2 " strCommand += " where pd2.parameters_group = pd1.parameters_group " strCommand += " and pd2.parameters_id = pd1.parameters_id " strCommand += " and pd2.parameters_effective_date <= @Parameters_Effective_Date) " strCommand += " ) ParamRequestedBy on bi.Requested_By = ParamRequestedBy.ParamRequestedByID " strCommand += " where 1=1 " strCommand += strShiftType strCommand += strShiftDate strCommand += strRequestedBy strCommand += txtCallNumberlike strCommand += strCallDate strCommand += strDispatcher strCommand += strEmployeeCompletingBillingInquiry strCommand += strWhenCreated strCommand += strWhoCreated strCommand += strWhenUpdated strCommand += strWhoUpdated strCommand += strIsDeleted strCommand += strWhenDeleted strCommand += strWhoDeleted strCommand += " group by DATENAME(year, Call_Date) + ' ' + case when len(cast(month(Call_Date) as varchar)) = 1 then '0' + cast(month(Call_Date) as varchar) else cast(month(Call_Date) as varchar) end " strCommand += " order by DATENAME(year, Call_Date) + ' ' + case when len(cast(month(Call_Date) as varchar)) = 1 then '0' + cast(month(Call_Date) as varchar) else cast(month(Call_Date) as varchar) end " End If Dim Parameters_Hashtable As New Hashtable Parameters_Hashtable("@CallNumber") = txtCallNumber.Text & "%" Parameters_Hashtable("@Parameters_Group") = Parameters_Group_In Parameters_Hashtable("@Parameters_Effective_Date") = Parameters_Effective_Date_In Dim dtBillingInquiries As DataTable = DataAccess.GetDataAsDataSet( _ strCommand, _ "BillingInquiries", _ Parameters_Hashtable, _ Error_Message _ ).Tables("BillingInquiries") If (Error_Message = "") Then If ViewState("Submit") = "Submit" Then grdResults.DataSource = dtBillingInquiries If dtBillingInquiries.Rows.Count > 0 Then grdResults.Visible = True grdResults.DataBind() lblNoneFound.Visible = False btnExportToExcel.Visible = True End If End If If ViewState("Submit") = "SubmitRequestedBy" Then grdResultsRequestedBy.DataSource = dtBillingInquiries If dtBillingInquiries.Rows.Count > 0 Then grdResultsRequestedBy.Visible = True grdResultsRequestedBy.DataBind() SetGraph(dtBillingInquiries) lblNoneFound.Visible = False btnExportToExcel.Visible = True End If End If If ViewState("Submit") = "SubmitCallDate" Then grdResultsCallDate.DataSource = dtBillingInquiries If dtBillingInquiries.Rows.Count > 0 Then grdResultsCallDate.Visible = True grdResultsCallDate.DataBind() SetGraph(dtBillingInquiries) lblNoneFound.Visible = False btnExportToExcel.Visible = True End If End If Else lblMessage.Text = Error_Message End If End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload