DataTimePicker for ListView

Question

Is there a way to use DateTimePicker as your searching device for ListView?

I don't know how to use DateTimePicker as my search engine...

HERE IS THE CODES FOR MY SEARCH:

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim itemcoll(100) As String

Me.ListView1.View = View.Details
Me.ListView1.GridLines = True
ListView1.Items.Clear()

conn = New SqlConnection("Data Source=#####;Initial Catalog=#####;Persist Security Info=True;User ID=#####;Password=#####")
Dim strQ As String = String.Empty
strQ = "SELECT ControlNo,EmpNo,CheckOutDate,CheckOutTime,TaxiNo,PlateNo,Model,Make FROM dbo.ChkInOut WHERE ControlNo ='" + txtsearch.Text + "'"

cmd = New SqlCommand(strQ, conn)
da = New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "Table")
Dim i As Integer = 0
Dim j As Integer = 0
For i = 0 To ds.Tables(0).Rows.Count - 1
    For j = 0 To ds.Tables(0).Columns.Count - 1
        itemcoll(j) = ds.Tables(0).Rows(i)(j).ToString()
    Next
    Dim lvi As New ListViewItem(itemcoll)
    Me.ListView1.Items.Add(lvi)
 Next

I just want to EXECUTE SELECT STATEMENT in DateTimePicker wherein when I selected a specific day/date . the ListView will populate itself the data from my DB in SQL SERVER where the transaction was made at the same date the user picks on DateTimePicker — Danjor, Feb 02 '13 at 03:55
sir R0MANARMY ive been doing this one program lately, all my questions was about it. like how to populate listview, how to connect to db using SQLconnection, etc. etc. I will post my search statement here, could you tell me where to put it? — Danjor, Feb 02 '13 at 04:02
You should try to format your code better before you post it. Also, this is a much better question (more specific). I'm going to delete my previous comments because they are no longer relevant. — Roman, Feb 02 '13 at 04:12

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

There are few problems with your code as is, so let's take them one at a time

SqlCommand inherits from DbCommand, which implements the IDisposable interface.

The primary use of this interface is to release unmanaged resources.

The best way do that is with the Using keyword. For a code example of that, take a look at the sample code at the bottom of this page.

Same goes for SqlConnection, wrap it in a Using statement.
Don't concatenate strings together to make SQL queries, this opens your application up to SQL Injection attacks. There are examples of how to create parameterized queries here and here (unfortunately I didn't see a good example on MSDN).

In your case, the query will look like this:
```
strQ = "SELECT ControlNo, ..<rest of columns>.. ,Model,Make " & _
       "FROM dbo.ChkInOut " & _
       "WHERE ControlNo = @controlNo"

cmd = New SqlCommand(strQ, conn)
cmd.Parameters.AddWidthValue("@controlNo", txtsearch.Text);

      ... rest of code here ...
```
To query by a user specified date, you need to first get the date from the DateTimePicker.Value property. Then construct a query (like in the example above) and pass a parameter with the selected date. You may find this question abou SQL Server dates helpful.

edited May 23 '17 at 12:18

Community

1
1

answered Feb 02 '13 at 04:46

Roman

19,581
6
68
84

cmd.Parameters.Add("@controlNo", txtsearch.Text); this line of codes has a problem it says 'Public Function Add(parameterName As String, value As Object) As System.Data.SqlClient.SqlParameter' is obselete: 'Add (String parameterName, Object value ) has been deprecated.' Use AddWidthValue(String parametername, Object value) – Danjor Feb 02 '13 at 05:08
Thank you for pointing out that the `Add` method is now obsolete. I've updated the answer. However, the error message even tells you what the problem is **and** how to fix it. The `Add` is obsolete, use `AddWithValue` instead. – Roman Feb 02 '13 at 05:11
sorry im newbie in programming field. sorry – Danjor Feb 02 '13 at 05:14
Don't be sorry, just read error messages, google things you don't understand, and if you still don't find an answer, then ask someone. – Roman Feb 02 '13 at 05:16
one question, why not stay with the codes that I made? what is the advantages of the codes that you post sir R0MANARMY? BTW, I used your codes and it works fine. – Danjor Feb 02 '13 at 05:16
same, but I've read the link about SQL Injections and all I think is that it's for me to have a clear and short codes instead of using long codes like what I always do... am I right about it? – Danjor Feb 02 '13 at 05:21
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/23825/discussion-between-r0manarmy-and-danjor) – Roman Feb 02 '13 at 05:22

DataTimePicker for ListView

1 Answers1