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:
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# 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)
{
cmd.Parameters["@p_CustomerID"].Value = recID + 1;
}
else
{
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;
}
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]