Dropdownlist: Database connections problems at EditItemTemplate

Hello,

I'm attempting to generate a dropdownlist by binding it to a database table. My screen has a view of the records plus a footer for inserting new records. I want to populate a drop down list of items from the database on both FooterTemplate and EditItemTemplate. I can successfully create the list on FooterTemplate (the insert new record fields). Here, I have no problems with my database connections.

On page load, if I hit 'Edit', it gives me this error:

There is already an open DataReader associated with this Connection which must be closed first.

I tried closing the datareader and I get an error:Invalid attempt to Read when reader is closed.

Here's the snippet of code:

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load'Put user code to initialize the page here

LoadDataFromDB()

IfNot IsPostBackThen

DataBindGrid()

EndIfEndSubSub LoadDataFromDB()Dim strSQLStmntAsStringDim cmdDefaultsAs SqlCommand' Create new command object passing it our SQL query' and telling it which connection to use.

strSQLStmnt = "SELECT tblTemplates.TemplateID, tblTemplates.Description, " _

& "tblTemplates.Tuition, tblTemplates.Books, tblTemplates.TrainingTable, tblTemplates.Housing, " _

& "tblResidency.ResidencyType, tblCategory.Type, tblCategory.Description AS CategoryDesc, " _

& "tblScholarship.ScholarshipType FROM tblTemplates INNER JOIN tblResidency ON tblTemplates.ResidencyID = " _

& "tblResidency.ResidencyID INNER JOIN tblScholarship ON tblTemplates.ScholarshipID = tblScholarship.ScholarshipID " _

& "INNER JOIN tblCategory ON tblTemplates.CategoryID = tblCategory.CategoryID"

cmdDefaults =

New SqlCommand(strSQLStmnt, conDefaults)' Open the connection, execute the command, and close the connection.

conDefaults.Open()

rdrDefaults = cmdDefaults.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

DefaultsDataGrid.ShowFooter =

TrueEndSubSub DataBindGrid()

DefaultsDataGrid.DataSource = rdrDefaults

DefaultsDataGrid.DataBind()

EndSubFunction BindResidency()As DataSet'Populate the ddlDataSetConst strSQLDDLAsString = _

"SELECT Distinct ResidencyID, ResidencyType FROM tblResidency"

Dim myDataAdapterAs SqlDataAdapter =New _

SqlDataAdapter(strSQLDDL, conDefaults)

myDataAdapter.Fill(ddlDataSet)

Return ddlDataSetEndFunction

And the asp.net:

<asp:TemplateColumn HeaderText="Residency">
<ItemTemplate>
<%# Container.DataItem("ResidencyType") %>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList id="add_residency" runat="server" Width="178px" DataSource="<%# BindResidency() %>" DataTextField="ResidencyType" DataValueField="ResidencyID">
</asp:DropDownList>
</FooterTemplate>
<EditItemTemplate>
<asp:DropDownList id="edit_residency" runat="server" Width="178px" DataSource="<%# BindResidency() %>" DataTextField="ResidencyType" DataValueField="ResidencyID">
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>

Any help would be appreciated!

Thanks,

Curtis

Sure enough, after posting I figured out what the problem was. Basically, I can easily create another SQLConnection to the SQL Server db and use it to populate all my drop down lists. May not be the most efficient use of database connections, but it works.

Thanks,

Curtis

|||

Without changing some structure of your code, that is how you would have to do it. The BindResidency is being called while the datagrid is looping through your datareader, so the connection is in use at that point. You can get around it, by pre-filling the dataset (Prior to the datagrid databind call) and having the function return it. You may also want to cache the dataset in your function, especially if the list doesn't change often.

sql

Dropdownlist with stored procedure problem

Hi Everyone,

I am trying to load the data into the dropdownlist using stored procedure. But when I run the code, the dropdownlist is empty. The code is shown below. Please help! Thanks.

