SQL返回错误计数以限制提交 [英] SQL returning incorrect count to limit submissions
本文介绍了SQL返回错误计数以限制提交的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2张桌子。当按下按钮时,存储过程检查table1是否有某些数据,并将用户的IP和当前日期和时间记录到table2中。
在我的代码中,我正在尝试在最近30分钟内检查用户IP的每次出现都会检查table2(ReverseTransferConsent_Attempt)。
尽管我反复提交信息,但它只返回1。有什么想法吗?
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?
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
推荐答案
我修好了。
我使用的是executeScalar而不是计算数据集:
I fixed it.
I used executeScalar instead of counting in a dataset:
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
这篇关于SQL返回错误计数以限制提交的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文