• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 714
  • 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.


Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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