Inserting date values into SQL

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:

Code:

---

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?:confused:

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.

That would be nice of you, hellswraith.

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.

VB Code:

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.

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