jwcolby
jwcolby at colbyconsulting.com
Fri Dec 7 23:32:01 CST 2007
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