ConnexusDave
asked on
Reading with an sqlreader in vb.net. I want to sum for multiple records, one member number. How do I keep a member number while continuing to read the file?
I want to sum for multiple records, one member number. How do I keep a member number while continuing to read the file?
read in a record, assign member_nbr to HoldMember_Nbr
process that record
read in a new record, check if Member_Nbr = HoldMember_Nbr
If it does not process that record
If it does write record to a new file.
read in a record, assign member_nbr to HoldMember_Nbr
process that record
read in a new record, check if Member_Nbr = HoldMember_Nbr
If it does not process that record
If it does write record to a new file.
Dim Member_Nbr, CurrentMbrNbr, Share_Nbr, TransType, DAATMWCNT, DACUATMWCT, DAFCCNT, DAATMWCNT37, DACUATMWCT37, DAFCCNT27, DAFCCNT37 As Integer
Dim TransDesc, NetOrg, TermID As String
Member_Nbr = 0
CurrentMbrNbr = 0
Share_Nbr = 0
TransType = 0
TermID = ""
TransDesc = ""
NetOrg = ""
DAATMWCNT = 0
DACUATMWCT = 0
DAFCCNT = 0
DAATMWCNT37 = 0
DACUATMWCT37 = 0
DAFCCNT27 = 0
DAFCCNT37 = 0
Dim TogetherFinalSortedquery As String
TogetherFinalSortedquery = "SELECT dbo.TogetherFinalSorted.MemberNumber,dbo.TogetherFinalSorted.TransDesc,dbo.TogetherFinalSorted.TransType, " & _
"dbo.TogetherFinalSorted.TermID,dbo.TogetherFinalSorted.NetOrg " & _
"FROM TogetherFinalSorted " & _
"ORDER BY dbo.TogetherFinalSorted.MemberNumber asc"
Using TogetherFinalSortedconnstring As New SqlConnection(dxp2connstring)
Dim TogetherFinalSortedreadercommand As New SqlCommand(TogetherFinalSortedquery, TogetherFinalSortedconnstring)
Try
TogetherFinalSortedconnstring.Open()
Dim TogetherFinalSortedreader1 As SqlDataReader = TogetherFinalSortedreadercommand.ExecuteReader()
While TogetherFinalSortedreader1.Read()
Dim TogetherFinalSortedAppend As New SqlConnection(dxp2connstring)
Try
TogetherFinalSortedAppend.Open()
Catch E15 As Exception
MsgBox(E15.Message)
End Try
If CurrentMbrNbr <> TogetherFinalSortedreader1(0) Then
GoTo AddRecord
Else
CurrentMbrNbr = TogetherFinalSortedreader1(0)
TransDesc = TogetherFinalSortedreader1(1)
TransType = TogetherFinalSortedreader1(2)
TermID = TogetherFinalSortedreader1(3)
NetOrg = TogetherFinalSortedreader1(4)
End If
If (Not (Trim(TransDesc) Like "CR ADJ") And Not (Trim(TransDesc) Like "DEPOSIT") And Not (Trim(TransDesc) Like "TFR DEP")) Then
If FindTermID(TermID) Then
If TransType = 27 Then
DACUATMWCT = DACUATMWCT + 1
Else
DACUATMWCT37 = DACUATMWCT37 + 1
End If
Else
If ((Not Trim(TransDesc) Like "RECUR" And Not (TransDesc) Like "PURCHSE" And Not (TransDesc) Like "WD REV ") And TransType Like "27") Then
DAATMWCNT = DAATMWCNT + 1
Else
If ((Not (TransDesc) Like "RECUR" And Not (TransDesc) Like "PURCHSE" And Not (TransDesc) Like "WD REV ") And TransType <> "27") Then
DAATMWCNT37 = DAATMWCNT37 + 1
Else
If TransDesc Like "PURCHSE" And (Not (NetOrg) Like "VSA" And Not (NetOrg) Like "VSI" And Not (NetOrg) Like "VSB") And TransType Like "27" Then
DAATMWCNT = DAATMWCNT + 1
Else
If TransDesc Like "PURCHSE" And (Not (NetOrg) Like "VSA" And Not (NetOrg) Like "VSI") And Not (NetOrg) Like "VSB" And TransType <> "27" Then
DAATMWCNT37 = DAATMWCNT37 + 1
Else
If (Trim(TransDesc) Like "PURCHSE" Or Trim(TransDesc) Like "RECUR") Or (NetOrg Like "VSA" Or (NetOrg) Like "VSI" Or (NetOrg) Like "VSB") Then
If TransType = 27 Then
DAFCCNT27 = DAFCCNT27 + 1
Else
DAFCCNT37 = DAFCCNT37 + 1
End If
End If
End If
End If
End If
End If
End If
End If
AddRecord:
If Member_Nbr = TogetherFinalSortedreader1(0) Then
GoTo SameMember
Else
If DAATMWCNT > 0 Or DACUATMWCT > 0 Or DAFCCNT27 > 0 Then
Member_Nbr = CurrentMbrNbr
Share_Nbr = 2
DAATMWCNT = DAATMWCNT
DACUATMWCT = DACUATMWCT
DAFCCNT = DAFCCNT27
End If
If (DAATMWCNT37 + DACUATMWCT37) > 0 Or Not (DAATMWCNT37 + DACUATMWCT37) = 0 Then
Member_Nbr = CurrentMbrNbr
Share_Nbr = 1
DAATMWCNT = DAATMWCNT37
DACUATMWCT = DACUATMWCT37
DAFCCNT = DAFCCNT37
End If
Dim FeeJobCountTableAppendCommand2 As String = "SELECT * FROM FeeJobCountTable"
Dim FeeJobCountTableAppendadapter2 As SqlDataAdapter = New SqlDataAdapter(FeeJobCountTableAppendCommand2, TogetherFinalSortedconnstring)
FeeJobCountTableAppendadapter2.SelectCommand = New SqlCommand(FeeJobCountTableAppendCommand2, TogetherFinalSortedconnstring)
Dim FeeJobCountTableAppendQuery2 As String = "insert into FeeJobCountTable " & _
"(Member_NBR,Share_Nbr,DAATMWCNT,DACUATMWCT,DAFCCNT)" & _
"VALUES (@Member_NBR,@Share_Nbr,@DAATMWCNT,@DACUATMWCT, @DAFCCNT)"
FeeJobCountTableAppendadapter2.InsertCommand = New SqlCommand(FeeJobCountTableAppendQuery2, TogetherFinalSortedconnstring)
FeeJobCountTableAppendadapter2.InsertCommand.Parameters.Add("@MemberNumber", SqlDbType.BigInt, 8).Value = TogetherFinalSortedreader1(0)
FeeJobCountTableAppendadapter2.InsertCommand.Parameters.Add("@Share_Nbr", SqlDbType.Int, 4).Value = TogetherFinalSortedreader1(1)
FeeJobCountTableAppendadapter2.InsertCommand.Parameters.Add("@DAATMWCNT", SqlDbType.Int, 4).Value = TogetherFinalSortedreader1(2)
FeeJobCountTableAppendadapter2.InsertCommand.Parameters.Add("@DACUATMWCT", SqlDbType.Int, 4).Value = TogetherFinalSortedreader1(3)
FeeJobCountTableAppendadapter2.InsertCommand.Parameters.Add("@DAFCCNT", SqlDbType.Int, 4).Value = TogetherFinalSortedreader1(4)
Using TogetherFinalSortedAppend
Try
Dim EntryDateAndTranDatewriterecordsAffected As Int32 = FeeJobCountTableAppendadapter2.InsertCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
TogetherFinalSortedAppend.Close()
Member_Nbr = 0
CurrentMbrNbr = 0
Share_Nbr = 0
TransType = 0
TermID = ""
TransDesc = ""
NetOrg = ""
DAATMWCNT = 0
DACUATMWCT = 0
DAFCCNT = 0
DAATMWCNT37 = 0
DACUATMWCT37 = 0
DAFCCNT27 = 0
DAFCCNT37 = 0
End If
SameMember:
End While
'***************************************************************************************************************************
Catch ex1 As Exception
MsgBox(ex1.Message)
End Try
TogetherFinalSortedconnstring.Close()
End Using
MsgBox("Shazam Process Files Created.")
You store in a variable that will only change when you detect that the value on the variable is different than the value on the current record
ASKER
Where in the code do you store the variable, the way I have it now it changes everytime you read a new record? I am used to Access code and does this routine different than vb.net. I have always used next record, but vb.net does not have (as far as I know) this function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you