Solved

How To: save Null values pass through storedproc parameters

Posted on 2004-10-07
3
808 Views
Last Modified: 2012-06-27
Private Function InsertEvaluation( _
        ByVal prmCustomerID As String, _
        ByVal prmID As String, _
        Optional ByVal prmSatisfaction As String = "", _
        Optional ByVal prmWillingToPay As String = "" _
        ) As Boolean

  Dim cmd As OleDbCommand = New OleDbCommand(sp, dbConn.Conn)
            cmd.CommandType = CommandType.StoredProcedure

...
...
            Dim CID As OleDbParameter = New OleDbParameter("p_ID", OleDbType.VarWChar)
             CID.Value = prmCustomerID
            cmd.Parameters.Add(pID)
...
..
end function

if i use
Private Function InsertEvaluation( _
        ByVal prmCustomerID As String, _
        ByVal prmID As String, _
        Optional ByVal prmSatisfaction As object= DBNull.value, _
        Optional ByVal prmWillingToPay As object= DBNull.value _
        ) As Boolean

i get a message that i have to use a constant value.,
0
Comment
Question by:Jerry_Pang
3 Comments
 
LVL 6

Assisted Solution

by:zulu_11
zulu_11 earned 40 total points
ID: 12246504
Private Function InsertEvaluation( _
        ByVal prmCustomerID As String, _
        ByVal prmID As String, _
        Optional ByVal prmSatisfaction As String = Convert.ToString( nothing ) , _
        Optional ByVal prmWillingToPay As String = Convert.ToString( nothing ) _
        ) As Boolean

try this
0
 
LVL 20

Assisted Solution

by:ihenry
ihenry earned 40 total points
ID: 12246623
Hi,

Overload the method would solve your problem,

    Private Function InsertEvaluation( _
            ByVal prmCustomerID As String, _
            ByVal prmID As String _
            ) As Boolean

        InsertEvaluation(prmCustomerID, prmID, DBNull.Value, DBNull.Value)
    End Function

    Private Function InsertEvaluation( _
            ByVal prmCustomerID As String, _
            ByVal prmID As String, _
            ByVal prmSatisfaction As Object, _
            ByVal prmWillingToPay As Object _
            ) As Boolean

    End Function

HTH
0
 
LVL 3

Accepted Solution

by:
skpatra earned 45 total points
ID: 12247271
Change the signature as

Private Function InsertEvaluation( _
        ByVal prmCustomerID As String, _
        ByVal prmID As String, _
        Optional ByVal prmSatisfaction As String = Nothing, _
        Optional ByVal prmWillingToPay As String = Nothing _
        ) As Boolean


Now, in the body add this:

If prmSatisfaction Is Nothing Then
    prmSatisfaction = DBNull.Value
End If

If prmWillingToPay Is Nothing Then
    prmWillingToPay = DBNull.Value
End If
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now