As the title states, I am currently trying to store values from my database in a list. In SSMS I have verified that my query pulls the data that I require, but when it comes to vb.net and putting those values into a list it appears to be failing. Below I have copied the code I am currently working with.

            Try
                Dim sb As New StringBuilder
                With sb
                    .Append("SELECT TypeID FROM EnrollmentType AS a ")
                    .Append("INNER JOIN Enrollment AS e ON a.TypeDescription = e.EnrollmentType ")
                    .Append("WHERE AccountNumber = @AccountNumber")
                End With
                Using connEType As New SqlConnection(ConfigurationManager.ConnectionStrings("Blah").ToString)
                    Using cmdEType As New SqlCommand(sb.ToString, connEType)
                        cmdEType.Parameters.AddWithValue("@AccountNumber", strgAccountNum)
                        connEType.Open()
                        Using sdrEType As SqlDataReader = cmdEType.ExecuteReader
                            If sdrEType.HasRows Then
                                While sdrEType.Read
                                    For i = 0 To dtEType.Rows.Count - 1
                                        listEType.Add(dtEType.Rows(i).Item("TypeID"))
                                    Next
                                End While
                            End If
                        End Using
                        connEType.Close()
                    End Using
                End Using
                lblSKUDescription.Text = dtEType.Rows.Count
            Catch ex As Exception
                Throw ex
                lblSKUDescription.Text = "Oops"
            End Try

Even though in SSMS my query works, here nothing is added to the list and my datatable has no rows of data. This piece of code is currently in the page load event because I figure I would want this list populated as soon as possible, I don't know if this matters. I would also note that there is another sql connection in the page load event, however I have changed any variable names that would be conflicting, I don't know if this matters either, but felt I would mention it.

Any and all help in this matter would be greatly appreciated and if I need to supply additional information I can.

CodePudding user response：

Your primary issue is that you are not actually using the sdrEType reader, instead you are just using the dtEType datatable, which hadn't been loaded. It's unclear why the For loop was necessary in the first place.

• You should use a multi-line string, rather than a StringBuilder, it;s much clearer.
• The Catch makes no sense, as the Throw ex wipes the stack trace, and does not run the following line. You should just remove it. If you really need the Catch, use Throw not Throw ex
• If sdrEType.HasRows is unnecessary, the While will sort that out.
• connEType.Close() is unnecssary, the Using will do that.
• Specify parameter types and lengths explicitly.
• Qualify each column in the SQL with its table alias/prefix.

Dim query As String = "
SELECT a.TypeID
FROM EnrollmentType AS a
INNER JOIN Enrollment AS e ON a.TypeDescription = e.EnrollmentType
WHERE e.AccountNumber = @AccountNumber;
"

Using connEType As New SqlConnection(ConfigurationManager.ConnectionStrings("Blah").ToString)
    Using cmdEType As New SqlCommand(query, connEType)
        cmdEType.Parameters.Add("@AccountNumber", SqlDbType.VarChar, 100).Value = strgAccountNum
        connEType.Open()
        Using sdrEType As SqlDataReader = cmdEType.ExecuteReader
            While sdrEType.Read
                listEType.Add(sdrEType("TypeID"))
            End While
        End Using
    End Using
End Using
lblSKUDescription.Text = dtEType.Rows.Count