• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

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


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;Location=name.something.net;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.

1 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.

rrbeckerAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now