Question SqlDataRaeader and Subitems in ListView

[jweddington001]

I have an employee timekeeping program that I am writing.I have a listview that contains nine columns. They pull the data via SELECT statement from multiple tables in SQL. I have one table called Overtime that contains a column with overtime codes specific to the employees overtime record The overtime table has a otdate column that contains the date that the overtime data was entered. My problem is that when the user of the program wants to select a date range for an employee from the form, it will properly display the information except that it doesn't match up the overtime code with the date that it was entered on. It runs down the column for all dates and I would like it to just show up on the date that it was entered. Any assistance is grealty appreciated.

The Table:



The Program at Runtime:

The Code:

Protected Sub BindRank()
        pwschedulelb.Items.Clear()
        Dim otcomm As New SqlCommand
        otcomm.Connection = otconn
        Dim read As SqlDataReader
        otcomm.CommandText = "SELECT Rank.squad, Cal.dt, Cal.dayname, MasterName.shift, Shift.starttime,  Shift.endtime, Rank.title, Shift.total, Overtime.Code From Rank, Cal, MasterName, Shift, Overtime WHERE Cal.dt BETWEEN '" & (DatePickerFrom.Text) & "' AND '" & (DatePickerTo.Text) & "' and MasterName.empno = '" & empnotb.Text & "' and MasterName.empno = Rank.empno and MasterName.shift = Shift.shiftno and MasterName.empno = Overtime.empno"
        otcomm.Connection.Open()
        read = otcomm.ExecuteReader
        Dim intCount As Decimal = 0
        Do While read.Read
            pwschedulelb.Items.Add(Trim(NNL(read("squad"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(read("dt"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(read("dayname"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(read("shift"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(Format(read("starttime"), "HH:mm"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(Format(read("endtime"), "HH:mm"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(read("title"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(read("total"))))
            pwschedulelb.Items(CInt(intCount)).SubItems.Add(Trim(NNL(read("Code"))))
            intCount = intCount + 1
        Loop
        read.Close()
        otcomm.Connection.Close()
        pwschedulelb.Refresh()
    End Sub
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]
 [/SIZE][/FONT][/SIZE][/FONT]

 

[Menthos]

Not exactly sure what you're after, but your query is pulling back exactly what you're asking it.... your BETWEEN is only being checked against the Cal.dt, when you should be applying it to the overtime.otdate field.

Take a look at the blog article on parametized queries by one of the contributors here jmcilhinney, it's pretty much the best way to build dynamic queries.

Secondly consider dropping some JOINs into your query, it would simplify it a lot.

 

[jweddington001]

More Details on Timekeeping program

I should add that the attached otform goes with the overtime table. I have an Overtime.otdate column that needs to go against a search between the two dat time pickers. I want to display all the dates in that range along with all of the other data such as total hours from-thru, rank, squad. etc. My problem is i am not sure how to just show the Overtime.code with the Overtime.otdate in the selected date time picker rang without the Overtime.code to attach itself to every dat it queries. I have a seperate Auxilliary Calendar table cal Cal. The date time pickers are quering the Cal.dt. I need the Overtime.otdate to match with the Cal.dt while showing the other dates in Cal.dt .Hopefully that provides a bit more information.

The Overtime Form:
This data gets inserted into the Overtime table

The Cal Table:

 

[JohnH]

This comment is not about your results or database code, but about the coding convention. Variable intCount is not necessary, and neither is looking up the ListViewItem from the collection each time you add a subitem. ListViewItemCollection.Add(String) method returns the ListViewItem object added to the collection, use this object when adding all the subitems. pseudo:

With lv.Items.Add(...)
    .SubItems.Add(...)
    .SubItems.Add(...)
End With

DataGridView control is generally more suitable for displaying tabular data by binding, so that is said also.