Using asp and vb.net I very successfully am able to use ADO technology with MYSQL's - MAXDB database (MAXDB is the successor to SAPDB).

I am confused though, in diagrams I have seen published by WROX ADO relies on OLE DB technology. Why is it that I can't seem to get to the data using the native OLE DB .net provider defined in the .net namespace known as System.Data.OleDB (please provide an explanation or clear my confusion on the technologies involved here).


Where I am getting hung up is on the line of code below, I dont know what I should put in the provider parameter. What I have there now I think is exclusively for SQL-SERVER but I am not sure.

stringConnectionString = "Provider=SQLOLEDB;dsn=contact;user id=CM;password=*****;"
objConnection = New System.Data.OleDb.OleDbConnection(stringConnection String)

Is this possible? Has anyone done it?


If it is not supported are there plans by MYSQL or Microsoft or anyone else to achieve this?



Thanks in Advance.

Hey,

Yes, your provider is a SQL Server provider. There has to be a MySQL provider. Not sure what it is though, as I haven't used MySQL. Just out of curiosity, what connection string did you use to connect to MySQL before? Could you post that...

Brian

1-for connecting to your datasource using the SQL Server .NET Data Provider
(only for Microsoft SQL Server version 7 or above)
your string connection is something like below
------
strConnection="server=YourServer;database=YourData base integrated security=<true/false>;";//also you can add your user an password
-----
2-for connecting to your datasource using the OLE DB.NET Data Provider
(for MS Access,Excel)
your string connection is something like below
----
strConnection="Provider=Microsoft.Jet.OleDb.4.0 data
source=C:\\....\\Northwind.mdb;";//for MS Access
strConnection = "Provider=Microsoft.Jet.OleDb.4.0; data source=C:\\...\\Inventory.xls; Extended Properties=Excel 8.0;";//for Excel
---
3-There are other providers for MYSQL,Oracle you can find them in www.microsoft.com then install them,you would have some new namespaces....
(nevertheless you can connect to your MySQl,Oracle through OLE DB.NET Data Provider but it is clear which one could be better!)

--------------------------------------------
Mehdi.

I think against Brian,your Provider is OLE DB.NET provider.

--------------------------------------------
Mehdi.

BMains asked: "Just out of curiosity, what connection string did you use to connect to MySQL before? Could you post that..."

I am not exactly attaching to MySql, I am attaching to MAXDB which is a higher-end product published/maintained by MySql. I am using the PIO-ADODB using vb.net. When I do it in ASP I use ADODB as well the code is practically identical.

I thought since I was using ADODB a MORE native .NET approach using this NameSpace: System.Data.OleDB should be possible. Although I am grateful for the fast and rapid responses to this point nobody has adaquately explained how to do it. I guess what I really need is the provider parameter for the OLEDB connectstring but thus far it has alluded me as to what to type there.

Imports ADODB

Dim daTitles As New OleDbDataAdapter
Dim CustName As String
Dim contADO As New ADODB.Connection
Dim contRS As New ADODB.Recordset
Dim flds As ADODB.Fields
Dim locDS As New DataSet
contADO.ConnectionString = "dsn=contact;user id=CM;password=****;"
contADO.Open()
contRS.Open("select cust_name from customers where cust_name like '%tore%' ", contADO, CursorTypeEnum.adOpenDynamic)

I dont think what I want to do will be possible, I just read the following from this website:

http://www.databaseadvisors.com/newl...tter042004.htm

Here is where a lot of open source packages fall down on data mangement. For example MAXDB (old SAPDB) from MySQL has a limited ODBC driver and no OLEDB driver Would be nice to have to link to dotNet. However you will have to wait for some kind spirited soul to write one, It would take a least 3 months of someone's time. I have heard of people offering to do it for $10,000. Maybe someone will do it at MySQL. Guys who can write this type of code are thin on the ground.

I will content myself to using the PIA-ADODB piece or the ODBC namespace: Microsoft.Data.Odbc. I kinda prefer the PIA-ADODB one as the syntax seems richer. I cant use seem to do something like this (see below) via ODBC namespace in dotnet.

'pseudo sample of referencing a field
' from a recordset/dataset via the fields name

recordSetobj.fields("CustomerName")

Hey,

Well DotNet does things differently, so it isn't using the same parameters in the same way. It refers to data as rows/columns, similar to how Excel holds the data.

Brian

Go here: http://www.connectionstrings.com

And look under MySql and DSN. I realize that you are not using MySql, but there do seem to be some .NET classes developed for MySql connectivity.