473,544 Members | 1,420 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Interesting Stored Procedure Problem..

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(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnAdd.Click
If Page.IsValid Then
If Page.IsValid Then
Dim cnn As SqlConnection = New SqlConnection( _
"Data Source=MyMachin e;Initial
Catalog=MyDataB ase;UID=bilbo;P WD=baggins")
Dim cmdInsert As SqlCommand = cnn.CreateComma nd()
cmdInsert.Comma ndType = CommandType.Sto redProcedure
cmdInsert.Comma ndText = "AddCompany "
'Add the proper parameters for this stored procedure
cmdInsert.Param eters.Add( _
"@CompanyNa me", SqlDbType.VarCh ar, 50)
cmdInsert.Param eters("@Company Name").Value = _
txtCompany.Text
'Add the output parameter and set its direction
cmdInsert.Param eters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Int))
cmdInsert.Param eters("@Company ID").Directio n = _
ParameterDirect ion.Output
cnn.Open()
cmdInsert.Execu teNonQuery()
cnn.Close()
lblCompanyID.Te xt = cmdInsert.Param eters( _
"@CompanyID").V alue

'<----------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.CreateComma nd()
cmdInsert2.Comm andType = CommandType.Sto redProcedure
cmdInsert2.Comm andText = "AddContact "
cmdInsert2.Para meters.Add(New SqlParameter( _
"@LastName" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@LastNa me").Value =
txtLastName.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@PreName", SqlDbType.Char, 10))
cmdInsert2.Para meters("@PreNam e").Value =
ddlPre.Selected Item.Value
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FirstName ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@FirstN ame").Value =
txtFirstName.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MiddleNam e", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Middle Name").Value =
txtMiddleName.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Suffix", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Suffix ").Value = txtSuffix.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Compan yID").Value =
lblCompanyID.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters.Add(New SqlParameter( _
"@JobTitle" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@JobTit le").Value =
txtJobTitle.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy1" ).Value = txtBusAdd1.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy2" ).Value = txtBusAdd2.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy3" ).Value = txtBusAdd3.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@POBox", SqlDbType.VarCh ar, 20))
cmdInsert2.Para meters("@POBox" ).Value = txtPOBox.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@City", SqlDbType.Char, 16))
cmdInsert2.Para meters("@City") .Value = txtCity.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@State", SqlDbType.Char, 16))
cmdInsert2.Para meters("@State" ).Value = txtState.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Zip", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Zip"). Value = txtZipCode.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Country", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Countr y").Value = txtCountry.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@WorkPhone ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@WorkPh one").Value =
txtBusPhone.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MobilePho ne", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@Mobile Phone").Value =
txtMobilePhone. Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MainPhone ", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@MainPh one").Value =
txtMainPhone.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FaxNumber ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@FaxNum ber").Value = txtFax.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Email", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Email" ).Value =
txtEmailAddress .Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Dept", SqlDbType.Char, 30))
cmdInsert2.Para meters("@Dept") .Value = txtDepartment.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat1") .Value =
ddlCategory1.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat2") .Value =
ddlCategory2.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat3") .Value =
ddlCategory3.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat4", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat4") .Value =
ddlCategory4.Se lectedItem.Valu e
cnn.Open()
cmdInsert2.Exec uteNonQuery()
cnn.Close()
End If
End If
End Sub
Nov 18 '05 #1
3 1759
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.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(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnAdd.Click
If Page.IsValid Then
If Page.IsValid Then
Dim cnn As SqlConnection = New SqlConnection( _
"Data Source=MyMachin e;Initial
Catalog=MyDataB ase;UID=bilbo;P WD=baggins")
Dim cmdInsert As SqlCommand = cnn.CreateComma nd()
cmdInsert.Comma ndType = CommandType.Sto redProcedure
cmdInsert.Comma ndText = "AddCompany "
'Add the proper parameters for this stored procedure
cmdInsert.Param eters.Add( _
"@CompanyNa me", SqlDbType.VarCh ar, 50)
cmdInsert.Param eters("@Company Name").Value = _
txtCompany.Text
'Add the output parameter and set its direction
cmdInsert.Param eters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Int))
cmdInsert.Param eters("@Company ID").Directio n = _
ParameterDirect ion.Output
cnn.Open()
cmdInsert.Execu teNonQuery()
cnn.Close()
lblCompanyID.Te xt = cmdInsert.Param eters( _
"@CompanyID").V alue

