Object reference not set to an instance of an object Error in creating connection string

Question

I want to create a connection string and use it to all forms in my application, but is showing error.

My code below throws the error: "Object reference not set to an instance of an object"

Imports System.Data.SqlClient
Imports System.Data.OleDb

Module sqlserverconnectionstring
    Public connsql As SqlConnection    
    Dim connstr As String     

    Public Sub sqlserverconnection()
        Try
             connstr = "Provider=SQLOLEDB.1; Data Source=" & My.Settings.sqlservername & ";Initial Catalog=" & My.Settings.sqlDBname & ";Password=" & My.Settings.sqlPswd & ";User ID=" & My.Settings.sqlUserName
             connsql.Open()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)

        End Try
    End Sub
    Public Sub sqlserverdisconnectdatabase()
        Try
            connsql.Close()
        Catch myerror As SqlClient.SqlException

        End Try
    End Sub
End Module

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Visual Vincent, Oct 26 '16 at 06:03

score 1 · Answer 1 · answered Oct 26 '16 at 06:04

The connsql field is never initialized, so it is always null (Nothing in VB.NET).

You'll want this:

Sub Connect()

    Try

        Me.connstr = "Provider=SQLOLEDB.1; Data Source=" & My.Settings.sqlservername & ";Initial Catalog=" & My.Settings.sqlDBname & ";Password=" & My.Settings.sqlPswd & ";User ID=" & My.Settings.sqlUserName

        Me.connsql = New SqlConnection( Me.connstr )
        Me.connsql.Open()

    Catch ex1 As InvalidOperationException
        MsgBox( ex1.Message )
    Catch ex2 As SqlException
        MsgBox( ex2.Message )
    Catch ex3 As ConfigurationErrorsException
        MsgBox( ex3.Message )

    End Try

End Sub

I note that it will be a better idea to store the entire connection string in your App.config file instead of building it at runtime (what if the user has an SSPI connection, for example?):

In your App.config file:

<configuration>
    <connectionStrings>
        <add name="db" connectionString="it goes here" />
    </connectionStrings>
</configuration>

Usage:

Me.connsql = new SqlConnection( ConfigurationManager.ConnectionStrings("db").ConnectionString )

score 0 · Answer 2 · answered Oct 26 '16 at 06:06

0

You need to create an object of the SqlConnection first, and set the connectionString befor opening the connection

connstr = "Provider=SQLOL ..."
connsql = new SqlConnection(connstr)   
connsql.Open()

answered Oct 26 '16 at 06:06

Tim Schmidt

1,233
12
26

score 0 · Answer 3 · answered Oct 26 '16 at 06:24

connstr = "Provider=SQLOLEDB.1; Data Source=" & My.Settings.sqlservername & ";Initial Catalog=" & My.Settings.sqlDBname & ";Password=" & My.Settings.sqlPswd & ";User ID=" & My.Settings.sqlUserName connsql = New SqlConnection(connstr) connsql.Open()

solved Thanks to all.....

Object reference not set to an instance of an object Error in creating connection string

3 Answers3