VB.Net SQL data to text file

Question

I am trying to write the data that is retrieved from the SQL query into a text file in Visual Basic 2012. Here is some code - I just need a push in the right direction:

Imports System.Data.SqlClient
Imports System.IO

Public Class frmResults
    Dim cn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\bryan\Documents\TeamGFinalProject\BookCollection.MDF;Integrated Security=True;Connect Timeout=30")
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    Dim table As New DataTable("sales")

Private Sub frmResults_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    cmd.Connection = cn
End Sub

Private Sub BtnReport_Click(sender As Object, e As EventArgs) Handles BtnReport.Click
    cn.Open()
    cmd.CommandText = "select * from sales  where ord_date BETWEEN '" & dtpStart.Text & "' AND '" & dtpEnd.Text & "' Order BY ord_date;"
   
    Using sw As StreamWriter = New StreamWriter("Dates.txt")
        Dim reader As SqlDataReader = cmd.ExecuteReader()

        sw.WriteLine(reader)
        reader.Close()
    End Using

    cn.Close()
End Sub

End Class

Welcome to stackoverflow... Can you explain what is the problem with your code exactly? — The_Black_Smurf
– The_Black_Smurf, Commented Jul 30, 2015 at 21:04

Saragis · Accepted Answer · 2015-07-30 21:00:06Z

1

When reading from a SqlDataReader, you need to read a row of the result (SqlDataReader.Read), do something with it, and then move on to the next record until you've got what you need. You can use the SqlDataReader.GetValues method to get all column-values from the row at once.

Suggestion: work with parameters to build your commandtext (see here).

Private Sub BtnReport_Click(sender As Object, e As EventArgs) Handles BtnReport.Click       
    cn.Open()
    cmd.CommandText = "select * from sales  where ord_date BETWEEN '" & dtpStart.Text & "' AND '" & dtpEnd.Text & "' Order BY ord_date;"

    Using sw As StreamWriter = New StreamWriter("Dates.txt")
        Using reader As SqlDataReader = cmd.ExecuteReader()
            Dim values(reader.FieldCount - 1) As Object

            While reader.Read
                reader.GetValues(values)

                sw.WriteLine(String.Join(vbTab, values))
            End While
        End Using
    End Using

    cn.Close()

answered Jul 30, 2015 at 21:00

Saragis

1,7906 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Ridings Over a year ago

This worked for me, but where it reads "Dates.txt" I put the entire file path. "C:\Users\UserName\Desktop\Dates.txt". However, if the file path is too long you will need to make it a separate string and then use that variable in the stream writer.

vbnet3d · Accepted Answer · 2015-07-30 21:00:19Z

You are very close.

The SqlDataReader object is somewhat similar to a StreamReader or other reader objects. What you need to do is actually iterate through your data with the reader object. As you read, the fields are populated into the reader object. You can then write that data out. Here is a simple working example:

While (reader.Read)
   'Get data row by row
   Dim row_data(reader.FieldCount - 1) As String
   Dim i As Integer
   For i = 0 To reader.FieldCount - 1
       row_data(i) = reader(i).ToString
   Next

   'Build a single string (simplistic CSV style)
   String.Join(",",row_data)

   'Write to file
   sw.WriteLine(reader)
End While

This may not be the most efficient way of handling this, but it should give you a good idea of what is going on.

guest · Accepted Answer · 2015-07-30 21:15:50Z

0

You need to loop over your result set and write to the file.

Here is a quick example.

How to stream data from sqldatareader to a text file in VB.Net

answered Jul 30, 2015 at 21:15

guest

1

1 Comment

Josh Over a year ago

Thanks for your first answer! It's helpful in these situations to copy and paste information from your source material directly to SO and linking to the source. - This helps SO better index your answer for search, and for users to quickly get answers. Cheers!

Collectives™ on Stack Overflow

VB.Net SQL data to text file

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related