We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Time difference in Gridview using textbox outside of gridview

Medium Priority
522 Views
Last Modified: 2013-11-07
Hi,

I have a gridview and textbox.  The textbox holds the current time for cutoff of entries being entered to the db. The Gridview should display data before the cutoff time.  After the cutoff the gridview should state "AFTER CUTOFF."  My code is below.  I am getting a null return when I run the code, but there should be some records displayed.  Any assistance you all can provide would be great!

Thanks
Protected Sub FillGrid()
        Dim NewConnection As New SqlConnection
 
        NewConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
 
        Dim cmd As New SqlCommand
        cmd.Connection = NewConnection
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "spFillGrid"
       
        For i As Integer = 0 To GridView1.Rows.Count - 1
            Dim row As GridViewRow = GridView1.Rows(i)
            Dim crTime As String = DirectCast(row.Cells(3).FindControl("lblbox1"), Label).Text
            
 
            cmd.Parameters.AddWithValue("@LogDateTime", TextBox2.Text)
 
            'code for time diff
 
            If crTime >= TextBox2.Text Then
                GridView1 = Nothing
                GridView1.EmptyDataText = "AFTER CUTOFF"
                'Else
                'txt_DateLogged.Text = TimeValue(Now)
                'txt_DateLogged.Text = DateValue(Now)
 
            End If
 
 
            Dim NewDA As New SqlDataAdapter
            NewDA.SelectCommand = cmd
            Dim NewDS As New Data.DataSet
            NewDA.Fill(NewDS)
            GridView1.DataSource = NewDS
        Next
        GridView1.DataBind()
 
    End Sub

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Are you trying to set a value for a TextBox that is part of the ItemTemplate for a TemplateField, or the EditItemTemplate?
Praveen VenuTechnical Project Manager

Commented:
What do upi mean by cutoff?

Are you trying to change the text of the LogDateTime field to After Cutoff for each row if the value of LogDateTime is greater than the value in the textbox?

Open in new window

Praveen VenuTechnical Project Manager

Commented:
What do you mean by cutoff?

Are you trying to change the text of the LogDateTime field to After Cutoff for each row if the value of LogDateTime is greater than the value in the textbox?

Author

Commented:
Thank you both for responding.  You bring up good questions.  I'll try to clarify the whole scenario better. The textbox will already be populated with the cutoff time from the database so it will be static. That field is read only from this screen and will be used as the basis for whether an item will show after the cutoff time.  This is for a bank that has a cutoff of 3:00 pm for entering deposits.  The gridview should only show deposits made before 3:00 pm.  Anything after should not show on this screen. Since the gridview is dynamic I need to identify the date/time field in the gridview and then  calculate whether that time in the gridview is less than the time in the textbox.  If it is then it should show on the grid.  If it's later than the textbox value then it should not appear on the gridview.  This would be a row by row calculation as each item would need to be identified.  
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Now, that sounds more like filtering the data that is presented in the GridView, based on the cutoff time.

Author

Commented:
Yes that would be very similar to what I am trying to do.  Basically filtering out the accts that did not receive deposits before the cutoff time.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008
Commented:
What I would do, then, is to fill a DataTable, and then create a DataView from the DataTable, and set the DataView.RowFilter property to filter out the accounts that didn't make the cutoff.

Simple example:


Dim dt As New DataTable()
Using adapter As New SqlDataAdapter("command text", "connection string")
   adapter.Fill(dt)
End Using
Dim dv As New DataView(dt)
dv.RowFilter = "EmployeeID = " & employeeID
Me.GridView1.DataSource = dv
Me.GridView1.DataBind()

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for the tip TheLearnedOne.  I'll give it a try and post back.

Author

Commented:
Bummer, it didn't work.  Getting a null result still.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Can you show me what you came up with?

Author

Commented:
Sure.  Maybe I set it up wrong.  Would you please check it out and let me know your thoughts?
Protected Sub FillGrid()
        Dim NewConnection As New SqlConnection
 
        NewConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
 
        Dim cmd As New SqlCommand
        cmd.Connection = NewConnection
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "spFillGrid"
       
        For i As Integer = 0 To GridView1.Rows.Count - 1
            Dim row As GridViewRow = GridView1.Rows(i)
            Dim crTime As String = DirectCast(row.Cells(3).FindControl("lblbox1"), Label).Text
           
 
            cmd.Parameters.AddWithValue("@LogDateTime", TextBox2.Text)
 
            'code for time diff
 
            If crTime >= TextBox2.Text Then
                GridView1 = Nothing
                GridView1.EmptyDataText = "AFTER CUTOFF"
                'Else
                'txt_DateLogged.Text = TimeValue(Now)
                'txt_DateLogged.Text = DateValue(Now)
 
            End If
Dim dt As New DataTable()
Using NewDA As New SqlDataAdapter("spFillGrid", NewConnection)
NewDA.Fill(dt)
End Using
Dim dv As New DataView(dt)
dv.RowFilter = "EmployeeID = " & employeeID
Me.GridView1.DataSource = dv
Dim NewDS As New Data.DataSet
NewDA.Fill(NewDS)
GridView1.DataSource = NewDS
        Next
        GridView1.DataBind()
 
    End Sub
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
I bet you didn't get anything.  *BIG GRIN*  

  That, my friend, was only an example of what you would need to do, in order to filter by a value.  That is one of my "canned" examples, which you would need to customize to fit your environment.  

What is the field name, and condition that you would like to filter by?
Praveen VenuTechnical Project Manager
Commented:
Change the text
MyFieldName
in the code with the Table Column name (the time column)
 Dim NewConnection As New SqlConnection
 
 NewConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim da as new SQLDataAdapter("spFillGrid", NewConnection)
Dim dt as DataTable
da.fill(dt)
Dim dv as new DataView(dt)
dv.RowFilter="MyFieldName < '" & TextBox2.Text & "'"
Gridview1.Datasource=dv
GridView1.Databind()

Open in new window

Author

Commented:
Thank you both so much for your assistance.  Putting the 2 together worked and now I am still on schedule.  You guys rock!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*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.