[RESOLVED] [2005] The ConnectionString property has not been initialized

[aconybeare]

I'm getting this weird error part of the way through the execution of my page, I know that the connection string exists and that earlier in the execution the connection opened successfully.

The ConnectionString property has not been initialized

Log File -

At the top of my page (page load)
Connection String: server=localhost;uid=myuid;pwd=mypwd;database=mydb
Connection State: Closed
In using block: Connection State: Open
Out Using block Connection State: Closed
Submission page: 3
before GetLabelsAndDefaults
Successful DB access here
after GetLabelsAndDefaults
before LoadAuthors
In LoadAuthors
In Using block
Connection state: Closed
Connection String: server=localhost;uid=myUID;pwd=myPwd;database=mydb
Attempt to open connection Failed (The ConnectionString property has not been initialized)

Here is the offending subs -

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sSubPage = clAct.Decrypt(clAct.DeNull(Request.QueryString("pg")))
        sMS_No = clAct.Decrypt(clAct.DeNull(Request.QueryString("ms_no")))
        sMtg_No = clAct.DeNull(Request.QueryString("Meeting_No"))
        sDoc_Type = clAct.DeNull(Request.QueryString("dt"))
 
        Call clAct.WriteToFile("Connection String: " & ConfigurationManager.ConnectionStrings("Sirius").ConnectionString & vbNewLine)
        Call clAct.WriteToFile("Connection State: " & myCon.State.ToString & vbNewLine)
        Using myCon
            If myCon.State = ConnectionState.Closed Then myCon.Open()
            Call clAct.WriteToFile("In using block: Connection State: " & myCon.State.ToString & vbNewLine)
        End Using
        Call clAct.WriteToFile("Out Using block Connection State: " & myCon.State.ToString & vbNewLine)
 
        If Page.IsPostBack = False Then
            clAct.LoginCheck() ' Ensure that the user is logged in.
 
            ' Get the menu
            litMenu.Text = menu.GetMenu(sSubPage.ToString).ToString
 
        Call clAct.WriteToFile("before GetLabelsAndDefaults" & vbNewLine)
        GetLabelsAndDefaults()
        Call clAct.WriteToFile("after GetLabelsAndDefaults" & vbNewLine)
        Call clAct.WriteToFile("before LoadAuthors" & vbNewLine)
        LoadAuthors() ' binds and loads the authors Datagrid
        Call clAct.WriteToFile("after LoadAuthors" & vbNewLine)
        End If
    End Sub
 
    Private Sub LoadAuthors()
        Call clAct.WriteToFile("In LoadAuthors" & vbNewLine)
        Try
            Using myCon
                Call clAct.WriteToFile("In Using block" & vbNewLine)
                Call clAct.WriteToFile("Connection state: " & myCon.State.ToString & vbNewLine)
                If myCon.State = ConnectionState.Closed Then
                    Call clAct.WriteToFile("Connection String: " & ConfigurationManager.ConnectionStrings("Sirius").ConnectionString & vbNewLine)
                    myCon.InitializeLifetimeService()
                    myCon.Open()
                End If
                Call clAct.WriteToFile("after connection state check" & vbNewLine)
                Call clAct.WriteToFile("before CreateDataTable" & vbNewLine)
                Dim dt As DataTable = CreateDataTable()
                Call clAct.WriteToFile("after CreateDataTable" & vbNewLine)
                Dim dr As DataRow
                Dim myReader As SqlDataReader
                Dim myCmd As New SqlCommand
                With myCmd
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "up_gGetMS_Authors"
                    .Connection = myCon
                    .Parameters.AddWithValue("@MS_No", sMS_No.ToString)
                    .Parameters.AddWithValue("@Version_No", Convert.ToInt16(1))
                    myReader = .ExecuteReader() ' Open the DataReader
                End With
 
                If myReader.HasRows = True Then
                    Do While myReader.Read()
                        dr = dt.NewRow()
                        dr(0) = Convert.ToString(clAct.DeNull(myReader("Author_ID")))
                        dr(1) = Convert.ToString(clAct.DeNull(myReader("User_ID")))
                        dr(2) = Convert.ToString(clAct.DeNull(myReader("First_Name")))
                        dr(3) = Convert.ToString(clAct.DeNull(myReader("Middle_Name")))
                        dr(4) = Convert.ToString(clAct.DeNull(myReader("Last_Name")))
                        dr(5) = Convert.ToString(clAct.DeNull(myReader("Affiliation")))
                        dr(6) = Convert.ToBoolean(clAct.DeNull(myReader("Presenting_Auth")))
                        dt.Rows.Add(dr)
                    Loop
                    myReader.Close()
                    myReader = Nothing
                Else
                    ' This page has not been saved before; add the submitting authors details into the Author datagrid by default
                    Dim bUCaseLName As Boolean = clAct.GetJnl_MS_Authors_UCase_Last_Name()
 
                    myReader = clAct.Find_Author(clAct.GetCookie("UserDetails", "Email").ToString)
                    If myReader.Read() Then
                        dr = dt.NewRow()
                        dr(0) = ""
                        dr(1) = Convert.ToString(clAct.DeNull(myReader("User_ID")))
                        dr(2) = Convert.ToString(clAct.DeNull(myReader("First_Name")))
                        dr(3) = Convert.ToString(clAct.DeNull(myReader("Middle_Name")))
                        dr(4) = IIf(bUCaseLName = True, Convert.ToString(clAct.DeNull(myReader("Last_Name"))).ToUpper, Convert.ToString(clAct.DeNull(myReader("Last_Name"))).ToString)
                        dr(5) = Convert.ToString(clAct.DeNull(myReader("Affiliation")))
                        dr(6) = Convert.ToBoolean(True)
                        dt.Rows.Add(dr)
                    End If
                    myReader.Close()
                    myReader = Nothing
                End If
                dgAuthors.DataSource = dt
                dgAuthors.DataBind()
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

[gep13]

Hello there,

Where do you set up myCon?

I am not one hundred percent sure about this, but it is my belief that the using, once it finishes it;s execution calls the dispose on the object in question, i.e. to tidy up resources. So when you try and access the myCon a second time, it no longer no what the connection string is.

The way that I approach this is to explicity state the connection string each time I connect to the database, the following code is for connecting to a MySql Database, but the theory is the same (oh, and it is in C# ):

public override List<CategoryDetails> GetCategories()
        {
            using (MySqlConnection cn = new MySqlConnection(this.ConnectionString))
            {
                MySqlCommand cmd = new MySqlCommand("ARTICLES_GetCategories", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetCategoryCollectionFromReader(ExecuteReader(cmd));
            }
        }

Hope this helps!!

Gary

[techgnome]

gep is right. the point of the Using syntax is to create a variable, instanciate it... then use it within that block, and when the End Using it encountered, the object is released and dispose is called on the object. This renders it useless.

-tg

[aconybeare]

Gep and Techgnome,

Thanks a bunch that's sorted it

Much appreciated

Cheers Al