Capturing text from a stored procedure

Hot Topic

How to save data from a form to a xml file?
Strange problem with mouse scrolling in Visual Studio 6
Grouping Radio Buttons
additional overloads for My.Computer.FileSystem.[DeleteFile|CopyFile|MoveFile|...] to handle a list of files
The type initializer threw an exception. - For no reason
ClickOnce Local Data
Change username of printjob
Adding a resource
Cint method problem
Cleaning up my code

Visual Basic

The type initializer threw an exception. - For no reason
Printing Images
VB 6.o enterprise edition crashed when I tried to view the form
How to detect CANCEL with the FolderBrowserDialog
Compiling VB6 from command line
Problems with Batch file
topmost process
Add text to a text box from a module
Clipboard - How to determine file is cut or copied ?
Saving User Settings

Capturing text from a stored procedure

The following stored procedure:

SELECT 'FROM:/SA- CLBSCODE' AS Transfer

FROM vwImport

GO

correctly displays three records in Query Analyzer.

But the file produced by the following code:

Dim stExport As String = ""
Dim stExportFile As String = ""

For Each dr As DataRow In ManForexCLSDataSet.Tables("spExport").Rows
For Each dc As DataColumn In ManForexCLSDataSet.Tables("spExport").Columns
stExport = stExport & dr.Item(dc.ToString).ToString & vbCrLf & vbCrLf
Next
stExport = stExport & vbCrLf & vbCrLf
Console.WriteLine(stExport)
Next

stExportFile = "G:\doc\Output.txt"
My.Computer.FileSystem.WriteAllText(stExportFile, stExport, False)

is empty.

The code jumps from the first For Each... (.Rows) to the

stExport line without looping.

Is this code correct for streaming the results of a stored procedure



Answer this question

Capturing text from a stored procedure

  • GregPow

    Where is your DataAdapter.Fill code You need to fill the dataset with your DataAdapter then check the actual DataSet and not the results returned in the query view. See what tables are there and what rows each table has, etc.

  • Adrian C

    I would double check that the stored procedure is getting executed. You need a DataAdapter and you need to fill it iirc to get a DataSet. If you are in doubt check the contents of the DataSet to see that the right tables exist. You can then drill down to the Rows collection for each datatable present.

    Also depending upon how many rows that you have you should probably change
    stExport = stexport & to use a StringBuilder
    Dim blExport as new StringBuilder()
    blExport.Append(dr.Item(dc.ToString()).ToString()).append(vbcrlf).append(vbcrlf)

    Each time you do stExport = stExpoprt & it's making a ne wcopy of the string. Depending upon how often this method is called and how many rows there are this could have a noticeable negative impact on overall performance.

    Hope this helps.

  • jorisp

    I finally got this to work. Here is the successful code for a stored procedure that returns a single column:

    Dim stExport As String = ""
    Dim stExportFile As String = ""
    Dim cnExport As New SqlConnection(stConnection)
    Dim ExportCommand As SqlCommand = New SqlCommand("spExport", cnExport)
    ExportCommand.CommandType = CommandType.StoredProcedure

    cnExport.Open()

    Dim reader As SqlDataReader = ExportCommand.ExecuteReader()

    Do While reader.Read()
    stExport = stExport & reader.GetString(0) & vbCrLf & vbCrLf
    Loop

    reader.Close()
    cnExport.Close()

    stExportFile = "C:\Output.txt"
    My.Computer.FileSystem.WriteAllText(stExportFile, stExport, False)

  • Chumpski

    I have previewed the data from the stored procedure in the Data Source window, and it appears to be correct there. I've been told I need a dataview, and then that I can parse the rows directly. Is there a standard, default method that will stream the results of a stored procedure to text

    The end result would be about 50 records with very long rows of concatenated text. I tried pointing the dataset code to other tables in the dataset, but the result is always the same: no records, and the code jumps from the first For Each... statement out of the loop.
  • Capturing text from a stored procedure
Answer this question