Select Statement using multi-list box values for WHERE IN SQL Clause

I have a gridview that is based on the selection(s) in a listbox.  The gridview renders fine if I only select one value from the listbox.  I recive this error though when I select more that one value from the listbox:

Syntax error converting the nvarchar value '4,1' to a column of data type int.  If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.

<

asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">

<SelectParameters>

<asp:Parameter Name="ListSelection" DefaultValue="1"/>

</SelectParameters>

</asp:SqlDataSource>

The selListID column is type integer in the database.

I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:

 

Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

Dim Item As ListItem

For Each Item In ListBox1.Items

If Item.Selected Then

If Label1.Text <> "" Then

Label1.Text = Label1.Text + Item.Value +

","

Else

Label1.Text = Item.Value +

","

End If

End If

Next

Label1.Text = Label1.Text.TrimEnd(

",")

SqlDataSourceAll.SelectParameters(

"ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)

End Sub

What am I doing wrong here?  Thanks!

0
ScooterB
1/11/2007 8:43:28 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
791 Views

Similar Articles

[PageSpeed] 30

Hello ScooterB my friend,

I have the answer for you.  First, run this SQL in your SQL Server database: -

 

CREATE  FUNCTION dbo.StringArrayIntoTable
(
 @String VARCHAR(8000),
 @Separator VARCHAR(1)
)
RETURNS @tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @pos INT,
@SubStr VARCHAR(10)


SET @pos = CHARINDEX(@Separator, @String)

WHILE @pos > 0
BEGIN
 SET @SubStr = SUBSTRING(@String, 0, @pos)

 INSERT INTO @tblStrings (Item) VALUES (@SubStr)

 SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
 SET @pos = CHARINDEX(@Separator, @String)
END
 
INSERT INTO @tblStrings (Item) VALUES (@String)
RETURN
END

This turns a parameter list into a table.  It comes into play later.  Experiment with the following: -

SELECT Item FROM dbo.StringArrayIntoTable('i,can,see,you', ',')
SELECT Item FROM dbo.StringArrayIntoTable('one|two|three', '|')

The first parameter is the string, the second is the separator.  From the web page you will pass in one string parameter containing each of the chosen items and then the database will do the rest. 

This next part you need to adapt to your scenario as my data is different but I have made the scenario similar.  Basically, my page has a listbox of countries.  You choose the countries and then the page shows the destinations in the chosen countries.  There is a table called tblDestination with records like "Majorca" and "London", etc and the CountryID field links back to the tblCountry table. 

 

CREATE PROCEDURE usp_GetDestinationsByCountries
(
 @CountryIDs AS VARCHAR(8000)
)

AS

SELECT * FROM tblDestination WHERE CountryID IN
 (SELECT Item FROM dbo.StringArrayIntoTable(@CountryIDs, '|'))

 

Then the web page is as follows: -

 

<%

@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!

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>

Countries: -

<p>

<asp:ListBox ID="lbxCountries" runat="server"

SelectionMode="Multiple" AutoPostBack="True"

OnSelectedIndexChanged="lbxCountries_SelectedIndexChanged">

<asp:ListItem Value="1">France</asp:ListItem>

<asp:ListItem Value="2">Italy</asp:ListItem>

<asp:ListItem Value="3">Spain</asp:ListItem>

<asp:ListItem Value="4">Japan</asp:ListItem>

</asp:ListBox>

</p>

<hr />

 

<asp:DataGrid ID="dgdDestinations" runat="server" DataSourceID="sqlCountries" />

<asp:SqlDataSource ID="sqlCountries" runat="server"

ConnectionString="<%$ConnectionStrings:HolidaysDB %>"

SelectCommandType="StoredProcedure"

SelectCommand="usp_GetDestinationsByCountries">

 

<SelectParameters>

<asp:FormParameter Name="CountryIDs"

Direction="Input" Type="String" />

</SelectParameters>

 

</asp:SqlDataSource>

 

</div>

</form>

</

body>

</

html>

 

using

System.Configuration;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

using

System.Data.SqlClient;

 

public

partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

 

// hanlde multiple items selected

protected void lbxCountries_SelectedIndexChanged(object sender, EventArgs e)

{

string strSelectedCountries = "";

foreach (ListItem li in lbxCountries.Items)

{

if (li.Selected)

{

strSelectedCountries += li.Value +

"|";

}

}

strSelectedCountries = strSelectedCountries.TrimEnd(

'|');

sqlCountries.SelectParameters[

"CountryIDs"].DefaultValue = strSelectedCountries;

}

 

 

This should take care of your issue. 

Kind regards

Scotty

 

0
ask_Scotty
1/11/2007 10:58:57 PM

Wow, thank you Scotty!  One problem though...when I run the form I get:

System.NullReferenceException was unhandled by user code

  Message="Object reference not set to an instance of an object."

 

At this line: 

SqlDataSourceAll.SelectParameters("ListID").DefaultValue = strSelectedIDs

I used your example exactly (with the exception of changing a few variable names and I did not know how to implement the "Using" references in your code to my VB code behind.  What am I still missing? 

WIth Sincerest Thanks for your help and best regards!  ScooterB

0
ScooterB
1/12/2007 4:02:36 PM

Hi ScooterB,

I would check the following: -

SqlDataSourceAll is the name of the asp:SqlDataSource in the asp page

ListID is the name of the asp:FormParameter in the aspx page, which I originally called CountryIDs

As for the "using" references, just change "using" to "imports" if using VB

 

Kind regards

Scotty

 

0
ask_Scotty
1/12/2007 5:15:10 PM

That was it...I had mistyped my asp:FormParameter name...it was ListIDs not ListID...works like a charm now...many thanks Scotty!

0
ScooterB
1/12/2007 6:34:27 PM
Reply:

Similar Artilces:

Using selected values from a Multi Select Listbox in a SQL query
I have seen a few post concerning this but don't really understand fully as I am new to this.  I would appreciate any help 1) First I loop through the Listbox control and get the selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End If Next Label5.Text = strPC The resulting string looks something like: 101, 102, 100 ,    I get rid of the last space and "," with the following statement Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) 2) I would then like to ...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Using a TextBox value as an Integer for a SQL select statement
 I want to use a TextBox entry in a SQL statement.  I want the user to put in a year and have a GridView that I have connected to a DataSource reflect the input by only displaying rows with data after the year selected.  I would like to restrict input values to valid integers automatically.  But most of all I need to be able the use the TextBox value in a SQL statement in the DataSource.  Something like this WHERE (Table.Year > @StartYear).  Where the @StartYear is associated with TextBox.Text.  But I get things like run time browser error "Conversi...

how to use sql select statement to find null values
how to use the select statement to find null values in the database. I thought of using: sql="SELECT * FROM Table where Field = DBNull" But I guess this doesn't work, any suggestions Try: sql="SELECT * FROM Table where Field isNull" David WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer' when it applies) The SQL standard is "WHERE Field IS NOT NULL". Some database...

combining 2 sql statements using Access version and not sql server to show results in a data list
Hi, I have 2 sql statements which will run in Access: private string m_cmdInitial = "Select Distinct Mid([Attribute Name],1,1) AS Initial FROM [Attributes]";  private string m_cmdCustmrs = "SELECT * FROM [Attributes] WHERE(Attributes.[Attribute Name]) LIKE '{0}*'"; The thing is that using SQL server the two sql statements can be combined using the format Function of ASP.net just by putting semicolon between the statements. The purpose is as follows: I am using 2 data lists. The first one is just a list of letters on top from which the user can choose a letter on which the term starts...

selecting multiple values in a list box using vb.net code
Hi, I have a list box with certain values. The SelectionMode property of List box is set to Multiple. I want to select multiple items based on the values of the items in the Listbox using the vb.net code. Can any one please help.Thanks eg: for i = 0 to listbox.items.count - 1 if listbox.items(i).selected then Response.Write("Selected") else Response.Write("Selection Needed") end if next refer links: http://www.411asp.net/func/content?tree=411asp/tutorial/specific/web/userinte/webcontr...

HELP:SQL statement not displaying or selecting record using passed values in the URL
Hello people, I am developing web application using Visual.Net 2003 in Csharp (Programming Language) using Microsoft Framework 1.1. I have done a datagrid and when one clicks on it he or she should be directed to a different page that will display selected record in edit mode. So the code behind the button is below: private void editAFlightCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)         {             string flightid = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();        ...

how to make one list box(this list box should have selected products) gets filled from another list box (for example this list box has the whole products) by using navigation control
Hello Mate!, I have two list boxes. one should get populated with whole products. and i have another list box in which i have to move some of the products which i like .   The design should be the one below: List Box1   List Box 2 all products   Selected products BMW    >> BMW BENZ     > LEXUS AUDI     <   LEXUS    <<     Could you please suggest me whether i have to use buttons(by which i move items from one list box to another)  ...

Use Session Variable in Sql Select statement.
if (!IsPostBack)        {            if (Session["SessionUserID"] != null)            {                string SessionName = Session["SessionUserID"].ToString();                                  &nbs...

Executing the i-sql statements using i-sql interface.
I am writing the c/c++ interface which will execute i-sql query statements to retrive information from the isql. It involves parsing the isql output to get the required information objects ( database, devices, ...etc). This parsing string method seems to be not appropriate, as the format of the isql statement might change in future release of ASEs. I am wondering is there any otherway I can access the information(like querying info of databases, devices, ...etc) from sybase server from "C/C++" program in structural manner??. Is it possible to get the return status(succe...

Viewing a SPIDs last SQL statement using sqltext()...though sometimes only partial SQL statements are shown
The dbcc sqltext() command seems to allow you to view the last SQL statement issued by an active SPID on the server. However I've noticed that sometimes only part of the SQL statement is returned - does anyone know why this might be, and if so whether it's due to a server configuration. Anyway, here's my notes on how to view the last SQL statement processed: ** How to see the last SQL a SPID has processed -- dbcc traceon(3604) Directs trace output to the client, rather than to the error log 1> dbcc traceon(3604) 2> go DBCC execution completed. If DBCC pr...

use if statement in select sql
Hi. i have the database with status field (true,false) in access  i have the cod in vb.net  strSqlQuery="select id,name,status FROM...  how i can use the IF in this code,as strSqlQuery="select id,name, if (status=true then status="ok" else "No")  FROM table....   thanks  Hello,You can use Select Case. For more help see the link,http://www.databasedev.co.uk/select-case-statement.htmlThanks,Deepesh Verma  Dont forget to click “Mark as Answer” on the post that helped you.This credits that member, earns...

Sql Datasource select statement
I have a gridview that uses a sqldatasource to display (classid, userid, startdate, enddate) from a class table.  My datagrid also contains a submit button.  When this button is clicked I am inserting this data into a new table called register.  I need a way to not show classes for the user if that user is already in the register table for that class.  Currently all items are being shown which would allow the user to register for the same class more than once.  Any ideas?   Thanks  you could make classid and userid as a pri...

Using Gridview for delete, update and select using seperate sql statements and parameters
Hi everyone, I have a gridview which is bound to a sqldatasource which runs a stored procedure using userid as a parameter.  This then puts into a gridview all the surveys that that user has created. This all work fine, the problem is that when I put a delete button on the grid it expects the same parameter that the select statement uses which is the userid.  However the stored procedure which deletes from the database uses surveyID as the parameter as i need the user to be able to choose which survey they want to delete. My question is how do you have a delete button which ru...

Web resources about - Select Statement using multi-list box values for WHERE IN SQL Clause - asp.net.sql-datasource

Caroline Rhea lists multi-unit LA home for $1.7M
The complex offers two units with two bedrooms and one bathroom each; and a one-bedroom, one-bathroom loft that sits over a four-car garage. ...

BRW Young Rich List: Dave Greiner on how to succeed and be a multi-millionaire
DAVE Greiner and Ben Richardson were debutantes on the BRW Young Rich List this year.

Why the rich love having an underground car park or atrium: Improvements among top 10 list of extravagant ...
A two or even three-storey atrium is seen as the ultimate way to impress guests while triple-storey underground garages holding up to ten cars ...

Resources last updated: 1/2/2016 3:33:07 AM