[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

Why wont oledatareader bring in new data?

The function is to return true if the sum sql returns a value over 100.  However if i am on the site and continually add funds dr(0) is always the original number and never changes.  I then run the sql on the server and it returns the correct number.  

    Private Function chargelimitreached() As Boolean 'matt added function
        Dim MySQL As String = "select SUM(damount) from tblHistory where dtdate between DATEADD(DAY,-14,getdate()) and GETDATE() and lDeviceNumber in (6,7) and dIDNumber = " & campusglobals.campusID
        Dim limitreached As Boolean = False
        Dim dr As OleDbDataReader = MainDataAccess.getDatareader(MySQL, "iAmecsAdvanced")
        MsgBox(dr(0).ToString)
        If (dr.HasRows) Then
            If (Not IsDBNull(dr(0))) Then
                If dr(0) > 100 Then
                    limitreached = True
                End If
            End If
        End If
        dr.Close()
        Return limitreached
    End Function

Open in new window

0
Millkind
Asked:
Millkind
1 Solution
 
CodeCruiserCommented:
Try following


 Private Function chargelimitreached() As Boolean 'matt added function
        Dim MySQL As String = "select SUM(damount) from tblHistory where dtdate between DATEADD(DAY,-14,getdate()) and GETDATE() and lDeviceNumber in (6,7) and dIDNumber = " & campusglobals.campusID
        Dim limitreached As Boolean = False
        Dim dr As OleDbDataReader = MainDataAccess.getDatareader(MySQL, "iAmecsAdvanced")
        If (dr.HasRows) Then
             dr.Read()
            If (Not IsDBNull(dr(0))) Then
                If dr(0) > 100 Then
                    limitreached = True
                End If
            End If
        End If
        dr.Close()
        Return limitreached
    End Function

Open in new window

0
 
MillkindAuthor Commented:
The page gets stuck.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you may want to request attention on this question and add the topic ".Net", you'll have a better chance of getting the right folks looking at your question, as this doesn't seem to be an classic asp related issue.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CodeCruiserCommented:
Have you stepped through the code to find the execution path and where its getting stuck?

Also, you can get rid of the reader altogether by using the ExecuteScalar method instead of ExecuteReader.
0
 
LordWabbitCommented:
DataReaders do not start on the first record, you have to call read once for the first record to come up, they are meant to be used for looping, for example
                Dim reader As OleDbDataReader = command.ExecuteReader()
        While reader.Read()
            Console.WriteLine(reader(0).ToString())
        End While

Open in new window

This is so you don't skip the first record, everytime read is called it moves to the next record.  If you started on the first record and entered the loop you would skip the first record.  So it starts BEFORE the first record (where there is no data) and when you enter the first time moves to the first record.
0
 
MillkindAuthor Commented:
The dr.read() is where everything is getting held up.  I am working on implementing the execute scalar
0
 
MillkindAuthor Commented:
This code has the same problem of not getting new data.

    Private Function chargelimitreached() As Boolean 'matt added function
        Dim conn As New SqlConnection("XXXXXX")
        Dim MySQL As String = "select SUM(damount) from tblHistory where dtdate between DATEADD(DAY,-14,getdate()) and GETDATE() and lDeviceNumber in (6,7) and dIDNumber = " & campusglobals.campusID
        Dim limitreached As Boolean = False
        Dim sumofpurchases As Integer = 0
        Dim cmd As New SqlCommand(MySQL, conn)
        conn.Open()
        If Not IsDBNull(cmd.ExecuteScalar()) Then
            sumofpurchases = Convert.ToInt32(cmd.ExecuteScalar())
        End If
        MsgBox(sumofpurchases)
        conn.Close()
        If sumofpurchases > 100 Then
            limitreached = True
        End If
        Return limitreached
    End Function

Open in new window

0
 
CodeCruiserCommented:
In that case, show us the code which is calling this function and also the code which is updating the database.
0
 
MillkindAuthor Commented:
The following is the code that is used when the patron want to add money.  If they are an employee certain criteria must be met.  The can only add less than 50 at a time and can not have added over 100 in the last 14 days. The call to the function is below.  Also the funds are added via a .dll called UpdatePatron.  The dll is unable to be read by me.  

Could the problem be to do with the browser caching?

 Protected Sub btnCharge_Click(sender As Object, e As EventArgs) Handles btnCharge.Click 'added by matt
        If (Not Page.IsValid) Then
            Exit Sub
        End If
        ''get amount
        Dim dAmount As Decimal = 0
        If RadioButton1.Checked = True Then
            dAmount = 10.0
        ElseIf RadioButton2.Checked = True Then
            dAmount = 25.0
        ElseIf RadioButton3.Checked = True Then
            dAmount = 50.0
        ElseIf RadioButton4.Checked = True Then
            dAmount = 75.0
        ElseIf RadioButton5.Checked = True Then
            dAmount = 100.0
        ElseIf RadioButton6.Checked = True Then
            dAmount = 200.0
        ElseIf RadioButton7.Checked = True Then
            Try
                Int32.Parse(amtno.Value)   'test to see if amount is whole dollars.
            Catch
                RESULT.Text = "Other Amount - must be whole dollars"
                Exit Sub
            End Try
            dAmount = Decimal.Parse(amtno.Value)
        End If
        ''if this is an employee check to make sure they have NOT charged more than 100 in the past two weeks, if true thye cannont add and give them the warning.
        ''if this is an employee check to make sure they only want to add 10, 25, 50 else give the warning and have them select another
        If deviceNumber = 6 Then 'this means they are an employee
            If chargelimitreached() Then
                proc1.Text = "You have exceeded the maximum payroll deduction amount for a two week period."
                Exit Sub
            End If
            If dAmount > 50 Then
                proc1.Text = "The amount you have selected is too high for Employee Payroll Deduction maximum of $50.00."
                Exit Sub
            End If
        End If
        ''set up variables for returned data
        Dim histVal As String
        histVal = ""
        Dim balance As Double
        'account stuff
        Dim accountName As String = ""
        Dim accountID As String = acct.Value
        Select Case accountID
            Case "65"
                accountName = da1
            Case "66"
                accountName = da2
            Case "67"
                accountName = da3
            Case "68"
                accountName = da4
            Case "69"
                accountName = da5
            Case "71"
                accountName = da6
            Case "71"
                accountName = da7
            Case Else
                accountName = ""
        End Select
        Dim val As Integer
        Dim u As UpdatePatron.UPatron
        u = New UpdatePatron.UPatron()
        val = u.UpdateAccounts("Gettysburg", 0, deviceNumber, 2, MainDataAccess.getDb(), campusglobals.campusID, accountID, dAmount, balance, histVal)
        'redirect to confirmation
        Response.Redirect("confirmation_Charges.aspx?Amount=" & dAmount & "&AcctName=" & accountName & "&Response=" & histVal)
    End Sub

Open in new window

0
 
CodeCruiserCommented:
And where do you call the updatepatron in terms of the workflow? Does it work properly if you update using your code?
0
 
MillkindAuthor Commented:
the update patrong call is near the end of the code i submitted.  It looks like this.

   Dim val As Integer
        Dim u As UpdatePatron.UPatron
        u = New UpdatePatron.UPatron()
        val = u.UpdateAccounts("Gettysburg", 0, deviceNumber, 2, MainDataAccess.getDb(),campusglobals.campusID, accountID, dAmount, balance, histVal)
0
 
CodeCruiserCommented:
Code looks fine. Seems to be a workflow issue somewhere. What is the sequence of actions? You charge someone, it checks that you have not charged 100 people already in last 14 days, lets you proceed, you try again and it should block you if you have reached 100?
0
 
MillkindAuthor Commented:
Yes that is correct and each time you add you are redirected to the confirmation page and return via a redirect button from that page if you choose.  The problem is that even if i close the browser (Chrome) and reopen it still has the same problem.
0
 
CodeCruiserCommented:
So you have 99 charges made and then you add your 100th charge and it still lets you add 101th charge?
0
 
MillkindAuthor Commented:
Yes.  Because the server has the correct data.  But the page keeps returning the first piece of data.
0
 
MillkindAuthor Commented:
Okay I found it.  I can't believe I missed it.  It was the SQl statement.  I had to add a day to the getdate for today.  Now it seems silly I didn't figure it out earlier.  Thanks for the help, sorry for the brain cramps. Any problem splitting the points between the three main contributors?

The original:
select SUM(damount) from tblHistory where dtdate between DATEADD(DAY,-14,getdate()) and GETDATE() and lDeviceNumber in (6,7) and dIDNumber = " & campusglobals.campusID

The new:
select SUM(damount) from tblHistory where dtdate between DATEADD(DAY,-14,getdate()) and dateadd(DAY,1,GETDATE()) and lDeviceNumber in (6,7) and dIDNumber =  & campusglobals.campusID
0
 
CodeCruiserCommented:
Glad you got it sorted :-)
0
 
MillkindAuthor Commented:
I figured it out
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now