Hi,

Can anyone help on my code. i'm not sure whether i'm code in right way or not. I would like to do

1) read total value from a particular table in 2 database (MyAccount & DealerAccount)from the most recent transaction
2) read grandtotal from another database from the most recent transaction
3) And at last i would like to get the current balance
-------> grandTotal = Sum(grandtotal)-(Totaldealer+Totaldealer1)

Please help.

Public Class Form1


    Public Function Checking_RetailerData(ByVal Grand As String, ByVal sValue As String, Optional ByVal bUpdate As Boolean = True) As String
        Checking_RetailerData = "0"

        'Try
        Dim myCommand As SqlCommand
        Dim strSQL As String = ""
        Dim myConnection As SqlConnection
        Dim connStr As String
        Dim dr As SqlDataReader

        connStr = "server=(local); database=Credit; Trusted_Connection=yes"
        myConnection = New SqlConnection(connStr)

        If myConnection.State = 0 Then
            myConnection.Open()
        End If

        strSQL = "SELECT top 1 GrandTotal FROM RetailerBalance WHERE BoothID='" & BoothID & "' order by TransactionDate desc"


        myCommand = New SqlCommand(strSQL, myConnection)
        dr = myCommand.ExecuteReader
        If dr.Read Then
            If Not dr.IsDBNull(0) Then
                GrandTotal = dr(0)

            End If
        End If
        'End If

        myConnection.Close()

        '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        connStr = "server=(local); database=MyAccount; Trusted_Connection=yes"
        myConnection = New SqlConnection(connStr)

        If myConnection.State = 0 Then
            myConnection.Open()
        End If


        strSQL = "SELECT sum(PaymentToDealer) FROM BillTransaction WHERE BoothID='" & BoothID & "' order by TransactionDate desc"


        myCommand = New SqlCommand(strSQL, myConnection)
        dr = myCommand.ExecuteReader
        If dr.Read Then
            If Not dr.IsDBNull(0) Then
                Dealer = dr(0)

            End If
        End If
        'End If

        myConnection.Close()

        '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

        connStr = "server=(local); database=DealerAccount; Trusted_Connection=yes"
        myConnection = New SqlConnection(connStr)

        If myConnection.State = 0 Then
            myConnection.Open()
        End If


        strSQL = "SELECT sum(PaymentToDealer) FROM BillTransaction WHERE BoothID='" & BoothID & "' order by TransactionDate desc"


        myCommand = New SqlCommand(strSQL, myConnection)
        dr = myCommand.ExecuteReader
        If dr.Read Then
            If Not dr.IsDBNull(0) Then
                Dealer1 = dr(0)

            End If
        End If
        'End If

        myConnection.Close()

        '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


        connStr = "server=(local); database=CreditLimit; Trusted_Connection=yes"
        myConnection = New SqlConnection(connStr)

        If myConnection.State = 0 Then
            myConnection.Open()
        End If

        strSQL = "INSERT INTO dbo.RetailerBalance([BoothID],[TransactionDateTime],[TransactionDate], [GrandTotal],) VALUES ('" & BoothID & "','" & CurrentTranxDate & GrandTotal - (Dealer + Dealer1) & "')"


        If myConnection.State = 0 Then
            myConnection.Open()
        End If

        myCommand = New SqlCommand(strSQL, myConnection)
        myCommand.ExecuteNonQuery()



        myConnection.Close()




    End Function

End Class

Just a few comments.

Be sure to dr.close each data reader before closing the connection to the MySql database.

On the INSERT commnad I will change it to sime thing like:

strSQL = "INSERT INTO dbo.RetailerBalance([BoothID],[TransactionDateTime],[TransactionDate], [GrandTotal],) VALUES ('" & BoothID & "','" & Format(CurrentTranxDate,"yyyy-MM-dd") & "', " & (GrandTotal - (Dealer + Dealer1)).ToString.Replace(System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencyDecimalSeparator,".") & ")"

Specially importannt to replace the decimal character by a point, that is the decimal separator accepted by MySQL. If the decimal separatos is the comma, it would be interpreted as a field separator.

Also interesting to format the date as universal format in order do avoit MySQL to decide if 11/07/2010 means July 11th, or Novembert 7th.

Hope this helps

Thank You

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.