2 Replies - 2591 Views - Last Post: 29 June 2010 - 02:03 PM Rate Topic: -----

#1 daniness  Icon User is offline

  • D.I.C Head

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

Foreign Key Values Not Passing on Button Click Event

Posted 29 June 2010 - 09:57 AM

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="#FF0000"]myCommand.ExecuteNonQuery().ToString()[/color]

        'myCommand.ExecuteNonQuery()
        myCommand.Parameters.Clear()

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


Is This A Good Question/Topic? 0
  • +

Replies To: Foreign Key Values Not Passing on Button Click Event

#2 demausdauth  Icon User is online

  • D.I.C Addict
  • member icon

Reputation: 181
  • View blog
  • Posts: 659
  • Joined: 03-February 10

Re: Foreign Key Values Not Passing on Button Click Event

Posted 29 June 2010 - 10:26 AM

First thing I see is that your datatypes do not match -
The stored procedure is expecting a varchar(2) but in your code you declare as an integer.

If depot_refnbr in the database is an integer then you should keep this datatype throughout your code as well

This post has been edited by demausdauth: 29 June 2010 - 10:28 AM

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: Foreign Key Values Not Passing on Button Click Event

Posted 29 June 2010 - 02:03 PM

Hi Demausdauth,

Thanks for your suggestion. I changed the datatypes for depot_refnbr in the class to integer and for @depotRefnbr to integer as well. However, what's happening is that the program is running fine, but changes made from selecting an item from a combobox are not being saved to the database. Any ideas please?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1