DATETIME, ADO, Parameterized INSERTs And Milliseconds, Oh My

I want to execute a paramterized ADO insert command into a SQL Server DATETIME column without losing the milliseconds. I can accomplish this without parameters, but that isn't what I want. Any suggestions?

It is OK that DATETIME only has a resolution of 3.33 milliseconds.

See the attached code:




Code Snippet

#include <string>

#include <iostream>

#import "C:Program FilesCommon FilesSystemadomsado15.dll" rename( "EOF", "ADOEOF" )

int main( int argc, char* argv[] )

{

::CoInitialize( NULL );

try

{

ADODB::_ConnectionPtr connection;

connection.CreateInstance(__uuidof( ADODB::Connection ) );


std::string connectionString;


connectionString.append( "Provider=SQLOLEDB;" );

connectionString.append( "Data Source=HPSERV1;" ); // Choose your server/instance.

connectionString.append( "Initial Catalog=tempdb;" );

connectionString.append( "Integrated Security=SSPI;" );

connection->ConnectionTimeout = 10;

connection->Open(

_bstr_t( connectionString.c_str() ),

_bstr_t( "" ),

_bstr_t( "" ),

ADODB::adOpenUnspecified );


std::string sqlStatement;


sqlStatement = "DROP TABLE TestTable1";


try

{

connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );

}

catch( const _com_error& )

{

// Ignore errors as table probably doesn't exist.

}


sqlStatement = "CREATE TABLE TestTable1 ( ColInt INT NOT NULL PRIMARY KEY, ColDate DATETIME )";

connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );



// ====================================================================

// Works (datetime resolution is 3.33 milliseconds so rounds to .347)


sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 1, '2007-12-28 20:05:16.345' )";

connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );



// ====================================================================

// Works (NULL is inserted)


sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 2, NULL )";

connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );



// ====================================================================

// Works (datetime resolution is 3.33 milliseconds so rounds to .347)


sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 3, CONVERT( DATETIME, '2007-12-28 20:05:16.345' ) )";

connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );




// ====================================================================


unsigned int colInt = 3;

sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( ?, ? )";


ADODB::_CommandPtr command;




// ====================================================================

// Fails (Operand type clash: ntext is incompatible with datetime)


colInt++;


command.CreateInstance( __uuidof( ADODB::Command ) );

command->ActiveConnection = connection;

command->CommandType = ADODB::adCmdText;

command->CommandText = _bstr_t( sqlStatement.c_str() );

command->Parameters->Append( command->CreateParameter(

_bstr_t(),

ADODB::adInteger,

ADODB::adParamInput,

4,

_variant_t( colInt ) ) );

command->Parameters->Append( command->CreateParameter(

_bstr_t(),

ADODB::adBSTR,

ADODB::adParamInput,

-1,

_bstr_t( "2005-10-25 09:10:11.012" ) ) );


try

{

command->Execute( NULL, NULL, ADODB::adCmdText );

}

catch( const _com_error& e )

{

std::cout << "Error at colInt=" << colInt << " (ADODB::adBSTR and _bstr_t):
"

<< " HRESULT = " << e.Error() << ": " << e.Description() << "
"

<< " SQL statement: " << command->CommandText << std::endl;

}




// ====================================================================

// Fails (Operand type clash: text is incompatible with datetime)


colInt++;


command.CreateInstance( __uuidof( ADODB::Command ) );

command->ActiveConnection = connection;

command->CommandType = ADODB::adCmdText;

command->CommandText = _bstr_t( sqlStatement.c_str() );

command->Parameters->Append( command->CreateParameter(

_bstr_t(),

ADODB::adInteger,

ADODB::adParamInput,

4,

_variant_t( colInt ) ) );

command->Parameters->Append( command->CreateParameter(

_bstr_t(),

ADODB::adChar,

ADODB::adParamInput,

-1,

_bstr_t( "2005-10-25 09:10:11.012" ) ) );


try

{

command->Execute( NULL, NULL, ADODB::adCmdText );

}

catch( const _com_error& e )

{

std::cout << "Error at colInt=" << colInt << " (ADODB::adChar and _bstr_t):
"

<< " HRESULT = " << e.Error() << ": " << e.Description() << "
"

<< " SQL statement: " << command->CommandText << std::endl;

}




// ====================================================================

// Fails (A datetime is inserted to table but milliseconds are zeroed)


colInt++;


::SYSTEMTIME sysNow;

::GetSystemTime( &sysNow );


double myTime;

::SystemTimeToVariantTime( &sysNow, &myTime );


// SystemTimeToVariantTime strips milliseconds, so we'll add some more

// back in as we're testing insert of milliseconds.


double desiredMilliseconds = 456;

myTime += desiredMilliseconds / 24.0 / 3600.0 / 1000.0;


command.CreateInstance( __uuidof( ADODB::Command ) );

command->ActiveConnection = connection;

command->CommandType = ADODB::adCmdText;

command->CommandText = _bstr_t( sqlStatement.c_str() );

command->Parameters->Append( command->CreateParameter(

_bstr_t(),

ADODB::adInteger,

ADODB::adParamInput,

4,

_variant_t( colInt ) ) );

command->Parameters->Append( command->CreateParameter(

_bstr_t(),

ADODB::adDate,

ADODB::adParamInput,

-1,

_variant_t( myTime, VT_DATE ) ) );


try

{

command->Execute( NULL, NULL, ADODB::adCmdText );

}

catch( const _com_error& e )

{

std::cout << "Error at colInt=" << colInt << " (ADODB::adDate and _variant_t VT_DATE):
"

<< " HRESULT = " << e.Error() << ": " << e.Description() << "
"

<< " SQL statement: " << command->CommandText << std::endl;

}

}

catch ( _com_error& e )

{

std::cout << "Unexpected error:
"

<< e.Description() << std::endl;

}


return 0;

}