?
Solved

sql query date less then 5min ago

Posted on 2011-04-26
27
Medium Priority
?
302 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...... ??

0
Comment
Question by:runnerjp2005
  • 15
  • 12
27 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35468388
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35468392
0
 

Author Comment

by:runnerjp2005
ID: 35468470
Like this ... cmd.CommandText = "Select Count(*) From nousersonline where Forum =@forumname and ActivityDate BETWEEN DATEADD(minute, -5, GETDATE())"
0
Technology Partners: 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!

 

Author Comment

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

0
 
LVL 83

Expert Comment

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

Author Comment

by:runnerjp2005
ID: 35473905
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

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35473916
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

0
 

Author Comment

by:runnerjp2005
ID: 35474049
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.
0
 

Author Comment

by:runnerjp2005
ID: 35474062
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

0
 

Author Comment

by:runnerjp2005
ID: 35474127
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474174
Why do you need all that? Is it not returning 0? try changing

Select Count(*)

to

Select IsNull(Count(*) , 0)
0
 

Author Comment

by:runnerjp2005
ID: 35474190
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

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474202
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
0
 

Author Comment

by:runnerjp2005
ID: 35474253
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474266
As I said in previous comment. You need to add value for @forumname parameter.

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

Author Comment

by:runnerjp2005
ID: 35474300
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

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474316
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

0
 

Author Comment

by:runnerjp2005
ID: 35474331
No value given for one or more required parameters.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474352
Try with

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

and comment out the parameters line.
0
 

Author Comment

by:runnerjp2005
ID: 35474461
i get the same error sadly
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474468
To test, execute these queries separately

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

Author Comment

by:runnerjp2005
ID: 35474478
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
0
 

Author Comment

by:runnerjp2005
ID: 35474492
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.
0
 

Author Comment

by:runnerjp2005
ID: 35474498
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??
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35474502
Ok lets try this then



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

0
 

Author Comment

by:runnerjp2005
ID: 35474528
It works!!!!!!!

What was the fix??
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35474867
You had n without quotes in DateAdd
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question