[dba-VB] Connection closed

Well I keep getting advice to use stored procedures in SQL Server.  That is
what I was trying to do.  I created a stored procedure that would take table
/ field names and dynamically construct and execute an append query.  I got
that working, but I could not iterate a table from TSQL so I was trying to
run the SP from VB.Net.  THAT is where I was getting the failure.  In the
end I had to get it finished so I just replaced the SP with the equivalent
directly out in VB.Net and then executed an action query (in VB.Net).  That
action query was the equivalent of the stored procedure, but because it was
constructed and executed in VB.Net code I could then call it from another
VB.Net function. 

It all worked and I performed an entire deconstruction of a "wide" table
into a set of "tall" tables.  I was implementing a schema that Arthur
recommended.

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:28 AM
To: dba-vb at databaseadvisors.com
Subject: Re: [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 

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