Hi. I have store procedure that insert books and want to execute it and get the 'RETURN' value( understanding if it was successfully executed), but i got the following error:
"Object reference not set to an instance of an object" - for this line:
USE [LIBRARY]
GO
/****** Object: StoredProcedure [dbo].[sp_insert_book] Script Date: 07/23/2012 14:17:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_insert_book]
(
@BookTitle varchar(80),
@AdditionalName varchar(50) = NULL,
@Language varchar(50),
@Genre varchar(50),
@Category varchar(50),
@ReleaseDate date,
@Publisher varchar(50),
@City varchar(50),
@NumberOfCopies int,
@InStock int,
@FirstName1 varchar(80) = NULL, @LastName1 varchar(80) = NULL,
@FirstName2 varchar(80) = NULL, @LastName2 varchar(80) = NULL,
@FirstName3 varchar(80) = NULL, @LastName3 varchar(80) = NULL,
@FirstName4 varchar(80) = NULL, @LastName4 varchar(80) = NULL,
@FirstName5 varchar(80) = NULL, @LastName5 varchar(80) = NULL
)
AS
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS ( SELECT title
FROM tbl_book
WHERE title = @BookTitle )
BEGIN
INSERT INTO tbl_book_publishing (publisher, city)
VALUES (@Publisher, @City)
DECLARE @publishing_id int
SET @publishing_id = ( SELECT IDENT_CURRENT('tbl_book_publishing') )
--SELECT * FROM tbl_book_publishing
DECLARE @category_id int
SET @category_id = (SELECT book_category_id
FROM tbl_book_category
WHERE category = @Category)
DECLARE @genre_id int
SET @genre_id = (SELECT book_genre_id
FROM tbl_book_genre
WHERE genre = @Genre)
DECLARE @book_language_id int
SELECT @book_language_id = book_language_id
FROM tbl_book_language
WHERE [language] = @Language
DECLARE @author_name_id1 int
SET @author_name_id1 = ( SELECT IDENT_CURRENT('tbl_book_author_name') )
DECLARE @book_author_name_id int
SET @book_author_name_id = @@IDENTITY
INSERT INTO tbl_book (title, additional_name, release_date, number_of_copies, in_stock,
library_id, book_language_id, book_publishing_id,
book_category_id, book_genre_id)
VALUES (@BookTitle, @AdditionalName, @ReleaseDate, @NumberOfCopies,
@InStock, 1, @book_language_id, @publishing_id, @category_id, @genre_id )
--SELECT * FROM tbl_book
DECLARE @book_id int
SET @book_id = @@IDENTITY
CREATE TABLE #BookAN
(
ID int identity(1,1) ,
FN varchar(80) NULL,
LN varchar(80) NULL
)
INSERT INTO #BookAN (FN, LN)
SELECT @FirstName2, @LastName2
UNION
SELECT @FirstName3, @LastName3
UNION
SELECT @FirstName4, @LastName4
UNION
SELECT @FirstName5, @LastName5
UNION
SELECT @FirstName1, @LastName1
SELECT *
INTO #BookAN_NOTNULL
FROM #BookAN
WHERE (FN IS NOT NULL) OR (LN IS NOT NULL)
DECLARE @ID int = -1
SELECT TOP(1) @ID = ID FROM #BookAN_NOTNULL
DECLARE @author_name_id int
WHILE ( @ID > 0 )
BEGIN
INSERT INTO tbl_book_author_name (first_name, last_name)
SELECT FN, LN
FROM #BookAN_NOTNULL
WHERE ID = @ID
SET @author_name_id = ( SELECT IDENT_CURRENT('tbl_book_author_name') )
--SELECT * FROM tbl_book_author_name
INSERT INTO tbl_book_author_name_mm (book_id, book_author_name_id)
SELECT @book_id, @author_name_id
DELETE FROM #BookAN_NOTNULL
WHERE ID = @ID
SET @ID = -1
SELECT TOP(1) @ID = ID FROM #BookAN_NOTNULL
--SELECT * FROM tbl_book_author_name_mm
END
COMMIT TRANSACTION
RETURN 1
END
ELSE
BEGIN
PRINT 'This book already exists in the database!'
ROLLBACK TRANSACTION
RETURN -2
END
--SELECT * FROM tbl_book_publishing
--SELECT * FROM tbl_book
--SELECT * FROM tbl_book_author_name
--SELECT * FROM tbl_book_author_name_mm
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RETURN -1
--SELECT ERROR_MESSAGE()
END CATCH
GO
Ok. I fixed my code like this and it' working, i have only one issue - how to get the value that RETURN command in my procedure returns, so i can know what is the the problem if such exist?
You can add an output parameter to your storesd procedure and get its value in c#.
Add '@retrunVal int output' as a new parameter in stored procedure and in c# code, add this new parameter to command object by setting its direction as output. There are some overloaded methods of
SqlComm.Parameters.Add. You can use that for setting direction of parameter. And you can access the result as follows
Veschich
Member
29 Points
279 Posts
Problem executin SP via ASP.NET
Jul 23, 2012 11:46 AM|LINK
Hi. I have store procedure that insert books and want to execute it and get the 'RETURN' value( understanding if it was successfully executed), but i got the following error:
"Object reference not set to an instance of an object" - for this line:
SqlDA.InsertCommand.CommandType = CommandType.StoredProcedure;
Here is my aspx.cs and sp_insert_book (sorry for the long code):
EDIT: the store procedure is working fine because i've tested in my db.
protected void Button_ADD_AddB_Click(object sender, EventArgs e) { SqlConnection SqlConn = new SqlConnection("Data Source = VESOORG; Initial Catalog = LIBRARY; Integrated Security = True "); SqlDataAdapter SqlDA = new SqlDataAdapter("sp_insert_book", SqlConn); try { SqlDA.InsertCommand.CommandType = CommandType.StoredProcedure; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@BookTitle", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@BookTitle"].Value = TextBox_Title_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@AdditionalName", SqlDbType.VarChar, 50)); SqlDA.InsertCommand.Parameters["@AdditionalName"].Value = TextBox_AddT_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@Language", SqlDbType.VarChar, 50)); SqlDA.InsertCommand.Parameters["@Language"].Value = DropDownList_Language_AddB.SelectedValue; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@Genre", SqlDbType.VarChar, 50)); SqlDA.InsertCommand.Parameters["@Genre"].Value = DropDownList_Genre_AddB.SelectedValue; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@Category", SqlDbType.VarChar, 50)); SqlDA.InsertCommand.Parameters["@Category"].Value = DropDownList_Category_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@ReleaseDate", SqlDbType.Date)); SqlDA.InsertCommand.Parameters["@ReleaseDate"].Value = TextBox_ReleaseDate_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50)); SqlDA.InsertCommand.Parameters["@City"].Value = TextBox_CityPub_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@NumberOfCopies", SqlDbType.Int)); SqlDA.InsertCommand.Parameters["@NumberOfCopies"].Value = TextBox_NumCopies_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@InStock", SqlDbType.Int)); SqlDA.InsertCommand.Parameters["@InStock"].Value = TextBox_InStock_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@FirstName1", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@FirstName1"].Value = TextBox_FirstName1_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@LastName1", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@LastName1"].Value = TextBox_LastName1_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@FirstName2", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@FirstName2"].Value = TextBox_FirstName2_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@LastName2", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@LastName2"].Value = TextBox_LastName2_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@FirstName3", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@FirstName3"].Value = TextBox_FirstName3_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@LastName3", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@LastName3"].Value = TextBox_LastName3_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@FirstName4", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@FirstName4"].Value = TextBox_FirstName4_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@LastName4", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@LastName4"].Value = TextBox_LastName4_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@FirstName5", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@FirstName5"].Value = TextBox_FirstName5_AddB.Text; SqlDA.InsertCommand.Parameters.Add(new SqlParameter("@LastName5", SqlDbType.VarChar, 80)); SqlDA.InsertCommand.Parameters["@LastName5"].Value = TextBox_LastName5_AddB.Text; SqlDA.InsertCommand.Parameters["@ReturnNumber"].Direction = ParameterDirection.Output; DataSet DS = new DataSet(); SqlDA.Fill(DS); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { SqlDA.Dispose(); SqlConn.Close(); } }USE [LIBRARY] GO /****** Object: StoredProcedure [dbo].[sp_insert_book] Script Date: 07/23/2012 14:17:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[sp_insert_book] ( @BookTitle varchar(80), @AdditionalName varchar(50) = NULL, @Language varchar(50), @Genre varchar(50), @Category varchar(50), @ReleaseDate date, @Publisher varchar(50), @City varchar(50), @NumberOfCopies int, @InStock int, @FirstName1 varchar(80) = NULL, @LastName1 varchar(80) = NULL, @FirstName2 varchar(80) = NULL, @LastName2 varchar(80) = NULL, @FirstName3 varchar(80) = NULL, @LastName3 varchar(80) = NULL, @FirstName4 varchar(80) = NULL, @LastName4 varchar(80) = NULL, @FirstName5 varchar(80) = NULL, @LastName5 varchar(80) = NULL ) AS BEGIN TRY BEGIN TRANSACTION IF NOT EXISTS ( SELECT title FROM tbl_book WHERE title = @BookTitle ) BEGIN INSERT INTO tbl_book_publishing (publisher, city) VALUES (@Publisher, @City) DECLARE @publishing_id int SET @publishing_id = ( SELECT IDENT_CURRENT('tbl_book_publishing') ) --SELECT * FROM tbl_book_publishing DECLARE @category_id int SET @category_id = (SELECT book_category_id FROM tbl_book_category WHERE category = @Category) DECLARE @genre_id int SET @genre_id = (SELECT book_genre_id FROM tbl_book_genre WHERE genre = @Genre) DECLARE @book_language_id int SELECT @book_language_id = book_language_id FROM tbl_book_language WHERE [language] = @Language DECLARE @author_name_id1 int SET @author_name_id1 = ( SELECT IDENT_CURRENT('tbl_book_author_name') ) DECLARE @book_author_name_id int SET @book_author_name_id = @@IDENTITY INSERT INTO tbl_book (title, additional_name, release_date, number_of_copies, in_stock, library_id, book_language_id, book_publishing_id, book_category_id, book_genre_id) VALUES (@BookTitle, @AdditionalName, @ReleaseDate, @NumberOfCopies, @InStock, 1, @book_language_id, @publishing_id, @category_id, @genre_id ) --SELECT * FROM tbl_book DECLARE @book_id int SET @book_id = @@IDENTITY CREATE TABLE #BookAN ( ID int identity(1,1) , FN varchar(80) NULL, LN varchar(80) NULL ) INSERT INTO #BookAN (FN, LN) SELECT @FirstName2, @LastName2 UNION SELECT @FirstName3, @LastName3 UNION SELECT @FirstName4, @LastName4 UNION SELECT @FirstName5, @LastName5 UNION SELECT @FirstName1, @LastName1 SELECT * INTO #BookAN_NOTNULL FROM #BookAN WHERE (FN IS NOT NULL) OR (LN IS NOT NULL) DECLARE @ID int = -1 SELECT TOP(1) @ID = ID FROM #BookAN_NOTNULL DECLARE @author_name_id int WHILE ( @ID > 0 ) BEGIN INSERT INTO tbl_book_author_name (first_name, last_name) SELECT FN, LN FROM #BookAN_NOTNULL WHERE ID = @ID SET @author_name_id = ( SELECT IDENT_CURRENT('tbl_book_author_name') ) --SELECT * FROM tbl_book_author_name INSERT INTO tbl_book_author_name_mm (book_id, book_author_name_id) SELECT @book_id, @author_name_id DELETE FROM #BookAN_NOTNULL WHERE ID = @ID SET @ID = -1 SELECT TOP(1) @ID = ID FROM #BookAN_NOTNULL --SELECT * FROM tbl_book_author_name_mm END COMMIT TRANSACTION RETURN 1 END ELSE BEGIN PRINT 'This book already exists in the database!' ROLLBACK TRANSACTION RETURN -2 END --SELECT * FROM tbl_book_publishing --SELECT * FROM tbl_book --SELECT * FROM tbl_book_author_name --SELECT * FROM tbl_book_author_name_mm END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION RETURN -1 --SELECT ERROR_MESSAGE() END CATCH GOUsha82
Member
710 Points
129 Posts
Re: Problem executin SP via ASP.NET
Jul 23, 2012 11:51 AM|LINK
Could you please verify whether SqlDA.InsertCommand is null? I think you need to set the stored procedure name somewhere.
Veschich
Member
29 Points
279 Posts
Re: Problem executin SP via ASP.NET
Jul 23, 2012 12:07 PM|LINK
Ok. I fixed my code like this and it' working, i have only one issue - how to get the value that RETURN command in my procedure returns, so i can know what is the the problem if such exist?
protected void Button_ADD_AddB_Click(object sender, EventArgs e) { SqlConnection SqlConn = new SqlConnection("Data Source = VESOORG; Initial Catalog = LIBRARY; Integrated Security = True "); try { SqlCommand SqlComm = new SqlCommand("sp_insert_book", SqlConn); SqlComm.CommandType = CommandType.StoredProcedure; SqlComm.Parameters.Add("@BookTitle", SqlDbType.VarChar, 80).Value = TextBox_Title_AddB.Text; SqlComm.Parameters.Add("@AdditionalName", SqlDbType.VarChar, 50).Value = TextBox_AddT_AddB.Text; SqlComm.Parameters.Add("@Language", SqlDbType.VarChar, 50).Value = DropDownList_Language_AddB.SelectedValue; SqlComm.Parameters.Add("@Genre", SqlDbType.VarChar, 50).Value = DropDownList_Genre_AddB.SelectedValue; SqlComm.Parameters.Add("@Category", SqlDbType.VarChar, 50).Value = DropDownList_Category_AddB.SelectedValue; SqlComm.Parameters.Add("@ReleaseDate", SqlDbType.VarChar, 10).Value = TextBox_ReleaseDate_AddB.Text; SqlComm.Parameters.Add("@Publisher", SqlDbType.VarChar, 50).Value = TextBox_Publisher_AddB.Text; SqlComm.Parameters.Add("@City", SqlDbType.VarChar, 50).Value = TextBox_CityPub_AddB.Text; SqlComm.Parameters.Add("@NumberOfCopies", SqlDbType.Int).Value = TextBox_NumCopies_AddB.Text; SqlComm.Parameters.Add("@InStock", SqlDbType.Int).Value = TextBox_InStock_AddB.Text; SqlComm.Parameters.Add("@FirstName1", SqlDbType.VarChar, 80).Value = TextBox_FirstName1_AddB.Text; SqlComm.Parameters.Add("@LastName1", SqlDbType.VarChar, 80).Value = TextBox_LastName1_AddB.Text; SqlComm.Parameters.Add("@FirstName2", SqlDbType.VarChar, 80).Value = TextBox_FirstName2_AddB.Text; SqlComm.Parameters.Add("@LastName2", SqlDbType.VarChar, 80).Value = TextBox_LastName2_AddB.Text; SqlComm.Parameters.Add("@FirstName3", SqlDbType.VarChar, 80).Value = TextBox_FirstName3_AddB.Text; SqlComm.Parameters.Add("@LastName3", SqlDbType.VarChar, 80).Value = TextBox_LastName3_AddB.Text; SqlComm.Parameters.Add("@FirstName4", SqlDbType.VarChar, 80).Value = TextBox_FirstName4_AddB.Text; SqlComm.Parameters.Add("@LastName4", SqlDbType.VarChar, 80).Value = TextBox_LastName4_AddB.Text; SqlComm.Parameters.Add("@FirstName5", SqlDbType.VarChar, 80).Value = TextBox_FirstName5_AddB.Text; SqlComm.Parameters.Add("@LastName5", SqlDbType.VarChar, 80).Value = TextBox_LastName5_AddB.Text; SqlConn.Open(); SqlComm.ExecuteNonQuery(); SqlConn.Close(); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { SqlConn.Close(); }Usha82
Member
710 Points
129 Posts
Re: Problem executin SP via ASP.NET
Jul 24, 2012 03:30 AM|LINK
You can add an output parameter to your storesd procedure and get its value in c#.
Add '@retrunVal int output' as a new parameter in stored procedure and in c# code, add this new parameter to command object by setting its direction as output. There are some overloaded methods of SqlComm.Parameters.Add. You can use that for setting direction of parameter. And you can access the result as follows
SqlComm.Parameters["@retrunVal"].Value.ToString()
Veschich
Member
29 Points
279 Posts
Re: Problem executin SP via ASP.NET
Jul 24, 2012 08:12 AM|LINK
10x all for your replies. In the end i decided to use RAISERROR to return a message . 10x again.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Problem executin SP via ASP.NET
Jul 25, 2012 01:40 AM|LINK
congratulation that your problem is solved!And welcome to here again……