FilterExpression And Listbox
Nov 12, 2007Hi.
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
ADVERTISEMENT
Filterexpression In ASP 2.0
Aug 25, 2006I 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>
FilterExpression
Jun 6, 2008I 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?
FilterExpression Syntax In C#
Aug 4, 2006I 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
SQLDatasource Filterexpression
Feb 5, 2007Hi
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
Or Statement In A FilterExpression
May 31, 2007I 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?
Issue With SQLDataSource And FilterExpression
Feb 21, 2007I'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
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?
Dynamically Change FilterExpression
Oct 1, 2007I 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.
FilterExpression And The SQLDataSource Control
Feb 16, 2008I 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
A Smalldatetime Value To A Year Value In FilterExpression
Mar 25, 2008Hi.
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
Using A Stored Procedure With A FilterExpression ? How To Sort ?
Jun 12, 2007Hello,
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
Sqldatasource.filterexpression - Different Use For/of - Populate A Dataset
Aug 31, 2007How 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).
SqlDataSource.FilterExpression: Can't Get It Work With Dates?
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") + "'#";
Sqldatasource Filterexpression In Code Mode
Apr 14, 2008I 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?
Modified Sqldatasource.filterExpression While Sqldatasouce.selecting Event
Apr 25, 2008I 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
Several Listbox's With A SP
Apr 30, 2007I am wandering how to "Properly do this" Without doing a dynamic SP. How do I do a search with the multiple listbox data. What do I pass the stored procedure?
SELECT ID, LAST_NAME || ', ' || FIRST_NAME AS FULLNAMEFROM BIT_USER1WHERE (TYPE_ID = 1) OR (TYPE_ID = 3) OR (TYPE_ID = 4) OR (TYPE_ID = 5) OR (BLDG_ID = 1) OR (BLDG_ID = 2)ORDER BY LAST_NAME, FIRST_NAME
How Do I Populate A Listbox From A SQL DB?
Feb 10, 2004Sorry if this is to basic but I am just starting out. Any help is appreciated.
Basically I am attempting to populate a listbox with items from a MSSQL DB so the user can select either one or multiple items in that listbox to search on.
Get &> Array &> Split &> Listbox
Jun 20, 2007Hi everyone, having some problems
Basically, using ASP.NET 2.0 and here is my problem,
Get data from table
Put into array
Split where there is a +
remove +'s
assign to listbox to give a list of everything in that table
The + split the courses, so in my Order table I have A + B + C etc and I want all of the different options in a list box (note different records have different entries it isn't always a b c)
I am testing my code, here is what I got
Public Sub TitleChange(ByVal Sender As Object, ByVal E As EventArgs) Try Dim DBConn As SqlConnection Dim DSPageData As DataSet = New DataSet() Dim VarTxtAOE As String DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=XXX") 'Dim DBDataAdapter As SqlDataAdapter DBDataAdapter = New SqlDataAdapter("Select AOE FROM TBL_Role WHERE Title = @ddlTitle", DBConn) DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar) DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue DBDataAdapter.Fill(DSPageData, "Courses") 'Need to find out what this rows business is about whats the number about? and am I doing it correct? 'txtAOE.Text = DSPageData.Tables("Courses").Rows(0).Item("AOE") 'txtAOE.Items.Add(New ListItem(DSPageData.Tables(0).Rows(0).Item("AOE"))) VarTxtAOE = DSPageData.Tables("Courses").Rows(0).Item("AOE") Dim VarArray() As String = VarTxtAOE.Split("+") Response.Write(VarArray()) txtAOE.DataSource = VarArray() txtAOE.DataBind() 'Response.Write(test) 'ListBox1.DataSource = test 'Response.Write(VarArray) Catch TheException As Exception lblerror.Text = "Error occurred: " & TheException.ToString() End Try End Sub My response.Write works correctly, but my list box doesn't, also I don't want to say which bit of the array like I have done using 1, I just want to display the whole array in my list box. I am not worrying about removing the +'s at the minute, just splitting my data and putting each section into a listbox
Maybe I am going about this the wrong way, but I have been trying a lot of different things and its hard to find any help
Thanks
Chris
Best Way To Use Listbox Selection In WHERE ... IN (...) Clause?
Aug 30, 2005I need to use the list of items from a multiselect listbox in a parameter of a query's IN clause.
Example:
I assemble the list from the listbox and set the paramter value so @CityList = 'London','Paris','Rome' .... etc.
A sample SQL would be:
SELECT * FROM Customers
WHERE City IN (@CityList)
However, the SQL will not work. It seems the whole string is put in
another set of single quotes by the compiler so it's treated as one
string literal instead of a list.
Is there a way around this?
Listbox Selected Values
Dec 1, 2005Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.
View 2 Replies View RelatedRemoving Duplicate In Listbox
Apr 23, 2008Hi There,
I am having a 1st listbox which is populating production lines, 2nd combobox to populate the production units. From here , the user will be able to select multiple production units from the combobox and hence populate the variable in listbox 3.
This is the query that I'm using to query on the variable table:
'SELECT DISTINCT PU_Id,Var_Id,Var_Desc from Variables
where PU_Id IN (@ProductionUnits)'
The problem now is that this would return all the variables for the selected production units and for those variables that have the same name, they would appear in the listbox as duplicates.
I wonder if there's any way to filter off or remove the duplicates in the listbox 3 by using sql query?
Thanks in advance for the help.
ksyong17
Selected Listbox Items Into Database
May 9, 2007How can i get ALL the selected items into the database? this loop only accepts one item. I have tried with a "clear" action, does not work.... protected void Button2_Click(object sender, EventArgs e) { if (Page.IsValid) { // Define data objects SqlConnection conn; SqlCommand comm; // Open the connection string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; // Initialize connection conn = new SqlConnection(connectionString); // Create command comm = new SqlCommand("INSERT INTO TestTabel (TestNavn) VALUES (@TestNavn)",conn); // Add command parameters foreach (ListItem item in TestListBox.Items) { if (item.Selected) { comm.Parameters.Add("@TestNavn", System.Data.SqlDbType.NVarChar); comm.Parameters["@TestNavn"].Value = Item.Text; } } // Enclose database code in Try-Catch-Finally try { // Open the connection conn.Open(); // Execute the command comm.ExecuteNonQuery(); // Reload page if the query executed successfully Response.Redirect("Default.aspx"); } catch(Exception Arg) { Response.Write(Arg.Message); // Display error message Label1.Text = "Error !"; } finally { // Close the connection conn.Close(); } } }
View 1 Replies View RelatedUse Listbox Values To Do Select Statement
May 20, 2007Hi,
I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2.
The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all.
public string str;protected void Button3_Click(object sender, EventArgs e)
{ArrayList itemsSelected = new ArrayList(); string sep = ",";
//string str;for (int i = 0; i < ListBox2.Items.Count; i++)
{if (ListBox2.Items[i].Selected)
{
itemsSelected.Add(ListBox2.Items[i].Value);
}
int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items.
str = ListBox2.Items[i].Value + sep;
Response.Write(str);
}
SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False");
String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable();
Adptr.Fill(Dt);
//return Dt;
GridView1.DataBind();
SqlCon.Close();
}
I did some changes and the new error message is
Incorrect syntax near the keyword 'from'.
Thank you
ListBox Mutli-Select Through A For Each Loop
Oct 23, 2007Hi everyone, not sure if a this topic has been covered yet (a have been looking all day), but as I am still very new to this, my problem is as follows:
In the Try .. Catch block below, data is posted from a form and the SqlCommand.ExecuteScalar() statement returns a Unique Job ID.
I am attempting to populate a subordinate table for qualifications which are selected from a ListBox, but rather than using qualification titles, I am using the values.
My problem is that only one value (the first) gets posted multiple times, when multiple values are selected.
Looking at the For Each loop in the inner Try Catch block, I am wondering whether there is some sort of Index pointer that needs to be incremented, in order to establish new values further down the list.
I have seen no evidence that this is the case, save for the fact that the value stalls on just the first.
Any help would be appreciated.
===== CODE ===
Try
C4LConnection.Open()
JobPostingID = SqlJobPost.ExecuteScalar()Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
Try
' Multiple Qualification EntriesSqlQualPost.Parameters.Add(New SqlParameter("@JobPostingID", SqlDbType.Int))
SqlQualPost.Parameters("@JobPostingID").Value = Int32.Parse(JobPostingID)SqlQualPost.Parameters.Add(New SqlParameter("@QualificationID", SqlDbType.Int))
SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
If Qualifications.SelectedIndex > -1 ThenFor Each Item In Qualifications.Items
If Item.Selected ThenResponse.Write("<br />SelectedItem Value: " & Qualifications.SelectedItem.Text)
QualPostingID = SqlQualPost.ExecuteScalar()SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
End If
Next
End IfCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Could not add qualifications <br />" & Exp.Message
End Try
failJobPost = FalseCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Error: could not post job to database <br />" & Exp.Message
Finally
C4LConnection.Close()
End Try
Selected Listbox Values In SQL Statement
Jan 9, 2008I have seen some information on this but have not understood fully the answers. I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer. How do I get selected values from a LIstbox into a SQL statement using the IN command
First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End IfNextLabel5.Text = strPC The result in the lable looks something like: 100, 101, 102 , I get rid of the trailing blank and commar with:Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) I have created a parameter called @PROPCLASS that will use the values from the selected values in the listbox in a SQL query using an IN statement: Cmd.Parameters.Add(New OleDbParameter("@PROPCLASS", Label6.Text)) strSQL = "SELECT * FROM web_transfer WHERE ( PROP_CLASS IN (@PROPCLASS)) AND (ACRES >= @lowSize)AND (ACRES < @HighSize) AND (YEAR = @lowYear)AND (SALE_PRICE > @lowPrice) AND (SALE_PRICE < @highPrice) "
It is really the first bit of the WHERE clause I am interested in. The sql statement works if I either type in the actual values IN( 100, 101,102) or if I select only one value in the list box. If I select two then the sql statement does not work.
My question is: How do I get the values selected in the List box into my SQL statement Thank you in advance, s
How To Insert All The Values From A Listbox Into The Database?
Jun 29, 2004I want that the user can chose several options from one ‘listbox’, and to do this, I have created two ‘listbox’, in the first one there are the options to select, and the second one is empty. Then, in order to select the options I want the user have select one or more options from the first ‘listbox’ and then click in a link to pass the options selected to the second ‘listbox’. Thus, the valid options selected will be the text and values in the second ‘listbox’. I have seen this system in some websites, and I think it is very clear.
Well, my question is, Is it possible to insert into the database all the values from a ‘listbox’ control? In my case from the second ‘listbox’ with all the values passed from the first one? If yes, in my database table I have to create one column (field) for every possible selected option?
Thank you,
Cesar
Grabbing A Value From Listbox To Query Database
Feb 1, 2006hello forum,
I need to grab a string value from a list box in from a web form, and pass it to a sql select command statement where that value is equal toall values in a database table(sql 2000).
example
zip code list box3315433254 845788547535454 selected value is 85475
I am putting that value in a string like this:
dim string_zip as stringstring_zip = zip_ListBox.text
Question, how do i pass that value to sql stament, i am using this but does not work.
SqlCommand1 = New SqlCommand("SELECT zip FROM table WHERE zip = string_zip", SqlConnection1)
Recordset Error While Filling The Listbox
Jun 9, 2004Hello all
I have shifted my vb/access database to vb/mysql and i have one form in my project in which i want to display all the records in the database in the list box . But while doing this i m getting the error " variable uses an automation type not supported in visual basic " . Moreover, it was working in Vb/access .
here is the code
Dim sql As String
intCountSW_ID = 0
sql = "select SW_IDEN, SW_NAME, SW_DELETE,SW_LEFTDATE from SV_SOCIALWORKER order by SW_NAME"
If rs.State = 1 Then rs.Close
Set connString = New ADODB.Connection
connString.CursorLocation = adUseClient
connString.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=;" _
& "DATABASE=shrivatsa;" _
& "UID=root;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
connString.Open
rs.ActiveConnection = connString
rs.CursorLocation = adUseClient
rs.Open sql, connString, adOpenStatic, adLockOptimistic
intCountSW_ID = rs.RecordCount
If intCountSW_ID > 0 Then
rs.MoveFirst
While Not rs.EOF
/* here comes the error on the below code
If rs("SW_DELETE") = 0 And IsNull(rs("SW_LEFTDATE"))
*/
Then
lstCaseName.AddItem rs("SW_IDEN") & " " & rs("SW_NAME")
End If
rs.MoveNext
Wend
rs.Close
End If
mfuncFillList = intCountSW_ID
Can Get Listbox To Load Data From Database
May 23, 2008I have a sql server database linked to my application. I have a table that I want one of the columns (Service Perfomed) to load in a list box. When I start the application nothing appears in the list box. What could I be doing wrong?
Here the code the visual studio created:
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Me.ServiceTableAdapter.Fill(Me.MaintenanceRecordsDataSet.Service)
End Sub
I've tried combo box, text box and these seem to work fine but not the list box.
Listbox To Only Appear If There Are Records Returned From The SQL Select Query
Oct 19, 2006I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
View 3 Replies View RelatedMultiple Selection Listbox As Data Control?
Aug 28, 2007Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc. i have the listbox as the control that is connected to the datasource (the sql used for it is select * from cars_table where color =@colorthis works fine when one item in the listbox is selected, but when multiples are selected it does not work what format does the =@color have to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it workingdoes anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to workthanks in advance!
View 1 Replies View RelatedHow Do I Store Multiple Selected Listbox Items
Jan 12, 2008Hi There
This is probably realy simple but since im still a newbie some help would be appreciated. I have a listbox bound to an sqldatasource which has names of people from my sql database employeeDetails table. I simply want to allow someone to select to select multiple names and insert them into another field in my database.
I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out
Kind Regads
Dan