We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

sql query date less then 5min ago

Member_2_5230414
on
Medium Priority
317 Views
Last Modified: 2012-05-11
Hello,

Im my db i store my time as 26/04/2011 17:04:17

what i want to do is run a query to get all results recorded between now and 5min ago
cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and ActivityDate BETWEEN...... ??

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Use the GetDate to get current datetime and use DataAdd(with -5) to get the datetime 5 mins ago


http://msdn.microsoft.com/en-us/library/ms186819.aspx
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:

Author

Commented:
Like this ... cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and ActivityDate BETWEEN DATEADD(minute, -5, GETDATE())"

Author

Commented:
  cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5," & DateTime.Now.ToString & " ') and " & DateTime.Now.ToString & ")"
           

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now()"

Author

Commented:
with this i get No value given for one or more required parameters.

my full code is
 Dim yes As Results = onforum.noposts("Main Forum")
        Userson.Text = yes.ToString

Open in new window


and
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Imports Class1

Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Results
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try

            con.Open()
            cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            Dim Res As Results
            Res = cmd.ExecuteScalar()
            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I have been trying to show you many times that you only need to use the Results structure when you were trying to return many many values from the function. In this case, you are only returning a single value.


Are you sure there are posts in past 5 minutes?
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Imports Class1

Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Integer
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try

            con.Open()
            cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            Dim Res As Integer
            Res = cmd.ExecuteScalar()
            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window

Author

Commented:
If there are 0 posts in the last 5 min wont integer display as 0?

Also i really do appriciate all the help you have given me and it just get a little confused every now and then.

Author

Commented:
Would it need to be :

   cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            Dim Res As Integer


            If Res <= "0" Then
                Res = "0"

            Else
                Res = cmd.ExecuteScalar()
            End If
            Return Res

Open in new window

Author

Commented:
At the moment i am using  Try

   
        con.Open()
            cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            Dim Res As Integer
            'Res = cmd.ExecuteScalar()
            If Res > 0 Then
                Res = 1
            Else
                Res = 2
            End If



            Return Res

Open in new window


But it returns 2 no matter what the time diff is?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Why do you need all that? Is it not returning 0? try changing

Select Count(*)

to

Select IsNull(Count(*) , 0)

Author

Commented:
i get Wrong number of arguments used with function in query expression 'IsNull(Count(*) , 0'. with
Select IsNull(Count(*) , 0) 

Open in new window



and


No value given for one or more required parameters. with
  cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Use

cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"

pass value for the forumname parameter and use this

Dim o As Object = cmd.ExecuteScalar()
If IsDBNull(o) = False Then
   Res = o
Else
   Res = 0
End If
Return Res

Author

Commented:
Ok i did this:
   cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            Dim Res As Integer
            Dim o As Object = cmd.ExecuteScalar()
            If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res

Open in new window

and got : No value given for one or more required parameters.



I have set my ActivityDate to show 27/04/2011 10:14:49 my local time is 11:46
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
As I said in previous comment. You need to add value for @forumname parameter.

cmd.Parameters.AddWithValue("@forumname", forumname)

Author

Commented:
Sorry i had allready done this in the code further up

Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Integer
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@forumname", forumname)
        Try

            con.Open()
            cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            Dim Res As Integer
            Dim o As Object = cmd.ExecuteScalar()
            If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res
          



            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try this



Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Integer
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb;")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try

            con.Open()
            cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"
            cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim Res As Integer
            Dim o As Object = cmd.ExecuteScalar()
            If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res
          



            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window

Author

Commented:
No value given for one or more required parameters.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try with

cmd.CommandText = "Select Count(*) From nousersonline where (ActivityDate BETWEEN DATEADD(n, -5, Now()) and Now())"

and comment out the parameters line.

Author

Commented:
i get the same error sadly
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
To test, execute these queries separately

Select Now(), * From nousersonline
Select  DATEADD(n, -5, Now()), * From nousersonline

Author

Commented:
When i try debugging it  Dim o As Object = cmd.ExecuteScalar() has a value oth nothing and it skipps

 If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res

Author

Commented:
Ok so with
Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Integer
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try

            con.Open()
            cmd.CommandText = "Select Now(), * From nousersonline "
            'cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim Res As Integer
            Dim o As Object = cmd.ExecuteScalar()
            If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res




            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window

I get Conversion from type 'Date' to type 'Integer' is not valid.

and with
Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Integer
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try

            con.Open()
            cmd.CommandText = "Select  DATEADD(n, -5, Now()), * From nousersonline "
            'cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim Res As Integer
            Dim o As Object = cmd.ExecuteScalar()
            If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res




            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window


i get No value given for one or more required parameters.

Author

Commented:
I also tested this
Public Class onforum
    Shared Function noposts(ByVal forumname As String) As Integer
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\perkinj\My Documents\Visual Studio 2010\WebSites\runningprofiles\forums\forum.mdb")
        Dim cmd As New OleDbCommand
        cmd.Connection = con
        Try

            con.Open()
            cmd.CommandText = "Select count(*) From nousersonline"
            'cmd.Parameters.AddWithValue("@forumname", forumname)
            Dim Res As Integer
            Dim o As Object = cmd.ExecuteScalar()
            If IsDBNull(o) = False Then
                Res = o
            Else
                Res = 0
            End If
            Return Res




            Return Res

        Catch ex As Exception
            Throw ex
        Finally

            con.Close()
        End Try

    End Function
End Class

Open in new window


and i get 1 so it must be something with the select command??
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
It works!!!!!!!

What was the fix??
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You had n without quotes in DateAdd
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.