CODEWITHSUNDEEP
sqlasp.netsql-servervb.net
Chidi Okeh
Chidi Okeh

Reputation: 1557

Display results for next 30 days if selected date is not available?

We are presenting users the option to search for records for a specific date or search by date range.

If however, the date that the user selected is not available, rather than display a message that user's selection is not found, show records for the next thirty days.

So far, my query is displaying records for dates that are older than today's day.

This is wrong.

Any ideas what's wrong with code below?

If ViewSelect = "Range" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("(CONVERT(VARCHAR(10), facilityAvailabilityDate, 101) >= @FromDate And CONVERT(VARCHAR(10), facilityAvailabilityDate, 101) <= @EndDate)")
    cmd.Parameters.AddWithValue("@FromDate", txtFromDate.Text)
    cmd.Parameters.AddWithValue("@EndDate", txtToDate.Text)
    startedWhere = True

ElseIf ViewSelect = "Specific" Then
    sb.Append(If(startedWhere, andors, " WHERE "))
    sb.Append("(CONVERT(VARCHAR(10), facilityAvailabilityDate, 101) = @SpecificDate)")
    cmd.Parameters.AddWithValue("@SpecificDate", txtSpecificDate.Text)
    startedWhere = True
End If

cmd.CommandText = sb.ToString()
'Response.Write(sb)
'Response.End()
' Dim cmd As New SqlCommand(strQuery) 
Dim dt As DataTable = GetData(cmd)

'No records found, so display records for next 30 days. 
If dt.Rows.Count = 0 Then

    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
    cmd.Parameters.AddWithValue("@EndDate", Date.Today.ToString("MM/dd/yyyy"))

    cmd.CommandText = "Select siteID,siteName,bldgName,Capacity,AvailableFacilities,facilityAvailabilityDate,Status " & _
",amenities,[Rental Fees] RentalFees,[Extra Hour Fees] ExtraHourFees,[Deposit Fees] DepositFees,[Cancellation Fees] CancelFees,[Key Deposit Fees] KeyDepFees, Duration " & _
" FROM ParksNRecsData" & _
" where facilityAvailabilityDate Between @FromDate And @EndDate"
    dt = GetData(cmd)
End If

GridView1.DataSource = dt
GridView1.DataBind()

SQL Server is database of choice.

Thanks in advance

Upvotes: 0

Views: 115

Answers (1)

Alan
Alan

Reputation: 3002

This block of code is selecting dates from the previous month, not the next month:

If dt.Rows.Count = 0 Then

    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@FromDate", Date.Today.AddMonths(-1))
    cmd.Parameters.AddWithValue("@EndDate", Date.Today.ToString("MM/dd/yyyy"))

Changing it to something like the following should achieve what you want:

If dt.Rows.Count = 0 Then

    cmd.Parameters.Clear()
    cmd.Parameters.AddWithValue("@FromDate", Date.Today)
    cmd.Parameters.AddWithValue("@EndDate", Date.Today.AddMonths(1))

Upvotes: 1

Related Questions