[RESOLVED] Milliseconds gettings stripped from SQL Server

[stateofidleness]

Gents,
I am pulling a single field from a SQL Server 2008 table and when performing the SELECT in Management Studio, the result shows as:

2011-08-03 17:46:36.997

Now, in my application I'm storing that returned result as a string and using it to later perform an INSERT using it.

I notice that after INSERTing into the database, I can see it is being stored as:

2011-08-03 17:46:36.000

Appears to be stripping off the milliseconds and replacing it with 000. In my case, milliseconds are important (sequencing thing) and can't figure out why it's doing that or how to fix it.

Thoughts?

[dbasnett]

How is the field in the database defined?

[ForumAccount]

And let's see the code you are using to insert.

[stateofidleness]

It is a "datetime" field in the DB.

Code for Insertion:

So, first I get the time from the database:
sActionTime = dsManitou.Tables(0).Rows(0).Item(2).ToString.Trim

Then, later I use that "sActionTime" variable (a String) to use in my INSERT:

Private Sub InsertVideoLaunchedComment()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim sqlString As String = Nothing
        Try
            'GET THE NEXT AVAILABLE SEQUENCE NUMBER FOR THE SPECIFIC EVENT NUMBER
            GetSequence()
 
            con.ConnectionString = "EDITED"
            con.Open()
            cmd.Connection = con
            sqlString = "INSERT INTO CLOG" & Date.Now.ToString("yyyy") & Date.Now.ToString("MM") & " (LASTUPD, CREDATE, SERIALNO, LSEQNO, EVENTNO, EVDATE, LOGDATE, OFFSET, EVTYPE, QUAL, QUAL2, SIGQUAL, SOURCE, USRID, EVSTRING, SESSION, OWS) VALUES (GETUTCDATE(), GETUTCDATE(), '" & iSerial & "', '" & iSequence & "', '" & iEventNum & "', '" & sActionTime & "', GETUTCDATE(), '-300', '29', '0', '0', '0', 'X', '" & sOperator & "', 'ReView Video Verification was successfully launched', '" & iSession & "', '" & iOpWorkstation & "')"
 
            cmd.CommandText = sqlString
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MyErrors &= Environment.NewLine & ex.ToString & Environment.NewLine
        Finally
            con.Close()
        End Try
    End Sub

*i'm not using a parameterized query because no input comes from user*

All the GETUTCDATE()'s return the correct date (including milliseconds), but for some reason the bolded "sActionTime" does not have the milliseconds on it.

[dbasnett]

This

sActionTime = dsManitou.Tables(0).Rows(0).Item(2).ToString.Trim

does not return milliseconds. The default for DateTime.ToString is M/d/yyyy h:mm:ss tt I think.

[stateofidleness]

So, I just experimented with making it a DateTime variable and storing it that way, then using it in my query that way as well. It all still works, but same problem exists.

I'll check into that dbas, that might be the problem (pretty silly if it is lol)

EDIT: trying ToString("yyyy-MM-dd HH:mm:ss:fff") as we speak!

[ForumAccount]

This is why you should be using parameters. Forget user input, you can specify values and the data base types with the parameter if need be. There should never be a need for concatenated SQL statements, even if there is no user input. Not to mention your code will be much cleaner and more readable.

[stateofidleness]

I hear ya... and I won't lie, part of it is laziness. Parameters add so much more code that I just wasn't eager to type today (i'll go back and update them, not too hard)

I changed it to the correct format before INSERTing and it is working now!

I guess I probably should have checked that default format, but I assumed that taking whatever that value was and converting it straight to a string would maintain the entire thing, but it's odd that it stripped the milliseconds off.

thanks fellas!