public DataSet getProvince()
{
DataSet ds = new DataSet();
SqlParameter myParam;

string conString;
SqlConnection myConnection;

conString = ConfigurationManager.AppSettings["connectionString"];
myConnection = new SqlConnection(conString);

SqlCommand cmd = new SqlCommand("stored_procedure_GetProvinces", myConnection);

SqlDataAdapter adpt = new SqlDataAdapter(cmd);

try
{

cmd.CommandType = CommandType.StoredProcedure;
myParam = cmd.Parameters.Add("@.province_key", SqlDbType.Int);
myParam.Direction = ParameterDirection.Output;

myParam = cmd.Parameters.Add("@.province_name", SqlDbType.NVarChar, 200);
myParam.Direction = ParameterDirection.Output;

adpt.Fill(ds, "Provinces");

myConnection.Close();

}
catch (SqlException ex)
{
Response.Write("Error: " + ex.Message);
}
return ds;
}

-----------

create procedure stored_procedure_GetProvinces( @.province_key int output, @.province_name nvarchar (200) output)AsSelect @.province_key=province_key, @.province_name=province_name From provinceGO

--------

<asp:DropDownList id="Dropdownlist_Province" DataValueField="province_key" DataTextField="province_name" DataSource='<%# getProvince() %>' Runat="server" >
</asp:DropDownList>

----------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO

CREATE TABLE province (
province_key int IDENTITY PRIMARY KEY,
province_name nvarchar (200) NULL
)
GO

INSERT INTO province(province_name) VALUES
('- Select -');
GO
INSERT INTO province (province_name) VALUES
('Ontario');
GO
INSERT INTO province (province_name) VALUES
('Alberta');
GO
INSERT INTO province (province_name) VALUES
('British Columbia');
GO
INSERT INTO province (province_name) VALUES
('Manitoba');
GO
INSERT INTO province(province_name) VALUES
('New Brunswick');
GO
INSERT INTO province (province_name) VALUES
('Newfoundland');
GO
INSERT INTO province (province_name) VALUES
('Northwest Territories');
GO
INSERT INTO province (province_name) VALUES
('Nova Scotia');
GO
INSERT INTO province (province_name) VALUES
('Nunavut');
GO
INSERT INTO province (province_name) VALUES
('Prince Edward Island');
GO
INSERT INTO province (province_name) VALUES
('Quebec');
GO
INSERT INTO province (province_name) VALUES
('Saskatchewan');
GO
INSERT INTO province (province_name) VALUES
('Yukon Territory');
GO

What is the value of your parameter?|||

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

|||

bullpit:

What is the value of your parameter?

Sorry, I don't know. Thanks.

|||

ndinakar:

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

After I modify my code and my stored procedure. I still get the empty dropdownlist. Please help. Thanks.

|||did you change the proc to the way I suggested? Also please post your new .NET code..|||

ndinakar:

did you change the proc to the way I suggested? Also please post your new .NET code..

My updated code is shown below. Thanks!

create procedure

stored_procedure_GetProvinces

(

@.province_key

intoutput,

@.province_name nvarchar (200)

output

)

As

Select

province_key, province_nameFromprovince

GO

--------

publicDataSet getProvince()

{

DataSet ds =newDataSet();SqlParameter myParam;string conString;SqlConnection myConnection;

conString =

ConfigurationManager.AppSettings["connectionString"];

myConnection =

newSqlConnection(conString);SqlCommand cmd =newSqlCommand("stored_procedure_GetProvinces", myConnection);SqlDataAdapter adpt =newSqlDataAdapter(cmd);try

{

cmd.CommandType =

CommandType.StoredProcedure;

adpt.Fill(ds,

"Provinces");

myConnection.Close();

}

catch (SqlException ex)

{

Response.Write(

"Error: " + ex.Message);

}

return ds;

}

--------- the end -------

|||

ndinakar:

what you need is:

Select province_key, province_nameFrom province

also, you need to modify your .NET code and remove the parameter part. The way you are doing, your proc will return just one set of values from the table, since there is no WHERE condition and your variables can only have one value at a time, SQL server will just pick a row at random and assign the values to the variables. Assuming you want to see ALL the values since you are binding it to a drop down, you just need the SELECT statement with the columns (without any variables). Also, look into using an ExecuteReader which is faster than filling a dataadapter and a dataset.

I have just tried using ExecuteReader. And I still get empty dropdownlist. Please help. Thanks.

SqlCommand objCmd;

SqlConnection objConn;

