Cannot find a parameter that needs to be specified

[mhmm]

Hi,

Sorry the title wasn't as descriptive as I'd hoped (word restriction on it) but I've been getting this error for a while and I'm not too sure how to fix it. I have a database which is for storing contacts, it has a few look up tables and one central table. To insert data from the GUI to the database I have a stored procedure to do this, however whenever I try and run the code I get this error:

Procedure or function 'SPAddContact' expects parameter '@Postcode', which was not supplied.


The code for running the stored procedure is below:

Using cmd As New SqlCommand("Contacts.dbo.SPAddContact")
            cmd.Connection = cs
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFName.Text
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLName.Text
 	    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = txtaddress1.Text
	    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = txtaddress2.Text
	    cmd.Parameters.Add("@Postcode", SqlDbType.VarChar).Value = txtpostcode.Text
            cmd.Parameters.Add("@DOB", SqlDbType.Date).Value = DOBDateTimePicker.Value.ToShortDateString
            cmd.ExecuteNonQuery()

        End Using

I've used this format because any other one that I've tried using has ended up with it saying it cannot find the SP.

This is the code for the stored procedure:

USE [contacts]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SPAddContact] @FirstName VARCHAR(50), @DOB DATE, @LastName VARCHAR(50), @Address1 VARCHAR(50), 
@Address2 VARCHAR(50), @Postcode VARCHAR(8)

AS
--Retrieved IDs are stored here
DECLARE @Postcode INT 
DECLARE @Address1ID INT 
DECLARE @Address2ID INT 
DECLARE @DOBID INT 

BEGIN
--Sets the variables to the value returned by the slect statements
SET @PostcodeID = (SELECT PostcodeID FROM ContactPostcode WHERE Postcode = @PostcodeID)
SET @Address1ID = (SELECT Address1ID FROM Address1 WHERE Address1 = @Address1ID)
SET @Address2ID = (SELECT Address2ID FROM Address2 WHERE Address2 = @Address2ID)
SET @DOBID = (SELECT DOBID FROM Birthday WHERE DOB = @DOBID)

INSERT INTO Build VALUES (@FirstName,@LastName,@Address1ID,@Address2ID,@PostcodeID,@DOBID);


END

Any help on this would be greatly appreciated!

I'm currently using Visual Basic 10

[TysonLPrice]

I don't know if it matters with the mechanism you are using but if you are just calling a stored procedure directly the parameters need to be given in the order they are declared in the SPROC. What you have posted has them out of order. Bottom line is not all the parameters the SP needs are being passed. Is there always something in the ttext boxes?

[mhmm]

I'll try putting them in order but what do you mean by

Bottom line is not all the parameters the SP needs are being passed.

?

[TysonLPrice]

Expected parameters

SPAddContact @FirstName VARCHAR(50), @DOB DATE, @LastName VARCHAR(50), @Address1 VARCHAR(50),
@Address2 VARCHAR(50), @Postcode VARCHAR(8)

Executing

Exec SPAddContact 'FirstName', 'DOB DATE', 'LastName', 'Address1' , 'Address2'
'Postcode'

If anything is missing you will get that error.
So if Address2 is missing it would "think" Postcode is address2 and say postcode is missing.

Every parameter must be suppled unless you make them optional. In the string you are passing you are missing a parameter.

[mhmm]

I would have thought it had something to do with these lines from the SP:

SET @PostcodeID = (SELECT PostcodeID FROM ContactPostcode WHERE Postcode = @PostcodeID)

I tried rearranging everything to be in the right order and still it came back with the same error

[riteshjain1982]

couple of problem that i can see in your SP is
1.I isn't it DECLARE @Postcode INT should be DECLARE @PostcodeID INT ?

2
SET @PostcodeID = (SELECT PostcodeID FROM ContactPostcode WHERE Postcode = @PostcodeID)
should be

SET @PostcodeID = (SELECT PostcodeID FROM ContactPostcode WHERE Postcode = @Postcode)

[TysonLPrice]

I would have thought it had something to do with these lines from the SP:

SET @PostcodeID = (SELECT PostcodeID FROM ContactPostcode WHERE Postcode = @PostcodeID)

I tried rearranging everything to be in the right order and still it came back with the same error

The error is plain and simple. It is expecting a parameter that isn't being passed.

[mhmm]

Thank you for getting back to me with this. I have a new error if it's okay to ask it in the same thread?

My drop down boxes are now passing System.Data.DataRowView instead of an actual value like I've told it to :s