Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
'Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.OleDb
Imports System.IO

Imports System.Data.SqlClient
Public Class frm_export_csv
    Dim cmd As SqlCommand = Nothing
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Private Sub frm_export_csv_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        max_min()
        date_select()
        '  FillGrid()
        If conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If
    End Sub
    Private Sub FillGrid()
        txt_total.Text = ""
        Try
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim cmdtext As String
            If rbt_time.Checked = True Then
                'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (tp.dataentry_date between '" & dtpFromDate.Text & "' and '" & dtpToDate.Text & "' ) order by ts.file_id DESC"
                'ssql = "select wo.*, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and (pt.dataentry_date between '" & dtpFromDate.Text & "' and '" & dtpToDate.Text & "' ) order by pt.file_id"
                'ssql = "select wo.*, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '7/2/2012' and (pt.dataentry_date < '" & dtpToDate.Text & "' ) order by pt.file_id"
                ' ssql = "select wo.file_id ,wo.file_name ,wo.file_type ,add_streetno ,add_prefix ,add_prefix_2 ,add_streetname,add_suffix,street_suffix_cd,name,date_installed,wo.city ,subdivision,service_wo_no,pipe_size,pipe_type,pressure,size_of_tap,length_main_to_curb_valve_ft,length_main_to_curb_valve_in,length_curb_valve_to_property_line_ft,length_curb_valve_to_property_line_in,length_main_to_property_line_ft,length_main_to_property_line_in,length_property_line_to_meter_ft,length_property_line_to_meter_in,size_of_main,type_of_main,depth_of_main,tested_with,tested_to,psig_for_minutes,installed_by_contractor,foreman,remark,PREMISE_NO,f_stub ,f_inserted ,f_retired ,f_leak ,f_split ,pipe_condition ,lenmtrtomain ,sceginspect ,nature_of_work ,kind_of_joint,split_sleeve,loc_of_cock,kind_of_box,type_coating ,DATE_ABANDON,reason_of_abandon,f_noblow ,f_gassource,f_tappingtee,f_linesealed ,f_defcoatcor,f_multimeter,f_serstop,f_cathodpro,f_tee,f_taptee ,f_galvanized ,tap_t_size,tap_t_inlet,tap_t_outlet,f_insulated,f_anoderiser ,otheranode_lbs ,connection_data ,misc_card_type ,f_relocate ,f_replace, STREET_LOCATION_1_CD, STREET_LOCATION_2_CD, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '7/2/2012' and (pt.dataentry_date < '" & dtpToDate.Text & "' ) order by pt.file_id"
                'ssql = "select wo.file_id ,wo.file_name ,wo.file_type ,add_streetno ,add_prefix ,add_prefix_2 ,add_streetname,add_suffix,street_suffix_cd,name,date_installed,wo.city ,subdivision,service_wo_no,pipe_size,pipe_type,pressure,size_of_tap,length_main_to_curb_valve_ft,length_main_to_curb_valve_in,length_curb_valve_to_property_line_ft,length_curb_valve_to_property_line_in,length_main_to_property_line_ft,length_main_to_property_line_in,length_property_line_to_meter_ft,length_property_line_to_meter_in,size_of_main,type_of_main,depth_of_main,tested_with,tested_to,psig_for_minutes,installed_by_contractor,foreman,remark,PREMISE_NO,f_stub ,f_inserted ,f_retired ,f_leak ,f_split ,pipe_condition ,lenmtrtomain ,sceginspect ,nature_of_work ,kind_of_joint,split_sleeve,loc_of_cock,kind_of_box,type_coating ,DATE_ABANDON,reason_of_abandon,f_noblow ,f_gassource,f_tappingtee,f_linesealed ,f_defcoatcor,f_multimeter,f_serstop,f_cathodpro,f_tee,f_taptee ,f_galvanized ,tap_t_size,tap_t_inlet,tap_t_outlet,f_insulated,f_anoderiser ,otheranode_lbs ,connection_data ,misc_card_type ,f_relocate ,f_replace, STREET_LOCATION_1_CD, STREET_LOCATION_2_CD, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '2012-04-09 16:14:08.077' and (pt.dataentry_date < '" & dtpToDate.Text & "' ) order by pt.file_id"
                cmdtext = "SO_FrmexportFillgrid"
                cmd = Nothing
                cmd = New SqlCommand(cmdtext, conn)
                cmd.CommandType = CommandType.StoredProcedure

                'ssql = "select wo.file_id ,wo.file_name ,wo.file_type ,add_streetno ,add_prefix ,add_prefix_2 ,add_streetname,add_suffix,street_suffix_cd,name,date_installed,wo.city ,subdivision,service_wo_no,pipe_size,pipe_type,pressure,size_of_tap,length_main_to_curb_valve_ft,length_main_to_curb_valve_in,length_curb_valve_to_property_line_ft,length_curb_valve_to_property_line_in,length_main_to_property_line_ft,length_main_to_property_line_in,length_property_line_to_meter_ft,length_property_line_to_meter_in,size_of_main,type_of_main,depth_of_main,tested_with,tested_to,psig_for_minutes,installed_by_contractor,foreman,remark,PREMISE_NO,f_stub ,f_inserted ,f_retired ,f_leak ,f_split ,pipe_condition ,lenmtrtomain ,sceginspect ,nature_of_work ,kind_of_joint,split_sleeve,loc_of_cock,kind_of_box,type_coating ,DATE_ABANDON,reason_of_abandon,f_noblow ,f_gassource,f_tappingtee,f_linesealed ,f_defcoatcor,f_multimeter,f_serstop,f_cathodpro,f_tee,f_taptee ,f_galvanized ,tap_t_size,tap_t_inlet,tap_t_outlet,f_insulated,f_anoderiser ,otheranode_lbs ,connection_data ,misc_card_type ,f_relocate ,f_replace, STREET_LOCATION_1_CD, STREET_LOCATION_2_CD, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '2012-08-31 00:00:00.000'"
            Else
                cmdtext = "SO_FrmexportFillgrid1"
                cmd = Nothing
                cmd = New SqlCommand(cmdtext, conn)
                cmd.CommandType = CommandType.StoredProcedure
                'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (ts.file_id between " & Me.txt_sfrom.Text & " and " & txt_sto.Text & ") order by ts.file_id DESC"
                'ssql = "select wo.*, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and  pt.submitted_by is  null  order by pt.file_id"
            End If
            Me.Cursor = Cursors.WaitCursor
            Dim ds As New DataSet
            'Dim da As New SqlDataAdapter(ssql, conn)
            Dim da As New SqlDataAdapter(cmd)
            da.SelectCommand.CommandTimeout = 5000
            da.Fill(ds)
            gdv_tosubmitdata.DataSource = ds.Tables(0)
        Catch ex As Exception
        Finally
            Cursor = Cursors.Default

        End Try
    End Sub
    Private Sub rbt_time_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbt_time.CheckedChanged
        If rbt_time.Checked = True Then
            lbl_21.Visible = True
            dtpFromDate.Visible = True
            lbl_22.Visible = True
            dtpToDate.Visible = True
            lbl_11.Visible = False
            txt_sfrom.Visible = False
            lbl_12.Visible = False
            txt_sto.Visible = False
        Else
            lbl_11.Visible = True
            txt_sfrom.Visible = True
            lbl_12.Visible = True
            txt_sto.Visible = True
            lbl_21.Visible = False
            dtpFromDate.Visible = False
            lbl_22.Visible = False
            dtpToDate.Visible = False
        End If
    End Sub
    Private Sub max_min()
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        Dim dr As SqlDataReader = Nothing
        Dim cmdtext As String = "SO_MaxMinFileid"
        cmd = Nothing
        ' cmd = New SqlCommand("select max(file_id), min(file_id) from tbl_parent where status not in ('N','A','S') ", conn)
        cmd = New SqlCommand(cmdtext, conn)
        cmd.CommandType = CommandType.StoredProcedure
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If (dr.Read) Then

            Me.txt_sto.Text = dr(0).ToString
            Me.txt_sfrom.Text = dr(1).ToString
        End If
        dr.Close()
    End Sub
    Private Sub date_select()
        dtpFromDate.Text = Date.Today.Date.AddDays(-1)
        dtpToDate.Text = Date.Today.Date.AddDays(0)
    End Sub
    Private Sub btn_choose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_choose.Click
        FillGrid()
        txt_total.Text = Me.gdv_tosubmitdata.RowCount - 1
    End Sub
    Private Sub dtpToDate_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpToDate.ValueChanged, dtpFromDate.ValueChanged, txt_sfrom.TextChanged, txt_sto.TextChanged
        ' FillGrid()
    End Sub
    Private Sub btn_reset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_reset.Click
        max_min()
        date_select()
        txt_total.Text = ""
    End Sub
    Private Function GetSampleData() As DataTable
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        Dim ssql As String
        Dim cmdtext As String
        If rbt_time.Checked = True Then
            'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (tp.dataentry_date between '" & dtpFromDate.Text & "' and '" & dtpToDate.Text & "' ) order by ts.file_id DESC"
            ' ssql = "select wo.*, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and (pt.dataentry_date between '" & dtpFromDate.Text & "' and '" & dtpToDate.Text & "' ) order by pt.file_id"
            'ssql = "select wo.*, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '6/6/2012' and (pt.dataentry_date < '" & dtpToDate.Text & "' ) order by pt.file_id" ' till 1 may 12
            'ssql = "select wo.file_id ,wo.file_name ,wo.file_type ,add_streetno ,add_prefix ,add_prefix_2 ,add_streetname,add_suffix,street_suffix_cd,name,date_installed,wo.city ,subdivision,service_wo_no,pipe_size,pipe_type,pressure,size_of_tap,length_main_to_curb_valve_ft,length_main_to_curb_valve_in,length_curb_valve_to_property_line_ft,length_curb_valve_to_property_line_in,length_main_to_property_line_ft,length_main_to_property_line_in,length_property_line_to_meter_ft,length_property_line_to_meter_in,size_of_main,type_of_main,depth_of_main,tested_with,tested_to,psig_for_minutes,installed_by_contractor,foreman,remark,PREMISE_NO,f_stub ,f_inserted ,f_retired ,f_leak ,f_split ,pipe_condition ,lenmtrtomain ,sceginspect ,nature_of_work ,kind_of_joint,split_sleeve,loc_of_cock,kind_of_box,type_coating ,DATE_ABANDON,reason_of_abandon,f_noblow ,f_gassource,f_tappingtee,f_linesealed ,f_defcoatcor,f_multimeter,f_serstop,f_cathodpro,f_tee,f_taptee ,f_galvanized ,tap_t_size,tap_t_inlet,tap_t_outlet,f_insulated,f_anoderiser ,otheranode_lbs ,connection_data ,misc_card_type ,f_relocate ,f_replace, STREET_LOCATION_1_CD, STREET_LOCATION_2_CD, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '7/2/2012' and (pt.dataentry_date < '" & dtpToDate.Text & "' ) order by pt.file_id"
            'ssql = "select wo.file_id ,wo.file_name ,wo.file_type ,add_streetno ,add_prefix ,add_prefix_2 ,add_streetname,add_suffix,street_suffix_cd,name,date_installed,wo.city ,subdivision,service_wo_no,pipe_size,pipe_type,pressure,size_of_tap,length_main_to_curb_valve_ft,length_main_to_curb_valve_in,length_curb_valve_to_property_line_ft,length_curb_valve_to_property_line_in,length_main_to_property_line_ft,length_main_to_property_line_in,length_property_line_to_meter_ft,length_property_line_to_meter_in,size_of_main,type_of_main,depth_of_main,tested_with,tested_to,psig_for_minutes,installed_by_contractor,foreman,remark,PREMISE_NO,f_stub ,f_inserted ,f_retired ,f_leak ,f_split ,pipe_condition ,lenmtrtomain ,sceginspect ,nature_of_work ,kind_of_joint,split_sleeve,loc_of_cock,kind_of_box,type_coating ,DATE_ABANDON,reason_of_abandon,f_noblow ,f_gassource,f_tappingtee,f_linesealed ,f_defcoatcor,f_multimeter,f_serstop,f_cathodpro,f_tee,f_taptee ,f_galvanized ,tap_t_size,tap_t_inlet,tap_t_outlet,f_insulated,f_anoderiser ,otheranode_lbs ,connection_data ,misc_card_type ,f_relocate ,f_replace, STREET_LOCATION_1_CD, STREET_LOCATION_2_CD, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '2012-04-09 16:14:08.077' and (pt.dataentry_date < '" & dtpToDate.Text & "' ) order by pt.file_id"
            ''ssql = "select wo.file_id ,wo.file_name ,wo.file_type ,add_streetno ,add_prefix ,add_prefix_2 ,add_streetname,add_suffix,street_suffix_cd,name,date_installed,wo.city ,subdivision,service_wo_no,pipe_size,pipe_type,pressure,size_of_tap,length_main_to_curb_valve_ft,length_main_to_curb_valve_in,length_curb_valve_to_property_line_ft,length_curb_valve_to_property_line_in,length_main_to_property_line_ft,length_main_to_property_line_in,length_property_line_to_meter_ft,length_property_line_to_meter_in,size_of_main,type_of_main,depth_of_main,tested_with,tested_to,psig_for_minutes,installed_by_contractor,foreman,remark,PREMISE_NO,f_stub ,f_inserted ,f_retired ,f_leak ,f_split ,pipe_condition ,lenmtrtomain ,sceginspect ,nature_of_work ,kind_of_joint,split_sleeve,loc_of_cock,kind_of_box,type_coating ,DATE_ABANDON,reason_of_abandon,f_noblow ,f_gassource,f_tappingtee,f_linesealed ,f_defcoatcor,f_multimeter,f_serstop,f_cathodpro,f_tee,f_taptee ,f_galvanized ,tap_t_size,tap_t_inlet,tap_t_outlet,f_insulated,f_anoderiser ,otheranode_lbs ,connection_data ,misc_card_type ,f_relocate ,f_replace, STREET_LOCATION_1_CD, STREET_LOCATION_2_CD, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and pt.submitted_date =  '2012-08-31 00:00:00.000'"
            cmdtext = "SO_FrmExportGetData"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure

        Else
            'ssql = "select ts.file_id as [ID], ts.file_name as [File], ts.add_streetno + ' ' + ts.add_prefix + ' ' + ts.add_streetname + ' ' + ts.add_suffix as [Address], ts.city as City, last_remark as [Remark] from tbl_servicewo ts join tbl_parent tp on ts.file_id = tp.file_id where tp.status not in ('N','A','S') and (ts.file_id between " & Me.txt_sfrom.Text & " and " & txt_sto.Text & ") order by ts.file_id DESC"
            ''ssql = "select wo.*, pt.status,pt.received_date,pt.received_by,pt.assigned_date,pt.assigned_to,pt.dataentry_date,pt.dataentry_by,pt.passed_date,pt.qa_verify_date,pt.qa_verify_by,pt.submitted_date,pt.submitted_by,pt.last_remark, pt.service_order from dbo.tbl_parent pt, dbo.tbl_servicewo wo where pt.file_id = wo.file_id and  pt.submitted_by is  null  order by pt.file_id"
            cmdtext = "SO_FrmExportGetData1"
            cmd = Nothing
            cmd = New SqlCommand(cmdtext, conn)
            cmd.CommandType = CommandType.StoredProcedure
        End If
        Dim CategoryAdapter As New SqlDataAdapter(cmd)
        Dim ProductData As New DataSet()
        CategoryAdapter.Fill(ProductData, "Categories")
        Return ProductData.Tables(0)
    End Function
    Private Sub btn_createcsv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_createcsv.Click
        Dim Csv As New Exporter()
        Using CsvWriter As New StreamWriter("C:\CSV\TestFile.csv")
            CsvWriter.Write(Csv.CsvFromDatatable(GetSampleData()))
        End Using
        System.Diagnostics.Process.Start("C:\CSV\TestFile.csv")
        MessageBox.Show("TestFile.csv file created at C:\CSV")
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fi As New FileInfo("C:\CSV\TestFile.csv")
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName
        Dim conn1 As New OleDbConnection(connectionString)
        conn1.Open()
        'the SELECT statement is important here,  
        'and requires some formatting to pull dates and deal with headers with spaces. 
        Dim cmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, conn1)
        Dim adapter1 As New OleDbDataAdapter
        adapter1.SelectCommand = cmdSelect
        Dim ds As New DataSet
        adapter1.Fill(ds, "DATA")
        gdv_tosubmitdata.DataSource = ds.Tables(0).DefaultView

        Dim dt As DataTable
        Dim dr As DataRow
        dt = ds.Tables(0)
        For Each dr In dt.Rows
            'MessageBox.Show(dr(0))





            'Try
            '    Dim cmd As New SqlCommand("Upload_Data", conn)
            '    cmd.CommandType = CommandType.StoredProcedure
            '    cmd.Parameters.Add(New SqlParameter("@file_id", dr(0)))
            '    cmd.Parameters.Add(New SqlParameter("@file_name", dr(1)))
            '    cmd.Parameters.Add(New SqlParameter("@file_type", dr(2)))
            '    cmd.Parameters.Add(New SqlParameter("@add_streetno", dr(3)))
            '    cmd.Parameters.Add(New SqlParameter("@add_prefix", dr(4)))
            '    cmd.Parameters.Add(New SqlParameter("@add_prefix_2", dr(5)))
            '    cmd.Parameters.Add(New SqlParameter("@add_streetname", dr(6)))
            '    cmd.Parameters.Add(New SqlParameter("@add_suffix", dr(7)))
            '    cmd.Parameters.Add(New SqlParameter("@street_suffix_cd", dr(8)))
            '    cmd.Parameters.Add(New SqlParameter("@name", dr(9)))
            '    cmd.Parameters.Add(New SqlParameter("@date_installed", dr(10)))
            '    cmd.Parameters.Add(New SqlParameter("@city", dr(11)))
            '    cmd.Parameters.Add(New SqlParameter("@subdivision", dr(12)))
            '    cmd.Parameters.Add(New SqlParameter("@service_wo_no", dr(13)))
            '    cmd.Parameters.Add(New SqlParameter("@pipe_size", dr(14)))
            '    cmd.Parameters.Add(New SqlParameter("@pipe_type", dr(15)))
            '    cmd.Parameters.Add(New SqlParameter("@pressure", dr(16)))
            '    cmd.Parameters.Add(New SqlParameter("@size_of_tap", dr(17)))
            '    cmd.Parameters.Add(New SqlParameter("@length_main_to_curb_valve_ft", dr(18)))
            '    cmd.Parameters.Add(New SqlParameter("@length_main_to_curb_valve_in", dr(19)))
            '    cmd.Parameters.Add(New SqlParameter("@length_curb_valve_to_property_line_ft", dr(20)))
            '    cmd.Parameters.Add(New SqlParameter("@length_curb_valve_to_property_line_in", dr(21)))
            '    cmd.Parameters.Add(New SqlParameter("@length_main_to_property_line_ft", dr(22)))
            '    cmd.Parameters.Add(New SqlParameter("@length_main_to_property_line_in", dr(23)))
            '    cmd.Parameters.Add(New SqlParameter("@length_property_line_to_meter_ft", dr(24)))
            '    cmd.Parameters.Add(New SqlParameter("@length_property_line_to_meter_in", dr(25)))
            '    cmd.Parameters.Add(New SqlParameter("@size_of_main", dr(26)))
            '    cmd.Parameters.Add(New SqlParameter("@type_of_main", dr(27)))
            '    cmd.Parameters.Add(New SqlParameter("@depth_of_main", dr(28)))
            '    cmd.Parameters.Add(New SqlParameter("@tested_with", dr(29)))
            '    cmd.Parameters.Add(New SqlParameter("@tested_to", dr(30)))
            '    cmd.Parameters.Add(New SqlParameter("@psig_for_minutes", dr(31)))
            '    cmd.Parameters.Add(New SqlParameter("@installed_by_contractor", dr(32)))
            '    cmd.Parameters.Add(New SqlParameter("@foreman", dr(33)))
            '    cmd.Parameters.Add(New SqlParameter("@remark", dr(34)))
            '    cmd.Parameters.Add(New SqlParameter("@PREMISE_NO", dr(35)))
            '    cmd.Parameters.Add(New SqlParameter("@f_stub", dr(36)))
            '    cmd.Parameters.Add(New SqlParameter("@f_inserted", dr(37)))
            '    cmd.Parameters.Add(New SqlParameter("@f_retired", dr(38)))
            '    cmd.Parameters.Add(New SqlParameter("@f_leak", dr(39)))
            '    cmd.Parameters.Add(New SqlParameter("@f_split", dr(40)))
            '    cmd.Parameters.Add(New SqlParameter("@pipe_condition", dr(41)))
            '    cmd.Parameters.Add(New SqlParameter("@lenmtrtomain", dr(42)))
            '    cmd.Parameters.Add(New SqlParameter("@sceginspect", dr(43)))
            '    cmd.Parameters.Add(New SqlParameter("@nature_of_work", dr(44)))
            '    cmd.Parameters.Add(New SqlParameter("@kind_of_joint", dr(45)))
            '    cmd.Parameters.Add(New SqlParameter("@split_sleeve", dr(46)))
            '    cmd.Parameters.Add(New SqlParameter("@loc_of_cock", dr(47)))
            '    cmd.Parameters.Add(New SqlParameter("@kind_of_box", dr(48)))
            '    cmd.Parameters.Add(New SqlParameter("@type_coating", dr(49)))
            '    cmd.Parameters.Add(New SqlParameter("@DATE_ABANDON", dr(50)))
            '    cmd.Parameters.Add(New SqlParameter("@reason_of_abandon", dr(51)))
            '    cmd.Parameters.Add(New SqlParameter("@f_noblow", dr(52)))
            '    cmd.Parameters.Add(New SqlParameter("@f_gassource", dr(53)))
            '    cmd.Parameters.Add(New SqlParameter("@f_tappingtee", dr(54)))
            '    cmd.Parameters.Add(New SqlParameter("@f_linesealed", dr(55)))
            '    cmd.Parameters.Add(New SqlParameter("@f_defcoatcor", dr(56)))
            '    cmd.Parameters.Add(New SqlParameter("@f_multimeter", dr(57)))
            '    cmd.Parameters.Add(New SqlParameter("@f_serstop", dr(58)))
            '    cmd.Parameters.Add(New SqlParameter("@f_cathodpro", dr(59)))
            '    cmd.Parameters.Add(New SqlParameter("@f_tee bit", dr(60)))
            '    cmd.Parameters.Add(New SqlParameter("@f_taptee", dr(61)))
            '    cmd.Parameters.Add(New SqlParameter("@f_galvanized", dr(62)))
            '    cmd.Parameters.Add(New SqlParameter("@tap_t_size", dr(63)))
            '    cmd.Parameters.Add(New SqlParameter("@tap_t_inlet", dr(64)))
            '    cmd.Parameters.Add(New SqlParameter("@tap_t_outlet", dr(65)))
            '    cmd.Parameters.Add(New SqlParameter("@f_insulated", dr(66)))
            '    cmd.Parameters.Add(New SqlParameter("@f_anoderiser", dr(67)))
            '    cmd.Parameters.Add(New SqlParameter("@otheranode_lbs", dr(68)))
            '    cmd.Parameters.Add(New SqlParameter("@connection_data", dr(69)))
            '    cmd.Parameters.Add(New SqlParameter("@misc_card_type", dr(70)))
            '    cmd.Parameters.Add(New SqlParameter("@f_relocate", dr(71)))
            '    cmd.Parameters.Add(New SqlParameter("@f_replace", dr(72)))


            '    cmd.Parameters.Add(New SqlParameter("@status", dr(73)))
            '    cmd.Parameters.Add(New SqlParameter("@received_date", dr(74)))
            '    cmd.Parameters.Add(New SqlParameter("@received_by", dr(75)))
            '    cmd.Parameters.Add(New SqlParameter("@assigned_date", dr(76)))
            '    cmd.Parameters.Add(New SqlParameter("@assigned_to", dr(77)))

            '    cmd.Parameters.Add(New SqlParameter("@dataentry_date", dr(78)))
            '    cmd.Parameters.Add(New SqlParameter("@dataentry_by", dr(79)))
            '    cmd.Parameters.Add(New SqlParameter("@passed_date", dr(80)))
            '    cmd.Parameters.Add(New SqlParameter("@qa_verify_date", dr(81)))
            '    cmd.Parameters.Add(New SqlParameter("@qa_verify_by", dr(82)))
            '    cmd.Parameters.Add(New SqlParameter("@submitted_date", dr(83)))
            '    cmd.Parameters.Add(New SqlParameter("@submitted_by", dr(84)))
            '    cmd.Parameters.Add(New SqlParameter("@last_remark", dr(85)))
            '    cmd.ExecuteNonQuery()



            'Catch ex As Exception
            '    MsgBox(ex.ToString, MsgBoxStyle.Information, "Error!")

            'End Try

        Next
        ds.Dispose()
        'gdv_tosubmitdata.DataBind()
        conn.Close()
    End Sub