SqlDataReader dataReader;

String strSql;

objConn =newSqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"));

strSql ="EXECUTE stored_procedure_GetProvinces";

objCmd =newSqlCommand(strSql, objConn);

try

{

objConn.Open();

dataReader = objCmd.ExecuteReader(

CommandBehavior.CloseConnection);

Dropdownlist_Provinces.DataSource = dataReader;

Dropdownlist_Provinces.DataTextField =

"province_name";

Dropdownlist_Provinces.DataValueField =

"province_name";

Dropdownlist_Provinces.DataBind();

}

catch

{

}

finally

{

objConn.Close();

objConn.Dispose();

}

|||

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

|||

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

|||

MayLam:

ndinakar:

Your stored proc would be like this:

create procedure dbo.stored_procedure_GetProvincesAsSelect province_key, province_nameFrom province GO

Then your .NET code could be someting like this: You might have to convert it to C#.

Protected objConAs New SqlConnection(ConfigurationSettings.AppSettings("conn"))pubic sub....Dim myCommandAs SqlCommandmyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="stored_procedure_GetProvinces"myCommand.CommandType = CommandType.StoredProcedureIf objCon.State = 0Then objCon.Open()dpunits.DataSource = SqlCmd1.ExecuteReaderdpunits.DataTextField ="province_name" dpunits.DataValueField ="province_key"dpunits.DataBind()objCon.Close()end sub

What is SqlCmd1 in SqlCmd1.ExecuteReader ? Thanks.

I replace SqlCmd1 with myCommand. And it is working now. Thank you very much for your help! Bye!

May

Dropdownlist with multiple fields

How can I get two fields to appear side by side in the DropDownList? Ex. FName and LName

Thanks

Here'sone example.

DropDownList instead of Textbox in Reporting Services

Hello,
I have a report that uses a stored procedure. The parameter from the stored procedure is a name field defined nvarchar(50). But I have a list of values for this field in another table; is there anyway to use the values from the other table to populate a dropdownlist of these values, instead of prompting the user for a textbox?
Thanks.Hi ,
for populating drop down list instead of default text box, you have to create another dataset which will fetch fields from another table.
while in layout, go to Report --> Report Parameters..
click on the parameter on left and on right side, select radio btn From query and specify dataset and select fields.
you can also select default value also. i think this is what u want right?
Thanks
Rohit
|||

Hey,

Yes, that is exactly what I was looking to do. Thanks a lot.

|||That's good for designing reports on the server (RDL). I'm using Visual Web Developer Express and cliking on Report-Report Parameters it seems there's no option for configuring parameters to come from a dataset. Is there a way around this?|||No takers so far. So let me re-phrase the question. Is there any way by which I can move parametrized reports from the server (RDL) to the client (RDLC)?|||Sure you can. You need to use the parameter objects to pass parameters to the report.

DropDownList inside CreateUserWizard

I am using the createuserwizard, and have set up my own profile table which hold first name, last etc. In my CreateUserWizard, the field for Vendor is a dropdownlist that is to be populated from a list in a database. I set up the form without the createuserwizard, and everything went skippy, but when I placed it into the wizard, I now cannot access the control of the dropdownlist.

I have tried doing..

Using SqlConnectionAsNew SqlConnection(WebConfigurationManager.ConnectionStrings("Personal").ConnectionString)
Dim VendorID As DropDownList
VendorID = CType(CreateUserWizard1.FindControl("VendorName"), DropDownList)

Dim MyReaderAs SqlDataReader
Dim selAsString =String.Format("SELECT VendorID, Name FROM Vendors")
Dim MyCommandAs SqlCommand
MyCommand =New SqlCommand
MyCommand.CommandText = sel
MyCommand.CommandType = CommandType.Text
MyCommand.Connection = SqlConnection
MyCommand.Connection.Open()
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
But when I go to databind, it says:
Object reference not set to an instance of an object.
VendorID.DataSource = MyReader
Line 30: VendorID.DataValueField = "VendorID"
Line 31: VendorID.DataTextField = "Name"

You actually have not instantiated your data reader. Try this:

Dim MyReaderAsNewSqlDataReader|||

