How do I insert data from windows form into SQL database?

See more:

SQL

C#3.5

I am trying to insert data into the database using stored procedure with C#.

Stored procedure is working fine at db level.

SqlConnection con = new SqlConnection("Data Source=XXXXXXXXXXX;Initial Catalog=XXXXXXXX;Trusted_Connection=true");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_ADD";
            //cmd.Parameters.Add("@sfn", SqlDbType.VarChar, 13).Value = txtsfn.Text.Trim();
            //cmd.Parameters.Add("@lrn", SqlDbType.VarChar, 13).Value = txtlrn.Text.Trim();
            cmd.Parameters.AddWithValue("@sfn", txtsfn.Text.ToString());
            cmd.Parameters.AddWithValue("@lrn", txtlrn.Text.ToString());
            cmd.Parameters.AddWithValue("@first_person_lname", txtfpln.Text.ToString());
            cmd.Parameters.AddWithValue("@first_person_fname", txtfpfn.Text.ToString());
            cmd.Parameters.AddWithValue("@second_person_lname", txtspln.Text.ToString());
            cmd.Parameters.AddWithValue("@second_person_fname", txtspfn.Text.ToString());
            cmd.Parameters.AddWithValue("@dom", dateTimePicker1.Value);
            cmd.Parameters.AddWithValue("@EVENT_TYPE", cmbcat.Text.ToString());
            cmd.Parameters.AddWithValue("@PLACE_OF_EVENT", cmbevntplace.Text.ToString());
            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

Stored Procedure:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER  PROCEDURE [dbo].[SP_ADD]
 -- +=====================================================================
 -- |  Title:   [DBO].[SP_CAMR_DOCUMENT_INDEX]
 -- |
 -- |
 -- |
 -- | Description:
 -- | The purpose to this stored procedure is insert information into the
 -- | camr_document_amendment
 -- +=====================================================================	
    @SFN						VARCHAR(13),
    @LRN						VARCHAR(13),
    @FIRST_PERSON_LNAME			VARCHAR(33),
    @FIRST_PERSON_FNAME			VARCHAR(25),
    @SECOND_PERSON_LNAME		VARCHAR(33),
    @SECOND_PERSON_FNAME		VARCHAR(25),
    @DOM						DATETIME,
    @EVENT_TYPE					CHAR(1),
    @PLACE_OF_EVENT				CHAR(3)
  
    
