Unable to Regain Connection After Losing It With Parameterized Insert

[TexasAggie]

In my application I am writing data to an instance of SQL Server Express 2008. I have an intermittent problem of losing connection and when this happens I have to restart the application in order to get everything going again. This is a big nuisance because it is a data logging application and I am losing data (have text file redundancy but prefer SQL data).

 

With each program execution I verify that the table for a particular test bench exists on the remote server and the local server. The local server is a buffer until I close the program in which case the data is bulk copied to the remote server running full SQL. Then I check to make sure that columns exist on both tables for each channel I am logging. If any column does not exist, I create it. Then I write data to the local instance at 1 Hz. If I encounter a glitch in that process I can reconnect but when I try to use the SQLCommand it says the connection state is broken.

 

Please see the code snippets below and help me to figure out how I can reconnect seamlessly.

 

First I connect to both servers.

               ' Connect to the remote server
               SQLConnString = "Server=IP\PC;Database=Data;Integrated Security=True;Connection Timeout=30;"

               Using SQLDataConnTemp As SqlConnection = New SqlConnection(SQLConnString)
                   SQLDataConnTemp.Open()


                   If SQLTableExists() Then
                       Dim cmdString As String = "SELECT MAX([RecID]) FROM " & TestCell
                       SQLCmd = New SqlCommand(cmdString, SQLDataConnTemp)
                       NumberOfRowsRemote = SQLCmd.ExecuteScalar + 1
                   Else
                       NumberOfRowsRemote = 0
                   End If                
               End Using

               ' Connect to local server
               SQLConnString = "Server=(local);Database=Data;Integrated Security=True;Connection Timeout=30;"
               SQLBufferConn = New SqlConnection(SQLConnString)
               SQLBufferConn.Open()

 

Now I check to make sure tables exist on both servers. Local check shown because SQLBufferCmd is the problem.

 

    Public Function SQLBufferTableExists() As Boolean
       SQLBufferCmd = SQLBufferConn.CreateCommand
       SQLBufferCmd.CommandText = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & TestCell & "') SELECT 1 ELSE SELECT 0"
       If SQLBufferCmd.ExecuteScalar <> 1 Then Return False Else Return True
   End Function

 

Now I build the insert string:

 

   Using SQLDataConnTemp As SqlConnection = New SqlConnection(SQLConnString)
           SQLDataConnTemp.Open()

           Try
               SQLCmd = SQLDataConnTemp.CreateCommand

   sqlInsertString = "INSERT INTO " & TestCell & " "
               sqlInsertString &= "([RecID],[Count],[RunNumber],[CurrentCycle],[CurrentMode],[TimeInMode],[CurrentTime],[CurrentDate],[Project Number],[Project Leader],[Client],[Version],ModeTable"

               sqlParameterNames &= "@RecID,@Count,@RunNumber,@CurrentCycle,@CurrentMode,@TimeInMode,@CurrentTime,@CurrentDate,@ProjectNumber,@ProjectLeader,@Client,@Version,@ModeTable"



               For a = 0 To AIChannelCount - 1
                   SQLCmd.CommandText = "IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'" & AIChannels(a).Name & _
                                           "' and Object_ID = Object_ID(N'" & TestCell & "')) BEGIN ALTER TABLE [" & _
                                           TestCell & "] ADD [" & AIChannels(a).Name & "] real END"
                   SQLCmd.ExecuteNonQuery()

                   SQLBufferCmd.CommandText = "IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'" & AIChannels(a).Name & _
                           "' and Object_ID = Object_ID(N'" & TestCell & "')) BEGIN ALTER TABLE [" & _
                           TestCell & "] ADD [" & AIChannels(a).Name & "] real END"
                   SQLBufferCmd.ExecuteNonQuery()

                   sqlInsertString &= ",[" & AIChannels(a).Name & "]"
                   sqlParameterNames &= ",@" & AIChannels(a).Name.Replace(" ", "").Replace("-", "").Replace("/", "") & Trim(a.ToString)
               Next
....


           Try
               sqlInsertString &= ")" & sqlParameterNames & ")"

               SQLBufferCmd = New SqlCommand(sqlInsertString, SQLBufferConn)
               SQLBufferCmd.Prepare()

               ' Add parameters to the SQL Command 
               SQLBufferCmd.Parameters.Add("@RecID", Data.SqlDbType.Int)
               SQLBufferCmd.Parameters.Add("@Count", Data.SqlDbType.Int)

....

 

Then I write data to the buffer at 1 Hz. This is why my problem is. This is where the error is encountered and where I can reconnect but then access to SQLBufferCmd fails.

 

           SQLBufferCmd.Parameters("@RecID").Value = SQLRowCount
           SQLBufferCmd.Parameters("@RunNumber").Value = RunNumber
           SQLBufferCmd.Parameters("@CurrentTime").Value = Now.TimeOfDay.ToString

                   [color="Red"][/color]Using SQLBufferConn As SqlConnection = New SqlConnection("Server=(local);Database=DEERDAQData;Integrated Security=True;Connection Timeout=30;")
                       SQLBufferConn.Open()
                       SQLBufferCmd.ExecuteNonQuery()      
                   End Using[/color]

 

After I encounter the first error, it will enter this using statement again and it is able to open the connection, but as soon as I try to access SQLBufferCmd it errors and says the connection state is broken. Any ideas?

 

18:58:09 BuildSQLDataString - Executing Non Query: 0 - System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)

at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at DQ.modFunctions.BuildSQLDataString()