Using a stringbuilder as a parameter to a stored procedure and returning a dataset

Question

I have a couple of problems relating to one of the parameters passing a number of values to a stored procedure and the result that comes back converting to dataset in order for this to be bound to an MS ReportViewer.

The error I am getting says that the the reader is closed.

My relevant code snippet is:

    Dim _listOfSites As New StringBuilder()
    Dim _resultDataSet As DataSet = New DataSet

    Using _conn as New SqlConnection()

     _conn.ConnectionString = _connString

       Try

          For i as Integer = 0 To _sites.Count - 1
            _listOfSites.Append(_sites(i))
              If _sites.Count > 1 Then
                _listOfSites.Append(",")
              End If
          Next

          _conn.Open()

          Dim _sqlCommand as SqlCommand = New SqlCommand("GetResults", _conn)

          _sqlCommand.Parameters.Add("@Sites", SqlDbType.Varchar).Value = _listOfSites
          _sqlCommand.Parameters.Add("@Date", SqlDbType.Date).Value = _date

         Dim _reader as SqlDataReader = _sqlCommand.ExecuteReader

         While _reader.Read
               _resultDataSet.Load(_reader, LoadOption.PreserveChanges, New String() {"RegionalResults"})
         End While
         _reader.Close()

Can anyone please help?

Thanks

score 0 · Accepted Answer · answered Nov 19 '12 at 20:30

0

Looks like you should not call _reader.Read as _resultDataSet.Load do it by itself and it could close the SqlDataReader. So instead of

 Dim _reader as SqlDataReader = _sqlCommand.ExecuteReader

 While _reader.Read
           _resultDataSet.Load(_reader, LoadOption.PreserveChanges, New String() {"RegionalResults"})
 End While
 _reader.Close()

Just write

 Using  _reader as SqlDataReader = _sqlCommand.ExecuteReader
      _resultDataSet.Load(_reader, LoadOption.PreserveChanges, New String() {"RegionalResults"})
 End Using

Hope that helps

answered Nov 19 '12 at 20:30

Artyom

3,507
2
34
67

Thanks for this. I will give it a try. However, do you know how to pass a stringbuilder, which can contain one or more values to a stored procedure to execute the query – Andy5 Nov 19 '12 at 20:45
That is impossible, only sql db type parameters could be passed to a stored procedure (a clr procedure also don't allow it - http://msdn.microsoft.com/en-us/library/ms131094.aspx). You could make it a string and pass to sp. – Artyom Nov 20 '12 at 06:31

Using a stringbuilder as a parameter to a stored procedure and returning a dataset

1 Answers1