locked
Problem executin SP via ASP.NET RRS feed

  • Question

  • User1863994004 posted

    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
    		
    
    GO
    
    




    Monday, July 23, 2012 7:46 AM

Answers

  • User1553110397 posted

    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()

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 23, 2012 11:30 PM

All replies

  • User1553110397 posted

    Could you please verify whether SqlDA.InsertCommand is null? I think you need to set the stored procedure name somewhere.

    Monday, July 23, 2012 7:51 AM
  • User1863994004 posted

    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();
                }



    Monday, July 23, 2012 8:07 AM
  • User1553110397 posted

    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()

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 23, 2012 11:30 PM
  • User1863994004 posted

    10x all for your replies. In the end i decided to use RAISERROR to return a message . 10x again.

    Tuesday, July 24, 2012 4:12 AM
  • User3866881 posted

    10x all for your replies. In the end i decided to use RAISERROR to return a message . 10x again.

    congratulation that your problem is solved!And welcome to here again……

    Tuesday, July 24, 2012 9:40 PM