列表框数据库插入. [英] List box database insert.

问题描述

尝试将列表框中的选定项目插入SQL Server数据库.

aspx.net代码:
文本框是:

公司名称
标题
联系
地址
城市
状态
邮政编码
电话
传真电话
手机
电子邮件地址



我的清单方块:

类别(列表框)

存放程序:

Trying to insert selected items in a list box to SQL server database.

aspx.net code:
text boxes are:

company name
title
contact
address
city
state
zip code
telephone
fax phone
cell phone
email address



My list box:

category (list box)

Store procedure:

-- Creates a new record in the [dbo].[CustomerContact] table.
ALTER PROCEDURE [dbo].[CustomerContactAdd]
    @p_CompanyName nvarchar(50),
    @p_Title nvarchar(50),
    @p_Contact nvarchar(50),
    @p_Address nvarchar(50),
    @p_City nvarchar(50),
    @p_State nvarchar(50),
    @p_ZipCode numeric(18,0),
    @p_Telephone nvarchar(50),
    @p_FaxPhone nvarchar(50),
    @p_CellPhone nvarchar(50),
    @p_EmailAddress nvarchar(50),
    @p_Category nvarchar(50),
    @p_CustomerID_out int output
AS

BEGIN TRANSACTION
IF @p_CustomerID_out = 0

BEGIN
    INSERT
    INTO [dbo].[CustomerContact]
        (
            [CompanyName],
            [Title],
            [Contact],
            [Address],
            [City],
            [State],
            [ZipCode],
            [Telephone],
            [FaxPhone],
            [CellPhone],
            [EmailAddress],
            [Category]
        )
    VALUES
        (
             @p_CompanyName,
             @p_Title,
             @p_Contact,
             @p_Address,
             @p_City,
             @p_State,
             @p_ZipCode,
             @p_Telephone,
             @p_FaxPhone,
             @p_CellPhone,
             @p_EmailAddress,
             @p_Category
        )

    SET @p_CustomerID_out = SCOPE_IDENTITY()

END

C#代码:

C# code:

public void Save_Click_Base(object sender, EventArgs args)
        {
            string query = "CustomerContactAdd";
            int recMode = 0;
            int recID = 0;
            SqlConnection con = new SqlConnection(GetConnectionString());
            con.Open();
           SqlCommand cmd = new SqlCommand(query, con);
            cmd.CommandType = CommandType.StoredProcedure;

             cmd.Parameters.Add(new SqlParameter("@p_CustomerID", SqlDbType.Int));
                if(recMode == 0)
                {
                   // New record
                    cmd.Parameters["@p_CustomerID"].Value = recID + 1;
                }
                else
               {
                    //Editing a record
                    cmd.Parameters["@p_CustomerID"].Value = recID;
                }
                    
            cmd.Parameters.Add("@p_CompanyName", SqlDbType.NVarChar).Value = CompanyName.Text;
            cmd.Parameters.Add("@p_CustomerID", SqlDbType.Int);
            cmd.Parameters["@p_CustomerID"].Value = 0;
            cmd.Parameters["@p_CustomerID"].Direction = ParameterDirection.Output;             
            cmd.Parameters.Add("@p_Title", SqlDbType.NVarChar).Value = Title.Text;
            cmd.Parameters.Add("@p_Contact", SqlDbType.NVarChar).Value = ContactName.Text;
            cmd.Parameters.Add("@p_Address", SqlDbType.NVarChar).Value = Address.Text;
            cmd.Parameters.Add("@p_City", SqlDbType.NVarChar).Value = City.Text;
            cmd.Parameters.Add("@p_State", SqlDbType.NVarChar).Value = State.Text;
            cmd.Parameters.Add("@p_ZipCode", SqlDbType.Decimal).Value = ZipCode.Text;
            cmd.Parameters.Add("@p_Telephone", SqlDbType.NVarChar).Value = Telephone.Text;
            cmd.Parameters.Add("@p_FaxPhone", SqlDbType.NVarChar).Value = FaxPhone.Text;
            cmd.Parameters.Add("@p_CellPhone", SqlDbType.NVarChar).Value = CellPhone.Text;
            cmd.Parameters.Add("@p_EmailAddress", SqlDbType.NVarChar).Value = EmailAddress.Text;
            foreach (ListItem item in Category.Items)
            {
                if (item.Selected && item.Value != "0")
                {
                    cmd.Parameters.Add("@p_Category", SqlDbType.NVarChar).Value = Category.Text;
                    cmd.ExecuteNonQuery();
                }
            }


        }

        private string GetConnectionString()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        }

收到错误消息

过程或函数"CustomerContactAdd"必须指定许多参数.

当我循环并转到cmd.ExecuteNonQuery

[修改:只是在pre标记中为您的sql代码添加了sql语言参数，以便对其进行更好的颜色编码]

Getting error message

Procedure or function ''CustomerContactAdd'' has to many arguments specified.

when I go to loop and get to the cmd.ExecuteNonQuery

[Modified: just added the sql language parameter in the pre tag for your sql code to color-code it better]

推荐答案

嗯，那么您可能指定了太多参数，因此您应该返回到代码，并仔细查看要添加的内容.

首先，添加一个名为@p_CustomerID的参数(顺便说一下，它根本没有在存储过程中定义.您有一个定义为@p_CustomerID_out的参数，但这并不相同).

然后，添加@p_CompanyName.然后，您添加一个新参数，并将其称为第一个参数.因此，您现在在对象中具有三个参数:

@p_CustomerID，@p_CompanyName，@p_CustomerID


然后，如果我们转到代码的底部，则为Category.Items中的每个项目添加了一个名为@p_Category的新参数.因此，如果有4个项目，那么您将有4个名为@p_Category
的参数
我不确定如何检查，但是您所拥有的参数不能超过存储过程中定义的参数.

最好首先创建所有参数(一次).然后，分别设置它们.这样，您将确保没有太多参数.

Well, then you probably have specified too many parameters so you should go back to your code and look at exactly what you''re adding.

You start out by adding a parameter called @p_CustomerID (which, by the way, isn''t defined in your stored procedure at all. You have a parameter defined as @p_CustomerID_out, but that''s not the same).

Then, you add @p_CompanyName. Then, you add a new parameter and call it the same as the first parameter. So, you now have three parameters in your object:

@p_CustomerID, @p_CompanyName, @p_CustomerID


Then, if we go to the bottom of the code, for each item in Category.Items, you add a new parameter called @p_Category. So, if there were 4 items, then you would have 4 parameters called @p_Category

I''m not sure how it checks, but you can''t have more parameters than are defined in your stored procedure.

You would be better to initially create all of your parameters (once). And, then set them individually. That way, you will ensure you don''t have too many parameters.

作为一个仅供参考，您在
上也会遇到问题

As an FYI, you also have a problem at

cmd.Parameters.AddWithValue("@p_CompanyName", SqlDbType.NVarChar).Value = CompanyName.Text; 

您不想使用AddWithValue您不会在@p_CompanyName

You didn''t want to use AddWithValue You won''t be getting what you expect into @p_CompanyName

这篇关于列表框数据库插入.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！