want to open two connections at same time

[didgydont]

hi all
at the moment i have the code below which works fine but i need to add another colum that adds the total of a field but the problem is it errors out if i try to open another connection while in the while it needs to add the total of price while TransID = Rec.Fields("TransID").Value

'read data
        Rec.Open("SELECT * FROM records order by TransID DESC", Conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
        Dim trasid As Integer = 0
        Dim disptrans As Integer = 0
        Dim transtolal As Decimal = 0
        If Not Rec.EOF Then
            trasid = Rec.Fields("TransID").Value + 1

        End If
        While Not Rec.EOF
            If Rec.Fields("TransID").Value < trasid And disptrans < 4 Then

                trasid = Rec.Fields("TransID").Value
                disptrans = disptrans + 1
                DataGridView2.Rows.Add(Rec.Fields("TransID").Value, Rec.Fields("Sold").Value)
            End If
            Rec.MoveNext()
        End While
        Rec.Close()

 

[jmcilhinney]

Please post in the forum most appropriate for the topic of your post, not the first one you come to. Moved.

This is a good opportunity to dump ADO and start using ADO.NET.

 

[didgydont]

is this what you ment ?

Private Function transtotal(ByVal transid As String) As Decimal
        Dim ktotal As Decimal = 0.
        Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & My.Settings.UserDatabaseLocation & ";Jet OLEDB:Database Password=sfdbpa55word;")
            connection.Open()
            Dim cmd As OleDbCommand
            Dim dr As OleDbDataReader
            cmd = New OleDbCommand("SELECT * FROM records where TransID =" & transid, connection)
            dr = cmd.ExecuteReader
            While dr.Read()
                ktotal = ktotal + dr(3)
            End While

        End Using

                Return ktotal
    End Function

 

[jmcilhinney]

That's ADO.NET alright. That's really not the way to sum a column though. You should use your SQL to generate the sum, which is far more efficient overall:

Using connection As New OleDbConnection("...")
    Dim command As New OleDbCommand("SELECT SUM(SomeColumn) FROM Records WHERE TransID = @TransID", connection)

    command.Parameters.AddWithValue("@TransID", transID)
    connection.Open()

    Return CDec(command.ExecuteScalar())
End Using

 

[didgydont]

thank you very much and sorry for post in wrong area

 

[cjard]

see the dw2 link in my signature