SQL Connection String Error: Object reference not set to an instance of an object

Question

I am trying a version 2 of my file upload question. This time I wanted to test SqlDbType versus OleDbType without a stored procedure. When testing this example, I get an error

Object reference not set to an instance of an object

on this line:

Dim connection As String = System.Configuration.ConfigurationManager.ConnectionStrings("Provider=""****"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd).ConnectionString

I don't understand why I get this error (first time using SqlDbType)

My table types are as follows:

byte: long raw
file_name: varchar2

Protected Sub Insert_Click(sender As Object, e As EventArgs) Handles Insert.Click

  Dim extension As String = System.IO.Path.GetExtension(FileUpload1.FileName) ' filename extension
  Dim filePath As String = Server.MapPath("~\") 'shows full path
  Dim fullPath As String = filePath + FileUpload1.FileName 'shows full path plus name
  Dim fileName As String = FileUpload1.FileName

  Dim fs As Stream = FileUpload1.PostedFile.InputStream
  Dim br As New BinaryReader(fs)
  Dim bytes As Byte() = br.ReadBytes(fs.Length)    

  Dim strQuery As String = "INSERT into t_classifieds " & "(addate, category, username, phonenbr, email, description, fulldescription, location, byte, file_name)" _
                               & " values ( val_date, val_category, val_user, val_phone, val_email, val_shortDes, val_longDes, val_location, val_byte, val_name )"

        Dim ClassifiedStr As New SqlCommand(strQuery)
        ClassifiedStr.Parameters.Add("val_date", SqlDbType.Date).Value = DateText.Text
        ClassifiedStr.Parameters.Add("val_category", SqlDbType.VarChar).Value = CategoryList.Text
        ClassifiedStr.Parameters.Add("val_user", SqlDbType.VarChar).Value = UserText.Text
        ClassifiedStr.Parameters.Add("val_phone", SqlDbType.VarChar).Value = PhoneText.Text
        ClassifiedStr.Parameters.Add("val_email", SqlDbType.VarChar).Value = EmailText.Text
        ClassifiedStr.Parameters.Add("val_shortDes", SqlDbType.VarChar).Value = ShortText.Text
        ClassifiedStr.Parameters.Add("val_longDes", SqlDbType.VarChar).Value = longText.Value
        ClassifiedStr.Parameters.Add("val_location", SqlDbType.VarChar).Value = LocationText.Text '[8]
        ClassifiedStr.Parameters.Add("val_byte", SqlDbType.Binary).Value = bytes
        ClassifiedStr.Parameters.Add("val_name", SqlDbType.Varchar).Value = fileName
        InsertUpdateData(ClassifiedStr)

End Sub

Private Sub InsertUpdateData(ClassifiedStr As SqlCommand)
    'Throw New NotImplementedException

    Dim connection As String = System.Configuration.ConfigurationManager.ConnectionStrings("Provider=""***"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd).ConnectionString

    Dim con As New SqlConnection(connection)

    ClassifiedStr.CommandType = CommandType.Text

    ClassifiedStr.Connection = con

    con.Open()

    ClassifiedStr.ExecuteNonQuery()

    con.Close()

    con.Dispose()
End Sub

When I debug line by line I can see it is working:

? bytes

{Length=9297}
(0): 137
(1): 80
(2): 78
(3): 71
(4): 13
(5): 10
(6): 26
(7): 10
(8): 0
(9): 0
(10): 0
(11): 13
(12): 73
(13): 72
(14): 68
(15): 82
(16): 0
(17): 0
(18): 2
(19): 176
(20): 0
(21): 0
(22): 0
(23): 128
(24): 8
(25): 2
(26): 0
(27): 0
(28): 0
(29): 124
(30): 195
(31): 205
(32): 101
(33): 0
(34): 0
(35): 0
(36): 9
(37): 112
(38): 72
(39): 89
(40): 115
(41): 0
(42): 0
(43): 14
(44): 196
(45): 0
(46): 0
(47): 14
(48): 196
(49): 1
(50): 149
(51): 43
(52): 14
(53): 27
(54): 0
(55): 0
(56): 0
(57): 7
(58): 116
(59): 73
(60): 77
(61): 69
(62): 7
(63): 223
(64): 6
(65): 23
(66): 12
(67): 49
(68): 40
(69): 151
(70): 192
(71): 200
(72): 240
(73): 0
(74): 0
(75): 0
(76): 7
(77): 116
(78): 69
(79): 88
(80): 116
(81): 65
(82): 117
(83): 116
(84): 104
(85): 111
(86): 114
(87): 0
(88): 169
(89): 174
(90): 204
(91): 72
(92): 0
(93): 0
(94): 0
(95): 12
(96): 116
(97): 69
(98): 88
(99): 116
< More... (The first 100 of 9297 items were displayed.) >

Answer 1

ConfigurationManager.ConnectionStrings is an array of strings returned by the system reading your application configuration file (the app.config or web.config). Your connection string should be stored there and given a key property to retrieve it from your application

Dim connection As String = ConfigurationManager.ConnectionStrings("myConnection").ConnectionString

and in your app.config you need to have these lines

<connectionStrings>
  <add name="myConnection" connectionString="user id=xxxx;data source=yyyyyy;password=zzzzzz"
  providerName="System.Data.OracleClient" />
</connectionStrings>

Of course this means that your connection string is hard coded in the app.config and thus you can't change it easily at run-time.

A possible alternative is the class OracleConnectionStringBuilder

With this class is possible to write a piece of code like this

Dim builder As New OracleConnectionStringBuilder
builder("Data Source") = strDatabase 
builder("User ID") = strUserID 
builder("Password") = strPassword
Console.WriteLine(builder.ConnectionString)