<<<< MsgBox 1 --> Data provider or other service returned an E_FAIL status. >>>>
Been there, done that (so have a lot of people via my research). Not so much a MySql thing as it is a Microsoft thing - I was seeing similar messages with not only MySql, but also Microsoft SQL (ADO and E_FAIL). Like Peter Simard, I decided to dump MyODBC and moved to VbMyQL from http://www.icarz.com/mysql . This decision came after a frustrating evolution process (over a period of two months)..... I started with RDO and worked my way up to ADO 2.7. With ADO 2.7 I was finally able to get stability (something I've been very used to with Visual Foxpro). Although 2.7 offered the highest reliability I still got errors - if a date range was not valid (Foxpro uses empty dates) *OR* a memo field (longtext) field was blank then I would get the E_FAIL status every time. My work around was to ensure that FOXPRO did not write an empty memo field or date (date set to 1900/01/01 and memos to ""). This was a flakey solution at best because if a client had one of these scenerios then my database utility manager (for support folks) would not permit my utility to access the data because of the dreaded E_FAIL message.... I suspect if you replace all your LONGTEXT fields with a chr(20), if they are empty, your problem will go away - flakey.... Once I converted to ICARZ's VbMyQL I was able to actually focus on programming, versus debugging and now have the beginning of an open source MySql manager in the works (VB6). I have not experienced *any* crashes or errors. As soon as I figure out how GNU works and can give proper credit where credit is due (icarz) I'll be publishing the utility and source. It does'n't offer all the bells and whistles of ADO but at least it works ;) Bill Kratochvil QuickCARE Developer www.QuickCARE.com P.S. Visual Foxpro works seemlessly with MySql - havn't had any problems since we started evaluating it (starting January we're moving to a client/server SQL/MySql backend). I almost canned the VB6 MySql management utility (started thinking about doing it in Fox) but now that I have a stable development environment for the data it's back on track - I am really impressed with Visual Basic - I've only been working with it for a few months but I trust the power of the open source utility that I'm developing might pull on seasoned veterans (to improve it and my code). -----Original Message----- From: deep kapasi [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 8:51 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: MySQL BUG Hi, I am facing strange problem while accessing a LONGTEXT field. I've been using VisualBasic 6.0 & MySQL 4.0.4 via ADO 2.0 by using MyODBC 3.51.04 on WIN2k Server (SP 2). Pls follow the below steps to generate a bug --> '/////////////////////////////////////////////////////' 1. Create a Table for testing '* Script to generate the table and insert a record *' USE TEST; CREATE TABLE Testing ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY , msg LONGTEXT NOT NULL , fld2 INT NOT NULL ) TYPE=InnoDB; INSERT INTO Testing(msg,fld2) VALUES("",1); 2. Create a VB Project '***************** VB CODE ***************** ' 'Step to create a VB project 'a. Open a new VB project 'b. Add reference of ADO 2.0 'c. By default New form will be added to the project 'd. Copy the below code in it Option Explicit Private mcnn As New ADODB.Connection Private Sub Form_Load() On Error GoTo DispError Dim rst As New ADODB.Recordset Dim strSQL As String 'change below connection string as per your db settings mcnn.Open "Driver={MySQL ODBC 3.51 Driver};SERVER=server;DB=test;UID=root;PWD=;OPTION=3" strSQL = "SELECT * FROM Testing" '*** Method 1. Client side recordset ***' ' If 'msg' field has blank string - below code will give error while trying ' to access Recordcount property, and strangely Recordpointer is at EOF ' If 'msg' field has value other than blank string & NULL then it will run properly With rst Set .ActiveConnection = mcnn .CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockPessimistic .Open Source:=strSQL, Options:=adCmdText 'Normally below statement should return 1 but on my m/c 'it generates E_FAIL status error. MsgBox .RecordCount 'it will display -1 as record pointer is at EOF, Strange? MsgBox "Record pointer: " & .AbsolutePosition End With Set rst = Nothing '****************************************' '*** Method 2. Server side recordset ***' ' If 'msg' field has blank string - below code will return NULL ' If 'msg' field has value other than blank string & NULL then it will return blank string Set rst = mcnn.Execute(CommandText:=strSQL, Options:=adCmdText) MsgBox "Msg: " & IIf(IsNull(rst.Fields("msg").Value), "<NULL>", rst.Fields("msg").Value) '****************************************' Exit Sub DispError: MsgBox Err.Description, vbCritical Resume Next End Sub '** END OF VB CODE **' '/////////////////////////////////////////////////////' 3. run the project. It will display 3 messagebox. On my m/c it display following messages MsgBox 1 --> Data provider or other service returned an E_FAIL status. MsgBox 2 --> Record Pointer: -1 MsgBox 3 --> Msg: <NULL> Any help is appreciated. - DEEP __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/products/myodbc/manual_toc.html (the manual) http://lists.mysql.com/ (the list archive) To unsubscribe, e-mail <[EMAIL PROTECTED]> To unsubscribe from Yahoo! Groups version, e-mail [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
