Is it possible to show only particular records in gridview with the followi

Question

Iamateur 0 Junior Poster in Training

14 Years Ago

There are 2 textboxs to accept from date and to date.

I will be comparing the fromdate value and todate with the table column.

If the record matches then only that row will get display in the datagridview.

.net:vb.net

4 Contributors
19 Replies
275 Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by Iamateur

Dani AI

Generated 9 Months Ago

Brief summary and a safe, reliable path forward (addresses the errors raised in earlier posts by and the working DataTable approach from ).

The goal is to return the actual rows that fall between two dates, not a count. Common pitfalls seen in this thread: building SQL by concatenating textbox text (SQL injection and format problems), treating dates as plain strings, using a COUNT when row data is required, and not handling time-of-day portions in datetime columns. The simplest robust workflow is: validate/parse the two inputs into DateTime values (use DateTimePicker controls if possible), use a parameterized query that compares the database date column to those DateTime parameters, fill a DataTable and assign it to the DataGridView.DataSource, and handle the “no rows” case explicitly.

Practical tips: ensure the database column is a date/datetime type (string columns must be converted or, preferably, changed to a proper date type). If the DB column stores times too, make the end date inclusive by using endDate = endDate.Date.AddDays(1).AddTicks(-1) (or compare with < startOfNextDay). Always use Using blocks (or otherwise dispose connections/commands) and prefer SqlParameter with SqlDbType.Date/DateTime so the server sees true dates rather than culture-dependent strings.

Example pattern (illustrative and safe — uses parameters and proper parsing):

Dim startDt As DateTime
Dim endDt As DateTime
If Not DateTime.TryParse(TextBoxFrom.Text, startDt) OrElse Not DateTime.TryParse(TextBoxTo.Text, endDt) Then
    ' invalid dates - handle accordingly
    Return
End If
endDt = endDt.Date.AddDays(1).AddTicks(-1)

Using cn As New SqlConnection(connString)
    Using cmd As New SqlCommand("SELECT * FROM Records WHERE DateColumn BETWEEN @start AND @end", cn)
        cmd.Parameters.Add("@start", SqlDbType.DateTime).Value = startDt
        cmd.Parameters.Add("@end", SqlDbType.DateTime).Value = endDt
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        da.Fill(dt)
        DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = dt
    End Using
End Using

Follow-up checks: confirm the actual table/column names used in earlier posts (IssueMBA / ida), ensure the date format entered matches parsing expectations or switch to DateTimePickers, and use ExecuteScalar only when a single aggregate (like a count) is required.

All 19 Replies

techite 7 Newbie Poster

14 Years Ago

Yes, you can use the 'BETWEEN' operator while executing the SQL Query.

adam_k 239 Master Poster

14 Years Ago

The statement that you are assinging to cmd won't take you anywhere.
1st of all you should change it to :

 cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA WHERE ida='" & textbox1.text & "' and rda='" & textbox2.text &"'", con)

And then you can add date criteria like this

     cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA WHERE ida='" & textbox1.text & "' and rda='" & textbox2.text &"' and date_field between '" & start_date & '" and '" & end_date & "'"

poojavb 29 Junior Poster

14 Years Ago

After else if u need to mention the condition and then continue with the code....if no condition is present then just use else

show the exact code what u have done after adam_k has post so we can help u more on it...

poojavb 29 Junior Poster

14 Years Ago

yes it will be better if u have one column only for date so that u can aslo use the between case

poojavb 29 Junior Poster

14 Years Ago

cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA where ida between '" + textbox1.text + "' and '" + textbox2.text + "'")

after between '=' sign wont come....

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Iamateur 0 Junior Poster in Training · Answer 1 · 2012-05-13T15:30:55+00:00

This is how I am trying to write code
vate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

con.Open()

        cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA WHERE ida=textbox1.text,rda=textbox2.text", con)
        dr = cmd.ExecuteReader()
        If Not dr.Read() Then
            MsgBox("Invalid details")
        ElseIf 


    After **ElseIf** I am not able to write exact code.
    I want to show those particular rows in the gridview and that code should be shown in else part.

Iamateur 0 Junior Poster in Training · Answer 2 · 2012-05-14T05:41:40+00:00

adam_k

cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA WHERE ida='" & textbox1.text & "' and rda='" & textbox2.text &"' and date_field between '" & start_date & '" and '" & end_date & "'"

here,
cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA WHERE ida='" & textbox1.text & "' and rda='" & textbox2.text &"' and date_field between '" & start_date & '" and '" & end_date & "'"

Why to show the date field between '" & start_date & '" and '" & end_date & "'"?
The ida and rda are the textboxes to accept the start date and end date itself.
I am actually not getting ur comment '" & start_date & '" and '" & end_date & "'"
And also after this condition how to show those records that match start date to end date in the gridview?

Iamateur 0 Junior Poster in Training · Answer 3 · 2012-05-14T14:08:26+00:00

