CODEWITHSUNDEEP
c#sqlsql-server
Anon
Anon

Reputation: 141

SqlException error when I insert into database

This is basically a method to insert a record into a table. It was working fine before I decided to add in a way to check if the Customer ID already exists in the database. I get a

'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Procedure or function InsertCustomer has too many arguments specified.

on the line

command.ExecuteNonQuery();

I don't understand what's wrong.

public void add()
{
    lblMessage.Text = "";
    command.Connection = conn;
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "CheckDetails";
    command.Parameters.AddWithValue("@CustID", txtCID.Text);
    conn.Open();
    int check = (int)command.ExecuteScalar();

    if (check == 0)
    {
        command.CommandText = "InsertCustomer";
        command.Parameters.Add("@CustID", SqlDbType.Int).Value = txtCID.Text;
        command.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFName.Text;
        command.Parameters.Add("@Surname", SqlDbType.VarChar).Value = txtLName.Text;
        command.Parameters.Add("@Gender", SqlDbType.VarChar).Value = rdoGender.Text;
        command.Parameters.Add("@Age", SqlDbType.Int).Value = txtAge.Text;
        command.Parameters.Add("@Address1", SqlDbType.VarChar).Value = txtAdd1.Text;
        command.Parameters.Add("@Address2", SqlDbType.VarChar).Value = txtAdd2.Text;
        command.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text;
        command.Parameters.Add("@Phone", SqlDbType.VarChar).Value = txtPhone.Text;
        command.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = txtMobile.Text;
        command.Parameters.Add("@Email", SqlDbType.VarChar).Value = txtEmail.Text;

        command.ExecuteNonQuery();

        lblMessage.Text = "Customer Details Added.";
    }
    else
    {
        lblMessage.Text = "Customer ID already exists.";
    }

    conn.Close();
}

Upvotes: 0

Views: 116

Answers (2)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Remove below parameter from your statement, you already add parameter in command:

command.Parameters.Add("@CustID", SqlDbType.Int).Value = txtCID.Text;

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

You are adding the same parameter twice:

command.Parameters.AddWithValue("@CustID", txtCID.Text);
// ....
command.Parameters.Add("@CustID", SqlDbType.Int).Value = txtCID.Text;

You can use command.Parameters.Clear();. But i'd prefer to use two different SqlCommands for the two procedures CheckDetails and InsertCustomer to avoid such issues.

Side-note: don't let the database try-cast the value for you. Use int.TryParse.

Upvotes: 3

Related Questions