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.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]
@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),
@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 @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.