Database

FirstPrevNext

ADO Errors ?!! Ravish31-Oct-01 2:27 Ravish 31-Oct-01 2:27  I am using SQL 7.0 with MDAC 5.0. One of the stored procedure has a Select statement followed by Insert statement. If the Insert statement fails due to any reason I cannot get the error in ADO Error collection object. But strangely if I put the insert first and then the select, the ADO error collection is filled ? What is wrong or What am I doing wrong ? Thanks I want a ADO wrap class not using MFC,who can help me? Jeason Zhao28-Oct-01 22:04 Jeason Zhao 28-Oct-01 22:04  Just as the title!!!!!! Thanks in advance! Please pardon my weak English! Re: I want a ADO wrap class not using MFC,who can help me? Daniel Turini28-Oct-01 22:18 Daniel Turini 28-Oct-01 22:18  You can try using something like this: <br /> #pragma warning(push)<br /> #pragma warning(disable: 4146)<br /> #import "\program files\common files\system\ado\msado25.tlb" rename("EOF", "adoEOF")<br /> #pragma warning(pop)<br /> then you can call ADO directly using smart pointers (e.g., ADODB::_RecordsetPtr). It's not MFC dependant. See more info in MSDN on how to do this. Furor fit laesa saepius patientia Why won't database shrink using MSDE? 25-Oct-01 4:39 25-Oct-01 4:39  Have an application that is targetted for both SQL Server 7 and 2000 and MSDE. Is there a known problem with the Transaction log within MSDE that prevents it from shrinking ? I'm calling various things like DBCC SHRINKFILE through Query Analyser but it often doesn't have any effect. Net result is that my transaction log keeps growing and growing - not something to ship to customers. Does anyone know of a dirty way around this problem ? Re: Why won't database shrink using MSDE? AndyG25-Oct-01 16:15 AndyG 25-Oct-01 16:15  I'm not totally sure this will work with MSDE, but its worth a shot. Try backing up the transaction log. When you backup the transaction log, the transaction log automatically get truncated. Truncating the log just gets rid of log entries that have been commited to the database, you will not lose any active transactions. Then try DBCC SHRINKFILE to physically shrink the file. Andy Gaskell, MCSD Re: Why won't database shrink using MSDE? 25-Oct-01 21:35 25-Oct-01 21:35  Thanks for the idea. We'd actually tried that out and it works fine in SQL2000 but not on MSDE. Now fairly convinced that it is a problem with the MSDE implementation as we can see that before the SHRINKFILE command the log is only 7% used. Another idea that someone gave us was to send some NULLOPs to the DB as a way of moving the active transactions around and then trying to shrink it. We've just downloaded the SQL2000 Desktop edition which seems to respond to shrink requests and if that works, we'll use that and throw out the older MSDE installation. I'd still be interested to know whether this is a bug in MSDE, a feature of MSDE by design to encourage you to distribute SQL2000. Numeric Datatype Michael Martin24-Oct-01 20:52 Michael Martin 24-Oct-01 20:52  Looking at the Numeric Datatype I see the following attributes by default. Length 9 Precision 18 Scale 0 I know Scale is the number of decimal places but what to Length and Precision mean? The actual size of the Datatype is 8 bytes so Length doesn't seems to number of bytes. Inside SQL Server and Online Help, haven't helped. Does anybody know the answer and where I can find this sort of information for other datatypes? Michael Martin Pegasystems Pty Ltd Australia martm@pegasystems.com +61 413-004-018 "Don't belong. Never join. Think for yourself. Peace" - Victor Stone Re: Numeric Datatype SasaT19-Nov-01 23:44 SasaT 19-Nov-01 23:44  It's easy to answer all your questions. To preserve some space here, I'll just tell you where can you find those informations - in the SQL Server Books Online. Search on the index tab for the 'length of data type' and you'll get all the informations you're asking for. Best regards, Sasa Checking flag adFldIsNullable with Field "Attributes" property 24-Oct-01 2:33 24-Oct-01 2:33  Hi! I'am using ADO 2.6 with the Microsoft OLE DB Provider for Jet 4.0 driver to programmatically check the nullability of a field with the ADO Field "Attributes" property. On Win9x and WinNT(SP6) my program worked OK, but on Win2K(SP2) this property returns True even if the field is a required (non-nullable) field. Where is the problem? Thanks, Florin SQL Query Drawil23-Oct-01 10:39 Drawil 23-Oct-01 10:39  Hello I use "RecordSet.Requery()" function in VC++ to get Records of access database, it works properly but when I use it with MS SQL 6.5 database I havn't goten any record, for the same condition and identical tables. Any help Best Regards Re: SQL Query Drawil23-Oct-01 22:07 Drawil 23-Oct-01 22:07  Hello again I am so sorry if i bother any body, I found the error, it was in the table contents. Best Regards ADO Resource pooling Alberto Richart23-Oct-01 6:20 Alberto Richart 23-Oct-01 6:20  Hi all, I have been testing the connection pooling in ADO 2.6, and seems to work fine. By changing the OLE DB Services parameter I have seen how the pooling is enabled or disabled, in this way: Private Declare Function GetTickCount Lib "kernel32" () As Long Private Sub Command1_Click() Dim start, finish, duration As Long Dim conn, conn2, conn3, conn4 As ADODB.Connection Dim strConn strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=ALBERT;Initial Catalog=MyCatalog;User ID=arichart;Trusted_Connection=Yes;OLE DB Services = -2;" 'Conn1 start = GetTickCount() Set conn = New ADODB.Connection conn.ConnectionString = strConn conn.CursorLocation = adUseClient conn.Open finish = GetTickCount() duration = finish - start Debug.Print "Duration 1: " & Str(duration) conn.Close Set conn = Nothing 'Conn2 start = GetTickCount() Set conn2 = New ADODB.Connection conn2.ConnectionString = strConn conn2.CursorLocation = adUseClient conn2.Open finish = GetTickCount() duration = finish - start Debug.Print "Duration 2: " & Str(duration) conn2.Close Set conn2 = Nothing I have two more connection objects in the code I have tested (omitted here to simplify). Ok. Perfect. The problem is that I have tested it again in Visual C++ and does not work. If I set the OLE DB Services to -1 (or 0), then pooling is enabled, and if I set it to -2, then pooling is disabled. Do you know why does it work in different ways in Visual C++ and Visual Basic? The code in Visual C++ looks like this. I use a wrapper class to encapsulate the ADODB::_ConnectionPtr object: CADOConnection Conn; CString strConn = _T("Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=ALBERT;Initial Catalog=MyCatalog;User ID=arichart;Trusted_Connection=Yes;OLE DB Services = -2;"); Conn.SetConnectionString(strConn); Conn.Connect(); The connect function looks like this: bool CADOConnection::Connect() { ASSERT ( m_pADOConnection == NULL ); if ( m_pADOConnection != NULL ) Close(false); #ifdef _DEBUG clock_t start, finish; start = clock(); #endif HRESULT hr = S_OK; try { TESTHR(m_pADOConnection.CreateInstance(_T("ADODB.Connection"))); m_pADOConnection->ConnectionString = m_strConnectionString.AllocSysString(); m_pADOConnection->CursorLocation = ADODB::adUseClient; TESTHR(m_pADOConnection->Open("", "", "",ADODB::adConnectUnspecified)); } catch ( _com_error &e ) { CString strError = e.Description().copy(); AfxMessageBox(strError); m_pADOConnection = NULL; return false; } #ifdef _DEBUG finish = clock(); double duration = (double)(finish - start) / CLOCKS_PER_SEC; TRACE("ADO Connection opened. Duration: %2.3f seconds\n", duration ); #endif return true; } I'm working on Windows 2000 I hope anybody could answer my question. Thanks in advance. ADO BeginTrans inside a try catch block John M. Drescher22-Oct-01 6:48 John M. Drescher 22-Oct-01 6:48  I have a couple of questions about using BeginTrans() in a try catch block. Below is an example of how I use ADO to modify a record in my Application. This application was originally made to use JET and I am tyring to migrate to MSDE. When I was using JET to maintain consistancy between the records of one connection and the records of another connection in the same app using a different thread I had to flush the cache in Jet ( using a call to pJetEngine->RefreshCache( m_pConnection)) and then call BeginTrans, update the data and call CommitTrans. In JET each ConnectionPtr has a cache on the database that is independent of other open connections. Using this procedure the thread will see and modify the current data in the db and not what was cached for this connection. Do we have to do someting like this with MSDE? Is there a way to flush the cache? Also is it possible that BeginTrans / CommitTrans will throw an exception? If so how do I handle BeginTrans inside a try / catch block? Do I call RollbackTrans in each of my catch routines? Could RollbackTrans() throw an exception? _RecordsetPtr pRst = NULL; try { TESTHR(pRst.CreateInstance(__uuidof(Recordset))); pRst->Open(_bstr_t(strQuery), m_pConnection, adOpenStatic , adLockOptimistic, adCmdText); m_pConnection->BeginTrans(); int count = pRst->GetRecordCount(); if ( count == 1 ) { // Modify record using PutCollect } pRst->Update(); pRst->Close(); pRst=NULL; m_pConnection->CommitTrans(); } catch (_com_error &e) { GenerateError(e.Error(), e.ErrorMessage(), e.Description(),__FILE__,__LINE__); } catch( CException* e) { GenerateError(e,__FILE__,__LINE__); } catch (...) { GenerateError(__FILE__,__LINE__); } Re: ADO BeginTrans inside a try catch block Wanderley M22-Oct-01 23:11 Wanderley M 22-Oct-01 23:11  Hi Do I call RollbackTrans in each of my catch routines? Looking at your sample, the answer is yes. But I have one question: why your try/catch block and begintrans/committrans enclose all your recordset operation? Regards, Wanderley Re: ADO BeginTrans inside a try catch block John M. Drescher23-Oct-01 7:14 John M. Drescher 23-Oct-01 7:14  There are several cases in my database where I need to guarantee that the data read from one ConnectionPtr is the latest data written to the database. This generally happens when I need to generate an ID for something and don't use an AutoNumber. One example of this is when I need to generate a Patient ID when the user has not entered a valid one and the case has been forcibly closed because of inactivity. As I said in my original description, because of caching that occurs in the connection at least when using JET to gaurantee you have the latest data from one ConnectionPtr to another you have to flush then cache (on reads) and do begintrans/committrans on all writes that need to be the latest. There is a MS KB article Q200300 that explains the problem as it refers to JET, it is a couple of years old and I have no idea if it applies to MSDE also. Below is taken from the KB article: In certain situations you may need to use two separate ADO connections when writing and reading data from an Access database using the Jet OLEDB Provider. For example, if you have two separate processes that are writing and reading to the same Access database, there is no way to share a single connection. In this situation, you can synchronize writes and reads with separate ADO connections if you follow these guidelines: The writer must start a transaction, using ADO's Connection.BeginTrans, prior to writing the data. The writer must make the database updates and then commit the transaction (using ADO's Connection.CommitTrans). The reader must call JRO.JetEngine.RefreshCache passing in it's connection prior to attempting to read the data. Re: ADO BeginTrans inside a try catch block Wanderley M23-Oct-01 11:50 Wanderley M 23-Oct-01 11:50  Hi I asked that because I use a different approach for try/catch and begin/rollback/committrans and even for custom generated PKIDs (I create it at stored procedure level). Sorry, I couldn't help this time. Regards, Wanderley Re: ADO BeginTrans inside a try catch block John M. Drescher23-Oct-01 12:34 John M. Drescher 23-Oct-01 12:34  Thanks. Actually you have been very helpful. I have not used any stored procedures (except the system ones) yet.. I've only started to migrate from JET to MSDE last friday and T-SQL is new to me. Re: ADO BeginTrans inside a try catch block Wanderley M23-Oct-01 22:45 Wanderley M 23-Oct-01 22:45  Hi One more thing: do you need to be sure that you have the latest data to create unique IDs? Do you keep a table of IDs and increment it whenever you need? I said I have a different approach because I just use try/catch blocks where it's more likely (is that right? english is my second language ) to have trouble. In your sample, I would use it only when opening the recordset. And I use begin/commit/rollback when I'm actually writing data (before addnew and after update, for example). Regards, Wanderley SQL Server 7.0 SP3 and money datatype Michael Martin21-Oct-01 20:46 Michael Martin 21-Oct-01 20:46  I have built a database that has some tables that use the money datatype. The pain in the arse in when I retrieve the data using SQL it always returns 4 decimal places. Is there some way that I force SQL Server to only store 2 decimal places? Or is the something I can do in the SQL statement to truncate/round it to 2 decimal places? Either will do as I only store 2 decimal places worth of value in there anyway. Michael Martin Pegasystems Pty Ltd Australia martm@pegasystems.com +61 413-004-018 "Don't belong. Never join. Think for yourself. Peace" - Victor Stone Re: SQL Server 7.0 SP3 and money datatype Wanderley M22-Oct-01 3:32 Wanderley M 22-Oct-01 3:32  Hi Is there a specific reason to use money? You could use numeric instead of money - this way you can define how many decimal places you need. Do you need to make calculations after you retrieve the data? If not, you could use SELECT STR(<your field="">, 8, 2) FROM [...] HTH Regards, Wanderley Re: SQL Server 7.0 SP3 and money datatype Michael Martin22-Oct-01 15:32 Michael Martin 22-Oct-01 15:32  Wanderley thanks for the information looks like what I need. There isn't any reason to use money other than I was lazy, saw it and used it. I will try it as numeric, otherwise I will use the SELECT trick. Michael Martin Pegasystems Pty Ltd Australia martm@pegasystems.com +61 413-004-018 "Don't belong. Never join. Think for yourself. Peace" - Victor Stone MSDE 2000 Question John M. Drescher21-Oct-01 8:57 John M. Drescher 21-Oct-01 8:57  I'm having a problem migrating from JET to MSDN 2000 in my application. I keep getting timeout errors and the following error message "cannot start more transactions on this session." Is this the result of the 5 connection limit on MSDE? I am only opening and using the program in one application that has multiple threads and opens the database with a connection for each thread that needs db access. Re: MSDE 2000 Question John M. Drescher22-Oct-01 5:48 John M. Drescher 22-Oct-01 5:48  I think I found the problem. It was with my transactions. I called _pConnection->BeginTrans() inside a try / catch block. When an exception was raised because of a duplicate index, the m_pConnection->CommitTrans() was not being called and the transaction was still open. The next call (and all subsequent) call to BeginTrans would fail. ADO DataGrid, MS Access & Server-side cursors Jim Stim18-Oct-01 22:53 Jim Stim 18-Oct-01 22:53  Can anyone tell me why recordset data from an Access table can't be displayed on a DataGrid control if the recordset cursor location is set to the server? Re: ADO DataGrid, MS Access & Server-side cursors Rashid Thadha21-Oct-01 22:40 Rashid Thadha 21-Oct-01 22:40  For a recordset to be bound to a grid, it must support bookmarks. A server side dynamic cursor on SQL Server does not support bookmarks. Try using a keyset cursor. also see msdn article Q224192 http://support.microsoft.com/support/kb/articles/Q224/1/92.ASP

Last Visit: 31-Dec-99 18:00     Last Update: 11-Jun-24 12:28 Refresh ᐊ Prev1...2446244724482449245024512452245324542455 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.