SQL datetime date range query from datepickers --sample code

[RickH]

Date Range from DateTime SQL column in VB.NET, converted from Joel
Alley's original C#
Not cleaned up, but works.
Might have minor errors since I didn't run exhaustive testing on it.
Painful finding all the gotchas. The 'order by' bug took me a while to
figure out --took pasting in Enterprise Manager to spot the problem.

<InfoPathEventHandler(MatchPath:="QueryToEdit",
EventType:=InfoPathEventType.OnClick)> _
Public Sub QueryToEdit_OnClick(ByVal e As DocActionEvent)
'thisXDocument.Query()
' thisXDocument.View.SwitchView("Data")
Dim oldCommand As String
Dim whereClause As String
Dim queryFieldNode As IXMLDOMNode
Dim curQueryFieldAttribute As IXMLDOMNode
Dim queryFieldAttributes As IXMLDOMNamedNodeMap
Dim adapter As ADOAdapter
adapter = thisXDocument.QueryAdapter

queryFieldNode =
thisXDocument.DOM.selectSingleNode("dfs:myFields/dfs:queryFields/q:Fuel_Received")

'The QueryFields are empty.
If (whereClause = Nothing) Then whereClause = String.Empty
'save orig
oldCommand = adapter.Command

'modify query
Dim myDateFields As IXMLDOMNode =
thisXDocument.DOM.selectSingleNode("/dfs:myFields")
Dim strStartDate =
myDateFields.selectSingleNode("/dfs:myFields/my:StartDate").text
Dim strEndDate =
myDateFields.selectSingleNode("/dfs:myFields/my:EndDate").text
Dim strSQLQuery, strReport_Date As String
Dim strOrderBy As String
Dim ModOldCommand As String = oldCommand

If strStartDate <> "" And strEndDate <> "" Then
If whereClause = String.Empty Then
whereClause = " WHERE (Report_Date >= '" &
CStr(CDate(strStartDate)) & " 00:00:00 AM') AND (Report_Date <= '" &
CStr(CDate(strEndDate)) & " 12:59:59 PM')"
'Need to remove Order by section
Dim intBadStart As Integer
If ModOldCommand.IndexOf("order by") <> -1 Then
intBadStart = ModOldCommand.IndexOf("order by")
Dim intBadEnd As Integer = ModOldCommand.Length
Dim intBadLength As Integer = (intBadEnd -
intBadStart)
strOrderBy = ModOldCommand.Substring(intBadStart,
intBadLength)
ModOldCommand = ModOldCommand.Remove(intBadStart,
intBadLength)
End If
End If
End If
If (whereClause <> "") Then
adapter.Command = ModOldCommand & whereClause & " " &
strOrderBy
System.Windows.Forms.Clipboard.SetDataObject(ModOldCommand
& whereClause, 1)
' Clear the QueryFields so the WHERE clause isn't
' automatically generated.
' If you do tho, you can't select other query fields!!!
queryFieldAttributes = queryFieldNode.attributes
curQueryFieldAttribute = queryFieldAttributes.nextNode()
While Not (curQueryFieldAttribute Is Nothing)
'curQueryFieldAttribute.text = "" 'prevents other
fields if uncommented
curQueryFieldAttribute = queryFieldAttributes.nextNode()
End While
End If
' Perform the query.
Try
thisXDocument.Query()
Catch ex As Exception
thisXDocument.UI.Alert("Failed to query.\n\n" + ex.Message)
End Try
' Reset the command so that subsequent queries are based on
' the correct SQL command text string.
adapter.Command = oldCommand
thisXDocument.View.SwitchView("Data")
End Sub

 

[Franck Dauché]

Hi Rick,

Thanks for taking the time to share with the community!

Franck Dauché

 

[RickH]

No problem, been looking for how to do that since InfoPath shipped.
And I really hate jscript samples, and C# is nice for programmers, but
not for less technical users.

CORRECTION: (records on end date not showing up)
whereClause = " WHERE (Report_Date >= '" &
CStr(CDate(strStartDate)) & " 00:00:00 AM') AND (Report_Date <= '" &
CStr(CDate(strEndDate)) & " 12:59:59 PM')"

SHOULD BE: (24hr time needed)
whereClause = " WHERE (Report_Date >= '" &
CStr(CDate(strStartDate)) & " 00:00:00') AND (Report_Date <= '" &
CStr(CDate(strEndDate)) & " 23:59:59')"