VB.Net sql data to text file

Question

Am Trying to write the data that is retrevied from the sql query into a text file in visual basic 2012 here is some code i have 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, Jul 30 '15 at 21:04

score 1 · Answer 1 · answered Jul 30 '15 at 21:00

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()

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. — Scott Ridings, Apr 05 '19 at 06:28

score 0 · Answer 2 · answered Jul 30 '15 at 21:00

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.

score 0 · Answer 3 · answered Jul 30 '15 at 21:15

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 '15 at 21:15

guest

1

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! – Josh Jul 30 '15 at 21:22

VB.Net sql data to text file

3 Answers3