从两个不同的文本框ASP.NET C＃将两个数据插入同一列

I try to insert two data into same column from two different text box. For example, one person that has two phone numbers. In this case the person and phone number are two separate table and phone number table is only have one column to store the phone number. And I try to insert the data using stored procedure. This is the stored procedure code.

我尝试从两个不同的文本框中将两个数据插入到同一列中。例如,一个人有两个电话号码。在这种情况下,人和电话号码是两个单独的表,电话号码表只有一列存储电话号码。我尝试使用存储过程插入数据。这是存储过程代码。

ALTER Procedure [dbo].[Ph]
@name varchar(200),
@phonenumber varchar(200)

AS
Begin

Declare @P_ID int 

insert into person(Name)
values (@name)

SELECT P_ID = SCOPE_IDENTITY()

insert into phonenumber(PhoneNumber, P_ID)
values (@phonenumber, @P_ID)

RETURN
END

However, when I try to insert the data, an error message appear that quote "Procedure or function Ph has too many arguments specified.". This is the C# code for inserting the data.

但是,当我尝试插入数据时,会出现一条错误消息,引用“过程或函数Ph指定的参数太多”。这是用于插入数据的C#代码。

using (SqlConnection con = new SqlConnection("Data Source=YAMADA;Initial Catalog=testtest;Integrated Security=True"))
        {
            using (SqlCommand cmd = new SqlCommand("Ph", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text;
                cmd.Parameters.Add("@phonenumber", SqlDbType.Int).Value = TextBox2.Text;
                cmd.Parameters.Add("@phonenumber", SqlDbType.Int).Value = TextBox3.Text;

                con.Open();
                cmd.ExecuteNonQuery();
            }
        }

Does somebody can help me to fix this error? Thanks in advance.

有人可以帮我解决这个错误吗?提前致谢。

4 个解决方案

#1

Alter you procedure to accept 2 phone numbers, then insert them one by one:

修改程序接受2个电话号码,然后逐个插入:

ALTER Procedure [dbo].[Ph]
    @name varchar(200),
    @phonenumber varchar(200),
    @phonenumber2 varchar(200)
AS
Begin

    Declare @P_ID int 

    insert into person(Name)
    values (@name)

    SELECT @P_ID = SCOPE_IDENTITY()

    insert into phonenumber(PhoneNumber, P_ID)
    values (@phonenumber, @P_ID)

    insert into phonenumber(PhoneNumber, P_ID)
    values (@phonenumber2, @P_ID)
END

Then add both phone numbers when you call the procedure:

然后在调用该过程时添加两个电话号码:

using (SqlConnection con = new SqlConnection("Data Source=YAMADA;Initial Catalog=testtest;Integrated Security=True"))
{
    using (SqlCommand cmd = new SqlCommand("Ph", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text;
        cmd.Parameters.Add("@phonenumber", SqlDbType.VarChar).Value = TextBox2.Text;
        cmd.Parameters.Add("@phonenumber2", SqlDbType.VarChar).Value = TextBox3.Text;

        con.Open();
        cmd.ExecuteNonQuery();
    }
}

#2

The procedure accepts only two but you have added three, remove the last parameter. One more thing you have to note is that the second parameter should be of type varchar , but you were specified them as integer. Please change them as well. Finally the code will be

该过程只接受两个,但您已添加三个,删除最后一个参数。还有一件事需要注意,第二个参数应该是varchar类型,但是你将它们指定为整数。请改变它们。最后代码将是

using (SqlCommand cmd = new SqlCommand("Ph", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text;
            cmd.Parameters.Add("@phonenumber", SqlDbType.VarChar).Value = TextBox2.Text;

            con.Open();
            cmd.ExecuteNonQuery();
        }

#3

First: You can only add each parameter once to the command.

第一:您只能在命令中添加一次参数。

As a solution you have three choices:

作为解决方案,您有三种选择:

1. The clean approch with a second datatable in the DB. E.g.: userid, phoneid, phonenumber The userid ist the foreign key to your usertable. The phoneid is combined with the userid the primary key of your new table. And last but not least the phonenumber is one of the numbers of your User. Like this you can add as many phonenumber as you like to.

   干净的approch,DB中有第二个数据表。例如:userid,phoneid,phonenumber userid是你的usertable的外键。 phoneid与userid结合使用新表的主键。最后但并非最不重要的是,phonenumber是您的用户号码之一。像这样你可以添加任意数量的phonenumber。

2. A dirty approch in my eyes: Change the column type of you phonenumber to nvarchar and add the phonenumbers as comma seperated values.

   我眼中的脏问题:将您的电话号码的列类型更改为nvarchar,并将电话号码添加为逗号分隔值。

3. Add more phonenumber columns. EG. Phonenumber1, Phonenumber2... But this approch is also not a desireable one as Theresa is still a problem when you want to add more numbers...

   添加更多的phonenumber列。例如。 Phonenumber1,Phonenumber2 ......但是这个approch也不是一个值得的,因为当你想要添加更多数字时,Theresa仍然是一个问题......

Last but not least: int is not a good choice as columntype. What so you want to do with numbers like [phone].

最后但并非最不重要:int不是columntype的好选择。你想用+49 177 77777777做什么。

#4

Try this:

using (SqlConnection con = new SqlConnection("Data Source=YAMADA;Initial Catalog=testtest;Integrated Security=True"))
{
    using (SqlCommand cmd = new SqlCommand("Ph", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text;
        cmd.Parameters.Add("@phonenumber", SqlDbType.Int).Value = TextBox2.Text + TextBox3.Text;

        con.Open();
        cmd.ExecuteNonQuery();
    }
}