'<----------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.CreateComma nd()
cmdInsert2.Comm andType = CommandType.Sto redProcedure
cmdInsert2.Comm andText = "AddContact "
cmdInsert2.Para meters.Add(New SqlParameter( _
"@LastName" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@LastNa me").Value =
txtLastName.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@PreName", SqlDbType.Char, 10))
cmdInsert2.Para meters("@PreNam e").Value =
ddlPre.Selected Item.Value
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FirstName ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@FirstN ame").Value =
txtFirstName.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MiddleNam e", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Middle Name").Value =
txtMiddleName.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Suffix", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Suffix ").Value = txtSuffix.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Compan yID").Value =
lblCompanyID.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters.Add(New SqlParameter( _
"@JobTitle" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@JobTit le").Value =
txtJobTitle.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy1" ).Value = txtBusAdd1.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy2" ).Value = txtBusAdd2.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy3" ).Value = txtBusAdd3.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@POBox", SqlDbType.VarCh ar, 20))
cmdInsert2.Para meters("@POBox" ).Value = txtPOBox.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@City", SqlDbType.Char, 16))
cmdInsert2.Para meters("@City") .Value = txtCity.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@State", SqlDbType.Char, 16))
cmdInsert2.Para meters("@State" ).Value = txtState.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Zip", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Zip"). Value = txtZipCode.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Country", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Countr y").Value = txtCountry.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@WorkPhone ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@WorkPh one").Value =
txtBusPhone.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MobilePho ne", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@Mobile Phone").Value =
txtMobilePhone. Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MainPhone ", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@MainPh one").Value =
txtMainPhone.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FaxNumber ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@FaxNum ber").Value = txtFax.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Email", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Email" ).Value =
txtEmailAddress .Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Dept", SqlDbType.Char, 30))
cmdInsert2.Para meters("@Dept") .Value = txtDepartment.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat1") .Value =
ddlCategory1.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat2") .Value =
ddlCategory2.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat3") .Value =
ddlCategory3.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat4", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat4") .Value =
ddlCategory4.Se lectedItem.Valu e
cnn.Open()
cmdInsert2.Exec uteNonQuery()
cnn.Close()
End If
End If
End Sub

Nov 18 '05 #2
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.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(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnAdd.Click
If Page.IsValid Then
If Page.IsValid Then
Dim cnn As SqlConnection = New SqlConnection( _
"Data Source=MyMachin e;Initial
Catalog=MyDataB ase;UID=bilbo;P WD=baggins")
Dim cmdInsert As SqlCommand = cnn.CreateComma nd()
cmdInsert.Comma ndType = CommandType.Sto redProcedure
cmdInsert.Comma ndText = "AddCompany "
'Add the proper parameters for this stored procedure
cmdInsert.Param eters.Add( _
"@CompanyNa me", SqlDbType.VarCh ar, 50)
cmdInsert.Param eters("@Company Name").Value = _
txtCompany.Text
'Add the output parameter and set its direction
cmdInsert.Param eters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Int))
cmdInsert.Param eters("@Company ID").Directio n = _
ParameterDirect ion.Output
cnn.Open()
cmdInsert.Execu teNonQuery()
cnn.Close()
lblCompanyID.Te xt = cmdInsert.Param eters( _
"@CompanyID").V alue

