DateTime control feeding SQL DB

AlexW · July 13th, 2011, 02:40 PM

I have a drop down lists representing day / month / year for someone to select their birth date. My code is:

Dim DoB As DateTime = New DateTime(CInt(year.SelectedValue), iMonth, CInt(day.SelectedValue))
DataSource.InsertParameters.Add("DateOfBirth", DoB)

DataSource.Insert()

I get an NVarChar conversion to DateTime error when I select a day thats greater than 12th. This seems obvious that I mixed up the day/month but I'm definitly adding the year / month / day in the correct order in the DoB contructor, iMonth contains '1' for Jan, '2' for Feb and the DateOfBirth field is DateTime in the SQL database.

Imar · July 13th, 2011, 05:47 PM

Hi Alex,

Are you referring to a specifc chapter or exercise? If so, can you specify which one?
Also, posting the markup for the control would help.

Cheers,

Imar

AlexW · July 14th, 2011, 08:01 AM

Sorry Imar, should have put this one in with general ASP section.

The drop down lists to specify the date are:
<asp:DropDownList ID="ddlDay" runat="server"></asp:DropDownList>
<asp:DropDownList ID="ddlMonth" runat="server"></asp:DropDownList>
<asp:DropDownList ID="ddlYear" runat="server"></asp:DropDownList>

I grab the month as a number (1 - 12) and pass to a DateTime control , is it possible the the VB DateTime control being YYMMDD and SQL DateTime field is YYDDMM? This is the only think I can think of seing that the error occurs only for days later than 12th.

Imar · July 14th, 2011, 08:50 AM

Yes, that's likely.

However, it's hard to say what exactly is going on without seeing your data controls. I am pretty sure the date is valid (or .NET would have crashed on instantiating the date), but it's the way the data is sent to the database that's causing the issue. You *add* a new parameter, so it might as well be an existing parameter that's causing the issue.

Cheers,

Imar

AlexW · July 14th, 2011, 09:17 AM

Hi Imar,

I've tried swapping the month/day and yes I get a different error when instantiating the DoB DateTime because it's an invalid date, my only guess is that the DateTime in the DB is the other way round. Do you know how I could specify the YYMMDD format for the DateTime field in the DB?

Imar · July 14th, 2011, 09:22 AM

Please post the relevant code of your data controls. I can only guess what's going on which won't do you any good. Also, please describe your database setup (is it on a different server, what are its regional settings, how does your table look and so on)

Imar

AlexW · July 15th, 2011, 05:30 AM

******Markup:

<asp:DropDownList ID="ddlDay" runat="server"></asp:DropDownList>
<asp:DropDownList ID="ddlMonth" runat="server"></asp:DropDownList>
<asp:DropDownList ID="ddlYear" runat="server"></asp:DropDownList>

******Initiating them (Page Load):

Dim day As DropDownList = CreateUserWizardStep1.ContentTemplateContainer.Fin dControl("ddlDay")
Dim month As DropDownList = CreateUserWizardStep1.ContentTemplateContainer.Fin dControl("ddlMonth")
Dim year As DropDownList = CreateUserWizardStep1.ContentTemplateContainer.Fin dControl("ddlYear")
For i As Integer = 1 To 31 Step 1
day.Items.Add(i)
Next

month.Items.Add("Jan")
month.Items.Add("Feb")
month.Items.Add("Mar")
month.Items.Add("Apr")
month.Items.Add("May")
month.Items.Add("Jun")
month.Items.Add("Jul")
month.Items.Add("Aug")
month.Items.Add("Sep")
month.Items.Add("Oct")
month.Items.Add("Nov")
month.Items.Add("Dec")

For i As Integer = DateTime.Now.Year - 16 To DateTime.Now.Year - 90 Step -1
year.Items.Add(i)
Next

*****Create User button (RegisterUser.CreatedUser):

Dim DataSource As SqlDataSource = CType(CreateUserWizardStep1.ContentTemplateContain er.FindControl("InsertUserDetails"), SqlDataSource)
Dim day As DropDownList = CType(CreateUserWizardStep1.ContentTemplateContain er.FindControl("ddlDay"), DropDownList)
Dim month As DropDownList = CType(CreateUserWizardStep1.ContentTemplateContain er.FindControl("ddlMonth"), DropDownList)
Dim iMonth As Integer
Dim year As DropDownList = CType(CreateUserWizardStep1.ContentTemplateContain er.FindControl("ddlYear"), DropDownList)

