Accessing a PROGRESS DB via an ODBC driver using SqlConnection in the code behind C#

[bnstaylor]

Hi,

firstly to the moderator my apologies in this is in the incorrect forum.

I have developed a web portal using ASP.NET to perform enquiries on our PROGRESS DB via an ODBC driver (32 bit). All the SQL queries work fine coded within the ".aspx" pages.

However, I am now required to merge the data from two seperate DBs with extacly the same schema - they are two separate companies.

Because of this I must now perform this in the code befind file (in C#).

I have thew following code:

SqlConnetconn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
　
// Create a command to extract the required data and assign it the connection string
SqlCommand cmd = new SqlCommand(@"SELECT PUB.ShipInsH.OrderNo 'OrderNo', PUB.ShipInsH.OrderDate 'OrderDate', PUB.ShipInsH.CustomerName 'Name',
PUB.ShipInsH.Site 'Site', PUB.ShipInsH.TransNo 'TransNo', PUB.ShipInsH.Entity 'Entity', PUB.ShipInsH.ExpDate 'ExpDate',
PUB.ShipInsH.RunId 'RunID', PUB.ShipInsH.Buyer 'Buyer',
PUB.ShipInsH.DeliveryDate 'Delivered', PUB.ShipInsH.DropSeqNum 'DropSeq',
PUB.ShipInsH.DateCreated 'Created'
FROM PUB.ShipInsH
WHERE ((PUB.ShipInsH.Buyer = ?)
AND ( PUB.ShipInsH.DateCreated >= ?) AND ( PUB.ShipInsH.DateCreated <= ?))
ORDER BY PUB.ShipInsH.OrderNo DESC", conn);
cmd.CommandType = CommandType.Text;
// Create a DataAdapter to run the command and fill the DataTable
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);


My connection string in the web.config file is as follows:


<add name="ConnectionString" connectionString="Dsn=PSC; User Id=sysprogress; Password= " providerName="System.Data.Odbc"/>


When I try to run the web page I receive the following error:

[h=1]Server Error in '/' Application.

---

[/h][h=2]Keyword not supported: 'dsn'.[/h][FONT=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Keyword not supported: 'dsn'.

Source Error:

Line 66: Line 67: Line 68: SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);Line 69: Line 70:

Is it not possible to perform the SqlConnection via an ODBC driver?

Any help/advice is appreciated.

Thx, Barry.

[/FONT]

 

[RealHeavyDude]

Re: Accessing a PROGRESS DB via an ODBC driver using SqlConnection in the code behind

I don't talk C# - therefore I can comment on the C# code.

But, If you ask me I could imagine that the XML is not valid as you either end up with a string that contains double quotes or do not close the stirng for password correctly.

connectionString="Dsn=PSC; User Id=sysprogress; Password= "providerName="System.Data.Odbc"

Just my 2 cents.

Heavy Regards, RealHeavyDude.

 

[bnstaylor]

Re: Accessing a PROGRESS DB via an ODBC driver using SqlConnection in the code behind

Hi RHD,

the XML is correct but when I copied and pasted it, I dropped a space before the "providerName". Also, the password is blank, hence a space character.

There is nothing wrong with the connection string as I can/have used it in several web pages where there is just straight ASP.NET code connecting to the DB and they all work fine.

The problem is to do with the "SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);" line in C#.

But, I appreciate your input.

Regards, Barry.

 

[Marian EDU]

Re: Accessing a PROGRESS DB via an ODBC driver using SqlConnection in the code behind

Hi RHD,

the XML is correct but when I copied and pasted it, I dropped a space before the "providerName". Also, the password is blank, hence a space character.

There is nothing wrong with the connection string as I can/have used it in several web pages where there is just straight ASP.NET code connecting to the DB and they all work fine.

The problem is to do with the "SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);" line in C#.

But, I appreciate your input.

Regards, Barry.

Guess you have to use OdbcConnection instead of SqlConnection for this to work... http://social.msdn.microsoft.com/Fo.../thread/9d7799a3-2850-43ce-acea-2a43cbe20c9d/

 

[bnstaylor]

Re: Accessing a PROGRESS DB via an ODBC driver using SqlConnection in the code behind

Hi Marian,

I'll try that tomorrow when I get to work and let you know the result.

Thx, Barry.