如何执行此存储过程并插入语句 [英] How do I do this store procedure and insert statement

问题描述

//存储过程

//Stored Procedure

ALTER PROCEDURE AddNewUserProc
   @NricNo VarChar(9),
   @donorNric VarChar(9),
   @donorName VarChar(30),
   @donorAddress VarChar(50),
   @donorPostalCode VarChar(6),
   @donorContactNo VarChar(8),
   @donorType VarChar(50),
   @donorOrganizationName VarChar(50)
AS
 
IF NOT EXISTS (SELECT NricNo FROM Donor WHERE NricNo = @NricNo)
BEGIN
INSERT INTO Donor(NricNo, Name, Address, PostalCode, ContactNo, Type,OrganizationName) VALUES(@NricNo,@donorNric,@donorName , @donorAddress, @donorPostalCode, @donorContactNo, @donorType, @donorOrganizationName)
END

//Insert statement
public int DonorInsert()
        {
            SqlConnection conn = new SqlConnection(_connStr);
            SqlCommand cmd = new SqlCommand("AddNewUserProc", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@NricNo", _donorNric);
            cmd.Parameters.AddWithValue("@donorName", _donorName);
            cmd.Parameters.AddWithValue("@donorAddress", _donorAddress);
            cmd.Parameters.AddWithValue("@donorPostalCode", _donorPostalCode);
            cmd.Parameters.AddWithValue("@donorContactNo", _donorContactNo);
            cmd.Parameters.AddWithValue("@donorType", _donorType);
            cmd.Parameters.AddWithValue("@donorOrganizationName", _donorOrganizationName);
            conn.Open();
            int nofRow = 0;
            nofRow = cmd.ExecuteNonQuery();
            conn.Close();
            return nofRow;
        }

我的插入语句是否检查如果数据库中存在NricNo，则需要能够创建相同的NricNo再次？

Does my insert statement check that if NricNo exist in the database, would need be able to create the same NricNo again?

推荐答案

检查这个



参数和列在insert语句中不匹配，添加一个所需的列名称为 @ donarNric

Check this

Parameter and columns are not matching in the insert statement , add a desired column name to @donarNric

ALTER PROCEDURE AddNewUserProc
(
   @NricNo VarChar(9),
   @donorNric VarChar(9),
   @donorName VarChar(30),
   @donorAddress VarChar(50),
   @donorPostalCode VarChar(6),
   @donorContactNo VarChar(8),
   @donorType VarChar(50),
   @donorOrganizationName VarChar(50)
   )
AS
begin
 
IF NOT EXISTS (SELECT NricNo FROM Donor WHERE NricNo = @NricNo)
BEGIN
INSERT INTO Donor(NricNo,Nric, Name, Address, PostalCode, ContactNo, Type,OrganizationName) VALUES(@NricNo,@donorNric,@donorName , @donorAddress, @donorPostalCode, @donorContactNo, @donorType, @donorOrganizationName)
END

end

和c＃

and in c#

public int DonorInsert()
     {
         SqlConnection conn = new SqlConnection(_connStr);
         SqlCommand cmd = new SqlCommand("AddNewUserProc", conn);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("@NricNo", _donorNric);  // assign valid value to the parameter
         cmd.Parameters.AddWithValue("@donorNric", _donorNric);  // this line is missing
         cmd.Parameters.AddWithValue("@donorName", _donorName);
         cmd.Parameters.AddWithValue("@donorAddress", _donorAddress);
         cmd.Parameters.AddWithValue("@donorPostalCode", _donorPostalCode);
         cmd.Parameters.AddWithValue("@donorContactNo", _donorContactNo);
         cmd.Parameters.AddWithValue("@donorType", _donorType);
         cmd.Parameters.AddWithValue("@donorOrganizationName", _donorOrganizationName);
         conn.Open();
         int nofRow = 0;
         nofRow = cmd.ExecuteNonQuery();
         conn.Close();
         return nofRow;
     }

这篇关于如何执行此存储过程并插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！