0

I've seen a lot of similar issues to this but it isn't presenting in the same way as others have - essentially, I'm getting a System.NullReferenceException message when trying to add my SQL Connection string, stored in the Project Settings, into a Module that will allow me to pass SQL Server Queries and return results through a Public Property. Just to be clear, I'm not referencing the App.config file (as I can't seem to see ConfigurationManager when I try to import it from System.Configuration) - instead, I'm using the My.Settings.MasterDataConnectionString method for calling the property into the SQLConnection object.

Here is my current command;

Imports System.Data.SqlClient
Public Module SQLDataSetQuery
    Private _sqlConnection As SqlConnection
    Private _sqlCommand As New SqlCommand()
    Private _sqlReader As SqlDataReader
    Private _sqlResults As DataSet = New DataSet()

    Public ReadOnly Property GetResults(Optional ByVal KeepMode As Integer = 0) As DataSet
        Get
            If KeepMode = 0 Then
                Return _sqlResults
            ElseIf KeepMode = 1 Then
                Return _sqlResults
                _sqlResults.Clear()
            ElseIf KeepMode = 2 Then
                _sqlResults.Clear()
                Return Nothing
            Else
                Return Nothing
            End If
        End Get
    End Property

    Public Sub ExecuteQuery(ByVal sqlQuery As String)
        _sqlResults.Tables.Add()
        _sqlConnection.ConnectionString = My.Settings.MasterDataConnectionString()

        _sqlConnection.Open()
        _sqlCommand.CommandText = sqlQuery
        _sqlCommand.CommandType = CommandType.Text
        _sqlCommand.Connection = _sqlConnection

        _sqlReader = _sqlCommand.ExecuteReader()

        If _sqlReader.HasRows Then
            _sqlResults.Tables(0).Load(_sqlReader)
        End If

        _sqlReader.Close()
        _sqlCommand.Dispose()
        _sqlConnection.Close()
        _sqlConnection.Dispose()
    End Sub
End Module

I don't understand why this isn't working - I've tried using System.My.Settings.MasterDataConnectionString, I've tried using the same command in the codeblock above without the () and also I've tried using the command appended with .ToString() (as I internally postulated that since it's stored as a Connectionstring, it may be a different Object type) but they are all coming up with the same error. I've checked the Project Settings and they are saved thusly;

Name: MasterDataConnectionString
Type: (Connectionstring)
Scope: Application
Value: Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\MasterData\MasterData.mdf;Integrated Security=True;Connect Timeout=30

I can open the Database file from within the Server Explorer in Visual Studio (and this uses the same details if I'm not wrong?) without issue and run queries against the Database tables without issue which further compounds my confusion...

I can post the App.config file on request, but as previously mentioned, I'm not using this file to obtain the data - from looking at other questions (notably, here, here, here and here) it seems that they are not using the Solution Settings to reference the Connection String - therefore, unless I'm hugely mistaken (and being hugely stupid), the issues aren't related.

Can someone point me in the right direction here?

Community
  • 1
  • 1
Fredulom
  • 786
  • 1
  • 8
  • 20
  • Does IntelliSense fill in "MasterDataConnectionString" for you when you start typing "My.Settings.Ma"? – Andrew Morton Apr 29 '16 at 09:21
  • @AndrewMorton, yes it does :S – Fredulom Apr 29 '16 at 09:27
  • It looks like you forgot to instantiate an SqlConnection, so you might as well give it the connection string when you do that: `_sqlConnection = New SqlConnection(My.Settings.MasterDataConnectionString)`. – Andrew Morton Apr 29 '16 at 09:34
  • @AndrewMorton, ahh! Thanks Andrew :) I tried doing as you suggested but it threw another exception stating that the `ConnectionString has not been initialised`; instead, I just created a blank `SQLConnection` and then set the ConnectionString as above :) Could you add this as an answer so I can mark it as correct? – Fredulom Apr 29 '16 at 09:53
  • I'm happy to hear you solved the problem, but as your question has turned into [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/q/4660142/1115360) I am supposed to vote to close it as a duplicate rather than adding an answer. I think that is better than you deleting your question, with regard to SO reputation. – Andrew Morton Apr 29 '16 at 10:07

0 Answers0