Interesting Stored Procedure Problem..

Discussion in 'ASP .Net' started by Bilbo, Nov 20, 2003.

  1. Bilbo

    Bilbo Guest

    I have a a headscratcher here:
    I have a form that when submitted should do 2 things when a user enters
    data and then clicks the Add button.
    Here goes:

    1. Call a stored procedure called AddCompany to insert the company name
    from the Company Name textbox into the COMPANY table and return the
    @@IDENTITY of the company name just input into the database back to a
    label on the form. THIS IS WORKING.

    2. Call another stored procedure called AddContact and input the
    remainder of the data from the form fields including the @@IDENTITY
    number I returned to the label into the CONTACT TABLE. This does NOT
    work. I am getting the following message: "Procedure or function
    AddContact has too many arguments specified"

    If anyone knows how to do this, PLEASE HELP!!
    TIA,
    Bilbo

    Below is the code for the button_click event that I have written so far:

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnAdd.Click
    If Page.IsValid Then
    If Page.IsValid Then
    Dim cnn As SqlConnection = New SqlConnection( _
    "Data Source=MyMachine;Initial
    Catalog=MyDataBase;UID=bilbo;PWD=baggins")
    Dim cmdInsert As SqlCommand = cnn.CreateCommand()
    cmdInsert.CommandType = CommandType.StoredProcedure
    cmdInsert.CommandText = "AddCompany"
    'Add the proper parameters for this stored procedure
    cmdInsert.Parameters.Add( _
    "@CompanyName", SqlDbType.VarChar, 50)
    cmdInsert.Parameters("@CompanyName").Value = _
    txtCompany.Text
    'Add the output parameter and set its direction
    cmdInsert.Parameters.Add(New SqlParameter( _
    "@CompanyID", SqlDbType.Int))
    cmdInsert.Parameters("@CompanyID").Direction = _
    ParameterDirection.Output
    cnn.Open()
    cmdInsert.ExecuteNonQuery()
    cnn.Close()
    lblCompanyID.Text = cmdInsert.Parameters( _
    "@CompanyID").Value

    '<----------The code works from to this point but errors out if I add in
    'the below code for second stored procedure---------------------------->

    Dim cmdInsert2 As SqlCommand = cnn.CreateCommand()
    cmdInsert2.CommandType = CommandType.StoredProcedure
    cmdInsert2.CommandText = "AddContact"
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@LastName", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@LastName").Value =
    txtLastName.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@PreName", SqlDbType.Char, 10))
    cmdInsert2.Parameters("@PreName").Value =
    ddlPre.SelectedItem.Value
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@FirstName", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@FirstName").Value =
    txtFirstName.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@MiddleName", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@MiddleName").Value =
    txtMiddleName.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Suffix", SqlDbType.Char, 10))
    cmdInsert2.Parameters("@Suffix").Value = txtSuffix.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@CompanyID", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@CompanyID").Value =
    lblCompanyID.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Addy1", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@JobTitle", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@JobTitle").Value =
    txtJobTitle.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Addy1", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Addy1").Value = txtBusAdd1.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Addy2", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Addy2").Value = txtBusAdd2.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Addy3", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Addy3").Value = txtBusAdd3.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@POBox", SqlDbType.VarChar, 20))
    cmdInsert2.Parameters("@POBox").Value = txtPOBox.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@City", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@City").Value = txtCity.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@State", SqlDbType.Char, 16))
    cmdInsert2.Parameters("@State").Value = txtState.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Zip", SqlDbType.Char, 10))
    cmdInsert2.Parameters("@Zip").Value = txtZipCode.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Country", SqlDbType.VarChar, 30))
    cmdInsert2.Parameters("@Country").Value = txtCountry.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@WorkPhone", SqlDbType.VarChar, 16))
    cmdInsert2.Parameters("@WorkPhone").Value =
    txtBusPhone.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@MobilePhone", SqlDbType.VarChar, 16))
    cmdInsert2.Parameters("@MobilePhone").Value =
    txtMobilePhone.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@MainPhone", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@MainPhone").Value =
    txtMainPhone.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@FaxNumber", SqlDbType.VarChar, 16))
    cmdInsert2.Parameters("@FaxNumber").Value = txtFax.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Email", SqlDbType.VarChar, 30))
    cmdInsert2.Parameters("@Email").Value =
    txtEmailAddress.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Dept", SqlDbType.Char, 30))
    cmdInsert2.Parameters("@Dept").Value = txtDepartment.Text
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Cat1", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Cat1").Value =
    ddlCategory1.SelectedItem.Value
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Cat2", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Cat2").Value =
    ddlCategory2.SelectedItem.Value
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Cat3", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Cat3").Value =
    ddlCategory3.SelectedItem.Value
    cmdInsert2.Parameters.Add(New SqlParameter( _
    "@Cat4", SqlDbType.VarChar, 50))
    cmdInsert2.Parameters("@Cat4").Value =
    ddlCategory4.SelectedItem.Value
    cnn.Open()
    cmdInsert2.ExecuteNonQuery()
    cnn.Close()
    End If
    End If
    End Sub
    Bilbo, Nov 20, 2003
    #1
    1. Advertising

  2. Bilbo,

    I would compare your stored procedure's input parameter list with the
    parameters you have defined for your sqlcommand. The error is reporting that
    you have at least one more parameter defined in your sqlcommand as your
    stored procedure. This means that a parameter is either missing from your
    stored procedure or you've defined an extra parameter in your sql command.

    --
    Sincerely,

    S. Justin Gengo, MCP
    Web Developer / Programmer

    Free code library at:
    www.aboutfortunate.com

    "Out of chaos comes order."
    Nietzche


    "Bilbo" <> wrote in message
    news:Xns9439A30B1FF55tmvbillyahoocomnospa@207.46.248.16...
    > I have a a headscratcher here:
    > I have a form that when submitted should do 2 things when a user enters
    > data and then clicks the Add button.
    > Here goes:
    >
    > 1. Call a stored procedure called AddCompany to insert the company name
    > from the Company Name textbox into the COMPANY table and return the
    > @@IDENTITY of the company name just input into the database back to a
    > label on the form. THIS IS WORKING.
    >
    > 2. Call another stored procedure called AddContact and input the
    > remainder of the data from the form fields including the @@IDENTITY
    > number I returned to the label into the CONTACT TABLE. This does NOT
    > work. I am getting the following message: "Procedure or function
    > AddContact has too many arguments specified"
    >
    > If anyone knows how to do this, PLEASE HELP!!
    > TIA,
    > Bilbo
    >
    > Below is the code for the button_click event that I have written so far:
    >
    > Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles btnAdd.Click
    > If Page.IsValid Then
    > If Page.IsValid Then
    > Dim cnn As SqlConnection = New SqlConnection( _
    > "Data Source=MyMachine;Initial
    > Catalog=MyDataBase;UID=bilbo;PWD=baggins")
    > Dim cmdInsert As SqlCommand = cnn.CreateCommand()
    > cmdInsert.CommandType = CommandType.StoredProcedure
    > cmdInsert.CommandText = "AddCompany"
    > 'Add the proper parameters for this stored procedure
    > cmdInsert.Parameters.Add( _
    > "@CompanyName", SqlDbType.VarChar, 50)
    > cmdInsert.Parameters("@CompanyName").Value = _
    > txtCompany.Text
    > 'Add the output parameter and set its direction
    > cmdInsert.Parameters.Add(New SqlParameter( _
    > "@CompanyID", SqlDbType.Int))
    > cmdInsert.Parameters("@CompanyID").Direction = _
    > ParameterDirection.Output
    > cnn.Open()
    > cmdInsert.ExecuteNonQuery()
    > cnn.Close()
    > lblCompanyID.Text = cmdInsert.Parameters( _
    > "@CompanyID").Value
    >
    > '<----------The code works from to this point but errors out if I add in
    > 'the below code for second stored procedure---------------------------->
    >
    > Dim cmdInsert2 As SqlCommand = cnn.CreateCommand()
    > cmdInsert2.CommandType = CommandType.StoredProcedure
    > cmdInsert2.CommandText = "AddContact"
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@LastName", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@LastName").Value =
    > txtLastName.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@PreName", SqlDbType.Char, 10))
    > cmdInsert2.Parameters("@PreName").Value =
    > ddlPre.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@FirstName", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@FirstName").Value =
    > txtFirstName.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@MiddleName", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@MiddleName").Value =
    > txtMiddleName.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Suffix", SqlDbType.Char, 10))
    > cmdInsert2.Parameters("@Suffix").Value = txtSuffix.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@CompanyID", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@CompanyID").Value =
    > lblCompanyID.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy1", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@JobTitle", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@JobTitle").Value =
    > txtJobTitle.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy1", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Addy1").Value = txtBusAdd1.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy2", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Addy2").Value = txtBusAdd2.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy3", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Addy3").Value = txtBusAdd3.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@POBox", SqlDbType.VarChar, 20))
    > cmdInsert2.Parameters("@POBox").Value = txtPOBox.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@City", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@City").Value = txtCity.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@State", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@State").Value = txtState.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Zip", SqlDbType.Char, 10))
    > cmdInsert2.Parameters("@Zip").Value = txtZipCode.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Country", SqlDbType.VarChar, 30))
    > cmdInsert2.Parameters("@Country").Value = txtCountry.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@WorkPhone", SqlDbType.VarChar, 16))
    > cmdInsert2.Parameters("@WorkPhone").Value =
    > txtBusPhone.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@MobilePhone", SqlDbType.VarChar, 16))
    > cmdInsert2.Parameters("@MobilePhone").Value =
    > txtMobilePhone.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@MainPhone", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@MainPhone").Value =
    > txtMainPhone.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@FaxNumber", SqlDbType.VarChar, 16))
    > cmdInsert2.Parameters("@FaxNumber").Value = txtFax.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Email", SqlDbType.VarChar, 30))
    > cmdInsert2.Parameters("@Email").Value =
    > txtEmailAddress.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Dept", SqlDbType.Char, 30))
    > cmdInsert2.Parameters("@Dept").Value = txtDepartment.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat1", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat1").Value =
    > ddlCategory1.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat2", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat2").Value =
    > ddlCategory2.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat3", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat3").Value =
    > ddlCategory3.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat4", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat4").Value =
    > ddlCategory4.SelectedItem.Value
    > cnn.Open()
    > cmdInsert2.ExecuteNonQuery()
    > cnn.Close()
    > End If
    > End If
    > End Sub
    S. Justin Gengo, Nov 20, 2003
    #2
    1. Advertising

  3. Bilbo

    Lloyd Sheen Guest

    I am betting that you are reusing the connection and the parameter
    collection already has the first parameter defined. When you add the second
    one the first parameter from the first call is still there. Just a thought
    but from experience.

    "Bilbo" <> wrote in message
    news:Xns9439A30B1FF55tmvbillyahoocomnospa@207.46.248.16...
    > I have a a headscratcher here:
    > I have a form that when submitted should do 2 things when a user enters
    > data and then clicks the Add button.
    > Here goes:
    >
    > 1. Call a stored procedure called AddCompany to insert the company name
    > from the Company Name textbox into the COMPANY table and return the
    > @@IDENTITY of the company name just input into the database back to a
    > label on the form. THIS IS WORKING.
    >
    > 2. Call another stored procedure called AddContact and input the
    > remainder of the data from the form fields including the @@IDENTITY
    > number I returned to the label into the CONTACT TABLE. This does NOT
    > work. I am getting the following message: "Procedure or function
    > AddContact has too many arguments specified"
    >
    > If anyone knows how to do this, PLEASE HELP!!
    > TIA,
    > Bilbo
    >
    > Below is the code for the button_click event that I have written so far:
    >
    > Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
    > System.EventArgs) Handles btnAdd.Click
    > If Page.IsValid Then
    > If Page.IsValid Then
    > Dim cnn As SqlConnection = New SqlConnection( _
    > "Data Source=MyMachine;Initial
    > Catalog=MyDataBase;UID=bilbo;PWD=baggins")
    > Dim cmdInsert As SqlCommand = cnn.CreateCommand()
    > cmdInsert.CommandType = CommandType.StoredProcedure
    > cmdInsert.CommandText = "AddCompany"
    > 'Add the proper parameters for this stored procedure
    > cmdInsert.Parameters.Add( _
    > "@CompanyName", SqlDbType.VarChar, 50)
    > cmdInsert.Parameters("@CompanyName").Value = _
    > txtCompany.Text
    > 'Add the output parameter and set its direction
    > cmdInsert.Parameters.Add(New SqlParameter( _
    > "@CompanyID", SqlDbType.Int))
    > cmdInsert.Parameters("@CompanyID").Direction = _
    > ParameterDirection.Output
    > cnn.Open()
    > cmdInsert.ExecuteNonQuery()
    > cnn.Close()
    > lblCompanyID.Text = cmdInsert.Parameters( _
    > "@CompanyID").Value
    >
    > '<----------The code works from to this point but errors out if I add in
    > 'the below code for second stored procedure---------------------------->
    >
    > Dim cmdInsert2 As SqlCommand = cnn.CreateCommand()
    > cmdInsert2.CommandType = CommandType.StoredProcedure
    > cmdInsert2.CommandText = "AddContact"
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@LastName", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@LastName").Value =
    > txtLastName.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@PreName", SqlDbType.Char, 10))
    > cmdInsert2.Parameters("@PreName").Value =
    > ddlPre.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@FirstName", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@FirstName").Value =
    > txtFirstName.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@MiddleName", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@MiddleName").Value =
    > txtMiddleName.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Suffix", SqlDbType.Char, 10))
    > cmdInsert2.Parameters("@Suffix").Value = txtSuffix.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@CompanyID", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@CompanyID").Value =
    > lblCompanyID.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy1", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@JobTitle", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@JobTitle").Value =
    > txtJobTitle.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy1", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Addy1").Value = txtBusAdd1.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy2", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Addy2").Value = txtBusAdd2.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Addy3", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Addy3").Value = txtBusAdd3.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@POBox", SqlDbType.VarChar, 20))
    > cmdInsert2.Parameters("@POBox").Value = txtPOBox.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@City", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@City").Value = txtCity.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@State", SqlDbType.Char, 16))
    > cmdInsert2.Parameters("@State").Value = txtState.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Zip", SqlDbType.Char, 10))
    > cmdInsert2.Parameters("@Zip").Value = txtZipCode.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Country", SqlDbType.VarChar, 30))
    > cmdInsert2.Parameters("@Country").Value = txtCountry.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@WorkPhone", SqlDbType.VarChar, 16))
    > cmdInsert2.Parameters("@WorkPhone").Value =
    > txtBusPhone.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@MobilePhone", SqlDbType.VarChar, 16))
    > cmdInsert2.Parameters("@MobilePhone").Value =
    > txtMobilePhone.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@MainPhone", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@MainPhone").Value =
    > txtMainPhone.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@FaxNumber", SqlDbType.VarChar, 16))
    > cmdInsert2.Parameters("@FaxNumber").Value = txtFax.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Email", SqlDbType.VarChar, 30))
    > cmdInsert2.Parameters("@Email").Value =
    > txtEmailAddress.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Dept", SqlDbType.Char, 30))
    > cmdInsert2.Parameters("@Dept").Value = txtDepartment.Text
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat1", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat1").Value =
    > ddlCategory1.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat2", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat2").Value =
    > ddlCategory2.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat3", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat3").Value =
    > ddlCategory3.SelectedItem.Value
    > cmdInsert2.Parameters.Add(New SqlParameter( _
    > "@Cat4", SqlDbType.VarChar, 50))
    > cmdInsert2.Parameters("@Cat4").Value =
    > ddlCategory4.SelectedItem.Value
    > cnn.Open()
    > cmdInsert2.ExecuteNonQuery()
    > cnn.Close()
    > End If
    > End If
    > End Sub
    Lloyd Sheen, Nov 20, 2003
    #3
  4. Bilbo

    Bilbo Guest

    It was Gengo's solution to this problem that did the trick...I have been
    looking at this so long its crossing my eyes. I had the @addy1 parameter
    in there more than once. Took it out and it works like a champ!
    Thanks for all of your help!
    :)
    Bilbo


    "Lloyd Sheen" <> wrote in
    news:Sjavb.128506$:

    > I am betting that you are reusing the connection and the parameter
    > collection already has the first parameter defined. When you add the
    > second one the first parameter from the first call is still there.
    > Just a thought but from experience.
    >
    > "Bilbo" <> wrote in message
    > news:Xns9439A30B1FF55tmvbillyahoocomnospa@207.46.248.16...
    >> I have a a headscratcher here:
    >> I have a form that when submitted should do 2 things when a user
    >> enters data and then clicks the Add button.
    >> Here goes:
    >>
    >> 1. Call a stored procedure called AddCompany to insert the company
    >> name from the Company Name textbox into the COMPANY table and return
    >> the @@IDENTITY of the company name just input into the database back
    >> to a label on the form. THIS IS WORKING.
    >>
    >> 2. Call another stored procedure called AddContact and input the
    >> remainder of the data from the form fields including the @@IDENTITY
    >> number I returned to the label into the CONTACT TABLE. This does NOT
    >> work. I am getting the following message: "Procedure or function
    >> AddContact has too many arguments specified"
    >>
    >> If anyone knows how to do this, PLEASE HELP!!
    >> TIA,
    >> Bilbo
    >>
    >> Below is the code for the button_click event that I have written so
    >> far:
    >>
    >> Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
    >> System.EventArgs) Handles btnAdd.Click
    >> If Page.IsValid Then
    >> If Page.IsValid Then
    >> Dim cnn As SqlConnection = New SqlConnection( _
    >> "Data Source=MyMachine;Initial
    >> Catalog=MyDataBase;UID=bilbo;PWD=baggins")
    >> Dim cmdInsert As SqlCommand = cnn.CreateCommand()
    >> cmdInsert.CommandType = CommandType.StoredProcedure
    >> cmdInsert.CommandText = "AddCompany"
    >> 'Add the proper parameters for this stored procedure
    >> cmdInsert.Parameters.Add( _
    >> "@CompanyName", SqlDbType.VarChar, 50)
    >> cmdInsert.Parameters("@CompanyName").Value = _
    >> txtCompany.Text
    >> 'Add the output parameter and set its direction
    >> cmdInsert.Parameters.Add(New SqlParameter( _
    >> "@CompanyID", SqlDbType.Int))
    >> cmdInsert.Parameters("@CompanyID").Direction = _
    >> ParameterDirection.Output
    >> cnn.Open()
    >> cmdInsert.ExecuteNonQuery()
    >> cnn.Close()
    >> lblCompanyID.Text = cmdInsert.Parameters( _
    >> "@CompanyID").Value
    >>
    >> '<----------The code works from to this point but errors out if I add
    >> in 'the below code for second stored
    >> procedure---------------------------->
    >>
    >> Dim cmdInsert2 As SqlCommand = cnn.CreateCommand()
    >> cmdInsert2.CommandType = CommandType.StoredProcedure
    >> cmdInsert2.CommandText = "AddContact"
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@LastName", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@LastName").Value =
    >> txtLastName.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@PreName", SqlDbType.Char, 10))
    >> cmdInsert2.Parameters("@PreName").Value =
    >> ddlPre.SelectedItem.Value
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@FirstName", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@FirstName").Value =
    >> txtFirstName.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@MiddleName", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@MiddleName").Value =
    >> txtMiddleName.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Suffix", SqlDbType.Char, 10))
    >> cmdInsert2.Parameters("@Suffix").Value =
    >> txtSuffix.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@CompanyID", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@CompanyID").Value =
    >> lblCompanyID.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Addy1", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@JobTitle", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@JobTitle").Value =
    >> txtJobTitle.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Addy1", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Addy1").Value =
    >> txtBusAdd1.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@Addy2", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Addy2").Value =
    >> txtBusAdd2.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@Addy3", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Addy3").Value =
    >> txtBusAdd3.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@POBox", SqlDbType.VarChar, 20))
    >> cmdInsert2.Parameters("@POBox").Value = txtPOBox.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@City", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@City").Value = txtCity.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@State", SqlDbType.Char, 16))
    >> cmdInsert2.Parameters("@State").Value = txtState.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Zip", SqlDbType.Char, 10))
    >> cmdInsert2.Parameters("@Zip").Value = txtZipCode.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Country", SqlDbType.VarChar, 30))
    >> cmdInsert2.Parameters("@Country").Value =
    >> txtCountry.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@WorkPhone", SqlDbType.VarChar, 16))
    >> cmdInsert2.Parameters("@WorkPhone").Value =
    >> txtBusPhone.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@MobilePhone", SqlDbType.VarChar, 16))
    >> cmdInsert2.Parameters("@MobilePhone").Value =
    >> txtMobilePhone.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@MainPhone", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@MainPhone").Value =
    >> txtMainPhone.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@FaxNumber", SqlDbType.VarChar, 16))
    >> cmdInsert2.Parameters("@FaxNumber").Value =
    >> txtFax.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@Email", SqlDbType.VarChar, 30))
    >> cmdInsert2.Parameters("@Email").Value =
    >> txtEmailAddress.Text
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Dept", SqlDbType.Char, 30))
    >> cmdInsert2.Parameters("@Dept").Value =
    >> txtDepartment.Text cmdInsert2.Parameters.Add(New
    >> SqlParameter( _ "@Cat1", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Cat1").Value =
    >> ddlCategory1.SelectedItem.Value
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Cat2", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Cat2").Value =
    >> ddlCategory2.SelectedItem.Value
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Cat3", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Cat3").Value =
    >> ddlCategory3.SelectedItem.Value
    >> cmdInsert2.Parameters.Add(New SqlParameter( _
    >> "@Cat4", SqlDbType.VarChar, 50))
    >> cmdInsert2.Parameters("@Cat4").Value =
    >> ddlCategory4.SelectedItem.Value
    >> cnn.Open()
    >> cmdInsert2.ExecuteNonQuery()
    >> cnn.Close()
    >> End If
    >> End If
    >> End Sub

    >
    >
    >
    Bilbo, Nov 20, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Leon Shaw

    Stored Procedure Problem

    Leon Shaw, Jul 28, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    362
    Jurjen de Groot
    Jul 29, 2003
  2. ElmoWatson

    Stored Procedure/Parameter problem

    ElmoWatson, Aug 4, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    482
    Jerry
    Aug 6, 2003
  3. The Clansman

    Stored Procedure problem

    The Clansman, May 19, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    342
    Julie
    May 19, 2004
  4. =?Utf-8?B?SnVzdGlu?=

    Problem with stored procedure

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    3,682
    Larry
    Dec 12, 2008
  5. Mike P
    Replies:
    0
    Views:
    3,274
    Mike P
    Jun 19, 2006
Loading...

Share This Page