AS

 DECLARE
 	@DOC_SEQ_NUM				INT,
	@MARRIAGE_BC				VARCHAR(18),
	@AMENDMENT_IND				CHAR(1),
	@REG_DT                     DATETIME,
	@FIRST_PERSON_MNAME			VARCHAR(18),
    @FIRST_PERSON_DOB			DATETIME,
	@SECOND_PERSON_MNAME		VARCHAR(18),
    @SECOND_PERSON_DOB			DATETIME,
	@DOL						DATETIME,
	@LST_MOD_USERID				VARCHAR(10),
	@ERR_CODE              		INT,
	@ERR_MSG               		VARCHAR(256),
   @TABLE_NAME         		VARCHAR(50),    		
	--@CERT_IMAGE_ID				IMAGE,
	@TRANNAME				VARCHAR(50)
	
 	
  SET @SFN = LTRIM(RTRIM(ISNULL(@SFN, ' ')))
  SET @LRN = LTRIM(RTRIM(ISNULL(@LRN, ' ')))
  SET @FIRST_PERSON_LNAME = LTRIM(RTRIM(ISNULL(@FIRST_PERSON_LNAME, ' ')))
  SET @FIRST_PERSON_FNAME = LTRIM(RTRIM(ISNULL(@FIRST_PERSON_FNAME, ' ')))
  SET @SECOND_PERSON_LNAME = LTRIM(RTRIM(ISNULL(@SECOND_PERSON_LNAME, ' ')))
  SET @SECOND_PERSON_FNAME = LTRIM(RTRIM(ISNULL(@SECOND_PERSON_FNAME, ' ')))
  SET @DOM = LTRIM(RTRIM(ISNULL(@DOM, ' ')))
  SET @EVENT_TYPE = LTRIM(RTRIM(ISNULL(@EVENT_TYPE, ' ')))
  SET @PLACE_OF_EVENT = LTRIM(RTRIM(ISNULL(@PLACE_OF_EVENT, ' ')))

  SET @ERR_CODE = 0
  SET @ERR_MSG  = ''
  SET @TABLE_NAME = 'DBO.CAMR_DOCUMENT_AMENDMENT'
  SET @DOC_SEQ_NUM = 1
  SET @MARRIAGE_BC = ''
  SET @AMENDMENT_IND ='Original'
  SET @REG_DT = GETDATE()       
  SET @LST_MOD_USERID = 'xxxxx'
  SET @FIRST_PERSON_MNAME = ''
  SET @FIRST_PERSON_DOB = ''
  SET @SECOND_PERSON_MNAME = ''
  SET @SECOND_PERSON_DOB = ''
  SET @DOL = GETDATE()
  SET @REG_DT =''
  --SET @CERT_IMAGE_ID = IMAGE
  SET @TRANNAME 	= 'INSERT DBO.CAMR_DOCUMENT_AMENDMENT'

  SET NOCOUNT ON

  SELECT @DOC_SEQ_NUM FROM DBO.CAMR_DOCUMENT_AMENDMENT
  WHERE SFN = @SFN

  BEGIN TRANSACTION @TRANNAME  
  IF EXISTS (SELECT * FROM CAMR_DOCUMENT_INDEX WHERE SFN != @SFN)
	BEGIN
  		IF EXISTS (SELECT * FROM DBO.CAMR_DOCUMENT_AMENDMENT WHERE SFN != @SFN)
			BEGIN
				
				INSERT INTO DBO.CAMR_DOCUMENT_AMENDMENT(
					SFN,
					DOC_SEQ_NUM, 
					LRN, 
					FIRST_PERSON_LNAME, 
					FIRST_PERSON_FNAME, 
					FIRST_PERSON_MNAME, 
					FIRST_PERSON_DOB, 
					SECOND_PERSON_LNAME, 
					SECOND_PERSON_FNAME, 
					SECOND_PERSON_MNAME, 
					SECOND_PERSON_DOB, 
					DOM, 
					DOL,
					AMENDMENT_IND,
					MARRIAGE_BC,
					EVENT_TYPE,
					PLACE_OF_EVENT,
					REG_DT,
					LST_MOD_USERID,
					LST_MOD_DT 
					)


				VALUES (@SFN, 
					@DOC_SEQ_NUM, 
					@LRN, 
					@FIRST_PERSON_LNAME, 
					@FIRST_PERSON_FNAME, 
				    @FIRST_PERSON_MNAME, 
					@FIRST_PERSON_DOB, 
					@SECOND_PERSON_LNAME, 
					@SECOND_PERSON_FNAME, 
					@SECOND_PERSON_MNAME, 
					@SECOND_PERSON_DOB, 
					@DOM,
					@DOL,
					@AMENDMENT_IND,
					@MARRIAGE_BC,
					@EVENT_TYPE,
					@PLACE_OF_EVENT,
					@REG_DT,
					@LST_MOD_USERID,
				 	GETDATE() 
				 )
			END

   	SET  @ERR_CODE = @@ERROR
   		IF @ERR_CODE <> 0
   			BEGIN                                     
   				SET @TABLE_NAME = 'DBO.CAMR_DOCUMENT_AMENDMENT'               
   				SET @ERR_MSG = CAST(@ERR_CODE AS CHAR(50))
   				ROLLBACK TRANSACTION @TRANNAME
   				RETURN                                                           
   			END 
			
	END
   ELSE
   	BEGIN    
		SET  @ERR_CODE = 5350                                 
   		SET @TABLE_NAME = 'DBO.CAMR_DOCUMENT_AMENDMENT'               
   		SET @ERR_MSG = 'NO ORIGINAL RECORD TO DO AMEND!...'
   		RETURN                                                           
   	END 
      COMMIT TRANSACTION @TranName 
   SET NOCOUNT OFF 
 
   		RETURN                                                           

Record gets save as per application but when I check database table record exists but all the fields from the application appears blank. I need help with parameter add part.

Thanks.