'<----------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.CreateComma nd()
cmdInsert2.Comm andType = CommandType.Sto redProcedure
cmdInsert2.Comm andText = "AddContact "
cmdInsert2.Para meters.Add(New SqlParameter( _
"@LastName" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@LastNa me").Value =
txtLastName.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@PreName", SqlDbType.Char, 10))
cmdInsert2.Para meters("@PreNam e").Value =
ddlPre.Selected Item.Value
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FirstName ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@FirstN ame").Value =
txtFirstName.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MiddleNam e", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Middle Name").Value =
txtMiddleName.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Suffix", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Suffix ").Value = txtSuffix.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Compan yID").Value =
lblCompanyID.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters.Add(New SqlParameter( _
"@JobTitle" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@JobTit le").Value =
txtJobTitle.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy1" ).Value = txtBusAdd1.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy2" ).Value = txtBusAdd2.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy3" ).Value = txtBusAdd3.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@POBox", SqlDbType.VarCh ar, 20))
cmdInsert2.Para meters("@POBox" ).Value = txtPOBox.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@City", SqlDbType.Char, 16))
cmdInsert2.Para meters("@City") .Value = txtCity.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@State", SqlDbType.Char, 16))
cmdInsert2.Para meters("@State" ).Value = txtState.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Zip", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Zip"). Value = txtZipCode.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Country", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Countr y").Value = txtCountry.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@WorkPhone ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@WorkPh one").Value =
txtBusPhone.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MobilePho ne", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@Mobile Phone").Value =
txtMobilePhone. Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MainPhone ", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@MainPh one").Value =
txtMainPhone.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FaxNumber ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@FaxNum ber").Value = txtFax.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Email", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Email" ).Value =
txtEmailAddress .Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Dept", SqlDbType.Char, 30))
cmdInsert2.Para meters("@Dept") .Value = txtDepartment.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat1") .Value =
ddlCategory1.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat2") .Value =
ddlCategory2.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat3") .Value =
ddlCategory3.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat4", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat4") .Value =
ddlCategory4.Se lectedItem.Valu e
cnn.Open()
cmdInsert2.Exec uteNonQuery()
cnn.Close()
End If
End If
End Sub

Nov 18 '05 #3
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************ *******@tostops pamhotmail.com> wrote in
news:Sj******** *************@n ews01.bloor.is. net.cable.roger s.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.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(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnAdd.Click
If Page.IsValid Then
If Page.IsValid Then
Dim cnn As SqlConnection = New SqlConnection( _
"Data Source=MyMachin e;Initial
Catalog=MyDataB ase;UID=bilbo;P WD=baggins")
Dim cmdInsert As SqlCommand = cnn.CreateComma nd()
cmdInsert.Comma ndType = CommandType.Sto redProcedure
cmdInsert.Comma ndText = "AddCompany "
'Add the proper parameters for this stored procedure
cmdInsert.Param eters.Add( _
"@CompanyNa me", SqlDbType.VarCh ar, 50)
cmdInsert.Param eters("@Company Name").Value = _
txtCompany.Text
'Add the output parameter and set its direction
cmdInsert.Param eters.Add(New SqlParameter( _
"@CompanyID ", SqlDbType.Int))
cmdInsert.Param eters("@Company ID").Directio n = _
ParameterDirect ion.Output
cnn.Open()
cmdInsert.Execu teNonQuery()
cnn.Close()
lblCompanyID.Te xt = cmdInsert.Param eters( _
"@CompanyID").V alue

'<----------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.CreateComma nd()
cmdInsert2.Comm andType = CommandType.Sto redProcedure
cmdInsert2.Comm andText = "AddContact "
cmdInsert2.Para meters.Add(New SqlParameter( _
"@LastName" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@LastNa me").Value =
txtLastName.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@PreName", SqlDbType.Char, 10))
cmdInsert2.Para meters("@PreNam e").Value =
ddlPre.Selected Item.Value
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FirstName ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@FirstN ame").Value =
txtFirstName.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MiddleNam e", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Middle Name").Value =
txtMiddleName.T ext
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Suffix", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Suffix ").Value =
txtSuffix.Text cmdInsert2.Para meters.Add(New
SqlParameter( _ "@CompanyID ", SqlDbType.Char, 16))
cmdInsert2.Para meters("@Compan yID").Value =
lblCompanyID.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters.Add(New SqlParameter( _
"@JobTitle" , SqlDbType.Char, 16))
cmdInsert2.Para meters("@JobTit le").Value =
txtJobTitle.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Addy1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy1" ).Value =
txtBusAdd1.Text cmdInsert2.Para meters.Add(New
SqlParameter( _ "@Addy2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy2" ).Value =
txtBusAdd2.Text cmdInsert2.Para meters.Add(New
SqlParameter( _ "@Addy3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Addy3" ).Value =
txtBusAdd3.Text cmdInsert2.Para meters.Add(New
SqlParameter( _ "@POBox", SqlDbType.VarCh ar, 20))
cmdInsert2.Para meters("@POBox" ).Value = txtPOBox.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@City", SqlDbType.Char, 16))
cmdInsert2.Para meters("@City") .Value = txtCity.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@State", SqlDbType.Char, 16))
cmdInsert2.Para meters("@State" ).Value = txtState.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Zip", SqlDbType.Char, 10))
cmdInsert2.Para meters("@Zip"). Value = txtZipCode.Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Country", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Countr y").Value =
txtCountry.Text cmdInsert2.Para meters.Add(New
SqlParameter( _ "@WorkPhone ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@WorkPh one").Value =
txtBusPhone.Tex t
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MobilePho ne", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@Mobile Phone").Value =
txtMobilePhone. Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@MainPhone ", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@MainPh one").Value =
txtMainPhone.Te xt
cmdInsert2.Para meters.Add(New SqlParameter( _
"@FaxNumber ", SqlDbType.VarCh ar, 16))
cmdInsert2.Para meters("@FaxNum ber").Value =
txtFax.Text cmdInsert2.Para meters.Add(New
SqlParameter( _ "@Email", SqlDbType.VarCh ar, 30))
cmdInsert2.Para meters("@Email" ).Value =
txtEmailAddress .Text
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Dept", SqlDbType.Char, 30))
cmdInsert2.Para meters("@Dept") .Value =
txtDepartment.T ext cmdInsert2.Para meters.Add(New
SqlParameter( _ "@Cat1", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat1") .Value =
ddlCategory1.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat2", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat2") .Value =
ddlCategory2.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat3", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat3") .Value =
ddlCategory3.Se lectedItem.Valu e
cmdInsert2.Para meters.Add(New SqlParameter( _
"@Cat4", SqlDbType.VarCh ar, 50))
cmdInsert2.Para meters("@Cat4") .Value =
ddlCategory4.Se lectedItem.Valu e
cnn.Open()
cmdInsert2.Exec uteNonQuery()
cnn.Close()
End If
End If
End Sub



Nov 18 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10880
by: Michael Trosen | last post by:
Hi Everyone, I hope someone can help, I'm pretty new to pro*c programming. I have the following application setup: a pro*c program calls a stored procedure and recieves a cursor back: the cursor is defined as: SQL_CURSOR delpt_cursor
8
2519
by: ºa¤Ö | last post by:
I find a interesting question, and I cannot solve it @.@ If i want to insert unicode data, I need using recordset.addnew instead of using "insert into table" query or "stored procedure" All unicode data inserted into database through insert query or stored procedure would become a question mark "?" no matter: 1. the field is nvarchar 2....
3
2787
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default" when I launched the IBM Distributed Debugger via D:\IBMDebug>idebug.exe -qdaemon -quiport=8000,8001 First, a bit of background. I am running DB2...
7
15574
by: Jeff Wang | last post by:
Hi all, Can someone help me out? I've been struggling with this for almost a week and still have no clue what's wrong. Basically I want to write a DB2 stored procedure for OS/390 in REXX. In this procedure it reads a dataset and return the first line of the dataset. I met two problems: Problem 1:
2
9203
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
2
3317
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have several stored procedures to Insert and update datas in our SQL database. Some stored procedures are smaller (insert datas in only one table) and some...
7
3430
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant' expects parameter '@EMail', which was not supplied. The field value was null in the database and not changed in the FormView so is...
4
3969
by: nishi57 | last post by:
I hope I can get some help regarding this issue, which has been going on for a while. I have a desktop user who is having problem running "Stored Procedures". The DB2 Connect application works fine but when he runs the stored procedure, he gets the following error message. "SYSPROC".CSGCSB54 - Run started. Data returned in result sets is...
9
4128
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with...
0
7424
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7607
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7772
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7709
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5297
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3409
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1841
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
988
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
661
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.