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

[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.