Inserting date values into SQL

[joethecool1]

If this isn't in the right subject forum, feel free to move it to a more appropriate one. This involves ASP and VB issues as well as SQL.

I'm trying to insert values from dropdown lists into a SQL Field who's data type is 'datetime'. Since the values from the lists are string, I've tried using Cdate to convert the data but I get an error when I do that:

System.InvalidCastException: Cast from string "" to type 'Date' is not valid.

Here's what I have in the code, server-side:

Public Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
    Dim MyMonth, MyMonth2, MyDay, MyDay2, MyYear, MyYear2 As Date
    MyMonth = CDate(ddlMonth.SelectedItem.Value)
    MyDay = CDate(ddlDay.SelectedItem.Value)
    MyYear = CDate(ddlYear.SelectedItem.Value)
    MyMonth2 = CDate(ddlMonth2.SelectedItem.Value)
    MyDay2 = CDate(ddlDay2.SelectedItem.Value)
    MyYear2 = CDate(ddlYear2.SelectedItem.Value)
    If txtWhatObjectGetsFocus.Text = "lnkAdd" Then
        'adding record to umv_messages SQL table
        Dim objSQLMsgInfo As New SQLComponent()
        objSQLMsgInfo.strConnection = objConstants.DBConnectionString_Umove
        objSQLMsgInfo.strSQL = "INSERT INTO umv_memoManagement (mgt_memoURL, mgt_memoDescription, mgt_forWhatGroup, mgt_forWhatMemberInGroup, mgt_effectiveStartDate, mgt_effectiveEndDate) _
        VALUES ('" & lstMemos.SelectedItem.Text & "' , '" & txtDescrip.Text & "' , '" & ddlGroup.SelectedItem.Value & "' , '" & ddlMember.SelectedItem.Value & "' , '" & _
        Trim(MyMonth) & "/" & Trim(MyDay) & "/" & Trim(MyYear) & "' , '" & Trim(MyMonth2) & "/" & Trim(MyDay2) & "/" & Trim(MyYear2) & "')"
        objSQLMsgInfo.ExecuteSqlStatement()
        'indicating message was added
        lblMessage.Text = "MESSAGE ADDED!"
    Else
        'update the umv_message table indicating message has been read
        Dim objSqlMessageUpdate As New SQLComponent()
        objSqlMessageUpdate.strConnection = objConstants.DBConnectionString_Umove
        objSqlMessageUpdate.strSQL = "UPDATE umv_memoManagement SET mgt_memoDescription = " & txtDescrip.Text & ", mgt_forWhatGroup = " & ddlGroup.SelectedItem.Value & ", mgt_forWhatMemberInGroup = " & ddlMember.SelectedItem.Value & _
        ", mgt_effectiveStartDate = " & Trim(MyMonth) & "/" & Trim(MyDay) & "/" & Trim(MyYear) & ", mgt_effectiveEndDate = " & Trim(MyMonth2) & "/" & Trim(MyDay2) & "/" & Trim(MyYear2) & " _
         WHERE mgt_memoURL = " & Trim(lstMemos.SelectedItem.Text)
        objSqlMessageUpdate.ExecuteSqlStatement()
        'indicating message was updated
        lblMessage.Text = "MESSAGE UPDATED!"
    End If
End Sub

On the client-side for the Dropdown control "ddlMonth", I've given the alpha months numeric values. I had hoped this would ease the date-conversion process; apparently it hasn't worked.

Can anyone help?

[hellswraith]

What you need to do is create a DateTime object. You need to get integers from the combo boxes somehow and use those in the constructor of the DateTime object. Then pass that to the SQL Server database.

I have an example at home, and when I get there I will try to post it up here if you still need it.

[joethecool1]

That would be nice of you, hellswraith.

[Musician]

You are trying to convert individual values for days, months and years to a date seperately. Combine them first and then convert:-

Dim MyMonth, MyMonth2, MyDay, MyDay2, MyYear, MyYear2 As String
dim Date, Date2 as Date
Date = Convert.ToDateTime(MyDay & "/" & MyMonth & "/" & MyYear)
Date2 = Convert.ToDateTime(MyDay2 & "/" & MyMonth2 & "/" & MyYear2)

Now you have your dates for insertion.

[hellswraith]

Dim month, day, year As Integer
    Dim month2, day2, year2 As Integer
    Dim date1, date2 As DateTime
    
    '  This assumes that the month and days are in order in the box
    month = ddlMonth.SelectedIndex + 1 
    day = ddlDay.SelectedIndex + 1
    year = CInt(ddlYear.SelectedItem.Value)
 
    month2 = ddlMonth2.SelectedIndex + 1 
    day2 = ddlDay2.SelectedIndex + 1
    year2 = CInt(ddlYear2.SelectedItem.Value)
 
    ' Check that I have the arguments in the correct order, I am
    ' not sure if I do.
    date1 = New DateTime(month, day, year)
    date2 = New DateTime(month2, day2, year2)

Try something like this. I did it by memory, so there may be a couple errors.

[joethecool1]

Two variations on one theme. Thank you both. I used something similar to what you two presented so its working now.