Interesting Stored Procedure Problem..

[Bilbo]

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]

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

 

[Lloyd Sheen]

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]

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