re: .net, dotnet connect via System.Data.OleDb MSDASQL.1 (MAXDB/SAPDB - published by MYSQL)

I have the vb.net Visual Studio STANDARD edition and cannot use "Server Explorer" with Microsoft OLE DB Provider for ODBC Drivers - it is restriced to MSDE and Access and MS SQL. I am hoping this only is disabled in VS Studio. I am assuming with the right connection string I can successfully connect in code. Can anyone confirm if this is true?

' My connection string below
"Provider=MSDASQL.1;Persist Security Info=False;User ID=CM;Extended Properties='DSN=Contact;UID=CM;pwd=mypass;SERVERDB =CONTACT;SERVERNODE=192.168.32.##;'"

The error message is as follows: The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc).

I know ODBC works and the ADODB Interop assembly in .net works ok (but I am having problems with datagrid update event. I was just hoping to be able to take advantage of the new .net syntax like datasets etc...

I would love to hear from anyone who successfully develops using .net with MAXDB/SAPDB via the Microsoft OLE DB Provider for ODBC Drivers.

Thanks in Advance

I can't get what you mean.Is there any relation between your provider and dataset object?I don't think.
>>I am having problems with datagrid update event...
can you tell us exactly what problem you have...
when you have to use ODBC provider why you insist on using OLE DB
(I told you there is a provider for MYSQL ,have you checked it?)


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

Here you have two problems
1-what is your suitable Data Provider?....that it seems ODBC
2-a problem in your datagrid event and your dataset....
There is not any connection between these two problems.

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

Mehdi - thanks for your response and sorry for the confusion. I will attempt to clarify and explain my insistance on trying to use the "Microsoft OLE DB Provider for ODBC Drivers".

1. I am not using MYSQL database. I am using a database called MAXDB which is a completely different product to MYSQL. Its a higher end product currently supported and sold by MYSQL ( more info on this db can be found at www.mysql.com). An OLEDB provider for MAXDB does not exist.

2. I can use the namespace Microsoft.Data.Odbc and this works OK but I don't like the interface. There doesn't seem to be a way to reference database values by fieldname. Correct me if I am wrong but I can't seem to do the asp equivelent of recordset.fields("cust_name") using this .net interface.

3. I can also use the ADODB interop assembly. This appears to work very nicely and is the exact same syntax as I am used to in old ASP. There is even a way to fill a datastore using a recordset. But when I fill a datastore from the ADODB recordset and than assign the datastore to a datagrid the .Text property of my TextBox control in the datagrid.OnUpdateCommand handler returns only the PREVIOUS value and NOT my changed value. I have implemented the Textbox within the <EditItemTemplate> tags between the <columns> tags.This is driving me crazy - I know I am getting a handle to the textbox because I can successfully modify other properties for example .Visible (I will post a separate thread for this problem and include code).

4. Because of problems 2 and 3 I am attempting to use "Microsoft OLE DB Provider for ODBC Drivers" and the following namespace: Imports System.Data.OleDb. If I can get this to work it has in my understanding the following advantages....

a. It is native .net functionality (I think) correct me if I am mistaken.

b. It will probably work with my datastore and datagrid, the adodb recordset I cannot get to work (see point 3) above.

I have only been programming in .net since Saturday morning, today is Monday. Please exscuse my lack of clarity - hopefully over time I can learn to write clear and precise questions.

Thanks again and if you can help I would sincerly appreciate it.

Dear humour,
I can't get the meaning of this sentence "Microsoft OLE DB Provider for ODBC Drivers"
Did you know .NET has also an ODBC provider this differs to OLE DB data provider
you can use ODBC provider through System.Data.Odbc namespace just like System.Data.OleDb namespace
but about your second problem...
you can do it exactly with ODBC provider you should now System.Data.Odbc.OdbcDataReader just do the same work recordsets did
(OdbcDataReader,SqlDataReader,OledbDataReader objects are equivalent to recordsets)
recordset.fields("cust_name")(ASP) == yourOdbcDataReader["cust_name"](ASP.NET C#)


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

Just tried MyODBCReader("field_name")

Works like a charm.

I knew about and started playing with the Microsoft.Data.ODBC namespace that syntax just alluded me. I thought one had retrieve fields from the reader object via the GetString, GetDate methods etc....

Thanks again for your help.

One may use the GetXXXXX methods of the datareaders but, as you have found, must use the field ordinals. This has its advantages.
A)Using ordinals is much faster than fieldnames
B)Using GetXXX obviously relieves you of having to do the type cast (which may also be faster).

All .NET data classes let you access the fields by name in some way or another.