How to Pass an empty string to a stored proc

[JAM]

Hi everyone

I'm trying to insert a new item using a stored procedure. In this case if the user leaves the category id textbox empty, then it sould be inserted as a null value.

here is my code

Dim catidparam As SqlParameter
           catidparam = New SqlParameter("@Category_id", SqlDbType.Int)
           catidparam.Direction = ParameterDirection.Input
           'make null insertion Possible
           catidparam.Value = IIf(IsDBNull(txtcategoryid.Text), DBNull.Value, txtcategoryid.Text)
           myCmd.Parameters.Add(catidparam)

 

But it doesn't work. I get error message saying "input string was not in a correct format"

Please help.

[Robby]

In the SP list of params, set the default value to Null.

 

@myVar varchar(10) Null,

[Robby]

I just noticed your code, instead do somthing like

 

catidparam.Value = String.Empty (of course only if textbox is blank)

[JAM]

Dear Robby

Thank you for replying.

Niether approach solve the problem.

[Robby]

Actually if the textbox is blank you should skip this line

 

myCmd.Parameters.Add(catidparam)

 

and only change this in your SP

@myVar varchar(10) Null

[CattleRustler]

is the column in the db set to accept null?

[JAM]

Yes Cattle its been set toaccept null.

Actually I have used Robby's approach by skiping and it work's.

Thank you every one.