Date column does not display in sorted in GridView using vb.net. In sql
server the select query is returning records in sorted manner or in order by. But for some reason GridView does not display properly. it goes to an event preRenderComplete then it binds automatically
Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete
'Force the selections made in page_load to be shown in the gridview by causing a postback
GridView1.DataBind()
If GridView1.Rows.Count > 0 Then
'this is not counting correctly disable until i get it figured out
'' lblMsg.Text = GridView1.Rows.Count.ToString + " Referrals"
Else
lblMsg.Text() = "No referrals to be processed"
End If
'Turn off the Background Contolls
'If Not IsPostBack Then PanelBackendControls.Visible = False
End Sub
End Region
_
Public Shared Function A02WF01_AdminView(ByVal strUserID As String, ByVal strTestMode As String, ByVal strSearchFieldValue As String, ByVal strDate As String) As DataTable
Dim sel As String
Dim conn As SqlConnection = New SqlConnection(WF01ConnectionString)
If strSearchFieldValue <> "" And strTestMode = "ON" Then
sel = "SELECT DISTINCT Since, WorkFlow_Step, "
sel = sel & " Started_By, Client_FullName, Product_Desc, "
sel = sel & " Branch_List, Event_AssignedID, DaysElapsed, Status,Instance_ID,Seq_ID,Form_Code "
sel = sel & " FROM A02W01ViewAllTest "
Dim WhereClause As String
Dim OrderClause As String
WhereClause = " WHERE ( Event_IsLatest = 1)"
If strUserID <> "Admin" Then
End If
'WhereClause = WhereClause + " AND WF_Start_UserID like " + "'" + strUserID + "')"
WhereClause = WhereClause + " And( Started_By Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR Client_FullName Like " + "'%" + strSearchFieldValue + "%'"
'WhereClause = WhereClause + " OR FullName Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR Product_Desc Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR Branch_List Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR DaysElapsed Like " + "'%" + strSearchFieldValue + "%')"
'WhereClause = WhereClause + " OR Form_Code Like " + "'%" + strSearchFieldValue + "%')"
OrderClause = " ORDER BY Since DESC"
sel = sel + WhereClause + OrderClause
ElseIf strSearchFieldValue <> "" And strTestMode <> "ON" Then
sel = "SELECT DISTINCT Since, WorkFlow_Step, "
sel = sel & " Started_By, Client_FullName, Product_Desc, "
sel = sel & " Branch_List, Event_AssignedID, DaysElapsed, Status "
sel = sel & " FROM A02W01ViewAll "
Dim WhereClause As String
Dim OrderClause As String
WhereClause = " WHERE ( Event_IsLatest = 1)"
If strUserID <> "Admin" Then
End If
'WhereClause = WhereClause + " AND WF_Start_UserID like " + "'" + strUserID + "')"
WhereClause = WhereClause + " AND( Started_By Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR Client_FullName Like " + "'%" + strSearchFieldValue + "%'"
'WhereClause = WhereClause + " OR Client_LastName Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR Product_Desc Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR Branch_List Like " + "'%" + strSearchFieldValue + "%'"
WhereClause = WhereClause + " OR DaysElapsed Like " + "'%" + strSearchFieldValue + "%'))"
'WhereClause = WhereClause + " OR Form_Code Like " + "'%" + strSearchFieldValue + "%'))"
OrderClause = " ORDER BY Since DESC"
sel = sel + WhereClause + OrderClause
End If
If strTestMode <> "ON" And strSearchFieldValue = "" Then
sel = "SELECT DISTINCT Since, WorkFlow_Step, "
sel = sel & " Started_By, Client_LastName, Client_FullName, Product_Desc, "
sel = sel & " Branch_List, Event_AssignedID, DaysElapsed, Status "
sel = sel & " FROM A02W01ViewAll "
Dim WhereClause As String
Dim OrderClause As String
WhereClause = " WHERE Event_IsLatest = 1"
'WhereClause = WhereClause + " AND (Event_IsLatest = 1) "
OrderClause = " ORDER BY Since DESC"
sel = sel + WhereClause + OrderClause
Else
If strSearchFieldValue = "" And strTestMode = "ON" And strDate = "" Then
sel = "SELECT DISTINCT Since, WorkFlow_Step, "
sel = sel & " Started_By, Client_FullName, Product_Desc, "
sel = sel & " Branch_List, Event_AssignedID, DaysElapsed, Status, Instance_ID,Seq_ID,Form_Code "
sel = sel & " FROM A02W01ViewAllTest "
Dim WhereClause As String
Dim OrderClause As String
WhereClause = " WHERE Event_IsLatest = 1"
'Display everything for Admin
' Comment below code
'If strUserID <> "Admin" Then WhereClause = WhereClause + " AND WF_Start_UserID like " + "'" + strUserID + "'"
OrderClause = " ORDER BY Since DESC"
sel = sel + WhereClause + OrderClause
ElseIf strSearchFieldValue = "" And strTestMode = "ON" And strDate <> "" Then
sel = ""
sel = sel & "SELECT TOP 100 PERCENT Since, WorkFlow_Step, "
sel = sel & "Started_By, Client_Fullname, Product_Desc, "
sel = sel & "Branch_List, Event_AssignedID, DaysElapsed, Status, "
sel = sel & "Instance_ID, Seq_ID, Form_Code "
sel = sel & " FROM A02W01ViewDistinct "
Dim WhereClause As String
Dim OrderClause As String
WhereClause = " WHERE Event_IsLatest = 1"
'Display everything for Admin
' Comment below code
'If strUserID <> "Admin" Then WhereClause = WhereClause + " AND WF_Start_UserID like " + "'" + strUserID + "'"
OrderClause = " ORDER BY YEAR(Since) DESC, MONTH(Since) DESC, DAY(Since) DESC"
sel = sel + WhereClause + OrderClause
End If
End If
Dim da As SqlDataAdapter = New SqlDataAdapter(sel, conn)
Dim ds As DataSet = New DataSet()
Try
conn.Open()
da.Fill(ds, "odsA02_Tracking")
conn.Close()
Catch e As SqlException
WFClassLib.PageError()
Finally
conn.Close()
End Try
If ds.Tables("odsA02_Tracking") IsNot Nothing Then _
Return ds.Tables("odsA02_Tracking")
'Return ds
'If ds.Tables("odsA02_Tracking") Is Nothing Then
Return Nothing
'End If
End Function
BorderStyle="Outset" CellPadding="4" DataSourceID="odsA02_Tracking"
ForeColor="#333333" GridLines="Vertical" Style="border-right: #0000ff thin solid;
table-layout: auto; border-top: #0000ff thin solid; font-size: x-small; border-left: #0000ff thin solid;
border-bottom: #0000ff thin solid; font-family: Arial; border-collapse: separate" Font-Size="Small" PageSize="30"
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:boundfield datafield="Since" HeaderText="Submit Date" ReadOnly=True SortExpression="Since" />
<asp:BoundField DataField="Started_By" HeaderText="Submitted By" SortExpression="Started_By" />
<asp:BoundField DataField="Client_FullName" HeaderText="Client Name" ReadOnly="True" SortExpression="Client_FullName" />
<asp:BoundField DataField="Product_Desc" HeaderText="Product" ReadOnly="True" SortExpression="Product_Desc" />
<asp:BoundField DataField="Branch_List" HeaderText="Branch" ReadOnly="True" SortExpression="Branch_List" />
<asp:BoundField DataField="Event_AssignedID" HeaderText="Assigned To" ReadOnly="True"
SortExpression="Event_AssignedID" />
<asp:BoundField DataField="DaysElapsed" HeaderText="Days Open" ReadOnly="True" SortExpression="DaysElapsed" />
<asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
<asp:TemplateField>
<ItemTemplate>
<asp:HiddenField ID=hdnInstanceID Value='<%#Eval("Instance_ID") %>' runat=server>
</asp:HiddenField>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:HiddenField ID=hdnSeqID Value='<%#Eval("Seq_ID") %>' runat=server/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:HiddenField ID=hdnFormCode Value='<%#Eval("Form_Code") %>' runat=server/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Label ID="lblMsg" runat="
server" Style="font-size: small; color: red; font-family: Arial"
Width="525px" Font-Bold="True"></asp:Label><br />
<br />
<asp:Button ID="btnReturn" runat="
server" Text="Return" /><br />
<br />
<asp:Label ID="lbltxtUserID" runat="
server" Text="txtUserID" Visible="False"></asp:Label>
<asp:TextBox ID="txtUserID" runat="
server" Visible="False" Width="226px"></asp:TextBox><br />
<asp:Label ID="label4" runat="
server" Text="TestModeOn" Visible="false"></asp:Label>
<asp:TextBox ID="TestModeOn" runat="
server" Visible="False" Width="226px"></asp:TextBox><br />
<br />
<asp:Label ID="lblSearchUserEntered" runat="
server" Visible="false" Text="searchText" ></asp:Label>
<asp:TextBox ID="searchText" runat="
server" Visible="False" Width ="226px" ></asp:TextBox>
<br />
<asp:Label ID="Label1" runat="
server" Text="txtInstance_ID" Visible="False"></asp:Label>
<asp:TextBox ID="txtInstance_ID" runat="
server" Visible="False" Width="226px"></asp:TextBox><br />
<asp:Label ID="Label2" runat="
server" Text="txtSeq_ID" Visible="False"></asp:Label>
<asp:TextBox ID="txtSeq_ID" runat="
server" Visible="False" Width="226px"></asp:TextBox><br />
<asp:Label ID="Label3" runat="
server" Text="txtForm_Code" Visible="False"></asp:Label>
<asp:TextBox ID="txtForm_Code" runat="
server" Visible="False" Width="226px"></asp:TextBox><br />
<br />
<asp:Label ID="lblSince" runat="
server" Visible="false" Text="Since" ></asp:Label>
<asp:TextBox ID="SortSince" runat="
server" Visible="False" Width ="226px" ></asp:TextBox>
<br />
<br />
<asp:ObjectDataSource ID="odsA02_Tracking" runat="
server" OldValuesParameterFormatString="original_{0}"
SelectMethod="A02WF01_AdminView" TypeName="WFA02DataObjects">
<SelectParameters>
<asp:ControlParameter ControlID="txtUserID" Name="strUserID" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="TestModeOn" Name="strTestMode" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="searchText" Name="strSearchFieldValue" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="SortSince" Name="strDate" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
</div>
</form>
