Solved

how can I get my connection to mysql db to work?

Posted on 2008-10-24
2
237 Views
Last Modified: 2012-05-05
I have had to change my connection string to a ODBC Driver rather than my old connection string and am trying to figure out how to get it to work. I put my connection string in the web.config file and the connection string is like so...but I am not sure how to create the new connection on my individual pages. For example...in code snippet. Help would be greatly appreciated. Thanks.

<connectionStrings>
   
    <add name="MyConnectionString" connectionString="Driver=(MySQL ODBC 3.51 Driver);Server=mysqldev.services.etc.lan;Database=etctrucktickets;User=etctruck_pxy;Password=8eThe8all;Option=3;" />
  </connectionStrings>
Imports MySql.Data.MySqlClient
Imports System.Data
Partial Class CreateFlocalRecords
    Inherits System.Web.UI.Page
    Dim conn As MySqlConnection
    Dim cmd As MySqlCommand
    Dim da As MySqlDataAdapter
    Dim ds, ds1, ds2, ds3, ds4, ds5, ds6 As DataSet
    Dim strSql, strSql1, strSql2, strSql3, strSql4, strSql5, strSql6 As String
    Dim ToDate As String
    Dim FromDate As String
    Dim yr, mo, day, wholeD, yr2, mo2, day2, wholeD2 As String
    Dim replaceMonth, replaceMonth2, replaceDay, replaceDay2 As String
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim menu As Control
        menu = LoadControl("menu.ascx")
        plhMenu.Controls.Add(menu)
        ToDate = Request.QueryString("ToDate")
        FromDate = Request.QueryString("FromDate")
 
        'Connect to database
        conn = New MySqlConnection(ConfigurationManager.AppSettings("MyConnectionString"))
 
        conn.Open()
 
        strSql = "SELECT CompanyNumber,typeofworkcode,workfor,LocationCodeOrigin,LocationCodeDestin1,Sum(Hours) as Hours,Sum(DeliveredDripGals) as DeliveredDripGals,Sum(DeliveredOilGals) as DeliveredOilGals,Sum(DeliveredWaterGals) as DeliveredWaterGals,Sum(TotalFreight) as TotalFreight,Sum(TotalDisposalChar) as TotalDisposalChar,"
        strSql &= "TicketNumber,TicketDateMonth,TicketDateDay,TicketDateYear,CostCompany,CostDivision,CostDepartment,TicketDescription from truckticketdetail"
        If (FromDate <> "") Then
            strSql &= " where STR_TO_DATE(concat(TicketDateMonth,'/',TicketDateDay,'/',TicketDateYear),'%m/%d/%Y')>= STR_TO_DATE('" & FromDate & "','%m/%d/%Y')"
        End If
 
        If (ToDate <> "") Then
            strSql &= " and STR_TO_DATE(concat(TicketDateMonth,'/',TicketDateDay,'/',TicketDateYear),'%m/%d/%Y') <= STR_TO_DATE('" & ToDate & "','%m/%d/%Y') "
        End If
 
        strSql &= " group by workfor,typeofworkcode,locationcodeorigin,locationcodedestin1"
 
        cmd = New MySqlCommand(strSql, conn)
        da = New MySqlDataAdapter(cmd)
        ds = New DataSet()
        da.Fill(ds)
        Dim trcust, ptco, ptloco, ptlocd, pthours, ptggal, ptinv, ptwtco, ptcname, ptoname, ptdname As String
        Dim trtick, trdatm, trdatd, trdaty, trcco, trcdiv, trcdpt, trdesc, trdisp As String
 
        Dim dt As DataTable
        Dim i As Integer
        Dim existsInDatabase As Boolean
        dt = ds.Tables(0)
 
 
        'Generates trpworkd table
        For i = 0 To dt.Rows.Count - 1
            ptco = dt.Rows(i)("CompanyNumber")
            ptwtco = dt.Rows(i)("typeofworkcode")
            trcust = dt.Rows(i)("WorkFor")
            'trfrtr = dt.Rows(i)("RateFreight")
            ptloco = dt.Rows(i)("LocationCodeOrigin")
            ptlocd = dt.Rows(i)("LocationCodeDestin1")
            pthours = dt.Rows(i)("Hours")
            ptggal = 0
            If (ptwtco >= 100 And ptwtco <= 199) Then
                ptggal = dt.Rows(i)("DeliveredDripGals")
            End If
            If (ptwtco >= 300 And ptwtco <= 399) Then
                ptggal = dt.Rows(i)("DeliveredOilGals")
            End If
            If (ptwtco >= 400 And ptwtco <= 499) Then
                ptggal = 0
            End If
            If (ptwtco >= 500 And ptwtco <= 599) Then
                ptggal = dt.Rows(i)("DeliveredWaterGals")
            End If
            If (ptwtco >= 600 And ptwtco <= 699) Then
                ptggal = 0
            End If
            ptinv = dt.Rows(i)("TotalFreight")
            trtick = dt.Rows(i)("TicketNumber")
            trdatm = dt.Rows(i)("TicketDateMonth")
            trdatd = dt.Rows(i)("TicketDateDay")
            trdaty = dt.Rows(i)("TicketDateYear")
            Try
                trcco = dt.Rows(i)("CostCompany")
            Catch ex As Exception
                trcco = 0
            End Try
            Try
                trcdiv = dt.Rows(i)("CostDivision")
            Catch ex As Exception
                trcdiv = 0
            End Try
            Try
                trcdpt = dt.Rows(i)("CostDepartment")
            Catch ex As Exception
                trcdpt = 0
            End Try
 
            trdesc = dt.Rows(i)("TicketDescription")
            trdisp = dt.Rows(i)("TotalDisposalChar")
 
            strSql1 = "Select Name from mslnamev where NANumber=" & trcust
            cmd = New MySqlCommand(strSql1, conn)
            da = New MySqlDataAdapter(cmd)
            ds1 = New DataSet()
            da.Fill(ds1)
            If (trcust <> 0) Then
                ptcname = ds1.Tables(0).Rows(0)("Name")
            Else
                ptcname = "Unknown"
            End If
 
            strSql2 = "Select LocationDescription from locationmaster where locationcode=" & ptloco
            cmd = New MySqlCommand(strSql2, conn)
            da = New MySqlDataAdapter(cmd)
            ds2 = New DataSet()
            da.Fill(ds2)
            If (ptloco <> 0) Then
                ptoname = ds2.Tables(0).Rows(0)("LocationDescription")
            Else
                ptoname = 0
            End If
 
 
 
            strSql3 = "Select LocationDescription from locationmaster where locationcode=" & ptlocd
            cmd = New MySqlCommand(strSql3, conn)
            da = New MySqlDataAdapter(cmd)
            ds3 = New DataSet()
            da.Fill(ds3)
 
            If (ptlocd <> 0) Then
                ptdname = ds3.Tables(0).Rows(0)("LocationDescription")
            Else
                ptdname = 0
            End If
 
            'If ((ptwtco >= 200 And ptwtco < 299) Or (ptwtco >= 400 And ptwtco < 499) Or (ptwtco >= 600 And ptwtco < 999)) Then
            strSql4 = "Select *,ID from trpworkd where CompanyNumber=" & ptco & " and CustomerNumber=" & trcust & " and LocationCodeOrigin=" & ptloco
            strSql4 &= " and LocationCodeDestin=" & ptlocd & " and typeofworkcode=" & ptwtco
            'strSql4 &= " and LocationCodeDestin=" & ptlocd & " and Hours=" & pthours & " and TotalFreight=" & ptinv & " and typeofworkcode=" & ptwtco & " and GallonsDrip=" & ptggal
            'strSql4 &= " and TicketNumber=" & trtick & " and TicketDateDay=" & trdatd & " and TicketDateMonth=" & trdatm & " and TicketDateYear=" & trdaty
            'strSql4 &= " and CostCompany = " & trcco & " and CostDivision=" & trcdiv & " and CostDepartment=" & trcdpt
 
            cmd = New MySqlCommand(strSql4, conn)
            da = New MySqlDataAdapter(cmd)
            ds4 = New DataSet()
            da.Fill(ds4)
 
 
            If (ds4.Tables(0).Rows.Count = 0) Then
                existsInDatabase = False
                strSql5 = "insert into trpworkd(CompanyNumber,CostCompany,CostDivision,CostDepartment,CustomerNumber,CustomerName,TypeOfWorkCode,LocationCodeOrigin,OriginDescription,LocationCodeDestin,DestinDescription,Hours,GallonsDrip,TotalFreight,TicketNumber,ServiceComment,TotalDisposalChar,TicketDateMonth,TicketDateDay,TicketDateYear) "
                strSql5 &= " values('" & ptco & "','" & trcco & "','" & trcdiv & "','" & trcdpt & "','" & trcust & "','" & ptcname & "','" & ptwtco & "','" & ptloco & "','" & ptoname & "','" & ptlocd & "','" & ptdname & "','" & pthours & "','" & ptggal & "','" & ptinv & "','" & trtick & "','" & trdesc & "','" & trdisp & "','" & trdatm & "','" & trdatd & "','" & trdaty & "')"
                cmd = New MySqlCommand(strSql5, conn)
                da = New MySqlDataAdapter(cmd)
                ds5 = New DataSet()
                da.Fill(ds5)
 
                'Write exception report
                Dim count As Integer
                count = count + 1
                If (count = 1) Then
                    table1.Visible = True
                    table2.Visible = True
 
                End If
 
                table3.Visible = True
                Dim row As TableRow
                Dim cell1, cell2, cell3, cell4, cell5, cell6 As TableCell
                row = New TableRow
                table3.Rows.Add(row)
                cell1 = New TableCell
                cell1.Width = 178
                cell1.Text = trcust & " - " & ptcname
                row.Cells.Add(cell1)
 
                cell2 = New TableCell
                cell2.Width = 55
                cell2.Text = ptwtco
                row.Cells.Add(cell2)
 
                cell3 = New TableCell
                cell3.Width = 155
                cell3.Text = ptloco
                row.Cells.Add(cell3)
 
                cell4 = New TableCell
                cell4.Width = 155
                cell4.Text = ptlocd
                row.Cells.Add(cell4)
 
                cell5 = New TableCell
                cell5.Width = 100
                cell5.Text = ptggal
                row.Cells.Add(cell5)
 
                cell6 = New TableCell
                cell6.Width = 160
                row.Cells.Add(cell6)
 
 
            Else
                existsInDatabase = True
                Dim ID1 As String
                ID1 = ds4.Tables(0).Rows(0)("ID")
                strSql6 = "update trpworkd set CompanyNumber='" & ptco & "',CostCompany='" & trcco & "',CostDivision='" & trcdiv & "',CostDepartment='" & trcdpt & "', CustomerNumber='" & trcust & "',CustomerName='" & ptcname & "',TypeOfWorkCode='" & ptwtco & "',LocationCodeOrigin='" & ptloco & "',OriginDescription='" & ptoname
                strSql6 &= "',LocationCodeDestin='" & ptlocd & "',DestinDescription='" & ptdname & "',Hours='" & pthours & "',GallonsDrip='" & ptggal & "',TotalFreight='" & ptinv & "',TicketNumber='" & trtick & "',TotalDisposalChar='" & trdisp & "',TicketDateMonth='" & trdatm & "',TicketDateDay='" & trdatd & "',TicketDateYear='" & trdaty & "',ServiceComment='" & trdesc & "'"
                strSql6 &= " where ID=" & ID1
                cmd = New MySqlCommand(strSql6, conn)
                da = New MySqlDataAdapter(cmd)
                ds6 = New DataSet()
                da.Fill(ds6)
            End If
 
            'End If
        Next
 
 
        Dim oilfactor, waterfactor As Decimal
        Dim oilbtu As Integer
        oilfactor = 0.02529
        waterfactor = 0.1756
        oilbtu = 4567
        Dim ds7, ds8, ds9, ds10, ds11 As DataSet
        Dim strSql7, strSql8, strSql9, strSql10, strSql11 As String
        Dim j As Integer
 
        strSql7 = "Select t.*,t1.GallonsDrip from truckticketxref t,trpworkd t1"
 
        If (FromDate <> "") Then
            strSql7 &= " where STR_TO_DATE(concat(TicketDateMonth,'/',TicketDateDay,'/',TicketDateYear),'%m/%d/%Y')>= STR_TO_DATE('" & FromDate & "','%m/%d/%Y')"
        End If
 
        If (ToDate <> "") Then
            strSql7 &= " and STR_TO_DATE(concat(TicketDateMonth,'/',TicketDateDay,'/',TicketDateYear),'%m/%d/%Y') <= STR_TO_DATE('" & ToDate & "','%m/%d/%Y')"
        End If
 
        strSql7 &= " and t.IXCONO = t1.CompanyNumber and t.IXWTCO = t1.typeofworkcode"
        strSql7 &= " and t.IXLOCO=t1.LocationCodeOrigin and t.IXLOCD=t1.LocationCodeDestin "
 
        cmd = New MySqlCommand(strSql7, conn)
        da = New MySqlDataAdapter(cmd)
        ds7 = New DataSet()
        da.Fill(ds7)
        Dim ptco1, ptwtco1, ptggal1, meternumber, gallonfactor, btufactor As String
        Dim TMPMCF, WKMCF, TMPBTU, WKMMBTU, CVTMCF, CVTMMBTU As Decimal
 
        For j = 0 To ds7.Tables(0).Rows.Count - 1
            ptco1 = ds7.Tables(0).Rows(j)("IXCONO")
            ptwtco1 = ds7.Tables(0).Rows(j)("IXWTCO")
            ptggal1 = ds7.Tables(0).Rows(j)("GallonsDrip")
            meternumber = ds7.Tables(0).Rows(j)("IXMETR")
            gallonfactor = ds7.Tables(0).Rows(j)("IXGALF")
            btufactor = ds7.Tables(0).Rows(j)("IXBTUF")
 
            If (ptwtco1 >= 100 And ptwtco1 < 300) Then
                TMPMCF = Convert.ToDecimal(ptggal1) * Convert.ToDecimal(gallonfactor)
                WKMCF = TMPMCF / 100000
                TMPBTU = WKMCF * Convert.ToDecimal(btufactor)
                WKMMBTU = TMPBTU / 1000
            End If
            If (ptwtco1 >= 300 And ptwtco1 < 400) Then
                TMPMCF = Convert.ToDecimal(ptggal1) * oilfactor
                WKMCF = TMPMCF
                TMPBTU = WKMCF * oilbtu
                WKMMBTU = TMPBTU / 1000
            End If
            If (ptwtco1 >= 500 And ptwtco1 < 600) Then
                TMPMCF = Convert.ToDecimal(ptggal1) * waterfactor
                WKMCF = TMPMCF
                WKMMBTU = 0
            End If
            CVTMCF = Convert.ToInt32(WKMCF)
            CVTMMBTU = Convert.ToInt32(WKMMBTU)
 
            strSql8 = "Select *,ID from itpwork where MeterNumber='" & meternumber & "'"
            cmd = New MySqlCommand(strSql8, conn)
            da = New MySqlDataAdapter(cmd)
            ds8 = New DataSet()
            da.Fill(ds8)
 
            If ToDate.Length = 8 Then
                yr = ToDate.Substring(6, 2)
                replaceMonth = ToDate.Substring(0, 2)
                mo = replaceMonth.IndexOf("/")
 
                If mo <= "0" Then
                    mo = ToDate.Substring(0, 2)
                Else
                    mo = "0" & ToDate.Substring(0, 1)
                End If
 
                replaceDay = ToDate.Substring(3, 2)
                day = replaceDay.IndexOf("/")
 
                If day = "1" Then
                    day = ToDate.Substring(2, 2)
                Else
                    day = "0" & ToDate.Substring(2, 1)
                End If
 
                ToDate = mo & "/" & day & "/" & yr
 
            End If
 
            If ToDate.Length = 9 Then
                yr = ToDate.Substring(7, 2)
 
                replaceMonth = ToDate.Substring(0, 2)
                mo = replaceMonth.IndexOf("/")
 
                If mo <= "0" Then
                    mo = ToDate.Substring(0, 2)
                Else
                    mo = "0" & ToDate.Substring(0, 1)
                End If
 
                replaceDay = ToDate.Substring(3, 2)
                day = replaceDay.IndexOf("/")
 
                If day = "1" Then
                    day = ToDate.Substring(2, 2)
                Else
                    day = "0" & ToDate.Substring(2, 1)
                End If
 
                ToDate = mo & "/" & day & "/" & yr
 
            End If
 
            If ToDate.Length = 10 Then
                yr = ToDate.Substring(8, 2)
 
                replaceMonth = ToDate.Substring(0, 2)
                mo = replaceMonth.IndexOf("/")
 
                If mo <= "0" Then
                    mo = ToDate.Substring(0, 2)
                Else
                    mo = "0" & ToDate.Substring(0, 1)
                End If
 
                replaceDay = ToDate.Substring(3, 2)
                day = replaceDay.IndexOf("/")
 
                If day <= "0" Then
                    day = ToDate.Substring(3, 2)
                Else
                    day = ToDate.Substring(2, 2)
                End If
 
                ToDate = mo & "/" & day & "/" & yr
 
            End If
 
            If FromDate.Length = 8 Then
                yr2 = FromDate.Substring(6, 2)
                replaceMonth2 = FromDate.Substring(0, 2)
                mo2 = replaceMonth2.IndexOf("/")
 
                If mo2 <= "0" Then
                    mo2 = FromDate.Substring(0, 2)
                Else
                    mo2 = "0" & FromDate.Substring(0, 1)
                End If
 
                replaceDay2 = FromDate.Substring(3, 2)
                day2 = replaceDay2.IndexOf("/")
 
                If day2 = "1" Then
                    day2 = FromDate.Substring(2, 2)
                Else
                    day2 = "0" & FromDate.Substring(2, 1)
                End If
 
                FromDate = mo2 & "/" & day2 & "/" & yr2
 
            End If
 
            If FromDate.Length = 9 Then
                yr2 = FromDate.Substring(7, 2)
 
                replaceMonth2 = FromDate.Substring(0, 2)
                mo2 = replaceMonth2.IndexOf("/")
 
                If mo2 <= "0" Then
                    mo2 = FromDate.Substring(0, 2)
                Else
                    mo2 = "0" & FromDate.Substring(0, 1)
                End If
 
                replaceDay2 = FromDate.Substring(3, 2)
                day2 = replaceDay2.IndexOf("/")
 
                If day2 = "1" Then
                    day2 = FromDate.Substring(2, 2)
                Else
                    day2 = "0" & FromDate.Substring(2, 1)
                End If
 
                FromDate = mo2 & "/" & day2 & "/" & yr2
 
            End If
 
            If FromDate.Length = 10 Then
                yr2 = FromDate.Substring(8, 2)
 
                replaceMonth2 = FromDate.Substring(0, 2)
                mo2 = replaceMonth2.IndexOf("/")
 
                If mo2 <= "0" Then
                    mo2 = FromDate.Substring(0, 2)
                Else
                    mo2 = "0" & FromDate.Substring(0, 1)
                End If
 
                replaceDay2 = FromDate.Substring(3, 2)
                day2 = replaceDay2.IndexOf("/")
 
                If day2 <= "0" Then
                    day2 = FromDate.Substring(3, 2)
                Else
                    day2 = FromDate.Substring(2, 2)
                End If
 
                FromDate = mo2 & "/" & day2 & "/" & yr2
 
            End If
 
            If (ds8.Tables(0).Rows.Count = 0) Then
 
                strSql9 = "insert into itpwork(FromDate,ToDate,MeterNumber,MeterNumberSub,ConvertedMCF,ConvertedMMBTU) values('"
                strSql9 &= FromDate & "','" & ToDate & "','" & meternumber & "',' ','" & CVTMCF & "','" & CVTMMBTU & "')"
                cmd = New MySqlCommand(strSql9, conn)
                da = New MySqlDataAdapter(cmd)
                ds9 = New DataSet()
                da.Fill(ds9)
            Else
                Dim ID2 As String
                ID2 = ds8.Tables(0).Rows(0)("ID")
                strSql10 = "update itpwork set MeterNumber='" & meternumber & "',MeterNumberSub=' ',ConvertedMCF='" & CVTMCF & "',ConvertedMMBTU='" & CVTMMBTU & "',FromDate='" & FromDate & "',ToDate='" & ToDate & "'"
                strSql10 &= " where ID=" & ID2
                cmd = New MySqlCommand(strSql10, conn)
                da = New MySqlDataAdapter(cmd)
                ds10 = New DataSet()
                da.Fill(ds10)
            End If
 
            ToDate = Request.QueryString("ToDate")
            FromDate = Request.QueryString("FromDate")
 
        Next
 
        If (table3.Visible = False) Then
            lblMessage.Visible = True
            lblMessage.Text = " No missing meter number to be inserted"
        End If
 
        If ToDate.Length = 8 Then
            yr = ToDate.Substring(6, 2)
            replaceMonth = ToDate.Substring(0, 2)
            mo = replaceMonth.IndexOf("/")
 
            If mo <= "0" Then
                mo = ToDate.Substring(0, 2)
            Else
                mo = "0" & ToDate.Substring(0, 1)
            End If
 
            replaceDay = ToDate.Substring(3, 2)
            day = replaceDay.IndexOf("/")
 
            If day = "1" Then
                day = ToDate.Substring(2, 2)
            Else
                day = "0" & ToDate.Substring(2, 1)
            End If
 
            ToDate = mo & "/" & day & "/" & yr
 
        End If
 
        If ToDate.Length = 9 Then
            yr = ToDate.Substring(7, 2)
 
            replaceMonth = ToDate.Substring(0, 2)
            mo = replaceMonth.IndexOf("/")
 
            If mo <= "0" Then
                mo = ToDate.Substring(0, 2)
            Else
                mo = "0" & ToDate.Substring(0, 1)
            End If
 
            replaceDay = ToDate.Substring(3, 2)
            day = replaceDay.IndexOf("/")
 
            If day = "1" Then
                day = ToDate.Substring(2, 2)
            Else
                day = "0" & ToDate.Substring(2, 1)
            End If
 
            ToDate = mo & "/" & day & "/" & yr
 
        End If
 
        If ToDate.Length = 10 Then
            yr = ToDate.Substring(8, 2)
 
            replaceMonth = ToDate.Substring(0, 2)
            mo = replaceMonth.IndexOf("/")
 
            If mo <= "0" Then
                mo = ToDate.Substring(0, 2)
            Else
                mo = "0" & ToDate.Substring(0, 1)
            End If
 
            replaceDay = ToDate.Substring(3, 2)
            day = replaceDay.IndexOf("/")
 
            If day <= "0" Then
                day = ToDate.Substring(3, 2)
            Else
                day = ToDate.Substring(2, 2)
            End If
 
            ToDate = mo & "/" & day & "/" & yr
 
        End If
 
        If FromDate.Length = 8 Then
            yr2 = FromDate.Substring(6, 2)
            replaceMonth2 = FromDate.Substring(0, 2)
            mo2 = replaceMonth2.IndexOf("/")
 
            If mo2 <= "0" Then
                mo2 = FromDate.Substring(0, 2)
            Else
                mo2 = "0" & FromDate.Substring(0, 1)
            End If
 
            replaceDay2 = FromDate.Substring(3, 2)
            day2 = replaceDay2.IndexOf("/")
 
            If day2 = "1" Then
                day2 = FromDate.Substring(2, 2)
            Else
                day2 = "0" & FromDate.Substring(2, 1)
            End If
 
            FromDate = mo2 & "/" & day2 & "/" & yr2
 
        End If
 
        If FromDate.Length = 9 Then
            yr2 = FromDate.Substring(7, 2)
 
            replaceMonth2 = FromDate.Substring(0, 2)
            mo2 = replaceMonth2.IndexOf("/")
 
            If mo2 <= "0" Then
                mo2 = FromDate.Substring(0, 2)
            Else
                mo2 = "0" & FromDate.Substring(0, 1)
            End If
 
            replaceDay2 = FromDate.Substring(3, 2)
            day2 = replaceDay2.IndexOf("/")
 
            If day2 = "1" Then
                day2 = FromDate.Substring(2, 2)
            Else
                day2 = "0" & FromDate.Substring(2, 1)
            End If
 
            FromDate = mo2 & "/" & day2 & "/" & yr2
 
        End If
 
        If FromDate.Length = 10 Then
            yr2 = FromDate.Substring(8, 2)
 
            replaceMonth2 = FromDate.Substring(0, 2)
            mo2 = replaceMonth2.IndexOf("/")
 
            If mo2 <= "0" Then
                mo2 = FromDate.Substring(0, 2)
            Else
                mo2 = "0" & FromDate.Substring(0, 1)
            End If
 
            replaceDay2 = FromDate.Substring(3, 2)
            day2 = replaceDay2.IndexOf("/")
 
            If day2 <= "0" Then
                day2 = FromDate.Substring(3, 2)
            Else
                day2 = FromDate.Substring(2, 2)
            End If
 
            FromDate = mo2 & "/" & day2 & "/" & yr2
 
        End If
 
        strSql11 = "Select FromDate,ToDate,MeterNumber,MeterNumberSub,ConvertedMCF,ConvertedMMBTU from itpwork where FromDate>= '" & FromDate & "' and ToDate<= '" & ToDate & "' "
        cmd = New MySqlCommand(strSql11, conn)
        da = New MySqlDataAdapter(cmd)
        ds11 = New DataSet()
        da.Fill(ds11)
        'localhost
        DataTable2CSV(ds11.Tables(0), "C:\IT\ITWORK.txt")
 
        'Development Server
        'DataTable2CSV(ds11.Tables(0), "f:\inetpub\aqpweb\TTFIX\IT\ITWORK.txt")
 
        'Production(Server)
        'DataTable2CSV(ds11.Tables(0), "f:\inetpub\ftproot\trucktickets\ITWORK.txt")
        conn.Close()
 
    End Sub
    Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String)
        DataTable2CSV(Table, filename, vbTab)
    End Sub
    Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String, _
        ByVal sepChar As String)
        Dim writer As System.IO.StreamWriter = Nothing
        Try
            writer = New System.IO.StreamWriter(filename)
 
            ' first write a line with the columns name
            Dim sep As String = " "
            Dim builder As New System.Text.StringBuilder
            'For Each col As DataColumn In table.Columns
            'builder.Append(sep).Append(col.ColumnName)
            'sep = sepChar
            'Next
            'writer.WriteLine(builder.ToString())
 
            ' then write all the rows
            For Each row As DataRow In table.Rows
                sep = ""
                builder = New System.Text.StringBuilder
 
                For Each col As DataColumn In table.Columns
                    builder.Append(sep).Append(row(col.ColumnName))
                    sep = sepChar
                Next
                writer.WriteLine(builder.ToString())
            Next
        Finally
            If Not writer Is Nothing Then writer.Close()
        End Try
    End Sub
End Class

Open in new window

0
Comment
Question by:bschave2
2 Comments
 
LVL 13

Accepted Solution

by:
TechTiger007 earned 500 total points
ID: 22798650
try this

string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

conn = New MySqlConnection(connectionString)



0
 

Author Closing Comment

by:bschave2
ID: 31509681
thanks for helping, but it wasn't the right connection string for odbc. the correct code is


  OdbcConnection con = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ToString());
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vb.net 2 43
Close tabpage of custom control 9 33
Creating a route in asp.net webforms 2 25
VB.net Progress Bar - Maximum Value too large 2 7
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now