[dba-VB] Connection closed

Any ideas out there? The ExecuteNonQuery complains that the connection is
closed.  If I break at that point (the catch) and do a lcnn.open it says the
connection is open.  Further it tells me the name of the server, the table
name etc.  Doesn't sound closed.


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, December 07, 2007 3:28 PM
To: dba-vb at databaseadvisors.com; dba-sqlserver at databaseadvisors.com
Subject: [dba-VB] Connection closed

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 

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com