Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Interesting Stored Procedure Problem..

Reply
Thread Tools

Interesting Stored Procedure Problem..

 
 
Bilbo
Guest
Posts: n/a
 
      11-20-2003
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
 
Reply With Quote
 
 
 
 
S. Justin Gengo
Guest
Posts: n/a
 
      11-20-2003
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" <(E-Mail Removed)> wrote in message
news:Xns9439A30B1FF55tmvbillyahoocomnospa@207.46.2 48.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



 
Reply With Quote
 
 
 
 
Lloyd Sheen
Guest
Posts: n/a
 
      11-20-2003
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" <(E-Mail Removed)> wrote in message
news:Xns9439A30B1FF55tmvbillyahoocomnospa@207.46.2 48.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



 
Reply With Quote
 
Bilbo
Guest
Posts: n/a
 
      11-20-2003
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" <(E-Mail Removed)> wrote in
news:Sjavb.128506$(E-Mail Removed) able.rogers.com:

> 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" <(E-Mail Removed)> wrote in message
> news:Xns9439A30B1FF55tmvbillyahoocomnospa@207.46.2 48.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

>
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
procedure as argument in procedure AlexWare VHDL 2 10-23-2009 09:14 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Sending email through Stored procedure Patrick ASP .Net 2 02-15-2005 08:29 AM
How to modify default Stored Procedure template in VS.NET database project ES ASP .Net 2 08-20-2004 08:19 PM
New Stored Procedure Template in .Net Sarmad Aljazrawi ASP .Net 0 12-16-2003 11:36 AM



Advertisments