I have a grid view binding with access data source, with 3 parameters and a button. I would like to have the select statement to return top 100 results, and when search field are entered, filter the result down.
First of all, I am not able to get top 100 results.
Second, when I enter search field, the gridview only would bind when all three parameters are filled in with text.
third, when all 3 are filled in with text, the results are not very accurate.
As far as I see，Access supports Like with "*" instead of "%"，So please change to this：
andyyew
SELECT TOP 100 Patient.MRN, Patient.PtName, Patient.PtDOB, Patient.PtSex, Visit.Doctor, Visit.Accession, Visit.[Image] FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN
WHERE (@MRN is NULL OR Patient.MRN LIKE '%' + @MRN + '%') AND
(@PNAME IS NULL OR Patient.PtName LIKE '%' + @PNAME + '%') AND
(@DNAME IS NULL OR Visit.Doctor LIKE '%' + @DNAME + '%') "
To this：
FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN
WHERE (@MRN is NULL OR Patient.MRN LIKE '*' + @MRN + '*') AND (@PNAME IS NULL OR Patient.PtName LIKE '*' + @PNAME + '*') AND (@DNAME IS NULL OR Visit.Doctor LIKE '*' + @DNAME + '*') "
As far as I see，Access supports Like with "*" instead of "%"
An MS Access application supports "*" as the wildcard character, but the JET OLEDB provider supports "%". Therefore your suggested change will not work. In fact, if you read the last comment from the OP, he got it working with "%".
An MS Access application supports "*" as the wildcard character, but the JET OLEDB provider supports "%". Therefore your suggested change will not work. In fact, if you read the last comment from the OP, he got it working with "%".
andyyew
Member
19 Points
73 Posts
AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 09, 2012 03:22 AM|LINK
I have a grid view binding with access data source, with 3 parameters and a button. I would like to have the select statement to return top 100 results, and when search field are entered, filter the result down.
First of all, I am not able to get top 100 results.
Second, when I enter search field, the gridview only would bind when all three parameters are filled in with text.
third, when all 3 are filled in with text, the results are not very accurate.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="StandingOrderSearch.aspx.vb" Inherits="secured_StandingOrderSearch" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { text-align: center; font-weight: bold; } .style2 { font-weight: bold; } </style> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Standing Order Search" Font-Names="Arial" Font-Size="15pt" ForeColor="#858585"></asp:Label> <br /> <br /> <table style="width:80%;" align="center"> <tr> <td colspan="6" bgcolor="#5D7B9D" class="style1"> <asp:Label ID="Label5" runat="server" Text="SEARCH" ForeColor="White"></asp:Label> </td> </tr> <tr> <td> <asp:Label ID="Label2" runat="server" Text="Patient Name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="pIDTXT" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label3" runat="server" Text="Patient ID:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="pNameTXT" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label4" runat="server" Text="Doctor's name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="dNameTXT" runat="server"></asp:TextBox> </td> </tr> <tr> <td colspan="6" style="text-align: center"> <asp:Button ID="Button1" runat="server" Text="Submit" EnableViewState="False" /> </td> </tr> <tr> <td colspan="6"> <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" style="text-align: center" AutoGenerateColumns="False" DataSourceID="AccessDataSource1" AllowPaging="True" AllowSorting="True" EnableViewState="False"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="MRN" HeaderText="MRN" SortExpression="MRN" /> <asp:BoundField DataField="PtName" HeaderText="Patient" SortExpression="PtName" /> <asp:BoundField DataField="PtDOB" DataFormatString=" {0:mm/dd/yyyy}" HeaderText="Patient DOB" SortExpression="PtDOB" /> <asp:BoundField DataField="PtSex" HeaderText="PtSex" SortExpression="PtSex" Visible="False" /> <asp:BoundField DataField="Doctor" HeaderText="Doctor" SortExpression="Doctor" /> <asp:BoundField DataField="Accession" HeaderText="Accession" SortExpression="Accession" /> <asp:BoundField DataField="Image" HeaderText="Image" SortExpression="Image" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> </table> <br /> </div> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="MYDBFILE.mdb" SelectCommand="SELECT TOP 100 Patient.MRN, Patient.PtName, Patient.PtDOB, Patient.PtSex, Visit.Doctor, Visit.Accession, Visit.[Image] FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN WHERE (@MRN = '' OR Patient.MRN LIKE '%' + @MRN + '%') AND (@PNAME = '' OR Patient.PtName LIKE '%' + @PNAME + '%') AND (@DNAME = '' OR Visit.Doctor LIKE '%' + @DNAME + '%')" CancelSelectOnNullParameter="False"> <SelectParameters> <asp:ControlParameter ControlID="pIDTXT" Name="MRN" PropertyName="Text" DefaultValue="""" /> <asp:ControlParameter ControlID="pNameTXT" Name="PNAME" PropertyName="Text" DefaultValue="""" /> <asp:ControlParameter ControlID="dNameTXT" Name="DNAME" PropertyName="Text" DefaultValue="""" /> </SelectParameters> </asp:AccessDataSource> <br /> <br /> <br /> </form> </body> </html>Mikesdotnett...
All-Star
154955 Points
19872 Posts
Moderator
MVP
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 09, 2012 04:56 AM|LINK
Change the SQL to use null parameters. Read this for more help: http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
andyyew
Member
19 Points
73 Posts
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 09, 2012 07:58 PM|LINK
I did as the link say and I got a
No value given for one or more required parameters.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="StandingOrderSearch.aspx.vb" Inherits="secured_StandingOrderSearch" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { text-align: center; font-weight: bold; } .style2 { font-weight: bold; } </style> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Standing Order Search" Font-Names="Arial" Font-Size="15pt" ForeColor="#858585"></asp:Label> <br /> <br /> <table style="width:80%;" align="center"> <tr> <td colspan="6" bgcolor="#5D7B9D" class="style1"> <asp:Label ID="Label5" runat="server" Text="SEARCH" ForeColor="White"></asp:Label> </td> </tr> <tr> <td> <asp:Label ID="Label2" runat="server" Text="Patient Name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="pIDTXT" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label3" runat="server" Text="Patient ID:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="pNameTXT" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label4" runat="server" Text="Doctor's name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="dNameTXT" runat="server"></asp:TextBox> </td> </tr> <tr> <td colspan="6" style="text-align: center"> <asp:Button ID="Button1" runat="server" Text="Submit" EnableViewState="False" /> </td> </tr> <tr> <td colspan="6"> <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" style="text-align: center" DataSourceID="AccessDataSource1" AllowPaging="True" AllowSorting="True" EnableViewState="False"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="MRN" HeaderText="MRN" SortExpression="MRN" /> <asp:BoundField DataField="PtName" HeaderText="Patient" SortExpression="PtName" /> <asp:BoundField DataField="PtDOB" DataFormatString=" {0:mm/dd/yyyy}" HeaderText="Patient DOB" SortExpression="PtDOB" /> <asp:BoundField DataField="PtSex" HeaderText="PtSex" SortExpression="PtSex" Visible="False" /> <asp:BoundField DataField="Doctor" HeaderText="Doctor" SortExpression="Doctor" /> <asp:BoundField DataField="Accession" HeaderText="Accession" SortExpression="Accession" /> <asp:BoundField DataField="Image" HeaderText="Image" SortExpression="Image" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> </table> <br /> </div> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="StandingOrder.mdb" SelectCommand="SELECT TOP 100 Patient.MRN, Patient.PtName, Patient.PtDOB, Patient.PtSex, Visit.Doctor, Visit.Accession, Visit.[Image] FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN WHERE (@MRN is NULL OR Patient.MRN LIKE '%' + @MRN + '%') AND (@PNAME IS NULL OR Patient.PtName LIKE '%' + @PNAME + '%') AND (@DNAME IS NULL OR Visit.Doctor LIKE '%' + @DNAME + '%') " CancelSelectOnNullParameter="False"> </asp:AccessDataSource> <br /> <br /> <br /> </form> </body> </html>andyyew
Member
19 Points
73 Posts
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 09, 2012 08:05 PM|LINK
I notice VS removed my SelectParameters, however after I add it back, nothing happens when seach is done.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="StandingOrderSearch.aspx.vb" Inherits="secured_StandingOrderSearch" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { text-align: center; font-weight: bold; } .style2 { font-weight: bold; } </style> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Standing Order Search" Font-Names="Arial" Font-Size="15pt" ForeColor="#858585"></asp:Label> <br /> <br /> <table style="width:80%;" align="center"> <tr> <td colspan="6" bgcolor="#5D7B9D" class="style1"> <asp:Label ID="Label5" runat="server" Text="SEARCH" ForeColor="White"></asp:Label> </td> </tr> <tr> <td> <asp:Label ID="Label2" runat="server" Text="Patient Name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="txtMRN" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label3" runat="server" Text="Patient ID:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="txtPname" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label4" runat="server" Text="Doctor's name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="TxtDname" runat="server"></asp:TextBox> </td> </tr> <tr> <td colspan="6" style="text-align: center"> <asp:Button ID="Button1" runat="server" Text="Submit" EnableViewState="False" /> </td> </tr> <tr> <td colspan="6"> <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" style="text-align: center" DataSourceID="AccessDataSource1" AllowPaging="True" AllowSorting="True" EnableViewState="False"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="MRN" HeaderText="MRN" SortExpression="MRN" /> <asp:BoundField DataField="PtName" HeaderText="Patient" SortExpression="PtName" /> <asp:BoundField DataField="PtDOB" DataFormatString=" {0:mm/dd/yyyy}" HeaderText="Patient DOB" SortExpression="PtDOB" /> <asp:BoundField DataField="PtSex" HeaderText="PtSex" SortExpression="PtSex" Visible="False" /> <asp:BoundField DataField="Doctor" HeaderText="Doctor" SortExpression="Doctor" /> <asp:BoundField DataField="Accession" HeaderText="Accession" SortExpression="Accession" /> <asp:BoundField DataField="Image" HeaderText="Image" SortExpression="Image" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> </table> <br /> </div> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="\\10.110.1.99\eob\StandingOrders\StandingOrder.mdb" SelectCommand="SELECT TOP 100 Patient.MRN, Patient.PtName, Patient.PtDOB, Patient.PtSex, Visit.Doctor, Visit.Accession, Visit.[Image] FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN WHERE (@MRN IS NULL OR Patient.MRN LIKE '%' + @MRN + '%') AND (@PNAME IS NULL OR Patient.PtName LIKE '%' + @PNAME + '%') AND (@DNAME IS NULL OR Visit.Doctor LIKE '%' + @DNAME + '%') " CancelSelectOnNullParameter="False"> <SelectParameters> <asp:ControlParameter ControlID="txtMRN" Name="MRN" Type="String" /> <asp:ControlParameter ControlID="txtPname" Name="PNAME" Type="String" /> <asp:ControlParameter ControlID="txtDname" Name="DNAME" Type="String" /> </SelectParameters> </asp:AccessDataSource> <br /> <br /> <br /> </form> </body> </html>andyyew
Member
19 Points
73 Posts
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 09, 2012 09:02 PM|LINK
Finally got it working.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="StandingOrderSearch.aspx.vb" Inherits="secured_StandingOrderSearch" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style type="text/css"> .style1 { text-align: center; font-weight: bold; } .style2 { font-weight: bold; } </style> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Standing Order Search" Font-Names="Arial" Font-Size="15pt" ForeColor="#858585"></asp:Label> <br /> <br /> <table style="width:80%;" align="center"> <tr> <td colspan="6" bgcolor="#5D7B9D" class="style1"> <asp:Label ID="Label5" runat="server" Text="SEARCH" ForeColor="White"></asp:Label> </td> </tr> <tr> <td> <asp:Label ID="Label2" runat="server" Text="Patient Name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="txtPname" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label3" runat="server" Text="Patient ID:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="txtMRN" runat="server"></asp:TextBox> </td> <td> <asp:Label ID="Label4" runat="server" Text="Doctor's name:" CssClass="style2"></asp:Label> </td> <td> <asp:TextBox ID="TxtDname" runat="server" Height="22px"></asp:TextBox> </td> </tr> <tr> <td colspan="6" style="text-align: center"> <asp:Button ID="Button1" runat="server" Text="Submit" EnableViewState="False" /> </td> </tr> <tr> <td colspan="6"> <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" style="text-align: center" AllowPaging="True" AllowSorting="True" EnableViewState="False" AutoGenerateColumns="False" DataSourceID="AccessDataSource1"> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:BoundField DataField="MRN" HeaderText="MRN" SortExpression="MRN" /> <asp:BoundField DataField="PtName" HeaderText="Patient" SortExpression="PtName" /> <asp:BoundField DataField="PtDOB" DataFormatString=" {0:mm/dd/yyyy}" HeaderText="Patient DOB" SortExpression="PtDOB" /> <asp:BoundField DataField="PtSex" HeaderText="PtSex" SortExpression="PtSex" Visible="False" /> <asp:BoundField DataField="Doctor" HeaderText="Doctor" SortExpression="Doctor" /> <asp:BoundField DataField="Accession" HeaderText="Accession" SortExpression="Accession" /> <asp:BoundField DataField="Image" HeaderText="Image" SortExpression="Image" /> </Columns> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> </td> </tr> </table> <br /> </div> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="\\StandingOrder.mdb" SelectCommand="SELECT TOP 100 Patient.MRN, Patient.PtName, Patient.PtDOB, Patient.PtSex, Visit.Doctor, Visit.Accession, Visit.[Image] FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN WHERE (@MRN IS NULL OR Patient.MRN LIKE '%' + @MRN + '%') AND (@PNAME IS NULL OR Patient.PtName LIKE '%' + @PNAME + '%') AND (@DNAME IS NULL OR Visit.Doctor LIKE '%' + @DNAME + '%') " CancelSelectOnNullParameter="False"> <SelectParameters> <asp:ControlParameter ControlID="txtMRN" Name="MRN" PropertyName="Text"/> <asp:ControlParameter ControlID="txtPname" Name="PNAME" PropertyName="Text" /> <asp:ControlParameter ControlID="txtDname" Name="DNAME" PropertyName="Text" /> </SelectParameters> </asp:AccessDataSource> <br /> <br /> <br /> </form> </body> </html>Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 11, 2012 02:03 AM|LINK
Hello andyyew：）
As far as I see，Access supports Like with "*" instead of "%"，So please change to this：
To this：
FROM Visit INNER JOIN Patient ON Visit.MRN = Patient.MRN
WHERE (@MRN is NULL OR Patient.MRN LIKE '*' + @MRN + '*') AND
(@PNAME IS NULL OR Patient.PtName LIKE '*' + @PNAME + '*') AND
(@DNAME IS NULL OR Visit.Doctor LIKE '*' + @DNAME + '*') "
Mikesdotnett...
All-Star
154955 Points
19872 Posts
Moderator
MVP
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 11, 2012 01:44 PM|LINK
An MS Access application supports "*" as the wildcard character, but the JET OLEDB provider supports "%". Therefore your suggested change will not work. In fact, if you read the last comment from the OP, he got it working with "%".
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: AccessDataSource, GridView, Multiple Select Parameters wont' update grid
May 12, 2012 12:19 AM|LINK
Sorry，I didn't notice that……