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()

What would cause this error? Problem signature: Problem Event Name: CLR20r3 Problem Signature 01: deer daq.exe Problem Signature 02: 5.3.6.35271 Problem Signature 03: 502d89fa Problem Signature 04: mscorlib Problem Signature 05: 4.0.0.0 Problem Signature 06: 4ee9a9f1 Problem Signature 07: 12df Problem Signature 08: 13 Problem Signature 09: System.ObjectDisposedException OS Version: 6.1.7601.2.1.0.256.48 Locale ID: 1033 Additional Information 1: 0a9e Additional Information 2: 0a9e372d3b4ad19135b953a78882e789 Additional Information 3: 0a9e Additional Information 4: 0a9e372d3b4ad19135b953a78882e789 Read our privacy statement online: http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409 If the online privacy statement is not available, please read our privacy statement offline: C:\Windows\system32\en-US\erofflps.txt

I am writing parameterized data to SQL at 1Hz from several test cell computers (maximum 16, currently running 5 or less). The code works fine though I was losing connection sometimes when using SQL Developer edition. We installed the full SQL server last week and all was well until I got the following error this morning. What would cause this error? Should I leave the connection open to avoid extra overhead in opening and closing it every second? How should I recover from this error? Close the connection, reopen, and retry? 10:55:21 FlightRecorder System.InvalidOperationException: Internal connection fatal error. at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DEER_DAQ.modFunctions.BuildSQLDataString() Thanks.

I added a check for connection state to work around that problem. Now I was testing again on a computer we will call B, and I saved my Visual Studio project on a computer we will call A. A is also running the instance of SQL Server Developer Edition which B is connected to. Simultaneously, B threw an object reference error on the Executing Non Query statement. Could this be because A (running Windows 7 pro) was busy and didn't manage SQL Server well enough? Can I expect this problem to go away when we get SQL Server 2012 running on a dedicated Windows Server machine? Thanks.

I just caught this error while testing it out (text message alert on error to allow me to check it quickly). Why would I lose connection? Because I'm developing this on Developer Edition? ExecuteNonQuery requires an open and available Connection. The connection's current state: Broken.

The connection remains open for the duration of the program execution. I can only speculate that the line that calls for ExecuteNonQuery is the line that fails since its call shows up in the error stack trace.

I have this system set up and working, but it will only run for so long (less than a few hours) before it throws an error. The error is shown below. Any idea what would cause this? Why would I get an object reference error when it has already written many lines before? I need to know if there is a SQL problem or a problem with my software. 9:41:28 FlightRecorder System.NullReferenceException: Object reference not set to an instance of an object. at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DEER_DAQ.modFunctions.BuildSQLDataString()

Following your link (which was for an older control called dataview) I found this link: http://msdn.microsoft.com/en-us/library/fbk67b6z.aspx And generated this code which works: sqlAdp = New SqlDataAdapter(sql, conn) Dim commandBuilder As New SqlCommandBuilder(sqlAdp) ' Populate a new data table and bind it to the BindingSource. Dim table As New DataTable() table.Locale = System.Globalization.CultureInfo.InvariantCulture sqlAdp.Fill(table) dgvSummary.DataSource = table I didn't have a table in my old code. Thanks!

I am using the same query to plot data and to show it in a datagridview. I get data to plot, but it won't display on my datagrid view. The DGV just stays grey. I've looked at a bunch of examples only and I can't find the problem. Any ideas? dgvSummary.AutoGenerateColumns = True sqlCmd = New SqlCommand(sql, conn) sqlAdp = New SqlDataAdapter(sqlCmd) sqlDs = New DataSet sqlAdp.Fill(sqlDs, "Development") dgvSummary.DataSource = sqlDs

This is more of what I was looking for: Time: Now.TimeOfDay.ToString Date: DateTime.Today Thanks, however!

What exact date object should I used when inserting a line of data into SQL such that SQL will understand it for ORDER BY, etc.?

I would prefer to use a real data format that SQL will recognize so that I can perform date functions later on.

I want to insert time into one column and date into another column. SQL is giving me hell trying to do this because my formats or data types my not be meshing between SQL and VB. This is what I've tried: (i have tried other methods as well) If Not sqlCmd.ExecuteScalar Then sqlCmd.CommandText = "CREATE TABLE " & TableName & " (" & _ "[RowCount] BIGINT NOT NULL PRIMARY KEY, " & _ "Count INT NOT NULL, " & _ "RunNumber INT NOT NULL, " & _ "CurrentCycle INT NOT NULL, " & _ "CurrentMode INT NOT NULL, " & _ "TimeInMode INT NOT NULL, " & _ "CurrentTime VARCHAR(8) NOT NULL," & _ "CurrentDate DATE NOT NULL," & _ "[Version] VARCHAR(10) NOT NULL);" sqlCmd.ExecuteNonQuery() Columns = "[RowCount],Count,RunNumber, CurrentCycle,CurrentMode,TimeInMode,CurrentTime, CurrentDate, [Version]" Data = "0,0,0,0,0,0,['" & Format(Now.Hour, "00") & ":" & Format(Now.Minute, "00") & ":" & Format(Now.Second, "00") & "']," & DateTimePicker1.Value.Date & ",['0.0.0']" cmdString = "INSERT INTO " & TableName & "(" & Columns & ") VALUES ( " & Data & ")" sqlCmd = New SqlCommand(cmdString, sqlConn) sqlCmd.ExecuteNonQuery() End If

I have tested this on my Development computer and the code is fairly simple. Columns = "[RowCount]" Data = RowCount 'RowCount will be the primary key and will always increment RowCount += 1 For count = 1 To lstChannels.Items.Count ' I will substitute my data arrays for the items in this test list Columns &= ",[" & lstChannels.Items.Item(count - 1) & "]" Data &= "," & Rnd(10) Next Dim cmdString As String = "INSERT INTO Development (" & Columns & ") VALUES ( " & Data & ")" sqlCmd = New SqlCommand(cmdString, sqlConn) sqlCmd.ExecuteNonQuery() Sound about right? One more question: When I add another channel to the table, all values before that addition for that channel are NULL. Is it possible to make this default to zero since these are all numeric values?

Thanks for all of your help.