Sorry, I left that line out of my post, but it was in there. I found the solution to my problem though.

I was originally putting in CreateUserWizard1.FindControl("VendorID"), DropDownList)
THe actual code that worked is...
CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("VendorID"), DropDownList)
Works a charm.
It's always a bugger not knowing these little "tricks"!!
Mick

|||

mickyjtwin:

Sorry, I left that line out of my post, but it was in there. I found the solution to my problem though.

I'm glad you found the solution. In the future, please be sure to copy-and-paste your exact code, not an approximation of it. Otherwise others (such as myself) will spend needless time trying to help you to debug problems that don't exist.

Dropdownlist in Textfield like Excel 'autofilter'

I'd like to build a dropdownlist in the texfield which describes the columns. This dropdownlist should show all distinct values of the column like the 'autofilter' in excel. This sholuld be done directly in the report not as a parameter in the head. Any ideas ?
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=fb75b6199de248a2988f407b48fc9791
*****************************************Hi
This really interests me 2. Have you already received feedback on this issue?
Koen
"Psycho Dad via SQLMonster.com" wrote:
> I'd like to build a dropdownlist in the texfield which describes the columns. This dropdownlist should show all distinct values of the column like the 'autofilter' in excel. This sholuld be done directly in the report not as a parameter in the head. Any ideas ?
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=fb75b6199de248a2988f407b48fc9791
> *****************************************
>sql

dropdownlist -default value!

hi ,

need your help please

I have got a dropdownlist which is getting it's items and values from the table with in SQL server , everything works fine.

I just want to be able to select default item , so when the page is loaded the dropdown list default will be that selected item.

as an exmple if the drop downlist items and values are as follow

item --> value
-------
city --> 1
tokyo --> 2
london -->3

these info is imported from the database
and I want by default tokyo to be selected

many thanks

M

------------
here is my code:

 Dim sqlConnection As SqlConnection
 Dim sqlDataAdapter As SqlDataAdapter
 Dim sqlCommand As SqlCommand
 Dim dataSet As DataSet
 Dim dataTable As DataTable
 sqlConnection = New SqlConnection("user id=sa;password=testfirm;database=ken_live;server=csfirm03")
 'pass the stored proc name and SqlConnection
 sqlCommand = New SqlCommand("Select * From _aci",sqlConnection)
 'instantiate SqlAdapter and DataSet
 sqlDataAdapter = New SqlDataAdapter(sqlCommand)
 dataSet = New DataSet()
 'populate the DataSet
 sqlDataAdapter.Fill(dataSet, "AA")
 'apply sort to the DefaultView to sort by CompanyName
 dataSet.Tables(0).DefaultView.Sort = "bsheet"
 city.DataSource = dataSet.Tables("AA").DefaultView
 city.DataTextField ="bsheet" ' what to display
 city.DataValueField ="bsheet" ' what to set as value
 city.DataBind()

Dim li as ListItem
FOR EACH li in city.Items
 IF li.value = 2 THEN
 li.selected = true
 END IF
NEXT

|||I would also advise using a DataReader object rather than a DataSet and DataAdapter. The DataReader is much faster and does not use nearly as much resources. Also be sure to clean up after you use the objects, for example:

city.DataBind()
sqlCommand.Dispose
sqlCommand = Nohting
sqlDataAdapter.Dispose
sqlDataAdapter = Nothing
dataSet.Dispose
dataSet = Nothing
sqlConnection.Close
sqlConnection = Nothing

HTH|||------ Edited by Colt ------
<code/> tag was added
------ Edited by Colt ------

thanks for the reply

I've changed the code, ( no error messages ) but no effect almost

 Dim sqlConnection As SqlConnection
 Dim sqlDataAdapter As SqlDataAdapter
 Dim sqlCommand As SqlCommand
 Dim dataSet As DataSet
 Dim dataTable As DataTable
 Dim li as listitem
 sqlConnection = New SqlConnection("user id=sa;password=testfirm;database=ken_live;server=csfirm03")
 'pass the stored proc name and SqlConnection
 sqlCommand = New SqlCommand("Select * From _aci",sqlConnection)
 'instantiate SqlAdapter and DataSet
 sqlDataAdapter = New SqlDataAdapter(sqlCommand)
 dataSet = New DataSet()
 'populate the DataSet
 sqlDataAdapter.Fill(dataSet, "AA")
 'apply sort to the DefaultView to sort by CompanyName
 dataSet.Tables(0).DefaultView.Sort = "bsheet"
 city.DataSource = dataSet.Tables("AA").DefaultView
 city.DataTextField ="bsheet" ' what to display
 city.DataValueField ="bsheet" ' what to set as value
 for each li in city.items
 if li.value="CITY" then
 li.selected=true
 end if
 next
 city.DataBind()

