Solved

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

Posted on 2008-10-24
2
230 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

911 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

21 Experts available now in Live!

Get 1:1 Help Now