End Class
Public Class Exporter
    Public Sub New()
        TextDelimiter = ","c
        TextQualifiers = """"c
        HasColumnHeaders = True
    End Sub
    Private _TextDelimiter As Char
    Public Property TextDelimiter() As Char
        Get
            Return _TextDelimiter
        End Get
        Set(ByVal value As Char)
            _TextDelimiter = value
        End Set
    End Property
    Private _TextQualifiers As Char
    Public Property TextQualifiers() As Char
        Get
            Return _TextQualifiers
        End Get
        Set(ByVal value As Char)
            _TextQualifiers = value
        End Set
    End Property
    Private _HasColumnHeaders As Boolean
    Public Property HasColumnHeaders() As Boolean
        Get
            Return _HasColumnHeaders
        End Get
        Set(ByVal value As Boolean)
            _HasColumnHeaders = value
        End Set
    End Property
    Public Function CsvFromDatatable(ByVal InputTable As DataTable) As String
        Dim CsvBuilder As New StringBuilder()
        If HasColumnHeaders Then
            CreateHeader(InputTable, CsvBuilder)
        End If
        CreateRows(InputTable, CsvBuilder)
        Return CsvBuilder.ToString()
    End Function
    Private Sub CreateRows(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
        For Each ExportRow As DataRow In InputTable.Rows
            For Each ExportColumn As DataColumn In InputTable.Columns
                Dim ColumnText As String = ExportRow(ExportColumn.ColumnName).ToString()
                ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
                CsvBuilder.Append(TextQualifiers + ColumnText + TextQualifiers)
                CsvBuilder.Append(TextDelimiter)
            Next
            CsvBuilder.AppendLine()
        Next
    End Sub
    Private Sub CreateHeader(ByVal InputTable As DataTable, ByVal CsvBuilder As StringBuilder)
        For Each ExportColumn As DataColumn In InputTable.Columns
            Dim ColumnText As String = ExportColumn.ColumnName.ToString()
            ColumnText = ColumnText.Replace(TextQualifiers.ToString(), TextQualifiers.ToString() + TextQualifiers.ToString())
            CsvBuilder.Append(TextQualifiers + ExportColumn.ColumnName + TextQualifiers)
            CsvBuilder.Append(TextDelimiter)
        Next
        CsvBuilder.AppendLine()
    End Sub
End Class


