DateTimePicker SQL Query

[dougontour]

Hi all,
I am very new to programming of any description, so firstly, please excuse me if I am asking a very stupid question.
My issue (and this is driving me nuts), is as follows:
I have created a form with bound controls on it. My database contains a table called "tblTimesheets".
One of the bound controls is a DateTimePicker, the interface allows you to choose a date, and then using some context menus, right click and change the colour of some "panels" on the screen. As well as changing the colour of the panel, I assign a value to a variable called "DaysWorkValue" that gets updated in the database along with the date chosen.
What I want to acheive now, is that if I choose the same date as I have done previously, that the panel changes back to the colour I assigned it last time, or at least show the value of the "DaysWorkValue" for the date chosen in an unbound "textbox" on my form, from that I can work it out.
But what I don't know is how, once the date is selected, do i query my SQL database table for that same date in the "DateChosen" field and return the values i want?
Thanks in advance, and I hope this makes sense.

 

[dougontour]

If my data adapter is called DATimesheet, and the field in the table is "WeekStarting" that i wish to base the "Search" on ... is this correct?
CalWeekStarting is the name of the bound combobox.

DATimeSheet.SelectCommand.Parameters("WeekStarting").Value = CalWeekStarting.ToString

I get an error stating that "Additional information: An SqlParameter with ParameterName 'WeekStarting' is not contained by this SqlParameterCollection."

How do I define the SQLParameterCollection?

Thanks again in advance.

 

[jmcilhinney]

I've only really worked with Access databases, not SQL Server, but I would expect that they would behave the same in this case. If you are not using a parameterised command you don't have to set the commands parameters. Just set the CommandText of the select command:

DATimeSheet.SelectCommand.CommandText = "SELECT * FROM tblTimeSheets WHERE WeekStarting = DateValue('" & DateChosen.Value & "')"

That DateValue part may be an Access thing, but if so you can use whatever SQL Server understands.

 

[dougontour]

Thank you so very much for that, I kind of managed to get a work around for the issue I was facing but it has brought on a few other problems it seems.

The way I ended up trying to do this was to create another Dataset for the select statement.
The way I use it is
DSPay2 is the Dataset based on the SELECT ... FROM ... WHERE statement
---------------------------------------------------------------------------
DsPay2.Clear()
DAFinances2.SelectCommand.Parameters("DateSelectedFromTimeDatePicker").Value = CalWeekStarting.Text

DAFinances2.Fill(DsPay2)

TextBox1.Text = DsPay2.tblTimeSheet.Rows(0).Item("DayOneWorkValue")

The issue I am facing is if there isnt a matching date returned from the query, the bolded line returns the error:

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll
Additional information: There is no row at position 0.

What I would like to happen is that if the date selected does not exist in the database, then the textbox isnt populated, and the form lets me continue working, waiting for a Click event that writes the newly entered data into the dataset (DSPay1)

I hope that makes sense.

D

 

[jmcilhinney]

Try:

If DsPay2.Tables("tblTimeSheet").Rows.Count > 0 AndAlso Not IsDBNull(DsPay2.Tables("tblTimeSheet").Rows(0)("DayOneWorkValue"))
	TextBox1.Text = DsPay2.Tables("tblTimeSheet").Rows(0)("DayOneWorkValue")
End If

The IsDBNull() bit is needed only if the DayOneWorkValue field can be Null.