read from multiple databases

[swathys]

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

 

[cjard]

Is there a problem with it?