?
Solved

Time difference in Gridview using textbox outside of gridview

Posted on 2009-02-17
14
Medium Priority
?
487 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

0
Comment
Question by:Blessed7777
  • 6
  • 5
  • 3
14 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23669233
Are you trying to set a value for a TextBox that is part of the ItemTemplate for a TemplateField, or the EditItemTemplate?
0
 
LVL 15

Expert Comment

by:Praveen Venu
ID: 23669576
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

0
 
LVL 15

Expert Comment

by:Praveen Venu
ID: 23669582
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?

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Blessed7777
ID: 23670197
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.  
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23674648
Now, that sounds more like filtering the data that is presented in the GridView, based on the cutoff time.
0
 

Author Comment

by:Blessed7777
ID: 23675076
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.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 1000 total points
ID: 23675212
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

0
 

Author Comment

by:Blessed7777
ID: 23676422
Thanks for the tip TheLearnedOne.  I'll give it a try and post back.
0
 

Author Comment

by:Blessed7777
ID: 23676866
Bummer, it didn't work.  Getting a null result still.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23677029
Can you show me what you came up with?
0
 

Author Comment

by:Blessed7777
ID: 23681277
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
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23683698
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?
0
 
LVL 15

Assisted Solution

by:Praveen Venu
Praveen Venu earned 1000 total points
ID: 23683811
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

0
 

Author Closing Comment

by:Blessed7777
ID: 31548049
Thank you both so much for your assistance.  Putting the 2 together worked and now I am still on schedule.  You guys rock!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

592 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