Solved

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

Posted on 2008-10-24
2
223 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
Comment Utility
try this

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

conn = New MySqlConnection(connectionString)



0
 

Author Closing Comment

by:bschave2
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 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

9 Experts available now in Live!

Get 1:1 Help Now