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