500pts. Trying to Connect to MySQL using OLEDB and display in Infragistics UltraGrid

Posted on 2007-07-30
Last Modified: 2008-01-09

Infragistics provides a data grid called Ultragrid. It requires an ADO OLEDB data connection in order to function 'properly' I currently have an Access database that I can connect to and display data via the Ultra Grid. Code for that is as follows:
-----code snippet----------
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim TheseFields As String
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Provider = "MSDataShape"
    cn.ConnectionString = "Data Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APPlusDBPath$ & "\APPlus32.MDB;Jet OLEDB:Database Password=xxxxxx;"
    TheseFields = "[ProductNo],[VendorID],[Description],[Color],[Size],[User1],[User2],[User3],[User4],[QTY],[UnitCost],[Taxable],[Retail],[InvoiceID],[ProductName],[QBInvoiceTxnID],[Category]"
    rs.Open "SELECT " & TheseFields$ & " from InvDetails WHERE DetailID = 0", cn, adOpenKeyset, adLockOptimistic
    Set UGdListInvoice.DataSource = rs
----end snippet-------

This part works good. I am now trying to pull some data from a MySQL database that resides on a remote Unix Server. I have installed MyOleDB, MySQL ODBC 3.51 and MySQL Connector/ODBC 3.51. I have attempted to extract data from the remote database and display it with variations of the following code:

-----code snippet---------
  Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Provider = "MySQLProv"
    cn.ConnectionString = "Provider=MySqlProv.3.9;Password=xxxxx;;Data Source=applus;User ID=MyUID;"
    cn.IsolationLevel = adXactReadUncommitted
    cn.CursorLocation = adUseServer
    rs.Open "SELECT * from aoserrors", cn, adOpenDynamic, adLockReadOnly
    Set Me.SSUltraGrid1.DataSource = rs
----------end snippet------------

So far I have been unsuccessful in extracting and displaying data. I keep getting the following error:

Run-time error .... One or more errors ocurred during processing of command.

Does anyone have any insite as to why this is happening and what I can do about it.

Any Help would be greatly appreciated.

Question by:rrbecker
    LVL 9

    Accepted Solution

    Offhand, the only thing I can think is that the mySQL server is denying the connection. This can happen if the user is set up as for example 'root@localhost'. (Allowing root to only connect from the local machine...)

    Make sure that you can create a connection to the mySQL server outside of your source code.
    If not, try creating a new user on mySQL with the host set as % so that your user will be created as 'testuser@%' which will allow the user testuser to create from anywhere.

    I'm afraid this is all I can come up with. If not, I don't know.

    LVL 2

    Author Comment

    Hi riaancornelius,

    Thanks for the reply.

    I actually connect and extract  data from the server and MySQL database for other applications using ODBC and a different Grid control. This is the first time that I have attempted to use the MySQL OLEDB
    method and the UltraGrid. All of that works so I believe the permission are OK... at least they are Ok when using ODBC.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now