Saturday, October 29, 2016

Upload and process txt file - using .split "," & vbcrlf







Imports System.Data
Imports System.Data.SqlClient


Public Class Form1
    Dim myconn As SqlConnection
    Dim mycmd As SqlCommand
    Dim mydr As SqlDataReader
    Dim dtable As DataTable
    Dim x() As String
    Dim y() As String
    Dim val1 As String
    Dim val2 As String


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dprocess()


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim fd As OpenFileDialog = New OpenFileDialog()

        fd.Title = "Open File Dialog"
        fd.InitialDirectory = "C:\"
        fd.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
        fd.FilterIndex = 2
        fd.RestoreDirectory = True

        If fd.ShowDialog() = DialogResult.OK Then
            txtaddress.Text = fd.FileName
        End If
        '------------------------------------------------------------------------
        Try
            Dim objreader As New System.IO.StreamReader(txtaddress.Text)
            txtloaded.Text = objreader.ReadToEnd
            objreader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dview()
    End Sub

  
    Public Sub dview()

        Try
            myconn = New SqlConnection(My.Settings.dSetting)
            If myconn.State = System.Data.ConnectionState.Closed Then
                myconn.Open()
            End If

            mycmd = New SqlCommand("select * from table1 ", myconn)
            mydr = mycmd.ExecuteReader()
            'MessageBox.Show("(" & xcounter & ") affected")
            Dim dtable As New DataTable()
            dtable.Load(mydr)

            DataGridView1.DataSource = dtable
            DataGridView1.Refresh()


            myconn.Close()
            mycmd.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "view")
            myconn.Close()
            mycmd.Dispose()
        End Try

    End Sub

    Public Sub dprocess()


        Dim n As Integer = 0
        Dim m As Integer = 0

        Try
            If Len(txtloaded.Text) <> 0 Then
                x = (txtloaded.Text.Split(vbCrLf))
                While x.Length <> n
                    x(n) = x(n).Replace(vbCr, "").Replace(vbLf, "")
                    y = x(n).Split(",")

                    dprocess2()

                    ' MsgBox(x(n))
                    n = n + 1
                End While

            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, "process")
        End Try

        Array.Clear(x, 0, x.Length)
        Array.Clear(y, 0, y.Length)


    End Sub

    Public Sub dprocess2()

        dsearch()


        Dim xcounter As Integer = 0
        Try
            myconn = New SqlConnection(My.Settings.dSetting)
            If myconn.State = System.Data.ConnectionState.Closed Then
                myconn.Open()
            End If

            y(1) = Val(y(1)) + Val(val1)
            y(2) = Val(y(2)) + Val(val2)

            mycmd = New SqlCommand("update table1 set val2='" & y(1) & "' , val3='" & y(2) & "'  where val1 ='" & y(0) & "'", myconn)
            xcounter = mycmd.ExecuteNonQuery
            MessageBox.Show("(" & xcounter & ") affected")

            myconn.Close()
            mycmd.Dispose()

            dview()



        Catch ex As Exception
            MessageBox.Show(ex.Message, "update")
            myconn.Close()
            mycmd.Dispose()


        End Try

    End Sub



    Public Sub dsearch()
        Dim i As Integer = 0
        Dim rcount As Integer = 0
        Dim ifound As Boolean = False



        Try
            rcount = DataGridView1.RowCount
            DataGridView1.MultiSelect = False

            While i < rcount And ifound = False
                DataGridView1.Rows(i).Selected = True

                If DataGridView1.SelectedRows(0).Cells(0).Value = y(0) Then

                    val1 = DataGridView1.SelectedRows(0).Cells(1).Value
                    val2 = DataGridView1.SelectedRows(0).Cells(2).Value

                    DataGridView1.CurrentCell = DataGridView1(0, i)

                    ifound = True

                End If

                i = i + 1

            End While


            
        Catch ex As Exception
            MessageBox.Show(ex.Message, "found")
        End Try

        If ifound = False Then MessageBox.Show("no record found", "")
    End Sub

End Class

No comments: