How to replace empty string parameter to Null in stored procedure and VB.net

Question

I have stored procedure that allow null parameter and works fine in SSMS. . Here is the SP

ALTER PROCEDURE [dbo].[sp_RepInventorySales]
@SalesOrderNo varchar(9) = null
AS
BEGIN
SELECT SalesOrderNo, ItemCode, ContainerNo, SealNo, ContainerType, OrderQty
FROM  SalesOrderDetail 
where SalesOrderNo = isnull(@SalesOrderNo, SalesOrderNo )

Actually the SP is the source of my crystal report. When Me.txtSalesOrderNo.Text is NOT empty I get the correct output.However, if textbox is empty, the stored procedure treats the parameter as empty string '' . How can i replace empty string to NULL? . Here is the code VB code

Private Dset As DataSet 
Private Da As SqlDataAdapter 

  Dim objRpt As New RepInventorySales /*used for crystal report*/
    Dset = New DataSet

Da = New SqlDataAdapter("sp_RepInventorySales '" & trim(Me.txtSalesOrderNo.Text) & "' ", Conn_DB)
 Da.Fill(Dset)
        If Dset.Tables(0).Rows.Count <> 0 Then
            objRpt.SetDataSource(Dset.Tables(0))

I tried to use IIF in query string in SqlDataAdapter to replace empty string to null but gives me error

            Da = New SqlDataAdapter("sp_RepInventorySales '" & Trim(Me.IIf(txtTextBox.Text Is Nothing, Null, txtTextBox.Text).Text) & "' ", Conn_DB)

This is the final solution. thank you for your help!

IF @SalesOrderNo = ''
    SET @SalesOrderNo = NULL
IF @ContainerType = ''
    SET @ContainerType = NULL

SELECT SalesOrderNo, ItemCode, ContainerNo, SealNo, ContainerType, OrderQty
FROM SalesOrderDetail
WHERE ( SalesOrderNo = @SalesOrderNo OR @SalesOrderNo IS NULL )
      AND
      ( ContainerType = @ContainerType OR @ContainerType IS NULL )

Possible duplicate of How to insert NULL into database if form field is empty using ASP.NET & VB — DeanOC
– DeanOC, Commented Nov 27, 2015 at 2:19

Community · Accepted Answer · 2017-05-23 11:58:53Z

1

Use DBNull.Value instead.

IF ELSE approach: (validate and assign before calling New SqlDataAdapter, therefore no need an IIF inside the sql adapter, just assign the value of textbox)

 If String.IsNullOrEmpty(txtSalesOrderNo.Text.ToString().Trim) = true Then
     txtSalesOrderNo.Text = DBNull.Value
 Else
     txtSalesOrderNo.Text = txtSalesOrderNo.Text
 End If

How to insert NULL into database if form field is empty

DBNull.Value

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Nov 27, 2015 at 1:48

japzdivino

1,7463 gold badges17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Allan Over a year ago

It tried IIF approach but still textbox is showing '' empty string Me.txtSalesOrderNo.Text = IIf(Me.txtSalesOrderNo.Text Is Nothing, DBNull.Value, Trim(Me.txtSalesOrderNo.Text)) Da = New SqlDataAdapter("sp_RepInventorySales '" & Trim(Me.txtSalesOrderNo.Text) & "' ", Conn_DB)

Allan Over a year ago

If Else approach gives me an error Value of type 'System.DBNull' cannot be converted to string

japzdivino Over a year ago

@Allan , alright .. just remove this .ToString() inside of this String.IsNullOrEmpty(txtSalesOrderNo.Text.ToString().Trim) , just replace it by String.IsNullOrEmpty(txtSalesOrderNo.Text.Trim) , because converting DB NULL values into string will get an error, i will modify the answer.

Allan Over a year ago

I remove as instructed but still the same eror ...If String.IsNullOrEmpty(Me.txtSalesOrderNo.Text.Trim) = True Then Me.txtSalesOrderNo.Text = DBNull.Value

