如何为列创建一个多值? [英] How can I create a multi value for a column?

问题描述

我有两个表TBLCustomers和TBLGroupCustomers.我想在第三个表中插入多值

I have two tables TBLCustomers and TBLGroupCustomers. I want to insert multi value in a third table that

CREATE TABLE [dbo].[TBLG_Groups] 
(
    [GrId]   INT           IDENTITY (1, 1) NOT NULL,
    [CEmail] VARCHAR (250) NOT NULL,
    [GName]  NVARCHAR (70) NOT NULL,
    [CName]  NVARCHAR (450) NULL, 
    PRIMARY KEY CLUSTERED ([GrId] ASC, [CEmail] ASC, [GName] ASC)
)

CEmail，TBLCustomers中的CName和TBLGroupCustomers

为此，我创建了一个存储过程:

To do this, I created a stored procedure:

CREATE PROC dbo.spG_Groups
    @CEmail VARCHAR(250),
    @GName NVARCHAR (70),
    @CName NVARCHAR(450)
AS
BEGIN
    INSERT INTO TBLG_Groups(CEmail, GName, CName)
    VALUES (@CEmail, @GName, @CName)
END

因为我想显示客户加入组的名称，所以我希望表的每个记录都有一个cname和gname，

Because I want show the name of customers join group I want each record of table have a single cname and gname,

C#代码

SqlCommand cmd1 = new SqlCommand("spG_Groups", conn);
cmd1.CommandType = CommandType.StoredProcedure;

List<String> YrStrList1 = new List<string>(); 

foreach (ListItem li in chGp.Items)
{
    if (li.Selected)
    {
        YrStrList1.Add(li.Value);

        cmd1.Parameters.Add(new SqlParameter("@CName", txtCName.Value));
        cmd1.Parameters.Add(new SqlParameter("@CEmail", txtemail.Value));
        cmd1.Parameters.Add(new SqlParameter("@GName", YrStrList1.ToString()));

        cmd1.ExecuteNonQuery();
    }
}

这不行我该怎么办?

推荐答案

在foreach循环之前，您需要一次定义参数一次，然后进行设置它们在每次迭代中的值-像这样:

You need to define the parameters once, before the foreach loop, and then just set their values on each iteration - something like this:

using (SqlCommand cmd1 = new SqlCommand("spG_Groups", conn))
{
     cmd1.CommandType = CommandType.StoredProcedure;

     // define the parameters *ONCE*
     cmd1.Parameters.Add("@CName", SqlDbType.NVarChar, 450);
     cmd1.Parameters.Add("@CEmail", SqlDbType.VarChar, 250);
     cmd1.Parameters.Add("@GName", SqlDbType.NVarChar, 70);

     List<String> YrStrList1 = new List<string>(); 

     foreach (ListItem li in chGp.Items)
     {
         if (li.Selected)
         {
              YrStrList1.Add(li.Value);

              // just set the *VALUES* of the parameters
              cmd1.Parameters["@CName"].Value = txtCName.Value;
              cmd1.Parameters["@CEmail"].Value = txtemail.Value;
              cmd1.Parameters["@GName"].Value = YrStrList1.ToString();

              cmd1.ExecuteNonQuery();
         }
     }
}

这篇关于如何为列创建一个多值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！