Microsoft KB Archive/246438: Difference between revisions
(importing KB archive) |
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= | '''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= | 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->Open(btCon,L"",L"",-1); | pCon->Open(btCon,L"",L"",-1); |
Revision as of 07:59, 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 =<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.#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