[dba-VB] Connection closed

jwcolby jwcolby at colbyconsulting.com
Fri Dec 7 14:27:30 CST 2007


I am trying to run the following code:

    Private Const cstrCnn2 As String = "Data Source = Azul;Initial Catalog =
HSID;Integrated Security=SSPI"

    Private Sub btnMigrateData_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnMigrateData.Click
        Dim lrow As DataRow
        Dim tbl As DataTable
        tbl = clsDataAccess.mtblGet("tblHSIDFldName")

        Dim lcnn As New SqlConnection(cstrCnn2)
        Dim cmdFldToVertical As New SqlCommand("USP_FldToVertical", lcnn)
        cmdFldToVertical.CommandType = CommandType.StoredProcedure
        For Each lrow In tbl.Rows
            If CInt(lrow("FLD_ID")) > 2 Then
                Debug.Print(lrow("Fld_ID").ToString & ", " &
lrow("Fld_Name").ToString)
                cmdFldToVertical.Parameters.Add("@SrcTblName",
SqlDbType.NVarChar, 100).Value = ""
                cmdFldToVertical.Parameters.Add("@FldNameID",
SqlDbType.Int).Value = CInt(lrow("Fld_ID"))
                cmdFldToVertical.Parameters.Add("@SrcFldName",
SqlDbType.NVarChar, 100).Value = lrow("Fld_Name").ToString
                Try
                    cmdFldToVertical.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        Next
    End Sub

I am getting an error on the try that the connection is closed:

"ExecuteNonQuery requires an open and available Connection. The connection's
current state is closed."

I did not get an error when I dimmed the cmd object which passes in the
connection object.  How do I check that the connection is open, or
troubleshoot why it is not open?



John W. Colby
Colby Consulting
www.ColbyConsulting.com 




More information about the dba-VB mailing list