Problem executin SP via ASP.NET RSS

• • Reply
  

  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
  		
  
  GO
  
  

  
  
  
  

  • Edited by Veschich on Jul 23, 2012 11:48 AM
  • Edited by Veschich on Jul 23, 2012 11:49 AM
• • Reply
  

  Usha82

  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.

• • Reply
  

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

  
  
  

  • Edited by Veschich on Jul 23, 2012 01:00 PM
• • Reply
  

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

   

  • Edited by Usha82 on Jul 24, 2012 03:30 AM
  • Marked as answer by decker dong - msft on Jul 29, 2012 03:36 AM
• • Reply
  

  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.

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Problem executin SP via ASP.NET

  Jul 25, 2012 01:40 AM|LINK

  Veschich

  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……