[RESOLVED] Posting Nulls to a SQL Server Stored Procedure

[aconybeare]

Hi,

I am trying to insert a record into my SQL server db table using a stored procedure. Some of the values can be null and have been marked as such on the db, but when I call the procedure I get an error message saying that an expected parameter is missing, these missing params are nulls that I'm trying to pass through.

Can anyone see where I'm going wrong with this?

Error -
_{"Procedure 'insert_New_Person' expects parameter '@address3', which was not supplied.insert failed"}

Any help will be most welcome

Cheers Al

Here is my vb.net function

Private Function Save_Person() As String
 
        ' Open the connection
        myCon.Open()
        
        Dim myCmd As New SqlCommand("insert_New_Person", myCon)
        myCmd.CommandType = CommandType.StoredProcedure
 
        ' Create a SqlParameter object to hold the output parameter value
        Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
 
        ' IMPORTANT - must set Direction as ReturnValue
        retValParam.Direction = ParameterDirection.ReturnValue
 
        ' Add the parameters to the Command's Parameters collection
        myCmd.Parameters.Add("@username", LCase(Username.Text))
        myCmd.Parameters.Add("@password", NewPassword1.Text)
        myCmd.Parameters.Add("@prefix", Prefix.Text)
        myCmd.Parameters.Add("@firstname", CSQ(FName.Text)) ' CSQ = Comment Single Quotes
        myCmd.Parameters.Add("@middlename", CSQ(MName.Text))
        myCmd.Parameters.Add("@lastname", CSQ(LName.Text))
        myCmd.Parameters.Add("@department", CSQ(Department.Text))
        myCmd.Parameters.Add("@institution", CSQ(Institution.Text))
        myCmd.Parameters.Add("@address1", CSQ(Address1.Text))
        myCmd.Parameters.Add("@address2", CSQ(Address2.Text))
        myCmd.Parameters.Add("@address3", CSQ(Address3.Text))
        myCmd.Parameters.Add("@address4", CSQ(Address4.Text))
        myCmd.Parameters.Add("@town", CSQ(City.Text))
        myCmd.Parameters.Add("@county", CSQ(State.Text))
        myCmd.Parameters.Add("@postcode", PostalCode.Text)
        myCmd.Parameters.Add("@country", ddlCountry.SelectedValue)
        myCmd.Parameters.Add("@telephone", Phone.Text)
        myCmd.Parameters.Add("@fax", Fax.Text)
        myCmd.Parameters.Add("@email", Email.Text)
        ' Finally, add the output parameter
        myCmd.Parameters.Add(retValParam)
 
        Dim dr As SqlDataReader
        ' Call the SProc...
 
        Try
            dr = myCmd.ExecuteReader()
            ' Now you can grab the output parameter's value...
            Dim retVal As Integer = Convert.ToInt32(retValParam.Value)
            If retVal > 0 Then
                Save_Person = "OK"
            Else
                Save_Person = "Error"
            End If
        Catch ex As Exception
            ' Record any exceptions and exit
            Dim myLiteral As New Literal
            myLiteral.Text = "The following exception occurred: <br />" + ex.Message.ToString
            Page.Controls.Add(myLiteral)
            Save_Person = "Error"
        End Try
 
        dr.Close()
        myCon.Close()
    End Function

Here is a copy of my stored Proc

CREATE Procedure insert_New_Person
(
	@username varchar(255),
	@password varchar(15),
	@prefix varchar(25),
	@firstname nvarchar(50),
	@middlename nvarchar(50),
	@lastname nvarchar(50),
	@department nvarchar(150),
	@institution nvarchar(100),
	@address1 nvarchar(100),
	@address2 nvarchar(100),
	@address3 nvarchar(100),
	@address4 nvarchar(100),
	@town nvarchar(50),
	@county nvarchar(50),
	@postcode nvarchar(25),
	@country nvarchar(100),
	@telephone varchar(50),
	@fax varchar(50),
	@email varchar(255)
)
As
	-- INSERT the New record
	INSERT INTO myDB.People
		(
		User_Name, 
		Password, 
		Prefix, 
		First_Name, 
		Middle_Name, 
		Last_Name,
		Department,
		Institution,
		Address1,
		Address2,
		Address3,
		Address4,
		Town,
		County,
		Postcode,
		Country,
		Telephone,
		Fax,
		Email
		)
	VALUES
		(
		@username, 
		@password, 
		@prefix, 
		@firstname, 
		@middlename, 
		@lastname,
		@department,
		@institution,
		@address1,
		@address2,
		@address3,
		@address4,
		@town,
		@county,
		@postcode,
		@country,
		@telephone,
		@fax,
		@email
		)
	-- Now return the User_ID of the newly inserted record
	RETURN SCOPE_IDENTITY()
Go