Multiple Searches For FilterExpression
May 31, 2007
This is what I have:
<asp:TextBox ID="TextBoxProjectCode" runat="server"></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server" Text="Search" /> <br>
<asp:TextBox ID="TextBoxProjectDate" runat="server" ></asp:TextBox>
<asp:Button ID="ButtonSearch" runat="server" Text="Search" />
SelectCommand="SELECT CLIENT_NAME, CLIENT_CODE, PROJECT_CODE, PROJECT_NAME, PROJECT_DESCRIPTION, EMPLOYEE_ID, ENTRY_DATE, ENTRY_TIME, ENTRY_DESCRIPTION, COMBINED_CODE, TIME_ENTRY_ID, BILLABLE FROM VIEW_TIME WHERE (EMPLOYEE_ID = @SELECT_EMPLOYEE_ID) ORDER BY ENTRY_DATE DESC" FilterExpression="PROJECT_CODE = '{0}' or ENTRY_DATE = '#{0}#' "
<FilterParameters>
<asp:ControlParameter ControlID="TextBoxProjectCode" Name="PROJECT_CODE" PropertyName="Text" Type="Int32" />
<asp:ControlParameter ControlID="TextBoxProjectDate" Name="ENTRY_DATE" PropertyName="Text" Type="DateTime" /></FilterParameters>
Why do I keep getting errors. What am I missing?
View 3 Replies
ADVERTISEMENT
May 6, 2008
Hi Guys,
I have to work with a poorly designed table :(, that has columns
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
Description varchar(8000) null,
ProductTypevarchar(255) null,
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1
thisID 3
parentid nuLL
DESCRIPTION: this is a question
id 20
thisID 3_1
parentID 3
DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Spencer
-- =============================================
ALTER PROCEDURE [dbo].[GetFAQs]
-- Add the parameters for the stored procedure here
@ProductType varchar(255)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE TEMP
(
IDINT,
thisIDvarchar(50) null,
parentIDvarchar(50) null,
Titlevarchar(255) null,
DescriptionQvarchar(8000) null,
DescriptionAvarchar(8000) null,
ProductTypevarchar(255) null,
)
SELECT
ID,
thisID,
parentID,
Title,
DescriptionQ,
DescriptionA,
ProductType
FROM
A2Z
WHERE
ProductType = @ProductType AND parentID IS NULL
END
GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
Any bright sparks out there that can help me?
Cheers
View 9 Replies
View Related
Aug 25, 2006
I am new to ASP. I am trying to use "Filterexpression".
FilterExpression="(Address_no >= '@Startno') AND (Address_no <= '@Endno')" >
I could not get it work. Could someone help me? Here is my code.
__________________________________________________________________________________
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="FarmedStreet.aspx.vb" Inherits="FarmedStreet" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvFarm" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
CellPadding="3" CellSpacing="2" DataKeyNames="StreetID" DataSourceID="dsfarm"
Font-Bold="False" Font-Names="Arial" Font-Size="11pt" PageSize="5">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<Columns>
<asp:CommandField ShowSelectButton="True">
<ItemStyle ForeColor="#00C000" />
</asp:CommandField>
<asp:BoundField DataField="StreetID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="StreetID" />
<asp:BoundField DataField="StartNo" HeaderText="StartNo" SortExpression="StartNo" />
<asp:BoundField DataField="EndNo" HeaderText="Endno" SortExpression="EndNo" />
<asp:BoundField DataField="Street" HeaderText="Street" SortExpression="Street" />
<asp:BoundField DataField="Zip" HeaderText="Zip" ReadOnly="True" SortExpression="Zip" />
<asp:CheckBoxField DataField="IsFarming" HeaderText="?" SortExpression="IsFarming" />
<asp:CommandField ShowEditButton="True">
<ItemStyle ForeColor="#00C000" />
</asp:CommandField>
</Columns>
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="dsfarm" runat="server" ConnectionString="<%$ ConnectionStrings:cnFarming %>"
SelectCommand="SELECT __Street.StreetID, __Street.Street, __Street.ZipID, __Street.IsFarming, __Street.StartNo, __Street.EndNo, __Zip.Zip FROM __Street INNER JOIN __Zip ON __Street.ZipID = __Zip.ZipID WHERE (NOT (__Street.StartNo IS NULL)) AND (NOT (__Street.EndNo IS NULL))">
</asp:SqlDataSource>
<asp:Button ID="btnResident" runat="server" BackColor="#00C000" Enabled="False" Font-Bold="True"
ForeColor="Yellow" Text="Resident" />
</div>
<asp:SqlDataSource ID="dsResidentA" runat="server" ConnectionString="<%$ ConnectionStrings:cnFarming %>"
SelectCommand="SELECT ProspectID, FName, LName, SAddress, SCity, SState, SZip, MAddress, MCity, MState, MZip, Bed, Bath, [S FT], phone, City, Address_no, Street, CatID, PropID, StreetID, ZipID, Apt FROM __Prospect WHERE (StreetID = @StreetID)"
FilterExpression="(Address_no >= '@Startno') AND (Address_no <= '@Endno')" >
<SelectParameters>
<asp:ControlParameter ControlID="gvFarm" Name="StreetID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="gvFarm" Name="Startno" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="gvFarm" Name="Endno" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="dsResidentA" PageSize="5">
<Columns>
<asp:BoundField DataField="ProspectID" HeaderText="ProspectID" InsertVisible="False"
SortExpression="ProspectID" />
<asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" />
<asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" />
<asp:BoundField DataField="Address_no" HeaderText="Address_no" SortExpression="Address_no" />
<asp:BoundField DataField="Apt" HeaderText="Apt" SortExpression="Apt" />
<asp:BoundField DataField="PropID" HeaderText="PropID" SortExpression="PropID" />
</Columns>
</asp:GridView>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</form>
</body>
</html>
View 1 Replies
View Related
Jun 6, 2008
I have a CheckBoxList which is bound to a SQLDataSource and populated with a list of courses. Under this I have a calendar control which is bound to another SQLDataSource control. When the page is first loaded this is empty because no courses are selected. I'd like it so that the user can select one or more course from the CheckBoxList and the appropraite events are loaded into the calendar control bound to the second SQLDataSource. I've tried using FilterExpression but this seems to work on the basis of a complete resultset being loaded and then filtered-out using the FilterExpression. I'm starting with no records and would like to Filter-In data from the database.
Is this possible using FilterExpression?
View 3 Replies
View Related
Aug 4, 2006
I have a some search components to filter a GridView follows:
<asp:DropDownList ID="ddFilterType" runat="server" ToolTip="Select field to search"> <asp:ListItem Selected="True">(ALL)</asp:ListItem> <asp:ListItem>SKU</asp:ListItem> <asp:ListItem>Vendor Name</asp:ListItem> <asp:ListItem>Item Name</asp:ListItem></asp:DropDownList><asp:TextBox ID="txtSearchFilter" runat="server"></asp:TextBox><asp:LinkButton ID="btnSearchFilter" runat="server">Filter</asp:LinkButton>
and C# code as follows:
protected void Page_Load(object sender, EventArgs e){ if (IsPostBack) { if (ddFilterType.SelectedValue != "ALL") { dsItems.FilterExpression = (ddFilterType.SelectedValue = txtSearchFilter.Text); gvItems.DataBind(); } else { gvItems.DataBind(); } }}
But when I run the search, I get:Exception Details: System.Data.EvaluateException: Cannot find column [the value in txtSearchFilter.Text].
How should I do my C# syntax to get this to work? Also, I would like to add further logic to check if the field is not the SKU field and have my expression do a LIKE expression instead of checking for equality. Any help with this is much appreciated.
Marc
View 1 Replies
View Related
Feb 5, 2007
Hi
on my page: two sqldatasources one has the sqlexperession:
SELECT DISTINCT [Skil] FROM [UsersView]UNIONSELECT 'Alles'
the other one :
SELECT [UserName], [Afdeling], [Skil], [RoepNaam] FROM [UsersView]
the first source is bound to a dropdown list. witch I want to use for filtering.
the second to a gridview.
the code for the dropdownlist selectedindexchanged is:protected void SkillDDL_SelectedIndexChanged(object sender, EventArgs e)
{
if (SkillDDL.SelectedValue.ToString() == "Alles")
{
SqlDataSource3.FilterExpression = "";
}
else
{
SqlDataSource3.FilterExpression = "Skil = '" + SkillDDL.SelectedValue + "'";
}
}
The strange thing is that initially 'Alles' is selected in the dropdown list and I see all the records. then I select an other value in the dropdownlist. and I get the filtered records, like I would expect.
then when I select 'Alles' again I keep my last selected records in view. I would expect that the filter is now an empty string and I would get all the records again. what do I do wrong?
thanks
Rob Warning
View 2 Replies
View Related
May 31, 2007
I have a filter expression and I have 2 data types one is an int and the other is a date format. I want to setup 2 textboxes to search for either the int or the date. I have the FilterExpression setup as follows:
FilterExpression="PROJECT_CODE = '{0}' or PROJECT_DATE L= '{0}'"<FilterParameters>
<asp:ControlParameter ControlID="TextBoxProjectCode" Name="PROJECT_CODE" PropertyName="Text" Type="Int16" />
<asp:ControlParameter ControlID="TextBoxProjectDate" Name="PROJECT_DATE" PropertyName="Text" /></FilterParameters>
I can take out one or the other and run the search and return results, but can not do it with both. Is there a way to do this and what do I need to do to make it work?
View 3 Replies
View Related
Nov 12, 2007
Hi. I want to use a FilterExpression in my sqldatasource that verify if a value is in a listbox with several values. How can I do that? Thanks
View 3 Replies
View Related
May 17, 2007
Dear all
I was doing research oh how to do some searches on dates that allow a bit of a freedom to the person searching. They should be able to specify the parameters.
For example how would you make a search for all events that happened on a specific day in any year,any month? example : every 18
Or how would you searh for values that happened in a specific month in any year and any day? eg. May
Or how would you search for values that happen in a specific year and any day and month? eg. 2007
Or how would you search for values that happen every 18 May?
I tried few things and can only work out the search for the year bit. The values are saved in the database as DateTime.
Any advice is apreciated.
Sincerely
View 2 Replies
View Related
May 10, 2008
Hey everybody,
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
Thanks in advanced guys!
View 2 Replies
View Related
May 13, 2006
I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.
All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.
DECLARE @return_value int
EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = N'04/28/2006',
@chr_div = N'NE',
@vch_portfolio_no = 3,
@vch_prop_cat = N'core'
SELECT 'Return Value' = @return_value
GO
How can I set this so that it will wild card the value. For example rather than having to specify
@chr_div = N'NE', I could specify something like
@chr_div = N *, so it would show both NE and SW values in the result set.
Anybody point me in a direction here. I have tried % but that does not seem to work, I get a
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '%'.
Thank in Advance
View 4 Replies
View Related
Feb 21, 2007
I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into.
My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...)
MY SQLSDATASOURCE <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="ClientSearch" SelectCommandType="StoredProcedure">
<FilterParameters>
<asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" />
<asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" />
</FilterParameters>
</asp:SqlDataSource>MY CODEBEHIND Dim FilterExpr As String If SearchLastName.Text = "" And _ SearchFirstName.Text = "" And _ SearchEmail.Text = "" And _ SearchComment.Text = "" And _ SearchAddress.Text = "" Then lblMessage.Text = "You didn't enter any search parameters. Please try Again." Me.GridViewSearch.DataSourceID = "" Exit Sub Else Me.GridViewSearch.DataSourceID = "SqlDataSource1" End If FilterExpr = "" If SearchLastName.Text <> "" Then FilterExpr = FilterExpr & "LastName Like '" & _ SearchLastName.Text & "%" & "' AND " End If If SearchFirstName.Text <> "" Then FilterExpr = FilterExpr & "FirstName Like '" & _ SearchFirstName.Text & "%" & "' AND " End If If SearchEmail.Text <> "" Then FilterExpr = FilterExpr & " Like '" & _ SearchEmail.Text & "%" & "' AND " End If If SearchComment.Text <> "" Then FilterExpr = FilterExpr & "[Comments] Like '" & "%" & _ SearchComment.Text & "%" & "' AND " End If If SearchAddress.Text <> "" Then FilterExpr = FilterExpr & "[Address] Like '" & "%" & _ SearchAddress.Text & "%" & "' AND " End If If Right(FilterExpr, 4) = "AND " Then FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4) End If Try Me.SqlDataSource1.FilterExpression = FilterExpr Me.SqlDataSource1.DataBind() Me.GridViewSearch.DataBind() Me.lblMessage.Text = Me.SqlDataSource1.FilterExpression Catch objException As SqlException Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next End Try End Sub
MY SPROCGO
ALTER PROCEDURE [dbo].[ClientSearch]
AS
SELECT C.ClientID, C.FirstName, C.LastName, A.Address,
C.Comments, C.EMail
FROM tblClient C INNER JOIN
tblClientAddresses A ON C.ClientID = A.ClientID
View 1 Replies
View Related
Oct 1, 2007
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.
Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx
Here is some of my code..Private Sub ApplyFilter()
Dim _filterExpression As String = ""
If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then
_filterExpression = "CategoryName = '{0}'"
End If Me.SqlDataSource1.FilterExpression = _filterExpression
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
ApplyFilter()
End Sub
But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
View 5 Replies
View Related
Feb 16, 2008
I have a SQLDatasource control on a web page. It is the datasource for a gridview control.I want the gridview to show all jones if the FirstName textbox is left blank or empty. Right now I have to put a % in the FirstName textbox to get what I want. If I make the FirstNameTextBox empty or remove the % from the FirstNameTextbox it returns all the names in the database no matter what the last name is.How do I get it to work without having to use the % in the FirstName Textbox? THANKS!FilterExpression="LastName LIKE '%{0}%' and FirstName LIKE '%{1}%'"><FilterParameters> <asp:ControlParameter ControlID="LastNameTextBox" Name="LastName" PropertyName="Text" DefaultValue="" /> <asp:ControlParameter ControlID="FirstNameTextBox" Name="FirstName" PropertyName="Text" DefaultValue="" /></FilterParameters>
Last Name: Jones___________First Name: %_____________FILTERBUTTON
GridviewLast Name First NameBob JonesBill Jones
View 6 Replies
View Related
Mar 25, 2008
Hi.
Is there a way to convert a smalldatetime value to a year value (1/1/2001 -> 2001) in this case?
I tried year(thisIsSmalldatetimeField) and datepart(yyyy, thisIsSmalldatetimeField)
Dim FilterExpression As String = String.Concat("thisIsSmalldatetimeField=" & DropDownList.SelectedValue & "")
mySqlDataSource.FilterParameters.Clear()
mySqlDataSource.FilterExpression = FilterExpression
View 6 Replies
View Related
Feb 21, 2004
BASIC QUESTION here:
Can you do JOINS with tables in a catalog?
View 4 Replies
View Related
Mar 7, 2005
Hi Everyone. I have created a procedure on sql to obtain information about two tables ( bookinfo and author). The procedure is :
CREATE PROCEDURE FindBookByAuthor
@Aname char
AS
select b.libcode, b.title, b.subject, a.authorname, b.instock
from bookinfo b join authorinfo a
on b.authorid = a.authorid
where a.authorname like '%' + @aname + '%'
GO
I have tested this procedure changing the variable @aname for the surname of the author. This works fine. The problem comes when I execute this procedure from web matrix. I use a textbox to obtain the data for the variable @aname. I send the data. What happens is that when I run the web page and look for a book through the author the select statement behaves differently and gives me a different output.
I would be glad to know why this happens
Thanks
View 7 Replies
View Related
Jul 5, 2002
Hi, I am looking for an easy and efficiant way to add a boolean search function to an already existing search i have using ASP pages and MSSQL 2000 SPROCS.
Currently I have some code such as:
CREATE PROCEDURE sp_name
@var1 int = NULL,
@var2 varchar(50) = NULL
AS
SELECT blah, blah, blah
FROM sometable INNER JOIN anothertable ON anID = anID
WHERE (table1.column2 = COALESCE(@var1,table1.column2) AND table2.column4 = COALESCE(@var2,table2.column4))
and this works like a charm, if i pass a value into @var1 or @var 2 it gives back results where those crietera match, or if I pass no values in it returns all records.... anyways, you get the idea...
Now I would like to add the ability to introduce a 3rd variable, and have it run a boolean search on a varchar(2500) column. This column is a description field for an image so I would like to be able to search the descriptions by passing in something like 'car OR bike AND travel' and return all the records that have the words 'car' or 'bike' and 'travel' somewhere in the description field.
How can i do this? I am using SQL Server Standard Edition so I don't believe I have full text search functions available, (not sure if that would even help).
Thanks all for your support and guidance!
P.S. i would also like to maintain the functinality so that if all the @var# are blank it still returns all records.
View 1 Replies
View Related
Apr 27, 2006
Hi,
I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.
There are 3 tables involved, the jobs, the applicants and the applications.
I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.
There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.
My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?
So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?
Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.
So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.
Thanks in advance.
View 2 Replies
View Related
Dec 22, 2006
Here is my requirement:
The script needs to enumerate the the databases in a givin instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.
What I don t understand:
What do they mean by: account with permissions on that server, which account re they talking about
and what do they mean by the SP references a specific file and account name? how can an SP reference a file or account name. i have never seen that?
Thanks for help.
View 13 Replies
View Related
Nov 3, 2005
For anybody that implemented fuzzy searches in SQL Server or any otherdatabase for that matter. I have implemented edit distance algorithm,specifically q-grams, in SQL Server and wanted to get some opinionsfrom anyone who used a similar algorithm in a DBMS.We are looking to implement fuzzy address search and are employingnumber of different techniques including synonyms for strings like"drive", "dr", "drv", etc... Number expansion, so 6 ave = Six Ave =6th Ave = 6... Now, we also have an implementation of edit distancealgorithm, which would cover misspellings and similar sounding words.One of the challenges is putting various techniques together. Synonymsand number expansion techniques go well together but no edit distance.Also, I am trying to figure out how to leverage edit distanceimplementation for 'contains' searches. So if a string like'Mississippi Municipal Building' would be returned if the Search is for'Misisipi Municipal'.Any thoughts?
View 1 Replies
View Related
Jul 20, 2005
Hi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.
View 1 Replies
View Related
Jun 12, 2007
Hello,
I have a Repeater control getting data from a SqlDataSource.The SqlDataSource uses a stored procedure which accepts a input parameter with the name "sort" (@sort).
How do I use the FilterExpression with FilterParameter for defining the input value for the stored procedure ?The input value comes from an asp:QueryStringParameter.Or can the FilterExpression only be used with SQL Statements defined directly (as for defining the where clause) ?
I guess the best way is to use the FilterExpression soo the data is cached in the Dataset which is needed/required when using FilterExpression.Alternativly I could use the SelectParameter instead FilterParameters, but I guess the data wont be cached then.Anyone who can help me and guide me in the right direction ?
Best regardsMartin
View 2 Replies
View Related
Aug 31, 2007
How does one programmatically retrieve the results from a sqldatasource that has had a filterexpression applied to it?
Let me elaborate, here is my code:
SqlDataSource1.FilterExpression = "MenuType = 'myfilteredvalue'"
Dim _dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)
_dv.Table.TableName = "Menu" Dim ds As New DataSet("Menus") ds.Tables.Add(_dv.Table.Copy()) 'Add relation: ParentID under MenuID
Dim relation As New DataRelation("ParentChild", ds.Tables!Menu.Columns!MenuID, ds.Tables!Menu.Columns!ParentID, True) relation.Nested = True
ds.Relations.Add(relation)
What I'm doing is, I have a sqlDataSource that pulls all the data from my DB. With this data in the sqlDataSourceSource, I want to then populate an XMLDatasource (via a dataset) with only the records I need, i.e. the filter. However, after I apply my filter above, the sqlDataSoruce still contains all the records. I thought maybe if I did a sqlDataSource.Bind() after my SqlDataSource.FilterExpression, but that didn't do anything either.In laymans terms:I want to say something like: dataset = sqldatasource.filter(my filter expression).
View 2 Replies
View Related
Mar 18, 2008
I am using the FilterExpression of an SqlDataSource object to filter the rows of a gridview. It works with searching text values but I cannot for the life of me figure out to get it to work with dates. The following is what I do for character searches: SomeDataSource.FilterExpression = fieldToSearch + " LIKE '%" + SearchTextBox.Text + "%'"; This works. However when I try to search for dates using the following it doesn't work (startDate and endDate are DateTime objects):SomeDataSource.FilterExpression = fieldToSearch + " BETWEEN #" + startDate.ToString("yyyy-MM-dd") + "# AND #" + endDate.ToString("yyyy-MM-dd") + "#";// or
SomeDataSource.FilterExpression = fieldToSearch + " = #" + startDate.ToString("yyyy-MM-dd") + "#";// or
SomeDataSource.FilterExpression = fieldToSearch + " = #'" + startDate.ToString("MM/dd/yyyy HH:mm:ss") + "'#";
View 6 Replies
View Related
Apr 14, 2008
I have wrote some codes as follows:
Dim sqldatasource3 As New SqlDataSource sqldatasource3.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("aspnetConnectionString").ConnectionString sqldatasource3.SelectCommand = "select stepno,steppersontype,stepname,steptype,fchoice,fforward,schoice,sforward from flow " sqldatasource3.FilterExpression = "stepname = '" & GridView1.SelectedRow.Cells(4).Text.Trim & "'" sqldatasource3.DataSourceMode = SqlDataSourceMode.DataSet Dim dv As DataView = sqldatasource3.Select(DataSourceSelectArguments.Empty()) dv.RowFilter = "stepname = '" & GridView1.SelectedRow.Cells(4).Text.Trim & "'"
but i found the filterexpression didn't work. No matter what value GridView1.SelectedRow.Cells(4).Text.Trim be,sqldatasource always return the whole dataset.I wanna know where these code are wrong? Can anyone help?
View 1 Replies
View Related
Apr 11, 2007
I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits.
Thank you
View 4 Replies
View Related
Jul 4, 2006
Hiya,
I have recently become responsible for a small database for a volunteer soccer league. I am reasonably savvy when it comes to development, but I have not had a lot of experience with administration before.
I need to do what I think must be pretty simple: set up full text indexing so I can use a CONTAINS search on a table. The table contains all of the fields the kids use, and each field has a number of divisions that typically play on that field; we use these 'favored divisions' to make scheduling a little easier. Now, one day when I have time, I will set up a proper, normalized, one-to-many relationship between the favored divisions and the playing fields, but right now it's basically like this:
fieldID (int, primary key, identity seed)
fieldName (varchar), e.g. High School Field
favored_divisions (varchar) - comma-delimited list of divisions, e.g. G10,B14,G12
I imagine it's probably database sacrilege to have a comma-delimited list like that, but we don't have the resources now to re-write that piece of the web application. My question is, in SQL Server 2005, what do I need to do to be able to do a full-text search on this field with the following query:
SELECT fieldID, fieldName
FROM playing_fields
WHERE CONTAINS(favored_divisions,'G10')
Right now the query runs and does not return an error, but does not return any results, either. IIRC, full-text indexing is enabled by default in SQL Server 2005, but I am not familiar with the procedure -- something about having to populate a catalog. Do I need to edit or set up a new index on the actual playing_fields table? What has to happen to make this work?
Thanks very much,
Sam
View 5 Replies
View Related
Jul 13, 2005
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet
If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If
Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement
..........End Function
View 2 Replies
View Related
Feb 9, 2005
i want to be able to do full text searches on a table that i have that already has data in it.. i created a new catalog, then started incremenetal population.. but nothing happened? will it only catalog the new data thats been entered into the table after i start incremental update? so since i have data in teh table already, would i need to do a full population first before i start incremenetal population?
thanks!
View 4 Replies
View Related
Dec 22, 2005
Hi,
For those implementing encrypted columns, what is the recommended approach when allowing users to also do partial searches on encrypted data? (ie email or creditcard info where the tables contain millions of rows). I understand one cannot have the encryption without performance impact, but the searches can be 10 to 20 times as long as when the info is stored in normal char(20) columns. Just looking as a way to try and lessen the impact.
Thanks
View 1 Replies
View Related
Apr 25, 2008
I facing a problem when i want to modified the sqldatasource.filterExpression while trigger sqldatasource.selecting event. 1 Protected Sub SqlDsProduct_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
2 Dim SqlDsProduct As SqlDataSource = CType(CompleteGridView1.DetailRows(0).FindControl("SqlDsProduct"), SqlDataSource)
3 SqlDsProduct.FilterExpression = Session("filter") ' filter i add up after user press search button
4 End subActually i'm using the CompleteGridview, which i downloaded from the web. the "SqlDsProduct" i reffering to is inside the CompleteGridView1. I'm using complet grid view because i want the hierarchy look for the gridview. So please help me how i gonna change the filter expression of the 2nd sqldatasource inside detailsTemplate of the completeGridview. Thank you. best regardvince
View 6 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
