Unable to connect to AS400 in C#

[rabidbunny23]

I am trying to connect to an AS400 database from C# code and cannot find what I am doing wrong.
This is the error that I get (thrown by the conn.Open()):
"Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation."

Here is the body of my code:
String connectionString = "Provider=IBMDA400;Data Source=myAS400;User ID=myUser;Password=myPassword;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString);
conn.Open(); <--- this is where it throws an Exception.
conn.Close();

Here is what I don't understand -- I am able to create a connection to the AS400 through the 'Server Explorer' with these settings:
Data Source: .NET Framework Data Provider for OLE DB
OLE DB Provider: IBM AS400 OLE DB Provider
Server: myAS400
User: myUser
Pass: myPassword

Any ideas what my problem is - and how I might be able to create that same connection within my C# code.

Thanks!

 

[AlexCuse]

When you create a connection using the server explorer, what is the connection string it generates?

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[rabidbunny23]

This is the Connection String generated from the Server Explorer: "Provider=IBMDA400;Data Source=myAS400;User ID=myUser;Force Translate=37

 

[AlexCuse]

Maybe you need to include the Force Translate option?

How is it getting your password when you connect (or is it prompting you)?

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[rabidbunny23]

I added Force Translate to the Connection String with the same results.
I entered my password when I created the Connection - it doesn't prompt me for it to connect through the Server Explorer -- but it doesn't add it to the Connection String.

 

[AlexCuse]

Can you post the whole method that is trying to make the connection?

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[rabidbunny23]

Sure -- it isn't much more than what I posted (I'm trying to keep it simple until I figure this out ...)

using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Odbc;

namespace Project1
{
class Class1
{
public static void Main()
{
String connectionString =
"Provider=IBMDA400;Data Source=myAS400;User ID=myUser;Password=myPassword;Force Translate=37";


OleDbConnection conn = new OleDbConnection(connectionString);

conn.Open();
conn.Close();
}
}
}

 

[AlexCuse]

You are not using "new System.Data.OleDb.OleDbConnection(connectionString);" as indicated above.

Try adding the namespace declaration System.Data.OleDb or reference it when you instantiate your objects.

Hope this helps,

Alex



I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[rabidbunny23]

... I do have the namespace: "using System.Data.OleDb" at the top of my code.

 

[AlexCuse]

No, you have the namespace

using System.Data.Odbc;

at the top of your code.

You could use ODBC I presume, but the ole connection will be much better to use if possible.

Hope this helps,

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[rabidbunny23]

oops -- you're right.

When I pasted my code over I 'cleaned' it up a bit by removing parts I didn't need to show - and I removed the "using System.Data.OleDb;" on accident instead of "using System.Data.Odbc;".

I am actually using the System.Data.OleDb in my code -- and I still get that error.

 

[AlexCuse]

Hm. Is there a forum for AS400's? The only thing I can think of is to try an ODBC connection, but if you have the provider you really should use OLE.

One more question, are you making the exact same connection through the wizard? (same login, same initial catalog, etc...)

Good Luck,

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[rabidbunny23]

Thanks Alex for all of your help -- It seems to be working for me now. Here is what I did:

From Windows:
Go into Admin Tools > Data Sources (ODBC)
Select the System DSN Tab and Add a new System Data Source
Select the ODBC driver used to connect to the AS400
Give it a Data Source Name, AS400 System Name, Library List (*USRLIBL)

From a c# program:

using System.Data.Odbc;

String connectionString = "Dsn=myAS400dsn;uid=myUser;pwd=myPassword";
OdbcConnection conn = new OdbcConnection(connectionString);
conn.Open();

string queryString = "SELECT * FROM myLibrary.myTable";
OdbcCommand command = new OdbcCommand(queryString, conn);
OdbcDataReader rs = command.ExecuteReader();

while (rs.Read())
{
  Console.WriteLine(rs.GetString(0));
}

conn.Close();

Thanks again for your help - I really appreciate it!

 

[AlexCuse]

Well, I'm sorry that you had to use ODBC, but I'm glad you got it working!



I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[JurkMonkey]

I'm sorry that you have to connect to an AS400 DB!

http://www.ubuntu.com/