Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a app to manage workflow at my work company, and a few days ago one table stopped loading. I have searched for help on various platforms and so far found none. I have a private 2016 SQL server that a connect to.

My Connection SQL Command:
Public Shared Sub Load(ByRef MyCollect As List(Of PaintSheetItem), Filter As String, OrderBy As String)
    MyCollect = New List(Of PaintSheetItem)

    Dim CMDValue As String = $"SELECT * FROM {TableName}"
    If Filter IsNot Nothing Then CMDValue &= $" WHERE {Filter}"
    If OrderBy IsNot Nothing Then CMDValue &= $" ORDER BY {OrderBy}"
    CMDValue &= ";"

    If SQLCon.State = System.Data.ConnectionState.Closed Then SQLCon.Open()

    Using CMD As New SqlCommand(CMDValue, SQLCon)
        Using reader As SqlDataReader = CMD.ExecuteReader()
            Try
                If reader.HasRows Then
                    While reader.Read()
                        Dim LF As New PaintSheetItem With {.Id = reader(0),
                        .PSDId = reader(1),
                        .KeyNo = reader(2),
                        .BaseKeyNo = reader(3),
                        .Description = reader(4),
                        .QTYOrd = reader(5),
                        .QTYShip = reader(6),
                        .NoCTNs = reader(8),
                        .Length = reader(9),
                        .LineWeight = reader(10),
                        .CustomColor = CompensateForSQLNull(FieldType.String, reader(11)),
                        .Notes = CompensateForSQLNull(FieldType.String, reader(12))}

                        LF.HasChanges = False
                        MyCollect.Add(LF)
                    End While
                End If
            Catch ex As Exception
                If Not SQLCon.State = System.Data.ConnectionState.Closed Then SQLCon.Close()
            Finally
                reader.Close()
                CMD.Dispose()
                If Not SQLCon.State = System.Data.ConnectionState.Closed Then SQLCon.Close()
            End Try
        End Using
    End Using

    If Not SQLCon.State = System.Data.ConnectionState.Closed Then SQLCon.Close()
End Sub



The Errors I get are:
1) A severe error occurred on the current command. The results, if any, should be discarded.

2) The relationship between the two objects cannot be defined because they are attached to different ObjectContext object. {This loades the data into a listbox, it hase a parent table/listbox and that parent has its parent table/listbox}

3) ExecuteReader requires an open and available Connection. The connection's current state is open. {This error message is a bit confusing}

4) On the Server I get this Error: The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

What I have tried:

I have tried using SQLLight to my load my data, and just sql commands. both don't load. It was working without issues for about 15 years and now this. There are eight computers connecting to the server, each updating, viewing and manipulating data. The thought that the server might have issues crossed my mind for it's over 6 years old. I don't know what to make of the server error, does that signifies I need to implant an await statement? not sure how to implement those into that shared class.
Posted
Updated 17-Sep-22 6:47am
Comments
Richard Deeming 20-Sep-22 8:55am    
You've written a helper method which forces you to write code which is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

1 solution

Quote:
1) A severe error occurred on the current command. The results, if any, should be discarded.

I don't have a solution for this.

Quote:
2) The relationship between the two objects cannot be defined because they are attached to different ObjectContext object. {This loades the data into a listbox, it hase a parent table/listbox and that parent has its parent table/listbox}

This sounds like you're using Entity Framework and you have more than one context instance alive at the same time, both of which are using the same database. You have to Dispose of one context before you create another one.

Are you making the mistake of creating a context for use everywhere is the class? DO NOT DO THAT! ALWAYS create a context instance as late as possible, do your work, then dispose of the context as early as possible.

Quote:
3) ExecuteReader requires an open and available Connection. The connection's current state is open. {This error message is a bit confusing}

If you have a DataReader open on a connection, you cannot do any other database operations on that same connection. If you're going to have a reader open, you have to have another connection to the database to do whatever work needs to be done while the reader is alive.

Quote:
4) On the Server I get this Error: The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

I'm going to take a wild guess and say that you're mixing Entity Framework code with straight SQL code you posted and are not managing connections properly. I can't say what you need to do to fix this because it would require a much deeper examination of a lot more of your code.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900