one of my webpages uses the following sql query to allow the user to search through the database and present the qualifying data in gridview:
SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%')
how could i expand this so that the user can also search through the database but instead by searching through another column such as [type]?
thanks in advance
SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%') OR ([Type] LIKE '%' + ? + '%')
yeah i tried that but it didnt work...i'll have another go though...
p.s. speedy reply! cheers
|||yeah again it didnt work i got this stack trace error which i dont have a clue what it means...
[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +58
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
System.Web.UI.WebControls.GridView.DataBind() +5
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67
System.Web.UI.Control.EnsureChildControls() +97
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731
paste your aspx code also
You should give your parameters names rather than using ?'s as placeholders. The problem is with the new query, it is expecting TWO parameters, one for each ? instead of one parameter used twice.
|||for some reason it double posted so this post was the same as below but now its this until i find out how to delete my own post if its possible
|||Motley: yeah dont worry i have always dont that, it would make no sense to keep on using ?s
<%@.PageLanguage="VB"AutoEventWireup="false"CodeFile="Login.aspx.vb"Inherits="Default2" %>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><htmlxmlns="http://www.w3.org/1999/xhtml"xmlns:spry="http://ns.adobe.com/spry">
<headid="Head1"runat="server"><metahttp-equiv="Content-Type"content="text/html; charset=iso-8859-1"/>
<scriptsrc="SpryMenuBar.js"type="text/javascript"></script> <linkhref="SpryMenuBarHorizontal.css"rel="stylesheet"type="text/css"/><linkhref="screen.css"rel="stylesheet"type="text/css"/>
<title>View Products</title></head>
<body><ulid="MenuBar1"class="MenuBarHorizontal">
<li><ahref="Default.aspx">Home</a></li>
<li><ahref="Gallery.aspx">Gallery</a></li>
<li><ahref="Database.aspx">Database</a></li>
<li><ahref="Contact us.aspx">Contact us</a></li>
<li><ahref="Login.aspx">Login</a></li> </ul><scripttype="text/javascript">
<!--
var MenuBar1 =new Spry.Widget.MenuBar("MenuBar1", {imgDown:"SpryMenuBarDownHover.gif", imgRight:"SpryMenuBarRightHover.gif"});
var MenuBar2 =new Spry.Widget.MenuBar("MenuBar2", {imgRight:"SpryMenuBarRightHover.gif"});
//-->
</script>
<br/>
<br/>
<br/>
<br/>
<formid="form1"runat="server">
<div>
<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>
<asp:ButtonID="Button1"runat="server"Text="Search"/><br/>
<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"
SelectCommand="SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + ? + '%')">
<SelectParameters>
<asp:ControlParameterControlID="TextBox1"DefaultValue="%"Name="?"PropertyName="Text"/>
</SelectParameters>
</asp:AccessDataSource>
<asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"
AutoGenerateColumns="False"CellPadding="4"DataKeyNames="product code"DataSourceID="AccessDataSource1"
ForeColor="#333333"GridLines="None"PageSize="5">
<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>
<Columns>
<asp:BoundFieldDataField="product code"HeaderText="product code"ReadOnly="True"
SortExpression="product code"/>
<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>
<asp:BoundFieldDataField="Type"HeaderText="Type"SortExpression="Type"/>
<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>
<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>
</Columns>
<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>
<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>
<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<EditRowStyleBackColor="#999999"/>
<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>
</asp:GridView>
<br/>
</div>
</form> </body></html>
and theres my webpage
|||<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>
<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>
<asp:ButtonID="Button1"runat="server"Text="Search"/><br/>
<asp:AccessDataSourceID="AccessDataSource1"runat="server"DataFile="~/App_Data/brakes.mdb"
SelectCommand="SELECT * FROM [Table1] WHERE ([comments] LIKE '%' + @.Comments + '%') OR ([Type] LIKE '%' + @.Type + '%') ">
<SelectParameters>
<asp:ControlParameterControlID="TextBox1"DefaultValue=""Name="comments"PropertyName="Text"/>
<asp:ControlParameterControlID="TextBox2"DefaultValue=""Name="type"PropertyName="Text"/>
</SelectParameters>
</asp:AccessDataSource>
|||
no luck! it still doesnt work, its a step forward though, the page actually loads without a error message, this time i can enter data into the textboxes to search and it just returns all the data not only results that should be returned...
btw how come the page loads when you use the @. in front of the controlD?
|||something to consider, page loads but the query doesnt work when using '@.comments' (even with songle control),
page loads and query works when using '?' and one control
page doesnt load when using '@.?'
only difference is that comments is text whereas '?' isnt, so what other non-text character can i use? ive tried using '#' but that doesnt work so is '?' the only control name i can use?
No comments:
Post a Comment