Select Case month.SelectedValue
Case "Jan"
iMonth = 1
Case "Feb"
iMonth = 2
Case "Mar"
iMonth = 3
Case "Apr"
iMonth = 4
Case "May"
iMonth = 5
Case "Jun"
iMonth = 6
Case "Jul"
iMonth = 7
Case "Aug"
iMonth = 8
Case "Sep"
iMonth = 9
Case "Oct"
iMonth = 10
Case "Nov"
iMonth = 11
Case "Dec"
iMonth = 12
End Select
Dim DoB As DateTime = New DateTime(CInt(year.SelectedValue), iMonth, CInt(day.SelectedValue))
DataSource.InsertParameters.Add("DateOfBirth", DoB)
DataSource.InsertParameters.Add("UserId", UserGUID.ToString())
.......
DataSource.Insert()

***** Database:
Is locally hosted currently using VWD, using the default ASPNETDB.mdf thats created when using .Net membership but I have added an extra table called UserDetails so I can store more user info. The DateOfBirth field is of 'datetime' type.

Would datetime2(7) type swap the mmdd over perhaps?

Cheers, Alex.

Imar · July 15th, 2011, 05:39 AM

Quote:

Please post the relevant code of your data controls.

What I am looking for is the code for the SqlDataSource control. I assume it has a SQL statement for the inserts / updates, as well as one or more parameters. It's those parameters that can make the difference.

Also, take a look at this:

Code:

DataSource.InsertParameters.Add("DateOfBirth", DoB)

There's an overload of Add that lets you specify the datatype. You can set up the parameter to be of type Date, which may solve the issue.

Finally (unrelated), if you use an overload of month.Items.Add you can specify the text to display (Jan) and the value (1) so you can remove the code that maps the month name to the month's number.

Cheers,

Imar

AlexW · July 15th, 2011, 07:31 AM

Hi Imar,

Dim DoB As DateTime = New DateTime(CInt(year.SelectedValue), iMonth, CInt(day.SelectedValue))
DataSource.InsertParameters.Add("DateOfBirth", System.Data.DbType.Date, DoB)

This comes up with "Overload resolution failed because no accessible 'Add' can be called without a narrowing conversion. Would this be because DoB is DateTime and the DBType in the Add method is just Date? I tried declaring DoB as 'Date' but no difference.

With adding the item / value would this be an overload of the Add method?
month.Items.Add("Jan", 1) doesnt seem to work.

Insert statement when creating user:
InsertCommand="INSERT INTO [UserDetails] ([UserId], [FirstName], [LastName], [DateOfBirth], [************], [Handicap], [OtherInterests], [HomeNumber], [MobileNumber], [UserName], [Email], [PictureUrl]) VALUES (@UserId, @FirstName, @LastName, @DateOfBirth, @************, @Handicap, @OtherInterests, @HomeNumber, @MobileNumber, @UserName, @Email, @PictureUrl)"

<InsertParameters>
<asp:ControlParameter Name="FirstName" Type="String" ControlID="FirstName" PropertyName="Text" />
<asp:ControlParameter Name="LastName" Type="String" ControlID="LastName" PropertyName="Text" />
<asp:ControlParameter Name="************" Type="String" ControlID="************" PropertyName="Text" />
<asp:ControlParameter Name="Handicap" Type="String" ControlID="Handicap" PropertyName="Text" />
<asp:ControlParameter Name="OtherInterests" Type="String" ControlID="OtherInterests" PropertyName="Text" />
<asp:ControlParameter Name="HomeNumber" Type="String" ControlID="HomeNumber" PropertyName="Text" />
<asp:ControlParameter Name="MobileNumber" Type="String" ControlID="MobileNumber" PropertyName="Text" />
<asp:ControlParameter Name="UserName" Type="String" ControlID="UserName" PropertyName="Text" />
<asp:ControlParameter Name="Email" Type="String" ControlID="Email" PropertyName="Text" />
</InsertParameters>

DateOfBirth isnt listed as a parameter because I'm inserting the parameter in the code behind.

Imar · July 15th, 2011, 09:07 AM

Did you try System.Data.DbType.DateTime as the type for the parameter?

Otherwise, try this:

1. Define the parameter in markup and set its Type to DateTime:

<asp:Parameter Name="DateOfBirth" Type="DateTime" />

2. Handle the Inserting event of the control and assign the value to the correct item in the values collection:

e.Values("DateOfBirth") = your date here

You find an example of this in the PlanetWrox web site, in the file Management/AddEditReview.aspx

If all that fails, you can also explicity create a date time string in the format:

YYYY/MM/DD

E.g.:

2011/07/15

and assign that to the parameter. This always works.

Quote:

month.Items.Add("Jan", 1) doesnt seem to work.

My bad. I thought there was an overload that accepted a value and a display item. Instead, you can do this:

Code:

DropDownList1.Items.Add(New ListItem() With {.Text = "Jan", .Value = "1"} )

Hope this helps,

Imar