[dba-VB] Connection closed

I thought you were USING vb.net, John.  I wondered why you were coding
it that way.

Charlotte Foust 

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, December 10, 2007 8:24 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Connection closed

When I tried to set the provider it barfed saying that "provider is not
a valid parameter" or some such.  This is actually closed.  I wrote the
whole damned thing in two tiny little functions in VB.Net. 


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 Charlotte
Foust
Sent: Monday, December 10, 2007 11:04 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [dba-VB] Connection closed

John,

Where are you setting your provider?

Charlotte Foust 

-----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 12:27 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