Click here to Skip to main content
14,869,378 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 2 tables. When the button is pressed a stored procedure checks table1 for certain data and logs the user's IP and the current date and time into table2.

In my code below I am trying to check table2 (ReverseTransferConsent_Attempt) for every occurrence of the user's IP within the last 30 minutes.

It only ever returns 1 though despite me submitting the info repeatedly. Any ideas?

VB
Protected Sub btn_Cont_Click(sender As Object, e As EventArgs) Handles btn_Cont.Click
    Dim StudentIDLast4 As Integer = Val(textSSN.Text)
    Dim StudentIDInst As String = textSID.Text.ToUpper
    Dim DateOfBirth As String = textDOB.Text
    Dim IPaddress As String = Request.UserHostAddress()

    Dim sqlConnection1 As New SqlConnection("Data Source=(localdb)\v11.0;Initial Catalog=tempdb;Integrated Security=True")
    Dim cmd As New SqlCommand
    Dim returnValue As String
    Dim returnCount As Integer

    cmd.CommandText = "proc_ReverseTransferConsent_Find_Match"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@StudentIDLast4", StudentIDLast4)
    cmd.Parameters.AddWithValue("@StudentIDInst", StudentIDInst)
    cmd.Parameters.AddWithValue("@DateOfBirth", DateOfBirth)
    cmd.Parameters.AddWithValue("@IPaddress", IPaddress)

    cmd.Connection = sqlConnection1

    ' code for the IP/time submission count
    Dim sqlConnection2 As New SqlConnection("Data Source=(localdb)\v11.0;Initial Catalog=tempdb;Integrated Security=True")
    Dim attempts As String
    Dim comm As New SqlCommand("SELECT [Count] = COUNT(*) FROM ReverseTransferConsent_Attempt WHERE IPaddress = @IPaddress AND CreatedDate > DATEADD(MINUTE, -30, GETDATE())", sqlConnection1)
    Dim ap As New SqlDataAdapter(comm)
    Dim ds As New DataSet()
    comm.Parameters.AddWithValue("@IPaddress", IPaddress)

    If Page.IsValid Then
        ' open connection2 and get count
        sqlConnection2.Open()
        ap.Fill(ds)
        attempts = ds.Tables(0).Rows.Count.ToString()
        sqlConnection2.Close()
        Label1.Text = attempts

        sqlConnection1.Open()
        returnValue = Convert.ToString(cmd.ExecuteScalar())
        sqlConnection1.Close()
        returnCount = returnValue.Length
        If attempts <= 5 Then
            If returnCount > 4 Then
                Response.Redirect("RTAgreement.aspx?rVal=" + returnValue)
            Else
                Label2.Text = "We could not find your information in our database."
            End If
        ElseIf attempts > 5 Then
            Label2.Text = "Only 5 submission attempts allowed per 30 minutes"
        End If
    End If
End Sub
Posted

1 solution

I fixed it.

I used executeScalar instead of counting in a dataset:

VB
Dim sqlConnection2 As New SqlConnection("Data Source=(localdb)\v11.0;Initial Catalog=tempdb;Integrated Security=True")
        Dim attempts As String
        Dim comm As New SqlCommand("SELECT [Count] = COUNT(*) FROM ReverseTransferConsent_Attempt WHERE IPaddress = @IPaddress AND CreatedDate > DATEADD(MINUTE, -30, GETDATE())", sqlConnection1)
        comm.Parameters.AddWithValue("@IPaddress", IPaddress)

        comm.Connection = sqlConnection2

        If Page.IsValid Then

            sqlConnection2.Open()
            attempts = Convert.ToString(comm.ExecuteScalar())
            sqlConnection2.Close()
            Label1.Text = attempts
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900