Date problem using LINQ to SQL

Hi clearchannel;

Building a list of items to insert into the db is more efficient then inserting one at a time.

Fernando

Dim listOfImages As New List(of MOMs_Image)()
While i < dsPictures.Tables(0).Rows.Count
    Dim newPicture As New MOMs_Image With { _
        .PictureName = dsPictures.Tables(0).Rows(i)("PictureName"), _
        .WorkOrderNo = dsPictures.Tables(0).Rows(i)("WorkOrderNo"), _
        .IssueNo = dsPictures.Tables(0).Rows(i)("IssueNo"), _
        .RoundName = dsPictures.Tables(0).Rows(i)("RoundName"), _
        .OperativeName = dsPictures.Tables(0).Rows(i)("OperativeName"), _
        .InChargeDate = dsPictures.Tables(0).Rows(i)("InChargeDate"), _
        .LocationNo = dsPictures.Tables(0).Rows(i)("LocationNo"), _
        .PanelNo = dsPictures.Tables(0).Rows(i)("PanelNo"), _
        .WorkDate = dsPictures.Tables(0).Rows(i)("WorkDate"), _
        .WorkTime = dsPictures.Tables(0).Rows(i)("WorkTime"), _
        .DesignName = dsPictures.Tables(0).Rows(i)("DesignName"), _
        .GPSLatitude = dsPictures.Tables(0).Rows(i)("GPSLatitude"), _
        .GPSLongitude = dsPictures.Tables(0).Rows(i)("GPSLongitude"), _
        .MOMSFeedback = dsPictures.Tables(0).Rows(i)("MOMSFeedback"), _
        .PlanNum = dsPictures.Tables(0).Rows(i)("PlanNum"), _
        .NameStreet = dsPictures.Tables(0).Rows(i)("NameStreet"), _
        .Address1 = dsPictures.Tables(0).Rows(i)("Address1"), _
        .Address2 = dsPictures.Tables(0).Rows(i)("Address2"), _
        .City = dsPictures.Tables(0).Rows(i)("City"), _
        .Country = sCountry _
    }
    listOfImages.Add(newPicture)
 
    If Exists(Server.MapPath("/temp/" & sCountry & "/" & dsPictures.Tables(0).Rows(i)("PictureName"))) Then
        File.Move(Server.MapPath("/temp/" & sCountry & "/" & dsPictures.Tables(0).Rows(i)("PictureName")), Server.MapPath("/imported/" & sCountry & "/" & dsPictures.Tables(0).Rows(i)("PictureName")))
    End If
 
    i = i + 1
End While
 
pdc.MOMs_Images.InsertAllOnSubmit(listOfImages)
pdc.SubmitChanges()

Select allOpen in new window

I will convert it to a collection, but that's not what the question is about...I have an issue with the date/time field types on insert when they are empty

To your statement, "BUT when the date/time fields are empty or of the wrong format it causes a cast error which I do not know how to rectify. I am guessing I need to check the dates/times for a type or empty value but not sure exactly what I need to code."

Most likely the date in the database is NOT nullable. So before using a date time value check to see if it is Nothing, something like this:

If WorkDate = Nothing Then
        ' Correct the issue by assigning a default or log the record to a file and have someone correct.
End If

As far as wrong format I suspect this DateTime is being converted from a string and to check it before use you can do this:

        Dim strWorkDate As String = "22/23/2009"

Dim WorkDate As DateTime
If Not DateTime.TryParse(strWorkDate, WorkDate) Then
    ' If you get here then the string date is invalid, log it and go on to the next record.
    MessageBox.Show("Invalid date")
    Return
End If
' If you made to here then the string date is valid and is assigned to the variable WorkDate
MessageBox.Show(WorkDate.ToString())

Fernando

The database is set to allow nulls so it is not that. Ignoring the issue of the

It's like I need to check for the empty date/time and then exclude it fomr the linq call, but obviously this can't be done within the object insertion!

Could you edit my code I have posted as I dont see how what you have typed will fix the problem with the casting of the date/time values when they are null?

In each of the three statements below what data type is returned  from the cell in the data table row.

dsPictures.Tables(0).Rows(i)("InChargeDate"),
dsPictures.Tables(0).Rows(i)("WorkDate"), _
dsPictures.Tables(0).Rows(i)("WorkTime"), _

Also when you state this, "date/time fields are empty or of the wrong format"

What does empty mean? Nothing?
What does "wrong format" mean? Seeming it coming from a data table.

I thought my question was easy to understand...

I have a WHILE loop, where in I am using LINQ2SQL and a stored procedure to create an object; newPicture and then I insert the object details into my DB table. The data for each newPicture is coming from the dataset. The 3 fields I am having issue with are:

WorkDate = smalldatetime in DB.
WorkTime = smalldatetime in DB
InChargeDate = smalldatetime in DB

Now in the dataset these fields could contain nothing i.e. no characters "".

When trying to create newPicture I get a cast error when any fo those 3 fields are contain no characters. So I need some sort of workaround to fix the issue of any of those 3 fields containing no value or characters.

IGNORE the wrong format as it is doesn't matter now.

Yes; and in order to know how to deal with the three values I need to know if they are in a String format, DateTime object.

When I read something like this, "Now in the dataset these fields could contain nothing i.e. no characters ""."
Well in the DB world you may have a value, and empty string or something called null which is not a value but the absents of all value which is not nothing.

The dataset values come from an xml file so they are not typed. The only place they are typed is in the DB table.

There will never be a case of Nothing or NULL being used in the XML!

All I need is some code to fix the casting issue for when either of those 3 fields are empty during the LINQ call to my INSERT stored procedure.

Sorry I guess I will be unable to help. Hopefully someone else will check in to this thread who has more knowledge then I.

Good Luck.

Thanks for trying but I have tried a few things and nothiing makes much difference.

anyone else have an answer for my problem or can explain what is wrong with my code or what I am doing?

Could I pass NULL to smalldattime field in some way to fix my problem?