CODEWITHSUNDEEP
.netsql-servervb.net
David Peterson
David Peterson

Reputation: 552

write sql server dataset to text file

I've called a stored procidure from my database and it gets some parameters correctly. Now i want to write that Recordset to a text file how should I do This?

Dim fs, a As Object
fs = CreateObject("Scripting.FileSystemObject")

'Creating Folder to temporarily hold the upload files'
'MkDir("D:\temp_output\")'

'Create the text file'
a = fs.CreateTextFile("D:\temp_output\1output.txt", True)

'Opening connection for recordsets'
adoConn = New ADODB.Connection
adoConn.Open(Connect_str)


adoCmd = New ADODB.Command
recordset = New ADODB.Recordset

With adoCmd
    'Input parameter'
    SelectedProject = "mydb"

    .CommandText = "thesp"
    .CommandType = CommandTypeEnum.adCmdStoredProc
    .Parameters.Append(.CreateParameter("@sdate", DataTypeEnum.adDecimal, ParameterDirectionEnum.adParamInput, , 20140420))
    .Parameters.Append(.CreateParameter("@Enddate", DataTypeEnum.adDecimal, ParameterDirectionEnum.adParamInput, , 20140419))
    .Parameters(0).Precision = 8
    .Parameters(1).Precision = 8

    .ActiveConnection = adoConn

End With

'Create recordset'
recordset = adoCmd.Execute

now how to write the results to the text file(1)?

Upvotes: 0

Views: 155

Answers (1)

Bjørn-Roger Kringsjå
Bjørn-Roger Kringsjå

Reputation: 9991

Why not use the built-in .net framework classes? Here's a simple example using a DataSet. It gives you the ability to both read and write data as XML.

Imports System.Data.SqlClient


Using connection As New SqlConnection("your_cs")
    connection.Open()
    Using command As SqlCommand = connection.CreateCommand()
        command.CommandText = "thesp"
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("@sdate", 20140420D)
        command.Parameters.AddWithValue("@Enddate", 20140419)
        Using adapter As New SqlDataAdapter(command)
            Using ds As New DataSet()
                adapter.Fill(ds)
                ds.WriteXml("D:\temp_output\1output.txt")
            End Using
        End Using
    End Using
End Using

Upvotes: 1

Related Questions