473,321 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Stored Procedure using SQLDataAdapter , DropDownList

39
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(object sender, EventArgs e)
{

if (!Page.IsPostBack)
{

SqlConnection con = new SqlConnection("user id=sa;password=cast;database=Hello_Dr;server=AUROR A-SERVER;");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Pro_Specialty";
cmd.Connection = con;

SqlParameter SPID = new SqlParameter("@SPID ", SqlDbType.NVarChar, 12);
SPID.Direction = ParameterDirection.Output;
Specialty_DropDownList.Text = SPID.Value.ToString();

SqlParameter Specialty = new SqlParameter("@Specialty ", SqlDbType.NVarChar, 50);
Specialty.Direction = ParameterDirection.Output;
Specialty_DropDownList.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_DropDownList.DataValueField = "SPID";

Specialty_DropDownList.DataTextField = "Specialty";

Specialty_DropDownList.DataSource = ds;

Specialty_DropDownList.DataBind();
con.Close();


}
}

I have created the stored procedure in sql server 2005 as

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




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

@SPID nvarchar(12)output,
@Specialty nvarchar(50)output


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


-- Insert statements for procedure here
select @SPID=SPID,@Specialty=Specialty from Specialty

END

But if I run, I get the error

Object reference not set to an instance of the object.
Jun 28 '08 #1
2 3246
kenobewan
4,871 Expert 4TB
Your select statement is a likely culprit here, try:
Expand|Select|Wrap|Line Numbers
  1. insert into Specialty (SPID,Specialty)
  2. 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.
Jun 28 '08 #2
suganya
39
Your select statement is a likely culprit here, try:
Expand|Select|Wrap|Line Numbers
  1. insert into Specialty (SPID,Specialty)
  2. 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_IDENTIFIER ON
go





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

@SPID nvarchar(12)output,
@Specialty nvarchar(50)output


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


-- Insert statements for procedure here
-- select @SPID=SPID,@Specialty=Specialty from Specialty
insert into Specialty (SPID,Specialty)
values(@SPID,@Specialty)

END




In form I have given the coding as

protected void Page_Load(object sender, EventArgs e)
{

if (!Page.IsPostBack)
{

SqlConnection con = new SqlConnection("user id=sa;password=cast;database=Hello_Dr;server=AUROR A-SERVER;");
con.Open();
SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Pro_Specialty";
cmd.Connection = con;

SqlParameter SPID = new SqlParameter("@SPID ", SqlDbType.NVarChar, 12);
SPID.Direction = ParameterDirection.Output;
Specialty_DropDownList.Text = SPID.Value.ToString();

SqlParameter Specialty = new SqlParameter("@Specialty ", SqlDbType.NVarChar, 50);
Specialty.Direction = ParameterDirection.Output;
Specialty_DropDownList.Text = Specialty.Value.ToString();

cmd.Parameters.Add(SPID);
cmd.Parameters.Add(Specialty);
cmd.ExecuteNonQuery();


SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Specialty_DropDownList.DataValueField = "@SPID";

Specialty_DropDownList.DataTextField = "@Specialty";

Specialty_DropDownList.DataSource = ds;

Specialty_DropDownList.DataBind();
con.Close();



}

But still I m getting the error as

Object reference not set to an instance of an object.
Jun 30 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Mullin Yu | last post by:
i have a stored procedure at sql server 2k. which will update records and select result from temp table. if i use SqlConnection class, and i do both. but, if i use OleDbConnection class, i can...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
0
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how...
2
by: Mike Hutton | last post by:
I have a rather odd problem. I have a SP which uses temp. tables along the way, and then returns a table of results: CREATE PROCEDURE dbo.usp_myproc( @pNameList VARCHAR(6000) ) AS
9
by: Nikolay Petrov | last post by:
How to fill DataSet from stored procedure?
2
by: Roy | last post by:
Hey all, Here's a small VB codeblock that connects to a database and uses 2 SQL queries then forms a relation for a master/detail view on the aspx side: Private Sub Binddata(ByVal name As...
5
by: ric_deez | last post by:
Hi there, I would like to create a simple search form to allow users to search for a job number based on a number of parameters. I think I understand how to use parameteres associated with Stored...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
1
by: sweatha | last post by:
Hi I am quite new in writing stored procedure. I have created a stored procedure for binding the data from DB. I have to bind data from Specialty table with 2 feilds. For that my stored procedure...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.