Vivek S. Over a year ago

txtSalesOrderNo.Text = DBNull.Value ain't possible

Vivek S. · Accepted Answer · 2015-11-27 05:43:48Z

1

I've revamped your method with a solution, You can try this

  Sub fun()
        Dim Dset As DataSet
        Dim Da As PgSqlDataAdapter
        Dim strParam As Object
        Dim objRpt As New crptAccountlist
        Dset = New DataSet
        If Trim(txtSalesOrderNo.Text) <> vbNullString Then
            strParam = Trim(txtSalesOrderNo.Text)
        Else
            strParam = DBNull.Value
        End If
        Da = New PgSqlDataAdapter("", Conn_DB)
        With Da
            .SelectCommand.CommandType = CommandType.StoredProcedure
            .SelectCommand.CommandText = "sp_RepInventorySales"
            .SelectCommand.Parameters.AddWithValue("@SalesOrderNo", strParam)
        End With
        Da.Fill(Dset)
    End Sub

See this Demo

edited Nov 27, 2015 at 5:43

answered Nov 27, 2015 at 5:13

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Comments

Fabio · Accepted Answer · 2015-11-27 05:52:07Z

1

You will always get empty string because you passing parameter as hardcoded string inside single quote ..."sp_RepInventorySales '" & trim(Me.txtSalesOrderNo.Text) & "'...

Add parameter to the SqlDataAdapter.SelectCommand.Parameters collection properly, then you can use DBNull.Value

Da = New SqlDataAdapter("sp_RepInventorySales", Conn_DB)
DA.SelectCommand.CommandType = CommandType.StoredProcedure
'Parameter's type need to be Object because DBNull.Value cannot be converted to string
Dim paramValue as Object = DirectCast(DBNull.value, Object)
If String.IsNullOrEmpty(Me.txtSalesOrderNo.Text) = false Then
    paramValue = Me.txtSalesOrderNo.Text.Trim()
End If
Dim param As New SqlParameter With {.ParameterName = "@SalesOrderNo",
                                    .SqlDbType = SqlDbType.VarChar,
                                    .Value = paramValue}
DA.SelectCommand.Parameters.Add(param)
Da.Fill(Dset)

edited Nov 27, 2015 at 5:52

answered Nov 27, 2015 at 5:00

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

2 Comments

Vivek S. Over a year ago

Side note : What's the point of using @YourParameterName because parameter name of proc sp_RepInventorySalesis already visible in the question i.e @SalesOrderNo

Allan Over a year ago

This one works! although code is lengthy I just add another parameter.

rjps12 · Accepted Answer · 2015-11-27 05:24:13Z

0

just put the condition on your SP

   IF @SalesOrderNo = '' SET @SalesOrderNo = null 
   IF @ContainerType= '' SET @ContainerType= null 
   SELECT SalesOrderNo, ItemCode, ContainerNo, SealNo, ContainerType, OrderQty
   FROM  SalesOrderDetail 
   where SalesOrderNo = @SalesOrderNo AND ContainerType = @ContainerType

edited Nov 27, 2015 at 5:24

answered Nov 27, 2015 at 2:23

rjps12

6057 silver badges15 bronze badges

5 Comments

Allan Over a year ago

Single parameter is working but adding another parameter extracted no records.

rjps12 Over a year ago

what's the parameter you added? @Allan

Allan Over a year ago

the 2nd parameter is containertype which i just copy the same If and set statement and add where clause same as the salesorderno.

rjps12 Over a year ago

@Allan please see my edit. no need to add isnull on where.. then if there are no result, the problem is in your data

Allan Over a year ago

The If and set statements in SP. this is the shortest way to replace '' string to null. This forum is amazing!!! Thanks zzzzzz and to you guys who spend time answering my query... @Fabio@Japz Divino

Collectives™ on Stack Overflow

How to replace empty string parameter to Null in stored procedure and VB.net

4 Answers 4

5 Comments

Comments

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related