parameter.add question - How do you handle null or empty values?

[emaduddeen]

Hi Everyone,

Can you tell me how to handle processing when parameter.add adds a parameter in which the value is null or empty?

In my code I add this parameter:

  objCommand.Parameters.Add(New OleDbParameter("@parameter7", pParameter7))

When this line executes I get an error that states @parameter has no default value.

            objCommand.ExecuteNonQuery()

Ok, well at least I thought I had it covered in here but I must be missing something:

  Public Function ExecuteInsertChangeDelete(ByVal pSqlQuery As String,
                                              Optional ByVal pParameter1 As String = Nothing,
                                              Optional ByVal pParameter2 As String = Nothing,
                                              Optional ByVal pParameter3 As String = Nothing,
                                              Optional ByVal pParameter4 As String = Nothing,
                                              Optional ByVal pParameter5 As String = Nothing,
                                              Optional ByVal pParameter6 As String = Nothing,
                                              Optional ByVal pParameter7 As String = Nothing,
                                              Optional ByVal pParameter8 As String = Nothing,
                                              Optional ByVal pParameter9 As String = Nothing,
                                              Optional ByVal pParameter10 As String = Nothing
                                              ) As Boolean

I thought that String = Nothing gives them a default value.

As a quick fix so I can import the data I made sure if any parameter was null I would pass it a single space character but is that the best way to handle that scenario?

Thanks.

Truly,
Emad

 

[jmcilhinney]

Nothing is used in VB code to represent null. To represent null in data going to or coming from a database, you use DBNull.Value.

Also, rather than this:

objCommand.Parameters.Add(New OleDbParameter("@parameter7", pParameter7))

do this:

objCommand.Parameters.AddWithValue("@parameter7", pParameter7)

This will handle your Nothing to DBNull conversion:

objCommand.Parameters.AddWithValue("@parameter7", If(CObj(pParameter7), DBNull.Value))

 

[emaduddeen]

Hi,

Sorry for not replying.

Thanks for the code.

I used the cObj and it works nicely.

Truly,
Emad