|||The DataBind() should preceed the For Loop that is selecting the default value.

city.DataBind()
for each li in city.items
 if li.value="CITY" then
 li.selected=true
 end if
 next

|||thanks again for following the case
but still the porblem does exist ( no error messages but no effect as well )

code:

session("A")="0"

'object vars
Dim sqlConnection As SqlConnection
Dim sqlDataAdapter As SqlDataAdapter
Dim sqlCommand As SqlCommand
Dim dataSet As DataSet
Dim dataTable As DataTable
Dim li as listitem

sqlConnection = New SqlConnection("user id=sa;password=testfirm;database=ken_live;server=csfirm03")

'pass the stored proc name and SqlConnection

sqlCommand = New SqlCommand("Select * From _aci",sqlConnection)

'instantiate SqlAdapter and DataSet

sqlDataAdapter = New SqlDataAdapter(sqlCommand)
dataSet = New DataSet()

'populate the DataSet

sqlDataAdapter.Fill(dataSet, "AA")

'apply sort to the DefaultView to sort by CompanyName

'dataSet.Tables(0).DefaultView.Sort = "bsheet"

city.DataSource = dataSet.Tables("AA").DefaultView

city.DataTextField ="bsheet" ' what to display

city.DataValueField ="bsheet" ' what to set as value

city.DataBind()

for each li in city.items

if li.value="CITY" then

li.selected=true

end if

next|||As you had presented earlier, the Value property of the DDL was populated with a numeric value and the Text property was propulated with the character description (i.e., CITY).

li.value = 2

or you can use

li.text = "CITY"

