variable through binding source to sql database

Roger

Sorry, looking back on my orginal question it seems to vague. Not only does the form contain BookingsBindingSource, but it also has a table adapter called BookingsTableAdapter and a dataset simply known as DataSet. I also have a control called BookingsBindingNavigator. All these controls were preconfigured as I dragged and dropped them from the datasource window onto the form therefore creating the navigation bar along the top of the form and creating the fields in the form of textboxes. Running the program works great with the app communicating with the SQL database and updating any changed fields.
The textboxes come preconfigured as well with databinding. Thats all good and well but I have replaced a textbox with a datetimepicker control and i want to databind this to the 'date' column on my SQL database. See my below code:

Garry

Public Class Form1
 
    Private Sub BookingsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BookingsBindingNavigatorSaveItem.Click
 
        Me.Validate()
        Me.BookingsBindingSource.EndEdit()
        Me.BookingsTableAdapter.Update(Me.DataSet.Bookings)
 
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Me.BookingsTableAdapter.Fill(Me.DataSet.Bookings)
 
    End Sub
 
    Private Sub DateTimePicker1_ValueChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
 
        Dim bookingdate As DateTime = DateTimePicker1.Value
 
    End Sub
End Class

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

bookingdate is the variable i need to get onto the SQL database

Open up your form in Design mode, and click on the DateTimePicker.  In its properties window, if necessary, click on the + sign next to DataBindings to expand it.  Click next to Value and, from the dropdown that appears, select from BookingsBindingSource - expanding it if necessary - and then the name of the field (probably BookingDate) that you want the DTP to be linked to.  That way, the DateTimePicker should be "bound" to your datatable in the same way as the TextBox that it has replaced was.  You should then be able to get rid of your DateTimePicker1_ValueChanged_1 sub.

There can, with DateTimePickers, be some quirks.  For instance, the above assumes that the DataType of the relevant field/column in your datatable is DateTime.  But try the above, and come back if you get problems, saying what they are.

Roger

Roger,

I have tried this before which would seem to work but then poses me with a problem. Lets say that I have a SQL database with a datetime field in it. When the app is run and it binds the data to the database it puts in the date as eg. 02/03/08 10:55:09. I would need to it to show 02/03/08 00:00:00. There doesn't appear to be any property within the control to achieve this.

Any suggestions?

Thanks,
Garry

Have a look at the DateTimePicker's .Format property.  Setting it to Short should display just the date part.  It won't show the 00:00:00, but do you really want to show that?  If you do, then it is achievable.  But it gets more complicated.

Roger

On reflection, you might do better to set the .Format property to Custom and put your own pattern in .CustomFormat.  This is because the pattern that is used for Short is dictated by the user's own system settings, and it may not be what you want.  I'm in the UK, so would use "dd/MM/yy" for the date part.  Judging by your time zone, you are too.  But if you want American style dates it would be "MM/dd/yy".  And if you DID want to show "00:00:00" whatever the _real_ part of the value was, you could add that as a literal to the custom format string.  Like this

    myDateTimePicker.CustomFormat = "dd/MM/yy 00:00:00"

or

    myDateTimePicker.CustomFormat = "MM/dd/yy 00:00:00"

Roger

This doesn't appear to work

At first i put it in the form load sub. Showed up in SQL as it did before
I then moved it to the datetimepicker value changed sub but that didnt work either.

Garry

Did you also set

    myDateTimePicker.Format = DateTimePickerFormat.Custom

Or, rather than coding, you can do both steps in the properties window for the DateTimePicker at design time.

Roger

Not working,

When i run the app the control shows the date and the 00:00:00 but when it binds to the database it still shows the date and the current time ie. the 02/03/08 10:55:09

Garry