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?