List availables tables: listbox

Showing posts with label listbox. Show all posts

Wednesday, March 21, 2012

Listbox to only appear if there are records returned from the SQL select query

I 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?

hi,

there must be datasource(dataset or reader) for that listbox i believe.

if its dataset then use dataset.tables[0].Rows.Count, if its reader then use reader.hasrows.

hope it helps.

regards,

satish.

|||

Thanks Satish,

I used an if statement to assess if rows > 0

Cheers,

Ben.

|||

cheers Ben Smile .

satish.

sql

Listbox that show a sql server field content

I would like 2 things:
First i would like to show in a listbox in my web page the content of a sql field.
Besides I would like depending on which value was selected in the first listbox that the second listbox show a definied content.
Thank U very muchHowdy

Try www.aspfree.com

Cheers

SG

listbox selected values

Hi. With VWD i've produced the following code.
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @.liedID)">
<SelectParameters>
<asp:ControlParameterControlID="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.

That's not really an easy thing to do. There are a number of "issues" to work around.
1) The WHERE clause would need to be changed to an equate to something like IN.
2) Using IN, you can't use a parameter to represent a list of values.
3) There is no way to get the list control to give you a comma delimited list of selected values.
You can get around the #2 issue by dynamically creating an executing SQL using the EXEC command like this:
DECLARE @.sql varchar(8000)
SET @.sql = 'SELECT * FROM ibs WHERE liedID IN (' + @.liedID + ')'
EXEC(@.sql)
You can get around #3 by building the string yourself by:
1)Use autopostbacks to keep track of the selected values, and store them in a hidden field or a session variable as a comma delimited string.
or
2)On postback, get the list of selectedindexes via listbox1.GetSelectedIndices like this:
dim mystring as string=""
for each x as Integer in listbox1.GetSelectedIndices
mystring &= ",'" & cstr(Listbox1.Items(x).Value) & "'"
next
mystring=mystring.trim(",")
HiddenField.text=mystring
|||Thanks for your post. But I'm not really good at this. Where in the code do I have to make the EXEC command?
<%@. 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:t ="urn:schemas-microsoft-com:time">
<head runat="server">
<?import namespace="t"
implementation="#default#time2">
<style>
.time {behavior: url(#default#time2);}
</style>
<title>IBSTEST</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Panel ID="Panel1" runat="server" Height="50px" Width="125px" Visible="true">
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"
SelectCommand="SELECT * FROM [ibs] ORDER BY [liedTitel]"></asp:SqlDataSource>
<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource2" DataTextField="liedTitel"
DataValueField="liedID" Font-Names="Arial" Font-Size="X-Small" Height="300px"
SelectionMode="Multiple" Width="350px"></asp:ListBox><br />
<br />
<asp:Button ID="Button1" runat="server" Text="Play" OnClick="Button1_Click" Height="25px" Width="75px" /></asp:Panel>
<br />
<asp:Panel ID="Panel2" runat="server" Height="50px" Width="349px" Visible="false">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"
SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @.liedID) ORDER BY NEWID()">
<SelectParameters>
<asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue"
Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<t:seq repeatCount="1">
</HeaderTemplate>
<ItemTemplate>
<t:par>
<t:audio dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' src='<%# DataBinder.Eval(Container.DataItem, "liedID") %><%# DataBinder.Eval(Container.DataItem, "liedExtensie") %>' type='<%# DataBinder.Eval(Container.DataItem, "liedType") %>' />
<div dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' class="time" timeAction="display">
Titel: <%# DataBinder.Eval(Container.DataItem, "liedTitel") %><br />
Artiest: <%# DataBinder.Eval(Container.DataItem, "liedArtiest") %></div>
<img dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' class="time" timeAction="display" src='<%# DataBinder.Eval(Container.DataItem, "liedImage") %>.jpg' />
</t:par>
</ItemTemplate>
<FooterTemplate>
</t:seq>
</FooterTemplate>
</asp:Repeater>
<br />
<br />
<asp:Button ID="Button2" runat="server" Height="25px" OnClick="Button2_Click" Text="Stop"
Width="75px" /></asp:Panel>
<br />
</div>
</form>
</body>
</html>
and the .cs code is
using System;
using System.Data;
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;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Panel1.Visible = false;
Panel2.Visible = true;
}
protected void Button2_Click(object sender, EventArgs e)
{
Panel1.Visible = true;
Panel2.Visible = false;
}
}
After your post, I think it's clear what have to be done. But I don't know where to put the code.
Thank you very much,
Kin Wei

