Inserting date values into SQL 1

[JoeCool32]

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? JJ

"Ignorance and prejudice and fear walk hand in hand" - Witch Hunt, by Rush

 

[jfrost10]

Um...well...Lets start with the variable assignment.

So what you're doing is this (sub'd values for ddlblah.selected.value)

MyMonth = CDate(12)
MyDay = CDate(20)
MyYear = CDate(2003)

Now, mymonth, myday, and myyear are all Date data types, which would mean they'd need a valid date. But you're passing in just one date item at a time. Maybe try this:

Dim myDate as Date
myDate = CDate(ddlMonth.SelectedItem.Value & "/" & ddlDay.SelectedItem.Value & "/" & ddlYear.SelectedItem.Value)

That way you have a valid date being assigned to a date datatype.

D'Arcy

 

[JoeCool32]

As Mr. Spock would say, "Fascinating."

Certainly looks promising. I'll give it a whirl.

BTW, I know I put in a lot of code; my SQL statements need a lot to be put in. JJ

"Ignorance and prejudice and fear walk hand in hand" - Witch Hunt, by Rush

 

[JoeCool32]

Well, that problem was solved! While we're at, maybe you could help me with a related issue.

When I originally populated the dropdowns from a SQL SELECT stmnt, the date parts returned had no value. I wanted to match what was returned with values I had put in the dropdowns programatically. For example, if the day from SQL was the 3rd, I'd want it to show up in the list at the 3 that I've inserted on client-side, like so:

1
2
3 <--
4
5

Right now it comes up like this:

3 <--
1
2
3
4
5

What do you think?

BTW, your first post has been marked. JJ

&quot;Ignorance and prejudice and fear walk hand in hand&quot; - Witch Hunt, by Rush