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