[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

[-TPM-]

Try passing a dbnull for the null parameters.

[aconybeare]

TPM,

Thanks for your reply

I've changed all params that could be null to the following -

myCmd.Parameters.Add("@middlename", IIf(MName.Text = vbNullString, CSQ(MName.Text), DBNull.Value))

Now got the following error -

_{Object reference not set to an instance of an object.}

Here is a list of my imported namespaces and my CSQ function just incase there is something glaringly obviously wrong there

Imports System
Imports System.DBNull
Imports System.IO
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
 
Private Function CSQ(ByVal str As String) As String
        If str = Nothing Then str = vbNullString
        CSQ = Replace(str, "'", "''")
End Function

[aconybeare]

I've changed my proc to the following and it's worked, the object reference error must be caused by the call to IIF??

'myCmd.Parameters.Add("@middlename", IIf(MName.Text = vbNullString, CSQ(MName.Text), DBNull.Value))
myCmd.Parameters.Add("@middlename", DBNull.Value)

Does anyone know what namespace I need to reference to use IIF? Or do I have to write the function myself as we used to have to do for vbScript?

[-TPM-]

Not sure about iif (maybe someone else can help on that), but for now why not just use an if, then, else? ie it textbow.text > nothing add param with textbox.text else add it with dbnull.

[aconybeare]

Hhmmm, I had my IIF condition the wrong way round -

myCmd.Parameters.Add("@middlename", IIf(MName.Text = vbNullString, CSQ(MName.Text), DBNull.Value))

should be -

myCmd.Parameters.Add("@middlename", IIf(MName.Text = vbNullString, [b]DBNull.Value, CSQ(MName.Text)[/b]))

Just relieved that it was me that spotted it

Thanks TPM for the DBNull tip.

Cheers Al

[Lord_Rat]

...snipped text...

	RETURN SCOPE_IDENTITY()

I have never seen this before, so I just now went and looked it up.

I have always used @@IDENTITY. Now, understanding the problem that triggers may cause me by using @@IDENTITY, I will begin using SCOPE_IDENTITY() -- all as a result of some innocuous code that you posted.

So, thanks! =)

[Lord_Rat]

Oh yeah, might I mention, code posted five months ago.... =)

[aconybeare]

No worries my Lord always happy to help even if it was indirectly