ListBox Mutli-Select through a For Each Loop

Hi 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 Entries

SqlQualPost.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 > -1Then

ForEach ItemIn Qualifications.Items

If Item.SelectedThen

Response.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)

EndIf

Catch ExpAs SqlException

failJobPost =True

lblError.Visible =True

lblError.Text ="Could not add qualifications <br />" & Exp.Message

EndTry

failJobPost =False

Catch ExpAs SqlException

failJobPost =True

lblError.Visible =True

lblError.Text ="Error: could not post job to database <br />" & Exp.Message

Finally

C4LConnection.Close()

EndTry

Hello Orion,

Orion Blue:
SqlQualPost.Parameters("@.QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)

In this statement you should use Item.Value. Currently you are looping through the items, but always refer to Qualifications.SelectedItem to get an ID.

|||

Thanks for the answer. Interestingly, I discovered the answer myself through trial and error. Once again, thanks for the reply.

listbox contents to sql stored procedure command parameter?

Hello,

Stuck in a spot and hoping someone will nudge me in the right direction...

I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it...

Dim connString As String
connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("InsertPage1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
' ......other (working) parameter statements.......
Dim parameterDates As New SqlParameter("@.Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = Session("lstDates")
myCommand.Parameters.Add(parameterDates)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.
i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?

tia
andyI would try Session("lstDates").ToString(), though I personally would try and debug it to see what exactly is in that session variable.

Are you getting an error message? If so, please give the EXACT message.|||thank you for the suggestion.

I've tried what you're saying and the table gets populated with the string:

"System.Web.UI.WebControls.ListItemCollection"

i took a stab in the dark and tried varbinary (sans .tostring) as the sqldatatype for the parameter/storedprocedure/columntype and i received:

Server Error in '/NET/LicenseRenewal' Application.
------------------------

Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Object must implement IConvertible.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidCastException: Object must implement IConvertible.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +196
LicenseRenewal.W4.WriteDBPage1() in C:\Documents and Settings\andrzej\VSWebCache\www.aea13.org\NET\LicenseRenewal\W4.aspx.vb:453
LicenseRenewal.W4.btnDONE_Click(Object sender, EventArgs e) in C:\Documents and Settings\andrzej\VSWebCache\www.aea13.org\NET\LicenseRenewal\W4.aspx.vb:158
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277

Seems it can't automatically convert a collection of listbox items into a sql column list, a string list (not one long string however) in the column would be sufficient.

i welcome/appreciate any further input.

thank you
andy|||forgot to say, the contents of the session("lstDates") are various quantities (less then 25 typically) of strings following the 'syntax' of "02/02/04 From: 08:00 AM To 09:00 AM". The string gets compiled from a text box and 2 other listboxes and added to the lstDates. I know the session("lstDates") holds valid data because i've used the session info to reload the listbox without problems

http://www.aea13.org/net/licenserenewal/w1p2.aspx is the page that has the listbox, the page following that is supposed to write to the database when someone clicks on the bottom button.

I took copies of these 2 pages out of the full application, spliced them together and have butchered the code to get rid of non (error) relevant functions. only the relevant parts work for this testing purpose, also the button is only writing the single parameter to a lone table with a lone column.

thanks again.|||ok, got tired of fighting with it so went around it...


Dim connString As String
connString = "............."
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("test1", myConnection)
myCommand.CommandType = CommandType.StoredProcedureDim strTempString As String
Dim lstTempListbox As New ListBox
Dim arrTemparray As New ArrayList
Dim x As Integer
lstTempListbox.DataSource = Session("lstDates")
lstTempListbox.DataBind()
For x = 0 To lstTempListbox.Items.Count - 1
If strTempString = "" Then
strTempString = lstTempListbox.Items(x).ToString
Else
strTempString = strTempString + lstTempListbox.Items(x).ToString
End If
Next
Dim parameterDates As New SqlParameter("@.Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = strTempString
myCommand.Parameters.Add(parameterDates)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

the table gets the data nicely formatted into a single cell and i can spit it back out in the RTF without formatting issues.

hope it helps someone else :)
andy

ListBox and Table Values

Hi

got small problems with the table values. I got three rows in a table

Index (int)
Product (varchar)
Price (float)

I could successfully connect to the database. I also get the values but somehow the ListBox don't show them...

Anyone can help me please ?

CODE

{
CDBVariant value;
char sql_statement [2048] = "";

//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty"),CDatabase::noOdbcDialog);

//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle"));
rs.Open(CRecordset::forwardOnly,sql_statement);

//Get quantity from Database
int n = rs.GetODBCFieldCount( );

while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )

{
rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_lVal));

rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));

rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) );

rs.MoveNext( );
}
}This is a VC++ code problem, I presume. Probably you should try some VC++ forums, for ex: www.codeguru.com

Best regards!sql

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me
Some examples here:
http://vyaskn.tripod.com/passing_arr...procedures.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me
|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help meSome examples here:
http://vyaskn.tripod.com/passing_ar..._procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help meSome examples here:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
--
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

Monday, March 19, 2012

ListBox

is it possible that a matrix inside a listbox1 and the listbox1 inside
another listbox2?
---
|list box2 |
| --
| | listbox1 |
| | --
| | |matrix |
| | | |
| | --
| | |
| --
| |
---
Thanksmayb i should say list instead of listbox...|||Try placing a subreport(s) inside a table. I have used this "layered"
concept before. HTH.
David
"SL" <SL@.discussions.microsoft.com> wrote in message
news:EAD22A3C-7A21-4A8D-BF05-43508BE1FBD9@.microsoft.com...
> is it possible that a matrix inside a listbox1 and the listbox1 inside
> another listbox2?
> ---
> |list box2 |
> | --
> | | listbox1 |
> | | --
> | | |matrix |
> | | | |
> | | --
> | | |
> | --
> | |
> ---
> Thanks|||i have found a way to solve my problem.. thanks anyway.

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

Listbox

I'm new to MS Reporting Services and I need help ....
I have a Stored Procedure and I'm passing 3 parameters. One of the
parameters is a listbox with several options. I want to automatically
populate those options from a database query. Problem is I am not sure
how to do this.
The report data is currently coming from a Stored Procedure that
returns a single dataset. Do I need to add another dataset or a
subreport? Need helpYou can handle this by creating a new dataset, which can either use a
stored proc that brings back the options you want in your listbox, or
manually typing in the query (dataset type of "Text").
Next, go to Report --> Parameters
Choose the parameter that you want a listbox for.
Under "Available Values," choose "From Query"
In the dataset field, select the new dataset you just created
In the "value field," choose the field from your dataset that holds the
"value" you want passed to the query that drives your report
In the "label field," choose the field from your dataset that you want
to display in the listbox.
John
melishbd wrote:
> I'm new to MS Reporting Services and I need help ....
> I have a Stored Procedure and I'm passing 3 parameters. One of the
> parameters is a listbox with several options. I want to automatically
> populate those options from a database query. Problem is I am not sure
> how to do this.
>
> The report data is currently coming from a Stored Procedure that
> returns a single dataset. Do I need to add another dataset or a
> subreport? Need help|||Sorry I had misunderstood...these guys seem to have you pointed in the
right direction now, anyway.
John
melishbd wrote:
> I'm new to MS Reporting Services and I need help ....
> I have a Stored Procedure and I'm passing 3 parameters. One of the
> parameters is a listbox with several options. I want to automatically
> populate those options from a database query. Problem is I am not sure
> how to do this.
>
> The report data is currently coming from a Stored Procedure that
> returns a single dataset. Do I need to add another dataset or a
> subreport? Need help

