添加一行到数据库，而不是多行？ [英] Add one row to database instead of multiple rows?

问题描述

所以目前我的code动态地添加从一个Visual Studio中继数据到数据库中。下面我的code运作良好，除了一个主要问题。在我的foreach循环，它会为每个转发器的文本框的新行。相反，我希望它共创1行。

例如，我的中继器可以有名字和姓氏为列。在我的中继器，它可以收集这些数据，如约翰的名字和史密斯的姓氏。它应该进入约翰·史密斯到表中的一行，但它实际上增加了约翰为第1行和史密斯为第2行。

我该如何解决这个问题？

下面是我的code动态地将数据到数据库：

 保护无效BtnSubmit_Click（对象发件人，EventArgs的发送）
{
    使用（SqlConnection的sqlConn =新的SqlConnection（ConfigurationManager.ConnectionStrings [Events2]。的ConnectionString））
    {
        sqlConn.Open（）;        使用（的SqlCommand SQLCMD =新的SqlCommand（））
        {            的foreach（的RepeaterItem rpItem在RepeaterForm.Items）
            {                标签lblDisplayName = rpItem.FindControl（lblDisplayName）作为标签;
                标签lblColumnName = rpItem.FindControl（lblColumnName）作为标签;
                文本框txtColumnValue = rpItem.FindControl（txtColumnValue）的文本框;                如果（txtColumnValue！= NULL）
                {
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText =spInsFormRegistrant;
                    sqlCmd.Parameters.Clear（）;                    sqlCmd.Parameters.Add（@ EVENTID，SqlDbType.Int）.value的= EVENTID;
                    sqlCmd.Parameters.Add（@ FormId，SqlDbType.Int）.value的= formId;
                    sqlCmd.Parameters.Add（@的ColumnName，SqlDbType.NVarChar）.value的= lblColumnName.Text;
                    sqlCmd.Parameters.Add（@ ColumnValue，SqlDbType.NVarChar）.value的= txtColumnValue.Text;                    sqlCmd.ExecuteNonQuery（）;
                }
            }
        }
    }
    PnlNone.Visible = FALSE;
    PnlExist.Visible = FALSE;
    PnlSuccess.Visible =真;
    PnlFail.Visible = FALSE;
}
 

存储过程code：

  ALTER PROCEDURE [DBO]。[spInsFormRegistrant]
     - 添加参数的存储过程在这里
     @EventId INT，
     @FormId INT，
     @ColumnName VARCHAR（100）
     @ColumnValue VARCHAR（100）
如
开始
     -  SET NOCOUNT ON加入到prevent额外的结果集，从
     - 与SELECT语句的干扰。
    SET NOCOUNT ON;  - 在此插入过程语句
声明@Query为nvarchar（4000）
声明@ParmDefinition为nvarchar（500）;设置@Query ='插入到注册（dateCreated会，EVENTID，FormId，'+
             （@ColumnName）+'）VALUES（CURRENT_TIMESTAMP，@EventId，@FormId，@ColumnValue）
设置@ParmDefinition = N'@ ColumnValue为varchar（100），@EventID INT，@FormID INT'
EXEC sp_executesql的@Query，@ParmDefinition，
                   @ColumnValue = @ColumnValue，
                   @EventID = @EventID，
                   @FormID = @FormID结束
 

修改

  ALTER PROCEDURE [DBO]。[spInsFormRegistrant]
     - 添加参数的存储过程在这里
     @EventId INT，
     @FormId INT，
     @ColumnName VARCHAR（100）
     @ColumnValue VARCHAR（100）
如开始
     -  SET NOCOUNT ON加入到prevent额外的结果集，从
     - 与SELECT语句的干扰。
    SET NOCOUNT ON;    声明@searchID INT  - 在此插入过程语句如果@searchID为null
 开始声明@Query为nvarchar（4000）
声明@ParmDefinition为nvarchar（500）    设置@Query ='插入到注册（dateCreated会，EVENTID，FormId，'+
             （@ColumnName）+'）VALUES（CURRENT_TIMESTAMP，@EventId，@FormId，@ColumnValue）
                设置@searchID = SCOPE_IDENTITY（）'    设置@ParmDefinition = N'@ ColumnValue为varchar（100），@EventID INT，INT @FormID，@searchID INT输出
    EXEC sp_executesql的@Query，@ParmDefinition，
                   @ColumnValue = @ColumnValue，
                   @EventID = @EventID，
                   @FormID = @FormID，
                   @searchID = @searchID输出;
结束
如果@searchID IS NOT NULL
开始
声明@ QUERY2为nvarchar（4000）
声明@ ParmDefinition2为nvarchar（500）    设置@ QUERY2 ='更新注册SET dateCreated会= CURRENT_TIMESTAMP，EVENTID = @EventId，FormId = @FormId，'+ QUOTENAME（@ColumnName）+'= @ColumnValue WHERE RegistrantId = @searchID
    设置@ ParmDefinition2 = N'@ ColumnValue为varchar（100），@EventID INT，INT @FormID，@searchID INT'
    Exec的sp_executesql的@ QUERY2，@ ParmDefinition2，
                   @ColumnValue = @ColumnValue，
                   @EventID = @EventID，
                   @FormID = @FormID，
                   @searchID = @searchID
