ExecuteReader: Connection property has not been initialized

Question

User-226995580 posted

Guys i am really not much experienced in coding, you might call me a noob so please a kind request to everyone answering. please be as detailed as possible. thank you

ExecuteReader: Connection property has not been initialized

Protected Sub btnLogin_Click(ByVal sender As Object, 
               ByVal e As System.EventArgs) Handles btnLogin.Click
    Dim name = txtUserName.Text.Trim
    Dim Pass = txtPassword.Text.Trim

    Try

        Dim conn As SqlConnection
        conn = DataBaseFunc.OpenConnection()

        Dim sqlSt As String = "select * FROM AdminLog WHERE AdminName = '" & name & "' AND AdminPassword = '" & Pass & "'"
        Dim SelCmd As SqlCommand = New SqlCommand(sqlSt, conn)

        Dim RrecSet As SqlDataReader

        RrecSet = SelCmd.ExecuteReader()

        If RrecSet.Read() Then
            Session("Admin") = RrecSet("AdminName")
            Response.Redirect("~/AdminPages/AdminHome.aspx")
        Else
            lblMsg.Text = "<p>Rong Username or Password</p>"
            txtUserName.Text = ""
            txtPassword.Text = ""
            txtUserName.Focus()

        End If
    Catch ex As Exception
        lblMsg.Text = ex.Message
    End Try
End Sub







DataBaseFunc

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic


Public Class DataBaseFunc
    Public Shared Sub EnsureOpenConnection(ByRef conn As SqlConnection)
        Try
            If conn.State <> Data.ConnectionState.Open Then
                conn.Open()
            End If
        Catch ex As Exception
        End Try
    End Sub


    Public Shared Function OpenConnection() As SqlConnection
        Try
            Dim ConnString As String = "server =.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BloodDb.mdf;Integrated Security=True;User Instance=True"
            Dim conn As New SqlConnection(ConnString)
            If conn.State <> Data.ConnectionState.Open Then
                conn.Open()
            End If
            Return conn
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class

Answer 1

User281315223 posted

You could probably simplify this a bit so that you wouldn't have to worry about initializing your connection within a separate function, as that can sometimes complicate things more than necessary :

Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click  
    Try
        ' Use a using statement to ensure that your connection is properly closed / disposed of '
        Using conn = New SqlConnection("server =.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BloodDb.mdf;Integrated Security=True;User Instance=True")
             ' Build your query (using parameters) '
             Dim query = "SELECT * FROM AdminLog WHERE AdminName = @Name AND AdminPassword = @Password"
             
             ' Build your command '
             Using comm = New SqlCommand(query, conn)
                   ' Open your connection '
                   conn.Open()

                   ' Add your parameters '
                   comm.Parameters.AddWithValue("@Name",txtUserName.Text.Trim)
                   comm.Parameters.AddWithValue("@Password",txtPassword.Text.Trim)

                   Using reader = comm.ExecuteReader()
                        If RrecSet.Read() Then
                              Session("Admin") = RrecSet("AdminName")
                              Response.Redirect("~/AdminPages/AdminHome.aspx")
                        Else
                              lblMsg.Text = "<p>Wrong Username or Password</p>"
                              txtUserName.Text = ""
                              txtPassword.Text = ""
                              txtUserName.Focus()
                        End If  
                   End Using
             End Using
        End Using
    Catch ex As Exception
        lblMsg.Text = ex.Message
    End Try
End Sub

Answer 2

User-226995580 posted

do i need to make changes in class too or only for the aspx page?

Answer 3

User281315223 posted

Using the above approach, you shouldn't need an external class at all as everything that you need to do is handled within the ASPX code.

Answer 4

User-226995580 posted

getting error in

If RrecSet.Read() Then

Session("Admin") = RrecSet("AdminName")

Answer 5

User-226995580 posted

getting rrecSet is not declared error

Answer 6

User281315223 posted

This was a typo on my part, as it should actually be 'reader' and not 'RrecSet' :

Using reader = comm.ExecuteReader()
       If reader.Read() Then
                 Session("Admin") = reader("AdminName")
                 Response.Redirect("~/AdminPages/AdminHome.aspx")
       Else
                 lblMsg.Text = "<p>Wrong Username or Password</p>"
                 txtUserName.Text = ""
                 txtPassword.Text = ""
                 txtUserName.Focus()
       End If  
End Using

Additionally, since you really are likely only wanting a single record to be returned, you could change your query as follows :

' Build your query (using parameters) '
Dim query = "SELECT TOP 1 * FROM AdminLog WHERE AdminName = @Name AND AdminPassword = @Password"

Answer 7

User-226995580 posted

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The Server was not found or was not accessible. Verify the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified) 

Answer 8

User281315223 posted

This is likely just an issue with your actual connection string and accessing your database.

You might want to consider reviewing over some of the recommendations mentioned in this related discussion.