0

hi doing i try to import data from sql server to text file bust i have some probleam like:---


2/15/2010 12:00:00 AM
=====================================================
POSITION GAMENO START ENDDAY AMOUNT TOTALSALE
=====================================================

#1                 223      92       92      $1       $0


   #1       121      92       92      $1       $0


   #2       222      115       115      $1       $0


   #2       121      14       14      $5       $0


   #3       333      20       20      $3       $0


   #8       232      10       10      $3       $0


   #100       121      5       5      $1       $0

=====================================================  
                                               $0

when i add some data in table and when run query i data have more then one desit then out put change in txt file i like to my all data in center align format so all data on under data field you can see "position " field one desit #1
#2
#100 when three desit i want all data in line my vb coding like:--

Imports System.Data.SqlClient
Imports System.IO.StreamWriter






Public Class Form1
    Private printFont As Font
    Private streamToPrint As System.IO.StreamReader
    Private Shared filePath As String
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim position As Integer
        Dim gameno As Integer
        Dim start As Integer
        Dim endday As Integer
        Dim amount As Integer
        Dim totasale As Integer
        Dim SUM As Integer
        Dim A As Date = Today
        'creat a text file first
        Dim oWrite As System.IO.StreamWriter
        oWrite = System.IO.File.CreateText("C:\Users\sunny\Desktop\sale.txt") 'connection string and mysqlconnect
        'oWrite = System.IO.File.WriteAllLines("C:\Users\sunny\Desktop\sale.txt", )
        Dim connstring As String = "Data Source=sunny-pc;Initial Catalog=master;Integrated Security=True"
        Dim conn As New SqlConnection(connstring) 'create selection query
        Dim selectSQL As String = "Select position,rtrim(gameno),rtrim(start),rtrim(endday),rtrim(amount),TOTALSALE = (ENDDAY-START)*AMOUNT from sale  ORDER BY POSITION ASC "
        Dim selectSQL1 As String = "SELECT SUM((ENDDAY-START)*AMOUNT)AS 'TOTAL INSTANT SALE' FROM sale "


        Dim cmd As New SqlCommand((selectSQL + selectSQL1), conn)

        'set cmd property
        cmd.CommandType = CommandType.Text 'open databaseconn.open()'create a new reader and execute the reader
        conn.Open()
        Dim myreader As SqlDataReader = cmd.ExecuteReader

        'Dim myreader As SqlDataReader = cmd.ExecuteReader
        oWrite.WriteLine()
        oWrite.Write(A)
        oWrite.WriteLine()
        oWrite.Write("=====================================================  ")
        oWrite.WriteLine()
        oWrite.Write("POSITION  ")
        oWrite.Write("GAMENO    ")
        oWrite.Write("START   ")
        oWrite.Write("ENDDAY  ")
        oWrite.Write("AMOUNT  ")
        oWrite.Write("TOTALSALE ")
        oWrite.WriteLine()
        oWrite.Write("=====================================================  ")
        oWrite.WriteLine()
        While myreader.Read

            position = myreader.GetValue(0)
            gameno = myreader.GetValue(1)
            start = myreader.GetValue(2)
            endday = myreader.GetValue(3)
            amount = myreader.GetValue(4)
            totasale = myreader.GetValue(5)
            SUM = myreader.GetValue(5)
            oWrite.WriteLine()
            If Len(position.ToString) = 1 Then
                oWrite.Write("   #00" & position & "       " & gameno & "      " & start & "       " & endday & "      $" & amount & "       $" & totasale)
            Else
                If Len(position.ToString) = 2 Then
                    oWrite.Write("   #0" & position & "       " & gameno & "      " & start & "       " & endday & "      $" & amount & "       $" & totasale)
                Else
                    If Len(position.ToString) = 3 Then
                        oWrite.Write("   #" & position & "       " & gameno & "      " & start & "       " & endday & "      $" & amount & "       $" & totasale)
                    Else

                        If Len(start.ToString) = 1 Then
                            oWrite.Write("   #" & position & "       " & gameno & "      00" & start & "       " & endday & "      $" & amount & "       $" & totasale)
                        Else
                            If Len(start.ToString) = 2 Then
                                oWrite.Write("   #" & position & "       " & gameno & "      0" & start & "       " & endday & "      $" & amount & "       $" & totasale)
                            Else
                                If Len(start.ToString) = 3 Then
                                    oWrite.Write("   #" & position & "       " & gameno & "      " & start & "       " & endday & "      $" & amount & "       $" & totasale)
                                Else

                                End If
                            End If
                        End If
                    End If
                End If
            End If
            oWrite.WriteLine()

            oWrite.WriteLine()



        End While
        oWrite.Write("=====================================================  ")
        oWrite.WriteLine()
        oWrite.Write("                                               $" & SUM)
        'close file
        oWrite.Close()



    End Sub
End Class

Edited by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by __avd
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.