Foreign Key Values Not Passing on Button Click Event

[daniness]

Hello All,

I've been having a hard time trying to figure out why some values, which happen to be foreign keys, are not passing between my stored procedure and my code on a button click event. I keep receiving the error, "Procedure or Function 'sp_OrderTracking_Update_UpdateItem' expects parameter '@depotRefnbr', which was not supplied. This is happening on the line in red. As far as I can see, the @depotRefnbr is being given a value. It appears that other values, which are not foreign keys are passing fine. Your assistance would be immensely appreciated.

Here is my code:

Stored Procedure:

ALTER PROCEDURE dbo.sp_OrderTracking_Update_UpdateItem
	(
	
	@site varchar(30),
	@siteRefnbr varchar(5),
	@aac varchar(10),
	@phoneNbr varchar(20),
	@faxNbr varchar(20),
	@depot varchar (20),
	@depotRefnbr varchar(2),
	@freight varchar(40),
	@freightRefnbr varchar(2),
	@dsptchr varchar(15),
	@dispatchRefnbr varchar(2),
	@email varchar(30)
	
	)
	
AS
/* Update Locations table */
BEGIN
        UPDATE    locations
        SET              site = @site, site_refnbr = @siteRefnbr, aac = @aac, telephone_nbr = @phoneNbr, fax_nbr = @faxNbr, depot_refnbr = @depotRefnbr, 
                              freight_refnbr = @freightRefnbr, dispatch_refnbr = @dispatchRefnbr, Email = @Email
        WHERE     (site_refnbr = @siteRefnbr) 

	
	END;

Class:

'Declare variables
Public Class clsMain

    Public Shared Location_str_value As String
    Public Shared AAC_str_value As String
    Public Shared Phone_str_value As String
    Public Shared Fax_str_value As String
    Public Shared Email_str_value As String
    Public Shared Depot_str_value As String
    Public Shared FrghtFrwrdr_str_value As String
    Public Shared Dsptchr_str_value As String

    Public Shared Location_refnbr As Integer
    Public Shared Depot_refnbr As String
    Public Shared Frght_refNbr As Integer
    Public Shared Dsptchr_refNbr As Integer

    'Public Shared Location_depot_refnbr As Integer
    Public Shared CorrespDepot As String
    Public Shared location As String

End Class

Button Click Event:

Private Sub btnSaveClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveClose.Click
        'Passing value from controls to variables

        clsMain.Location_str_value = txtLoc.Text
        'clsMain.Location_refnbr = frmLocations.cboLocations.SelectedValue
        clsMain.Location_refnbr = lblSiteRefNbr.Text
        clsMain.AAC_str_value = txtAAC.Text
        clsMain.Phone_str_value = txtPhone.Text
        clsMain.Fax_str_value = txtFax.Text
        clsMain.Email_str_value = txtEmail.Text
        'clsMain.Depot_str_value = cboDepot.Text


        'MsgBox(cboDepot.SelectedValue)
        'clsMain.Depot_refnbr = cboDepot.SelectedValue

        clsMain.Depot_refnbr = cboDepot.SelectedValue
        clsMain.FrghtFrwrdr_str_value = cboFreight.Text
        clsMain.Frght_refNbr = cboFreight.SelectedValue
        clsMain.Dsptchr_str_value = cboDispatcher.Text
        clsMain.Dsptchr_refNbr = cboDispatcher.SelectedValue

        If Not conn.State = ConnectionState.Open Then conn.Open()

        'Create command object, open stored procedure, and pass connection string
        Dim myCommand As SqlCommand = New SqlCommand("dbo.sp_OrderTracking_Update_UpdateItem", conn)
        myCommand.CommandType = Data.CommandType.StoredProcedure

        myCommand.Parameters.AddWithValue("@site", clsMain.Location_str_value)
        myCommand.Parameters.AddWithValue("@siteRefnbr", clsMain.Location_refnbr)
        myCommand.Parameters.AddWithValue("@aac", clsMain.AAC_str_value)
        myCommand.Parameters.AddWithValue("@phoneNbr", clsMain.Phone_str_value)
        myCommand.Parameters.AddWithValue("@faxNbr", clsMain.Fax_str_value)
        myCommand.Parameters.AddWithValue("@email", clsMain.Email_str_value)
        myCommand.Parameters.AddWithValue("@depot", clsMain.Depot_str_value)
        myCommand.Parameters.AddWithValue("@depotRefnbr", clsMain.Depot_refnbr)
        myCommand.Parameters.AddWithValue("@freight", clsMain.FrghtFrwrdr_str_value)
        myCommand.Parameters.AddWithValue("@freightRefnbr", clsMain.Frght_refNbr)
        myCommand.Parameters.AddWithValue("@dsptchr", clsMain.Dsptchr_str_value)
        myCommand.Parameters.AddWithValue("@dispatchRefnbr", clsMain.Dsptchr_refNbr)

 [COLOR="red"]       myCommand.ExecuteNonQuery().ToString()[/COLOR]
        'myCommand.ExecuteNonQuery()
        myCommand.Parameters.Clear()

        MessageBox.Show("Location successfully saved.", "Changes Saved", MessageBoxButtons.OK)
        
    End Sub

 

[cjard]

Try the following:

Add a new dataset to your project
Right click the surface of the set and choose Add.. Query
A device called QueriesTableAdapter appears, followed by a wizard
When asked for the type of query, choose "use an existing stored procedure"
Choose your stored procedure
Name it something sensible
Finish the wizard
In saveclose_click code do this:

Dim ta as New [I]WhateverDataSetName[/I]TableAdapters.QueriesTableAdapter
ta.[I]WhateverQueryName[/I](txtLoc.Text, .... )

Yes, just two lines.. one to make the tableadapter, the other to run the query. Does it work now?

Note you dont need to bother copying the textbox values into variables.. All youre doing is establishing another pointer to the same string in memory; just make your code smaller and life easier by skipping that part. ie dont go writing ta.WhateverQueryName(clsMain.Location_str_value, ...) - just ditch those class level variables completely. If yo ulike you can add more queries to the one tableadapter and then just have one instance of the adapter on the form, and use it in your save, delete, new etc buttoin clicks