locked
ExecuteReader: Connection property has not been initialized RRS feed

  • 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
    Saturday, June 13, 2015 11:51 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 13, 2015 12:15 PM
  • 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"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 13, 2015 2:34 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 13, 2015 8:58 PM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 13, 2015 12:15 PM
  • User-226995580 posted

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

    Saturday, June 13, 2015 12:36 PM
  • 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.

    Saturday, June 13, 2015 12:53 PM
  • User-226995580 posted

    getting error in

    If RrecSet.Read() Then

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

    Saturday, June 13, 2015 2:29 PM
  • User-226995580 posted

    getting rrecSet is not declared error

    Saturday, June 13, 2015 2:33 PM
  • 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"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 13, 2015 2:34 PM
  • 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) 

    Saturday, June 13, 2015 2:49 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 13, 2015 8:58 PM