Question Update SQL Table Using Stored Procedure

daniness

Well-known member
Joined
Feb 12, 2010
Messages
49
Programming Experience
Beginner
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:
VB.NET:
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:
VB.NET:
'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
 
You're creating variables in your stored procedure not parameters. Try changing it to something like this.

VB.NET:
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)
AS
BEGIN

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
 
Hi MattP,

Thanks for your reply. I tried adding the "BEGIN" and "END", but I'm still receiving the "Procedure sp_OrderTracking_Update_UpdateItem has no parameters and arguments were supplied." error. Do you have any other ideas as to what's causing this?

Thanks in advance!
 
Back
Top