By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

Interesting Stored Procedure Problem..

P: n/a
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
Nov 18 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <tm*****@yahoo.com.nospam> wrote in message
news:Xn**********************************@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

Nov 18 '05 #2

P: n/a
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" <tm*****@yahoo.com.nospam> wrote in message
news:Xn**********************************@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

Nov 18 '05 #3

P: n/a
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" <sq*******************@tostopspamhotmail.com> wrote in
news:Sj*********************@news01.bloor.is.net.c 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" <tm*****@yahoo.com.nospam> wrote in message
news:Xn**********************************@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



Nov 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.