Microsoft KB Archive/246438: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - ">" to ">") |
||
Line 74: | Line 74: | ||
<li><p>Paste the following code into your implementation file:<br /> | <li><p>Paste the following code into your implementation file:<br /> | ||
<br /> | <br /> | ||
'''Note''' You must change the User ID=<username | '''Note''' You must change the User ID=<username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.</p> | ||
<pre class="codesample">#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" ) | <pre class="codesample">#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" ) | ||
Line 95: | Line 95: | ||
pRs.CreateInstance(__uuidof(Recordset)); | pRs.CreateInstance(__uuidof(Recordset)); | ||
btCon = L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<username | btCon = L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<username>;Password=<strong password>;Initial Catalog=Pubs;Data Source=(local)"; | ||
btSQL = L"SELECT * FROM employee WHERE fname='Test'"; | btSQL = L"SELECT * FROM employee WHERE fname='Test'"; | ||
pCon- | pCon->Open(btCon,L"",L"",-1); | ||
pRs- | pRs->Open(btSQL,pCon.GetInterfacePtr(), | ||
adOpenStatic,adLockOptimistic,-1); | adOpenStatic,adLockOptimistic,-1); | ||
// THIS CODE TRUNCATES MILLISECONDS BY USING _RecordsetPtr- | // THIS CODE TRUNCATES MILLISECONDS BY USING _RecordsetPtr->AddNew(): | ||
pRs- | pRs->AddNew(); | ||
pRs- | pRs->GetFields()->GetItem(L"emp_id")->PutValue(L"ABC12345M"); | ||
pRs- | pRs->GetFields()->GetItem(L"fname")->PutValue(L"Test"); | ||
pRs- | pRs->GetFields()->GetItem(L"lname")->PutValue(L"Test"); | ||
d = 36438.409988773; //1999-10-05 09:50:23.030 | d = 36438.409988773; //1999-10-05 09:50:23.030 | ||
varDate = d; | varDate = d; | ||
varDate.vt = VT_DATE; | varDate.vt = VT_DATE; | ||
pRs- | pRs->GetFields()->GetItem(L"hire_date")->PutValue(varDate); | ||
pRs- | pRs->Update(); | ||
// SET BREAKPOINT ON NEXT LINE: | // SET BREAKPOINT ON NEXT LINE: | ||
pRs- | pRs->Requery(-1); | ||
varDate = pRs- | varDate = pRs->GetFields()->GetItem(L"hire_date")->GetValue(); | ||
d = varDate; // Value now rounded down to the nearest second. (36438.409988426) | d = varDate; // Value now rounded down to the nearest second. (36438.409988426) | ||
// THIS CODE CORRECTLY INSERTS MILLISECONDS BY USING _ConnectionPtr- | // THIS CODE CORRECTLY INSERTS MILLISECONDS BY USING _ConnectionPtr->Execute(): | ||
pCon- | pCon->Execute(L"UPDATE employee SET hire_date = '1999-10-05 09:50:23.030' WHERE fname = 'Test'",&vtMissing,-1); | ||
pRs- | pRs->Requery(-1); | ||
varDate = pRs- | varDate = pRs->GetFields()->GetItem(L"hire_date")->GetValue(); | ||
d = varDate; // Value now contains the correct value. (36438.409988773) | d = varDate; // Value now contains the correct value. (36438.409988773) | ||
// Clean up: | // Clean up: | ||
pRs- | pRs->Delete(adAffectCurrent); | ||
} | } | ||
catch (_com_error& e) | catch (_com_error& e) | ||
Line 142: | Line 142: | ||
<ol style="list-style-type: lower-alpha;"> | <ol style="list-style-type: lower-alpha;"> | ||
<li>Using _RecordsetPtr- | <li>Using _RecordsetPtr->AddNew() the milliseconds are truncated when the value is stored in SQL Server.</li> | ||
<li>Using _ConnectionPtr- | <li>Using _ConnectionPtr->Execute() the milliseconds are preserved and correctly stored in SQL Server.</li></ol> | ||
</li></ol> | </li></ol> | ||
Revision as of 09:43, 21 July 2020
Article ID: 246438
Article Last Modified on 10/31/2003
APPLIES TO
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
This article was previously published under Q246438
SYMPTOMS
When using an ADO Recordset to insert a date/time value into a SQL Server database, the millisecond portion of the value is truncated and the value is rounded off to the nearest second.
RESOLUTION
For a workaround see the "More Information" section.
STATUS
This behavior is by design.
MORE INFORMATION
Steps to Reproduce Behavior
- Create a Microsoft Visual C++ console application.
Paste the following code into your implementation file:
Note You must change the User ID=<username> value and the password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" ) int main(int argc, char* argv[]) { _ConnectionPtr pCon; _RecordsetPtr pRs; _variant_t varDate; double d; _bstr_t btCon; _bstr_t btSQL; CoInitialize(NULL); try { pCon.CreateInstance(__uuidof(Connection)); pRs.CreateInstance(__uuidof(Recordset)); btCon = L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<username>;Password=<strong password>;Initial Catalog=Pubs;Data Source=(local)"; btSQL = L"SELECT * FROM employee WHERE fname='Test'"; pCon->Open(btCon,L"",L"",-1); pRs->Open(btSQL,pCon.GetInterfacePtr(), adOpenStatic,adLockOptimistic,-1); // THIS CODE TRUNCATES MILLISECONDS BY USING _RecordsetPtr->AddNew(): pRs->AddNew(); pRs->GetFields()->GetItem(L"emp_id")->PutValue(L"ABC12345M"); pRs->GetFields()->GetItem(L"fname")->PutValue(L"Test"); pRs->GetFields()->GetItem(L"lname")->PutValue(L"Test"); d = 36438.409988773; //1999-10-05 09:50:23.030 varDate = d; varDate.vt = VT_DATE; pRs->GetFields()->GetItem(L"hire_date")->PutValue(varDate); pRs->Update(); // SET BREAKPOINT ON NEXT LINE: pRs->Requery(-1); varDate = pRs->GetFields()->GetItem(L"hire_date")->GetValue(); d = varDate; // Value now rounded down to the nearest second. (36438.409988426) // THIS CODE CORRECTLY INSERTS MILLISECONDS BY USING _ConnectionPtr->Execute(): pCon->Execute(L"UPDATE employee SET hire_date = '1999-10-05 09:50:23.030' WHERE fname = 'Test'",&vtMissing,-1); pRs->Requery(-1); varDate = pRs->GetFields()->GetItem(L"hire_date")->GetValue(); d = varDate; // Value now contains the correct value. (36438.409988773) // Clean up: pRs->Delete(adAffectCurrent); } catch (_com_error& e) { HRESULT hr = e.Error(); const TCHAR* szError = e.ErrorMessage(); _bstr_t btDesc = e.Description(); } return 0; }
- Change the connect string to reflect your SQL Server Pubs database
- Compile, set a breakpoint (as indicated in the code comments), and run the code.
- In stepping through the code, you will see that setting the date to a value containing millisecond precision produces different outcomes depending upon the method used to insert the record:
- Using _RecordsetPtr->AddNew() the milliseconds are truncated when the value is stored in SQL Server.
- Using _ConnectionPtr->Execute() the milliseconds are preserved and correctly stored in SQL Server.
Workaround
Use the Execute method of the Connection object to update/insert records with millisecond precision and use a string literal for the date value as outlined in the code earlier.
Keywords: kbdatabase kbprb KB246438