Ni antara kerja aku...

Sekarang aku tgh develop sistem guna VB.NET untuk baca data dr .txt file format (text tu dari PTPTN) dan insert into database b4 process the data.
Ni coding untuk :-
1.Get .txt file from h/disk
2.Upload into server (C: temp)
3.Read the data from text file
4.Insert into gridview
5.From gridview, add the data into database

Sebenarnya, byk lagi process, aku jek terkial2 sbb VB.net ni baru..kadang2 aku buat sampai nak muntah, kadang2 aku buat sampai naik benci..kadang aku buat aku syok sendiri..Masalahnya, PM aku asyik kejar jek...BILA NAK SIAP?? itu yg buat aku paling BENCI..kehekehekeh

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim TabSize As Integer
Dim Line As String
Dim Table As DataTable
Dim Row As DataRow
Dim com As New SqlCommand

Dim nums As Integer

'insert data into table
com.CommandText = "insert into tablename(FileName,AutoFileName)" & _
" values(@FileName,@AutoFileName)"
com.Connection = conn

com.Parameters.Add(New SqlParameter("@FileName", SqlDbType.VarChar, 50))
com.Parameters("@FileName").Value = txtFile.Text.Trim
com.Parameters.Add(New SqlParameter("@AutoFileName", SqlDbType.VarChar, 50))
com.Parameters("@AutoFileName").Value = Label2.Text.Trim
com.ExecuteNonQuery()

Dim dr As SqlDataReader
com.CommandText = "select * from tablename where filename = '" & txtFile.Text & "'"
com.Connection = conn
dr = com.ExecuteReader
While dr.Read
lblFileID.Text = dr("FileID")
End While
nums = Convert.ToInt32(lblFileID.Text, 16)
Table = CreateDataTable()
TabSize = 15
Try

Dim sr As StreamReader
sr = File.OpenText("C:\Temp\" & txtFile.Text & "." & "txt")
Do
Line = sr.ReadLine

Row = Table.NewRow()
Row("FileID") = nums
Row("Kelompok") = Left(Line, 10)
Row("KodUniversiti") = Mid(Line, 11, 3)
Row("KumPelajar") = Mid(Line, 14, 2)
Row("RujPTPTN") = Mid(Line, 16, 14)
Row("StudentID") = Mid(Line, 30, 14)
Row("NRICNo") = Mid(Line, 44, 12)
Row("Nama") = Mid(Line, 56, 80)
Row("Waran") = Mid(Line, 136, 8)
Row("Status") = Mid(Line, 144, 2)
Row("AmountPotongan") = Mid(Line, 146, 6)
Row("NilaiBersih") = Mid(Line, 152, 8)
Row("Pembayaran") = Mid(Line, 160, 8)
Row("JanaBayaran") = Mid(Line, 168, 8)
Row("AkaunPelajar") = Mid(Line, 176, 14)
Table.Rows.Add(Row)
Loop Until Line Is Nothing
sr.Close()

Dim cm As New SqlCommand

GridView1.DataSource = Table
GridView1.DataBind()

Catch ex As Exception
Label1.Text = ex.Message.ToString
End Try

Dim SQLCopy As SqlBulkCopy
SQLCopy = New SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction)
SQLCopy.DestinationTableName = "PTPTN"
SQLCopy.ColumnMappings.Add("FileID", "FileID")
SQLCopy.ColumnMappings.Add("Kelompok", "Kelompok")
SQLCopy.ColumnMappings.Add("KodUniversiti", "KodUniversiti")
SQLCopy.ColumnMappings.Add("KumPelajar", "KumpulanPelajar")
SQLCopy.ColumnMappings.Add("RujPTPTN", "RujukanPTPTN")
SQLCopy.ColumnMappings.Add("StudentID", "NoPelajar")
SQLCopy.ColumnMappings.Add("NRICNo", "NRICNo")
SQLCopy.ColumnMappings.Add("Nama", "Nama")
SQLCopy.ColumnMappings.Add("Waran", "Waran")
SQLCopy.ColumnMappings.Add("Status", "Status")
SQLCopy.ColumnMappings.Add("AmountPotongan", "AmountPotongan")
SQLCopy.ColumnMappings.Add("NilaiBersih", "NilaiBersih")
SQLCopy.ColumnMappings.Add("Pembayaran", "TarikhBayaran")
SQLCopy.ColumnMappings.Add("JanaBayaran", "TarikhJanaBayaran")
SQLCopy.ColumnMappings.Add("AkaunPelajar", "NoAkaun")
Try
SQLCopy.WriteToServer(Table)
Catch ex As Exception
Label1.Text = ex.Message.ToString
End Try

End Sub

Private Function CreateDataTable() As DataTable
Dim table As New DataTable
Dim Column As DataColumn
Try

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "FileID"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Kelompok"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "KodUniversiti"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "KumPelajar"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "RujPTPTN"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "StudentID"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "NRICNo"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Nama"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Waran"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Status"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "AmountPotongan"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "NilaiBersih"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Pembayaran"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "JanaBayaran"
table.Columns.Add(Column)

Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "AkaunPelajar"
table.Columns.Add(Column)

Catch ex As Exception
Label1.Text = ex.Message.ToString
End Try
Return table