Update SQL Table Using Stored Procedure in VB.Net

Receiving Procedure has no parameters & arguments were supplied er

Page 1 of 1

7 Replies - 13085 Views - Last Post: 14 June 2010 - 05:37 AM Rate Topic: -----

#1 daniness  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 79
  • Joined: 08-January 10

Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 09:43 AM

Hello All,

I would appreciate if you could please advise on the problem I'm running into while attempting to get my app to update a database. I'm using a stored procedure, but am receiving a "Procedure sp_OrderTracking_Update_UpdateItem has no parameters and arguments were supplied" error. Here is my stored procedure:
 ALTER PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem

	DECLARE 
	@site varchar(30),
	@siteRefnbr integer(5),
	@aac varchar(10),
	@phoneNbr varchar(20),
	@faxNbr varchar(20),
	@depotRefnbr integer(1),
	@freightRefnbr integer(2),
	@dispatchRefnbr integer(1),
	@email varchar(30)
	
	 
	/* Update Locations table */
	        UPDATE    locations
	        SET              site = @site, siteRefNbr = @siteRefnbr, aac = @aac, phoneNbr = @phoneNbr, faxNbr = @faxNbr, depotRefnbr = @depotRefnbr, 
	                              freightRefnbr = @freightRefnbr, dispatchRefnbr = @dispatchRefnbr, Email = @Email
	        WHERE     (siteRefNbr =@siteRefnbr')

	RETURN;


This is my code for the Save button click event, which is clicked when a change is made to one of the textboxes/comboboxes which is on the form:
'Save changes to Locations db
    Private Sub btnSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveClose.Click
        If Not conn.State = ConnectionState.Open Then conn.Open()

        Dim site, aac, phoneNbr, faxNbr, email As String
        Dim siteRefNbr, depotRefNbr, freightRefNbr, dispatchRefNbr As Integer

        site = txtLoc.Text
        aac = txtAAC.Text
        phoneNbr = txtPhone.Text
        faxNbr = txtFax.Text
        email = txtEmail.Text

        siteRefNbr = frmLocations.cboLocations.SelectedValue
        depotRefNbr = cboDepot.SelectedValue
        freightRefNbr = cboFreight.SelectedValue
        dispatchRefNbr = cboDispatcher.SelectedValue


        Dim myCommand As SqlCommand = New SqlCommand("dbo.sp_OrderTracking_Update_UpdateItem", conn)
        myCommand.CommandType = Data.CommandType.StoredProcedure

        myCommand.Parameters.AddWithValue("@site", site)
        myCommand.Parameters.AddWithValue("@siteRefnbr", siteRefNbr)
        myCommand.Parameters.AddWithValue("@aac", aac)
        myCommand.Parameters.AddWithValue("@phoneNbr", phoneNbr)
        myCommand.Parameters.AddWithValue("@faxNbr", faxNbr)
        myCommand.Parameters.AddWithValue("@Email", email)
        myCommand.Parameters.AddWithValue("@depotRefnbr", depotRefNbr)
        myCommand.Parameters.AddWithValue("@freightRefnbr", freightRefNbr)
        myCommand.Parameters.AddWithValue("@dispatchRefnbr", dispatchRefNbr)

        myCommand.ExecuteNonQuery().ToString()
End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Update SQL Table Using Stored Procedure in VB.Net

#2 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 09:49 AM

in ur stored procedure u have the parameters set up incorrectly. they are set up as variable instead of parameters. you put the parameters in the storedprocedure in () after the name of the SP. so it should be like this.


 ALTER PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem
(
        @site varchar(30),
        @siteRefnbr integer(5),
        @aac varchar(10),
        @phoneNbr varchar(20),
        @faxNbr varchar(20),
        @depotRefnbr integer(1),
        @freightRefnbr integer(2),
        @dispatchRefnbr integer(1),
        @email varchar(30)
 )       
         
        /* Update Locations table */
                UPDATE    locations
                SET              site = @site, siteRefNbr = @siteRefnbr, aac = @aac, phoneNbr = @phoneNbr, faxNbr = @faxNbr, depotRefnbr = @depotRefnbr, 
                                      freightRefnbr = @freightRefnbr, dispatchRefnbr = @dispatchRefnbr, Email = @Email
                WHERE     (siteRefNbr =@siteRefnbr')

        RETURN;


Was This Post Helpful? 0
  • +
  • -

#3 daniness  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 79
  • Joined: 08-January 10

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 09:56 AM

Hi bflosabre91,

Thanks for your reply. I added the parenthesis around the parameters, but I'm still receiving the same error. Do you have any additional suggestions? :notify:
Was This Post Helpful? 0
  • +
  • -

#4 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 10:31 AM

Did you also remove the DECLARE keyword in your parameters?
Like this:
ALTER PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem
(
        @site varchar(30),
        @siteRefnbr integer(5),
        @aac varchar(10),
        @phoneNbr varchar(20),
        @faxNbr varchar(20),
        @depotRefnbr integer(1),
        @freightRefnbr integer(2),
        @dispatchRefnbr integer(1),
        @email varchar(30)
)        
         
        /* Update Locations table */
                UPDATE    locations
                SET              site = @site, siteRefNbr = @siteRefnbr, aac = @aac, phoneNbr = @phoneNbr, faxNbr = @faxNbr, depotRefnbr = @depotRefnbr, 
                                      freightRefnbr = @freightRefnbr, dispatchRefnbr = @dispatchRefnbr, Email = @Email
                WHERE     (siteRefNbr =@siteRefnbr')

        RETURN;



Not this:
ALTER PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem
(
        DECLARE --This DECLARE keyword shouldn't be here and will cause an error
        @site varchar(30),
        @siteRefnbr integer(5),
        @aac varchar(10),
        @phoneNbr varchar(20),
        @faxNbr varchar(20),
        @depotRefnbr integer(1),
        @freightRefnbr integer(2),
        @dispatchRefnbr integer(1),
        @email varchar(30)
 )      
         
        /* Update Locations table */
                UPDATE    locations
                SET              site = @site, siteRefNbr = @siteRefnbr, aac = @aac, phoneNbr = @phoneNbr, faxNbr = @faxNbr, depotRefnbr = @depotRefnbr, 
                                      freightRefnbr = @freightRefnbr, dispatchRefnbr = @dispatchRefnbr, Email = @Email
                WHERE     (siteRefNbr =@siteRefnbr')

        RETURN;


This post has been edited by keakTheGEEK: 11 June 2010 - 10:33 AM

Was This Post Helpful? 0
  • +
  • -

#5 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 10:53 AM

u have something extra at the end of the SP and i dont think u should have a return at the end, u should have an END. im not sure what flavor of sql your are using but if i were to create this SP, mine would look like this(MSSQL Server):

 alter PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem
(
        @site varchar(30),
        @siteRefnbr int,
        @aac varchar(10),
        @phoneNbr varchar(20),
        @faxNbr varchar(20),
        @depotRefnbr int,
        @freightRefnbr int,
        @dispatchRefnbr int,
        @email varchar(30)
 )       
         as Begin 
SET NOCOUNT ON;
        /* Update Locations table */
                UPDATE locations
                SET site = @site, siteRefNbr = @siteRefnbr, aac = @aac, phoneNbr = @phoneNbr, faxNbr = @faxNbr, depotRefnbr = @depotRefnbr, 
                                      freightRefnbr = @freightRefnbr, dispatchRefnbr = @dispatchRefnbr, Email = @Email
                WHERE siteRefNbr =@siteRefnbr

END


This post has been edited by bflosabre91: 11 June 2010 - 11:37 AM

Was This Post Helpful? 0
  • +
  • -

#6 daniness  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 79
  • Joined: 08-January 10

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 11:19 AM

Hi Keak,

I've tried removing the Declare, to no avail.

Bflosabre, I'm using MS SQL Server Express 2005.

Any other pointers?
Was This Post Helpful? 0
  • +
  • -

#7 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 11 June 2010 - 11:44 AM

did u copy what i posted in the last post and execute that? is there a reason ur command is myCommand.ExecuteNonQuery().ToString()? im not sure of the reason for the ToString()? i just use myCommand.ExecuteNonQuery() and that works with no problem. thats prob not ur issue but im just curious.
Was This Post Helpful? 0
  • +
  • -

#8 daniness  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 79
  • Joined: 08-January 10

Re: Update SQL Table Using Stored Procedure in VB.Net

Posted 14 June 2010 - 05:37 AM

bflosabre91 and others,

I did try what you posted and tried executing it. I tried both "myCommand.ExecuteNonQuery()" and "myCommand.ExecuteNonQuery().ToString()", but am still receiving the "Procedure sp_OrderTracking_Update_UpdateItem has no parameters and arguments were supplied." error. I'm using the .String() b/c I'm trying to update a row in the Locations table, which contains string values such as email address, etc.

I would appreciate any further advice. Thanks! :helpsmilie:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1