HTH|||hi again :(

it didn't work.

always it is coming up wit it's own default one ( which is the fist row of data in database)

please , please , any alternative way

again I'm no getting any error messages

many thanks|||Please post your current code.|||

public Sub page_load(sender As Object, e As EventArgs)
 If Not Page.IsPostback Then
 session("A")="0"
 'object vars
 Dim sqlConnection As SqlConnection
 Dim sqlDataAdapter As SqlDataAdapter
 Dim sqlCommand As SqlCommand
 Dim dataSet As DataSet
 Dim dataTable As DataTable
 sqlConnection = New SqlConnection("user id=sa;password=testfirm;database=ken_live;server=csfirm03")
 'pass the stored proc name and SqlConnection
 sqlCommand = New SqlCommand("Select * From _aci",sqlConnection)
 'instantiate SqlAdapter and DataSet
 sqlDataAdapter = New SqlDataAdapter(sqlCommand)
 dataSet = New DataSet()
 'populate the DataSet
 sqlDataAdapter.Fill(dataSet, "AA")
 'apply sort to the DefaultView to sort by CompanyName
 'dataSet.Tables(0).DefaultView.Sort = "bsheet"
 city.DataSource = dataSet.Tables("AA").DefaultView
 city.DataTextField ="bsheet" ' what to display
 city.DataValueField ="bsheet" ' what to set as value
 city.DataBind()
 sqlCommand = New SqlCommand("select distinct datepart(yy,dateincur) as tempdate from ken_non_matter where nonmat in (935,1035,1040) order by datepart(yy,dateincur) desc",sqlConnection)
 sqlDataAdapter = New SqlDataAdapter(sqlCommand)
 dataSet = New DataSet()
 'populate the DataSet
 sqlDataAdapter.Fill(dataSet, "tempdate")
 'apply sort to the DefaultView to sort by CompanyName
 ' dataSet.Tables(0).DefaultView.Sort = "tempdate"
 year.DataSource = dataSet.Tables("tempdate").DefaultView
 year.DataTextField ="tempdate" ' what to display
 year.DataValueField ="tempdate" ' what to set as value
 year.DataBind()
 End if
 for each li in city.items
 if li.value="CITY" then
 li.selected=true
 end if
 next
End Sub

|||As you had presented earlier, the Value property of the DDL was populated with a numeric value and the Text property was propulated with the character description (i.e., CITY).

li.value = 2

or you can use

li.text = "CITY"

You are using li.value="CITY" (this is wrong according to your previous posts)

USE li.value = 2

HTH|||You shouldn't need to loop through the liost collection, either.

After you databind (and you should always bind the list before you attempt to set a default value), simply do the following:

city.Items.FindByValue("YOUR VALUE OR FIELDNAME GOES HERE").selected = true;

You can also use FindByText, if you prefer (city.Items.FindByText("text").selected = true)

If the ddl might have already contained a selected item, be sure to use ClearSelection first, or you'll throw an error...|||this is going arround

I manage to find where I'm going wrong but don't know how to fix it

for each li in city.items

If (li.text = "CITY ") then
li.selected=true
response.write(li.value) ' just Entered it as a test ! which doesn't print at all

End if

next

It runs the FOR loop fine but doesn't enter the IF statment.

just for test if I add

for each li in city.items

response.write("-"+li.value+"-")

if (li.text = "CITY ") then
li.selected=true
response.write(li.value) ' just Entered it as a test ! which doesn't print at all

End if

next

it will print

-AFSC --CITY --HKONG --INDIA --NZ -

whitch there is a space after the string therefore , I add a space after the 'city ' in my code

for each li in city.items

response.write("-"+li.value+"-")

if (li.text = "CITY ") then
li.selected=true
response.write(li.value) ' just Entered it as a test ! which doesn't print at all

End if

next

but still it is not entering the IF section

any ideas|||You're writing too much code. Try the method I posted above...|||it is small code but I've explained it,
it doesn't run

DropdownLIst box question, Stored Procedure

Hi All,

I have a dropdownlist box with values (All, Paris, London, New York) -- Cities

Another dropdwonlist box with values (All, Bank of America, City Bank, CIBC) -- Banks

And have a stored procedure that populates the info:

CREATE Procedure Search
(
@.City nvarchar(50),
@.Bank nvarchar(50)
)
AS SELECT * FROM TableA WHERE
city = @.city AND bank= @.bank GO

If user selects a value anything other than "All" in both dropdownbox, everything is OK.

My question is, if user selects "All" in any of these dropdown combobox, how can I seach for all Cities or Banks.

SQL needs to run as a stored procedure. If it was on client end, it would be alot easier.

Any ideas?

Thanks for your help.

Duplicate post, see
http://forums.asp.net/1138420/ShowPost.aspx

Dropdown list values

Dear Readers of this post,

I have a dropdownlist that is populated with an sqldatasource as follows:

SELECT[Project_ID], [Title] FROM [Projects] WHERE [Username] = @.Username AND Hide ='false'

The Datavalue vield of the DDL is populated with the [Title].

When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.

any ideas

Sat

The Title can be obtained through the DDL.SelectedValue property. However, to me this all looks a bit arse about face.

Project_ID suggests an identity value/Primary key. That would normally be the DataValue field, whereas the more easily understood (by humans/users) Title would appear as the DataText field. Also, you would only usually insert the Project_ID value into another table as the Foreign Key. Title would be retrieved in future Select operations through the use of a Join clause in your query.

As it is, you cannot retrieve the DataText field value directly from a dropdownlist under any circumstances. You have to query the database for the DataText value that belongs to the DataValue value.

|||

If this application is rolling out to production I strongly suggest you delegate your T-SQL to sprocs defined at the db layer - inline T-SQL statements in the SqlDataSource is not good for code seperation.

|||

gbarnett:

If this application is rolling out to production I strongly suggest you delegate your T-SQL to sprocs defined at the db layer - inline T-SQL statements in the SqlDataSource is not good for code seperation.

Huh? If Sat was bothered about code separation, he/she wouldn't be using SqlDataSources. And I see nothing in the OP to suggest that code separation is an issue here.