Stored Procedure using SQLDataAdapter , DropDownList

[suganya]

Hi

I have to bind the DropDownList box with ID field hidden using SQLDataAdapter.

For that I have given the coding as

protected void Page_Load(objec t sender, EventArgs e)
{

if (!Page.IsPostBa ck)
{

SqlConnection con = new SqlConnection(" user id=sa;password= cast;database=H ello_Dr;server= AURORA-SERVER;");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Sto redProcedure;
cmd.CommandText = "Pro_Specialty" ;
cmd.Connection = con;

SqlParameter SPID = new SqlParameter("@ SPID ", SqlDbType.NVarC har, 12);
SPID.Direction = ParameterDirect ion.Output;
Specialty_DropD ownList.Text = SPID.Value.ToSt ring();

SqlParameter Specialty = new SqlParameter("@ Specialty ", SqlDbType.NVarC har, 50);
Specialty.Direc tion = ParameterDirect ion.Output;
Specialty_DropD ownList.Text = Specialty.Value .ToString();
cmd.Parameters. Add(SPID);
cmd.Parameters. Add(Specialty);
SqlDataAdapter da = new SqlDataAdapter( cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Specialty_DropD ownList.DataVal ueField = "SPID";

Specialty_DropD ownList.DataTex tField = "Specialty" ;

Specialty_DropD ownList.DataSou rce = ds;

Specialty_DropD ownList.DataBin d();
con.Close();


}
}

I have created the stored procedure in sql server 2005 as

set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go




-- =============== =============== ===============
-- Author: <Author,,Name >
-- Create date: <Create Date,,>
-- Description: <Description, ,>
-- =============== =============== ===============
ALTER PROCEDURE Pro_Specialty

@SPID nvarchar(12)out put,
@Specialty nvarchar(50)out put


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


-- Insert statements for procedure here
select @SPID=SPID,@Spe cialty=Specialt y from Specialty

END

But if I run, I get the error

Object reference not set to an instance of the object.

[kenobewan]

Your select statement is a likely culprit here, try:

insert into Specialty (SPID,Specialty)
values(@SPID,@Specialty)

Why is the field name and table name the same? Means that you are still likely to get an error? You may also run into probs later inserting the id this way.

You may need further resources to learn about sql :hint:, try stickies, tutorials etc.

[suganya]

Your select statement is a likely culprit here, try:

insert into Specialty (SPID,Specialty)
values(@SPID,@Specialty)

Why is the field name and table name the same? Means that you are still likely to get an error? You may also run into probs later inserting the id this way.

You may need further resources to learn about sql :hint:, try stickies, tutorials etc.

Hi Kenobewan

I have changed the select statement into the statement that U have given above as

set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go





-- =============== =============== ===============
-- Author: <Author,,Name >
-- Create date: <Create Date,,>
-- Description: <Description, ,>
-- =============== =============== ===============
ALTER PROCEDURE [dbo].[Pro_Specialty]

@SPID nvarchar(12)out put,
@Specialty nvarchar(50)out put


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.


-- Insert statements for procedure here
-- select @SPID=SPID,@Spe cialty=Specialt y from Specialty
insert into Specialty (SPID,Specialty )
values(@SPID,@S pecialty)

END




In form I have given the coding as

protected void Page_Load(objec t sender, EventArgs e)
{

if (!Page.IsPostBa ck)
{

SqlConnection con = new SqlConnection(" user id=sa;password= cast;database=H ello_Dr;server= AURORA-SERVER;");
con.Open();
SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Sto redProcedure;
cmd.CommandText = "Pro_Specialty" ;
cmd.Connection = con;

SqlParameter SPID = new SqlParameter("@ SPID ", SqlDbType.NVarC har, 12);
SPID.Direction = ParameterDirect ion.Output;
Specialty_DropD ownList.Text = SPID.Value.ToSt ring();

SqlParameter Specialty = new SqlParameter("@ Specialty ", SqlDbType.NVarC har, 50);
Specialty.Direc tion = ParameterDirect ion.Output;
Specialty_DropD ownList.Text = Specialty.Value .ToString();

cmd.Parameters. Add(SPID);
cmd.Parameters. Add(Specialty);
cmd.ExecuteNonQ uery();


SqlDataAdapter da = new SqlDataAdapter( cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Specialty_DropD ownList.DataVal ueField = "@SPID";

Specialty_DropD ownList.DataTex tField = "@Specialty ";

Specialty_DropD ownList.DataSou rce = ds;

Specialty_DropD ownList.DataBin d();
con.Close();



}

But still I m getting the error as

Object reference not set to an instance of an object.