如何将数据从Windows窗体插入SQL数据库？ [英] How do I insert data from windows form into SQL database?
本文介绍了如何将数据从Windows窗体插入SQL数据库？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试使用C＃存储过程将数据插入数据库。
存储过程在数据库级别正常工作。
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.
推荐答案
你似乎在使用错误存储过程的名称：
在c＃代码中，您使用名称SP_ADD
You seem the be using the wrong name of the stored procedure:
In the c# code you use the nameSP_ADD
cmd.CommandText = "SP_ADD";
在SQL代码中，它被称为 SP_CAMR_ADD_AMENDMENT
In the SQL code it is called SP_CAMR_ADD_AMENDMENT
ALTER PROCEDURE [dbo].[SP_CAMR_ADD_AMENDMENT]
这篇关于如何将数据从Windows窗体插入SQL数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
