Hi,
Im new to .net programming,I have been stuck with a dynamic search form. I have a dropdownlist and a text box. The dropdownlist actually hold the filed name where when i search is "where dropdownlist value like textfield value
this is my front end
<tr>
<td style="height: 14px;background-color:#ffd6d6;">
<asp:Label ID="lblsearch" runat="server" Text="SEARCH"></asp:Label> <asp:DropDownList
ID="ddlSearchType" runat="server">
<asp:ListItem Value="Select">Select Criteria</asp:ListItem>
<asp:ListItem Value="FirstName">First Name</asp:ListItem>
<asp:ListItem Value="LastName">Last Name</asp:ListItem>
<asp:ListItem Value="Email">Email</asp:ListItem>
<asp:ListItem Value="userid">Username</asp:ListItem>
<asp:ListItem Value="DomainName">Domain Name</asp:ListItem>
<asp:ListItem Value="TransferDomainName">Transfer Domain Name</asp:ListItem>
<asp:ListItem Value="SubDomainName">Sub Domain Name</asp:ListItem>
<asp:ListItem Value="Any">Any Criteria</asp:ListItem>
</asp:DropDownList> <asp:TextBox ID="TxtKeyword" runat="server"></asp:TextBox> <asp:Button
ID="ButtonSearch" runat="server" Text="Search" OnClick="ButtonSearch_Click" CausesValidation="true"/>
</td>
</tr>
<tr>
<td>
<div>
<asp:Label ID="lblerror" runat="server" Text="Label" Font-Bold="True" Font-Italic="True" ForeColor="Maroon"></asp:Label>
<br><asp:Label ID="lbltotcount" runat="server" Text="" Font-Bold="True" Font-Italic="True" ForeColor="Maroon"></asp:Label><br><asp:RequiredFieldValidator InitialValue="Select" ID="RequiredFieldValidator1" runat="server" ErrorMessage="Please Select a search criteria" Display="Static" ControlToValidate="ddlSearchType"></asp:RequiredFieldValidator>
<br><asp:RequiredFieldValidator InitialValue="" ID="RequiredFieldValidator2" runat="server" ErrorMessage="Please enter a keyword" Display="Static" ControlToValidate="TxtKeyword"></asp:RequiredFieldValidator>
</div>
</td>
</tr>
<tr>
<td style="height: 3px;">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="accountid" Visible="false"
DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" PageSize="2" BackColor = "#DFDFDF" BorderColor = "Red" BorderStyle = "Dotted" ForeColor = "Black" >
<HeaderStyle BackColor="SeaShell" ForeColor="Black"/>
<AlternatingRowStyle BackColor="WhiteSmoke" />
<Columns>
<%-- <asp:BoundField DataField="accountid" HeaderText="AccountID" InsertVisible="False"
ReadOnly="True" SortExpression="accountid" />--%>
<%--<asp:TemplateField HeaderText="AccountID">
<itemtemplate>
<asp:HyperLink ID="HyperLink" runat="server" target="_blank" NavigateUrl='edit.aspx?accountid=accountid' >Test</asp:HyperLink>
</itemtemplate>
</asp:TemplateField> --%>
<asp:hyperlinkfield datatextfield="accountid" datanavigateurlfields="accountid" datanavigateurlformatstring="../Edit/EditStatus.aspx?accountid={0}" headertext="AccountID" target="_blank" />
<asp:BoundField DataField="userid" ReadOnly="True" HeaderText="Username" SortExpression="userid" />
<asp:BoundField DataField="firstname" ReadOnly="True" HeaderText="First Name" SortExpression="firstname" />
<asp:BoundField DataField="lastname" ReadOnly="True" HeaderText="Last Name" SortExpression="lastname" />
<asp:BoundField DataField="email" ReadOnly="True" HeaderText="Email" SortExpression="email" />
<asp:BoundField DataField="domainname" HeaderText="Domain Name" SortExpression="domainname" />
<asp:BoundField DataField="transferdomainname" HeaderText="Transfer Domain Name" SortExpression="transferdomainname" />
<asp:BoundField DataField="subdomainname" HeaderText="Sub Domain Name" SortExpression="subdomainname" />
<asp:BoundField DataField="newemailaccount" HeaderText="Ximplify Email" SortExpression="newemailaccount" />
<asp:CheckBoxField DataField="sunemail" HeaderText="Sun Mail Status" SortExpression="sunemail" />
<asp:BoundField DataField="datecreated" ReadOnly="True" HeaderText="Date Activated" SortExpression="datecreated" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
<PagerStyle HorizontalAlign="Center" ForeColor="DarkSlateBlue" BackColor="SeaShell" ></PagerStyle>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:XimplifyConnectionString %>"
SelectCommand="select * from [TBL_UserAccounts]"
DeleteCommand="DELETE FROM [TBL_UserAccounts] WHERE [AccountID] = @AccountID"
UpdateCommand="UPDATE [TBL_UserAccounts] SET [FirstName] = @FirstName, [LastName] = @LastName, [DomainName] = @DomainName, [SubDomainName] = @SubDomainName, [TransferDomainName] = @TransferDomainName, [NewEmailAccount] = @NewEmailAccount, [AvailableSpace] = @AvailableSpace, [SUNEmail] = @SUNEmail WHERE [AccountID] = @AccountID">
<DeleteParameters>
<asp:Parameter Name="AccountID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="AccountID" Type="Int32" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="DomainName" Type="String" />
<asp:Parameter Name="SubDomainName" Type="String" />
<asp:Parameter Name="TransferDomainName" Type="String" />
<asp:Parameter Name="NewEmailAccount" Type="String" />
<asp:Parameter Name="AvailableSpace" Type="Int32" />
<asp:Parameter Name="SUNEmail" Type="Boolean" />
</UpdateParameters>
</asp:SqlDataSource>
</td>
</tr>and this is my codebehind
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lblerror.Text = ""
lbltotcount.Text = ""
End Sub
Protected Sub ButtonSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strkeyword, strsearchtype, strsql, strresult As String
strkeyword = TxtKeyword.Text
strsearchtype = ddlSearchType.SelectedValue
GridView1.Visible = True
Select Case ddlSearchType.SelectedValue
Case "Any"
strQuery = "select * from [TBL_UserAccounts] where (userid LIKE '%" & TxtKeyword.Text & "%') OR (firstname LIKE '%" & TxtKeyword.Text & "%') OR (lastname LIKE '%" & TxtKeyword.Text & "%') OR (email LIKE '%" & TxtKeyword.Text & "%') OR (domainname LIKE '%" & TxtKeyword.Text & "%') OR (transferdomainname LIKE '%" & TxtKeyword.Text & "%') OR (subdomainname LIKE '%" & TxtKeyword.Text & "%')"
Case Else
strQuery = "select * from [TBL_UserAccounts] where (" & strsearchtype & " LIKE '%" & TxtKeyword.Text & "%')"
End Select
If (strsearchtype = "Any") Then
strsql = "select count(userid) from [TBL_UserAccounts] where (userid LIKE '%" & TxtKeyword.Text & "%') OR (firstname LIKE '%" & TxtKeyword.Text & "%') OR (lastname LIKE '%" & TxtKeyword.Text & "%') OR (email LIKE '%" & TxtKeyword.Text & "%') OR (domainname LIKE '%" & TxtKeyword.Text & "%') OR (transferdomainname LIKE '%" & TxtKeyword.Text & "%') OR (subdomainname LIKE '%" & TxtKeyword.Text & "%')"
Else
strsql = "select count(userid) from [TBL_UserAccounts] where (" & strsearchtype & " LIKE '%" & strkeyword & "%')"
End If
SqlDataSource1.SelectCommand = strQuery
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim myCommand As New SqlCommand(strsql, myConnection)
myConnection.Open()
Dim mySqlDataReader As SqlDataReader
mySqlDataReader = myCommand.ExecuteReader
Do While (mySqlDataReader.Read())
strresult = Convert.ToString(mySqlDataReader.GetValue(0))
Loop
If strresult <> "0" Then
lbltotcount.Text = "Total number of records is : " & strresult
ElseIf strresult = "0" Then
lbltotcount.Text = "No Records found for the search"
End If
GridView1.DataBind()
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
SqlDataSource1.SelectCommand = strQuery
End SubMy problem is when i click sorting or editing, it loads the whole set of data, which i believe is calling SelectCommand="select * from [TBL_UserAccounts]" , so the number of pages increase and the id is not correct.All it suppose to do is sort the search results or display editable fields when i click edit.
Please help me to shed some light. I have been struggling with this problem for some time. :( :sad: :icon_sad:
Thanks
Meg