Monday, February 20, 2012

List a city only once

Hi...I want one listbox showing cities but I dont want to list a city more than one time....

I know that DISTINCT maybe could work...But I dont get it to work correctly....

The code:

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>

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

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

SelectCommand="SELECT DISTINCT [City] FROM [Location]">

</asp:SqlDataSource>

I got the message:

The text data type cannot be selected as DISTINCT because it is not comparable

The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)

You can't select distinct on "text" datatypes.

Not only that, but text, ntext, and image datatypes are depricated and should no longer be used. Perhaps a datatype change is in order for your Location table.

http://msdn2.microsoft.com/en-us/library/ms187993.aspx|||So what should I do....I just want one of every city....and it must be of the type "text"....Am I right?|||You should probably use varchar or nvarchar datatypes.

You could probably cast your text column to varchar and then execute your select distinct. You could do that on the fly.

See if this works:
select distinct(cast(city as varchar(50))) from location|||

I changed the City from text to VarChar(50) in the database and then I used:

SelectCommand="SELECT DISTINCT(cast(City as varchar(50))) FROM [Location]">

Got the following message:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'City'

|||Alias the output.

select distinct(cast(city as varchar(50))) as city from location|||

Thanx it works!

What is the code doing, because I am gonna describe it for someone else?

|||It's converting the datatype of city from "text" to "varchar."

You can't select distinct on a column datatype of text, and as a result your column should have its datatype updated in the table, permanently.

So in the SQL I gave you, we are simply converting on-the-fly instead of making a permanent change.|||

Okey...sorry, but I thaught that I should change it to varchar(50) in the database too....And it was therefore it worked

Now I changed it back to text and I got the following:

Erroemassage:

The data types text and nvarchar are incompatible in the equal to operator

(but what is the different between text and varchar(50)?....)

|||Change it in the database, and then you don't need to execute my SQL. You're first "select distinct city from location" will work just fine then.

You should really change the text field to varchar (or nvarchar if you desire) in the database because the text datatype is no longer supported and is removed from the next version of SQL Server.

So, change it in the database, and ignore everything else I've written here.

As far as your latest error message, I presume you're doing something other than a select distinct (cast......) from location.

Phil|||...|||

Okey....So everything I should do is to change text to varchar(50) in the database....

What is the 50 standing for?...is it max 50 characters? Can I use varchar(Max) instead of varchar(50)?

|||What?

For information on data types, please read: http://msdn2.microsoft.com/en-us/library/ms187752.aspx|||

Tigers21 wrote:
Okey....So everything I should do is to change text to varchar(50) in the database....
What is the 50 standing for?...is it max 50 characters? Can I use varchar(Max) instead of varchar(50)?

Yes, make sure that the length of the varchar field is big enough to hold your data. I wouldn't use max... You aren't going to have a city name 8,000 characters long, are you?|||

Ok...Thank you so much!

(I saw the link you gave me now!).....thanx!

Wednesday, March 21, 2012

Listbox to only appear if there are records returned from the SQL select query

Listbox that show a sql server field content

listbox selected values

ListBox Mutli-Select through a For Each Loop

listbox contents to sql stored procedure command parameter?

ListBox and Table Values

listbox and sql stored procedure

listbox and sql stored procedure

listbox and sql stored procedure

Monday, March 19, 2012

ListBox

Listbox

Listbox

Monday, February 20, 2012

List a city only once

List availables tables

Blog Archive

About Me