Datareader to Datatable gets stuck...

[Guest]

The following Function used to return a DataSet.

After much reading regarding being able to use sorting within the table of
a gridview, I rewrote my function so that it returns a datatable.

All compiles well, but it always times out at the

'Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo Or
CommandBehavior.CloseConnection)
Dim reader As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)

I tried CommandBehavior.KeyInfo Or CommandBehavior.CloseConnection but it
always gets stuck. What gives?

=====================================================

Public Shared Function GetActionList_T(ByVal sSL As String, _
ByVal sDir As String, _
ByVal sSup As String, _
ByVal sLocName As String, _
ByVal sDeptName As String, _
ByVal sGenOrSpec As String, _
ByVal sOrderBy As String) As
DataTable

'This function toggles between General Or Specific class courses
'It produces the crosstable Training Action list
'This list grows to the right depending on the 'Max' value of
courses that an individual must
'take. This 'MaxCourses' value is determined inside the procedure
based on
'the choices the user makes


Dim query As String

Dim connection As SqlConnection = GetMembershipConnection()


'qActionGrid_GEN creates a select statement that only includes
parameters that were used

Select Case sGenOrSpec
Case "Gen"
query = "qActionGrid_GEN"
Case "Spec"
query = "qActionGrid_SPEC"
End Select

Dim cmd As New SqlCommand(query, connection)
cmd.CommandType = CommandType.StoredProcedure

'Set parameter variables for the values collected on the find page
Dim pSL As New SqlParameter("@SL", SqlDbType.VarChar, 50)
Dim pDIR As New SqlParameter("@DIR", SqlDbType.VarChar, 50)
Dim pSup As New SqlParameter("@Sup", SqlDbType.VarChar, 50)
Dim pLocName As New SqlParameter("@LocName", SqlDbType.VarChar, 100)
Dim pDeptName As New SqlParameter("@DeptName", SqlDbType.VarChar, 100)
Dim pOrderBy As New SqlParameter("@OrderBy", SqlDbType.VarChar, 100)

'only set variables for the textboxes that were filled in
With cmd
'clear any existing parameters
.Parameters.Clear()
'Add the parameters only if the textbox value was filled in
If Len(sSL) <> 0 Then
pSL.Direction = ParameterDirection.Input
pSL.Value = sSL
.Parameters.Add(pSL)
End If

If Len(sDir) <> 0 Then
pDIR.Direction = ParameterDirection.Input
pDIR.Value = sDir
.Parameters.Add(pDIR)
End If

If Len(sSup) <> 0 Then
pSup.Direction = ParameterDirection.Input
pSup.Value = sSup
.Parameters.Add(pSup)
End If

If Len(sLocName) <> 0 Then
pLocName.Direction = ParameterDirection.Input
pLocName.Value = sLocName
.Parameters.Add(pLocName)
End If

If Len(sDeptName) <> 0 Then
pDeptName.Direction = ParameterDirection.Input
pDeptName.Value = sDeptName
.Parameters.Add(pDeptName)
End If

If Len(sOrderBy) <> 0 Then
pOrderBy.Direction = ParameterDirection.Input
pOrderBy.Value = sOrderBy
.Parameters.Add(pOrderBy)
End If
End With



'use a reader to fill a datatable with the results of the query


connection.Open()
'Dim reader As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.CloseConnection)
Dim reader As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim schema As DataTable = reader.GetSchemaTable()
Dim columns(schema.Rows.Count - 1) As DataColumn
Dim column As DataColumn
'Build the schema for the table that will contain the data.
For i As Integer = 0 To columns.GetUpperBound(0)
column = New DataColumn
column.AllowDBNull = CBool(schema.Rows(i)("AllowDBNull"))
column.AutoIncrement = CBool(schema.Rows(i)("IsAutoIncrement"))
column.ColumnName = CStr(schema.Rows(i)("ColumnName"))
column.DataType = CType(schema.Rows(i)("DataType"), Type)
If column.DataType Is GetType(String) Then
column.MaxLength = CInt(schema.Rows(i)("ColumnSize"))
End If
column.ReadOnly = CBool(schema.Rows(i)("IsReadOnly"))
column.Unique = CBool(schema.Rows(i)("IsUnique"))
columns(i) = column
Next i


Dim data As New DataTable
Dim row As DataRow
data.Columns.AddRange(columns)
'Get the data itself.
While reader.Read()
row = data.NewRow()
For i As Integer = 0 To columns.GetUpperBound(0)
row(i) = reader(i)
Next i
data.Rows.Add(row)
End While
reader.Close()
Return data
End Function

 

[Cor Ligthert [MVP]]

Jonefer,

If you really want an answer, than sent beside your problem a piece of code
that shows the problem, not a program or a part of your program. To revise
your program, you have to tell the tarif you want to pay for that.

Cor

 

[Guest]

Cor, what does 'thAn senT beside your problem' mean??

I think what you're trying to say is thEn senD along with your problem a
piece of code."

And Cor, I did do just specifically what you asked for

1) I showed the part of my code that has the problem and even described it
in the title. "Datareader to Datatable gets stuck"

2) The piece of the code that has the problem is:
'Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo Or
CommandBehavior.CloseConnection)
Dim reader As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)

3) Then I sent (now it appropriate to say 'Sent' because it is past tense)
the entire procedure so that someone could suggest whether or not this is
appropriate despite the fact that it compiles with no errors.


I realize that replying in similar manner to your insensitive comments is
blunt.
Thus, you will prove to me how much of an ego you have if I don't get an
answer now.

There is nothing more frustrating than recieving an e-mail that shows
someone answered my question, only to find sensless criticism.

If you don't like how the person asks a question, then *NOT 'than'* do not
respond.
If you do respond, make sure you are completely clear and that your grammar
is correct.

Lighten up Mr. Lighthert.

 

[Cor Ligthert [MVP]]

What do you think beside "then send"

Let's wait how many answers you get that brings you to the solution it was
just an advise, however your behaviour about that thAn senD were you know
what it means set you in a special corner for me..

Cor