Sorry let me change it as both dates form one column only.
Is this right?

cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA where ida between='" & textbox1.text & "' and '" & textbox2.text & "'

Iamateur 0 Junior Poster in Training · Answer 4 · 2012-05-15T04:24:13+00:00

poojavb
Ok thanku for reply .
But,Is this right?
cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA where ida between='" & textbox1.text & "' and '" & textbox2.text & "'")

Iamateur 0 Junior Poster in Training · Answer 5 · 2012-05-15T10:17:30+00:00

poojavb
I have written code in this way. Accept from date and to date from text boxes then with the help of coding show the particular rows in gridview. The code I wrote is not so correct but I tried it: I have written code in button.

 con.Open()
        i = DataGridView1.CurrentCell.RowIndex
        cmd = New SqlCommand("SELECT COUNT(*) FROM IssueMBA WHERE ida between '" + TextBox1.Text + "'and  '" + TextBox2.Text + "'", con)
        dr = cmd.ExecuteReader()
        If Not dr.Read() Then
            MsgBox("Invalid details")
        ElseIf ((dr(0).ToString() >= TextBox1.Text) And (dr(0).ToString() <= TextBox2.Text)) Then
            DataGridView1.Rows(i).Cells(0).Value.ToString()
            DataGridView1.Rows(i).Cells(1).Value.ToString()
            DataGridView1.Rows(i).Cells(2).Value.ToString()
            DataGridView1.Rows(i).Cells(3).Value.ToString()
            DataGridView1.Rows(i).Cells(4).Value.ToString()
        End If




        con.Close()

Can you correct it?

poojavb 29 Junior Poster · Answer 6 · 2012-05-15T11:36:23+00:00

Y are u validating the condition again....its the same thing that the query will give u...just show the datagrid with the result as I have shown....

ElseIf ((dr(0).ToString() >= TextBox1.Text) And (dr(0).ToString() <= TextBox2.Text)) Then

 Try
                Dim myCommand As New SqlCommand
                With myCommand
                    .CommandText = "select * IssueMBA WHERE ida between '" + TextBox1.Text + "'and  '" + TextBox2.Text + "'"
                   .CommandType = CommandType.Text
                    .Connection = Conn
                End With
                Dim dt As New DataTable
                dt.Load(myCommand.ExecuteReader)
                With DatagridView1
                    .AutoGenerateColumns = True
                    .DataSource = dt
                End With
            Catch ex As Exception
                Throw ex
            End Try

instead of writing select count(*) write the column names that u need in ur datagridview

Iamateur 0 Junior Poster in Training · Answer 7 · 2012-05-15T16:46:32+00:00

poojavb
Yes I want to show all the columns with matching rows as per the condition.
So I used (*)

adam_k 239 Master Poster · Answer 8 · 2012-05-15T18:34:02+00:00

let's get back to step 1: Define your need. Are you trying to get a number showing how many records match your criteria (ie count(*)) or are you trying to get all records matching (ie select * from ... ).

In both cases criteria is the same, but the result is different.

Iamateur 0 Junior Poster in Training · Answer 9 · 2012-05-16T04:35:27+00:00

adam_k
Currently what I am trying to do is :
1]Added 2 textboxes and a button and gridview on the form.
2] User will enter 2 dates in both text boxes. This 2 dates will be considered as frmdate and todate.
3]Then User will click on the button.
4]The button code should do:
Match this frmdate and todate to one of the columns called as "ida" in the database table IssueMBA.
So,from this column ,the rows that are between frmdate and todate should display that whole row in the gridview.
5] for eg: the user enters frmdate as 1/5/2012 and todate as 5/5/2012 then in the gridview the only rows that match this criteria should be displayed.
So, the whole row matching this criteria should be displayed.

Iamateur 0 Junior Poster in Training · Answer 10 · 2012-05-16T10:30:41+00:00

poojavb
Thanku very much Pooja :) Your code worked out. Thanks a lot for help :)

poojavb 29 Junior Poster · Answer 11 · 2012-05-16T10:54:22+00:00

Ur welcome.....vote me up :) and mark the thread as solved if it helped u....

Iamateur 0 Junior Poster in Training · Answer 12 · 2012-05-26T06:05:23+00:00

Iamateur 0 Junior Poster in Training

14 Years Ago

Poojavb actually this code doesn show all rows

poojavb 29 Junior Poster · Answer 13 · 2012-05-26T07:28:55+00:00

select * will show all the rows....if u r mentioning some column names then it wont show??

what query are u using??? and what rows do u want to return??? paste it....

Iamateur 0 Junior Poster in Training · Answer 14 · 2012-05-26T08:37:54+00:00

"select * from StudCIssue WHERE ida between '" + TextBox1.Text + "'and '" + TextBox2.Text + "'"

Is it possible to show only particular records in gridview with the followi

Dani AI

Recommended Answers Collapse Answers

All 19 Replies

Recommended Answers