结束
 结束
 

解决方案

这是因为你正在执行在转发每件商品的存储过程。

So currently my code dynamically adds data from a Visual Studio Repeater into a database. My code below works well except for one main issue. During my foreach loop, it creates a new row for each Repeater textbox. Instead, I want it to create 1 row altogether.

For example, my Repeater could have FirstName and LastName as columns. In my Repeater, it could collect this data, such as John for the FirstName and Smith for the LastName. It should enter John Smith into one row of the table, but it actually adds John as row 1 and Smith as row 2.

How can I fix this?

Here is my code that dynamically adds the data to the database:

protected void BtnSubmit_Click(object sender, EventArgs e)
{
    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Events2"].ConnectionString))
    {
        sqlConn.Open();

        using (SqlCommand sqlCmd = new SqlCommand())
        {

            foreach (RepeaterItem rpItem in RepeaterForm.Items)
            {

                Label lblDisplayName = rpItem.FindControl("lblDisplayName") as Label;
                Label lblColumnName = rpItem.FindControl("lblColumnName") as Label;
                TextBox txtColumnValue = rpItem.FindControl("txtColumnValue") as TextBox;

                if (txtColumnValue != null)
                {
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "spInsFormRegistrant";
                    sqlCmd.Parameters.Clear();

                    sqlCmd.Parameters.Add("@EventId", SqlDbType.Int).Value = eventId;
                    sqlCmd.Parameters.Add("@FormId", SqlDbType.Int).Value = formId;
                    sqlCmd.Parameters.Add("@ColumnName", SqlDbType.NVarChar).Value = lblColumnName.Text;
                    sqlCmd.Parameters.Add("@ColumnValue", SqlDbType.NVarChar).Value = txtColumnValue.Text;

                    sqlCmd.ExecuteNonQuery();
                }
            }
        }
    }
    PnlNone.Visible = false;
    PnlExist.Visible = false;
    PnlSuccess.Visible = true;
    PnlFail.Visible = false;
}

Stored procedure code:

ALTER PROCEDURE [dbo].[spInsFormRegistrant]
    -- Add the parameters for the stored procedure here
     @EventId int,
     @FormId int,
     @ColumnName varchar(100),
     @ColumnValue varchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

 -- Insert statements for procedure here
declare @Query nvarchar(4000)
declare @ParmDefinition nvarchar(500);

set @Query = 'INSERT into Registrant(DateCreated,EventId,FormId,'+ 
             (@ColumnName) +') values (CURRENT_TIMESTAMP, @EventId, @FormId, @ColumnValue)'
set @ParmDefinition = N'@ColumnValue varchar(100), @EventID int, @FormID int'
exec sp_executesql @Query, @ParmDefinition, 
                   @ColumnValue = @ColumnValue,
                   @EventID = @EventID,
                   @FormID = @FormID

END

EDIT

ALTER PROCEDURE [dbo].[spInsFormRegistrant]
    -- Add the parameters for the stored procedure here
     @EventId int,
     @FormId int,
     @ColumnName varchar(100),
     @ColumnValue varchar(100)
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @searchID int

 -- Insert statements for procedure here

IF  @searchID IS null
 BEGIN

declare @Query nvarchar(4000)
declare @ParmDefinition nvarchar(500)

    set @Query = 'INSERT into Registrant(DateCreated,EventId,FormId,'+ 
             (@ColumnName) +') values (CURRENT_TIMESTAMP, @EventId, @FormId, @ColumnValue)
                set @searchID = SCOPE_IDENTITY()'

    set @ParmDefinition = N'@ColumnValue varchar(100), @EventID int, @FormID int, @searchID INT OUTPUT'
    exec sp_executesql @Query, @ParmDefinition, 
                   @ColumnValue = @ColumnValue,
                   @EventID = @EventID,
                   @FormID = @FormID,
                   @searchID = @searchID OUTPUT;
END
IF  @searchID IS NOT null
BEGIN
declare @Query2 nvarchar(4000)
declare @ParmDefinition2 nvarchar(500)

    set @Query2 = 'UPDATE Registrant SET DateCreated = CURRENT_TIMESTAMP, EventId = @EventId, FormId = @FormId, ' +quotename(@ColumnName)+ ' =  @ColumnValue WHERE RegistrantId = @searchID'
    set @ParmDefinition2 = N'@ColumnValue varchar(100), @EventID int, @FormID int, @searchID int'
    exec sp_executesql @Query2, @ParmDefinition2, 
                   @ColumnValue = @ColumnValue,
                   @EventID = @EventID,
                   @FormID = @FormID,
                   @searchID = @searchID
END
 END

解决方案

That is because you are executing your stored procedure for each item in the repeater.

这篇关于添加一行到数据库，而不是多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！