ASP.NET OLE DB Connection not working

[Guest]

Using the Application Panel Group, I am trying to establish an OLE database connection to a remote SQL server. When creating the connection it says it successfully connects. However, I am unable to browse the tables in the DB. I have had this same problem using DW MX 2004 as well as a trial version of DW8. I've installed the extension created to fix this problem with no change.

I'm hoping someone else has run into this problem with DW8 and knows a way to get around it. By the way, I get the same results on 3 computers. They all run WinXP with SP2 and IE7. I have one last computer (win2k) that I am going to try it on (since this seems to be an XP SP2 problem), but haven't had a chance to try it yet. Either way, I shouldn't have to drop down to an out dated OS to run new software.

[Creativ_Technologies]

what does your conneciton string look like, and don't post the username and password?

[Guest]

HI, thanks for the quick reply. Here's the connection string, straight from the hosting provider:

Provider=sqloledb;Data Source=whsql-v07.prod.mesa1.secureserver.net;Initial Catalog=MyDatabase;User ID=MyAccount;Password=my password;

[Creativ_Technologies]

Try a conneciton with a custom sql connection:

"Driver={SQL Server};Server=IP Address; DATABASE=myDatabase; UID=userID; PWD=password;"

That is if your provider allows it.

[Guest]

Ok, I changed the connection string as you suggested. DW now sees the tables in the DB, but when I created a very simple DataSet and uploaded the page, I get this error:

System.ArgumentException: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.
at System.Data.OleDb.OleDbConnectionString.ValidateParse()
at System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl)
at System.Data.OleDb.OleDbConnectionString.ParseString(String connectionString)
at System.Data.OleDb.OleDbConnection.set_ConnectionString(String value)
at System.Data.OleDb.OleDbConnection..ctor(String connectionString)
at DreamweaverCtrls.DataSet.NewDbConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()

I assume this is because the connection string you suggested is an ODBC connection and not and OLE. If I'm way off base (which is a very good possibility), let me know.

Could this be related to the issue Adobe identified with MX2004 and ASP.NET pages not being able see the DB tables even though the connection string was correct?

Is there a chance I've incorrectly setup the connection to my testing server (which is just the production server) and that's the cause?

Just thinking out loud.

Thanks again for your help.

[Creativ_Technologies]

Well it has to be something with the conneciotn string. If you were able to see the tables with the custom string, but not the other, I am guessing it has something to do with the Data Source. Try placing the IP address there instead of the name of the server. You could also send me a private message with the connection info, so I can try it on my machine to see if will work or not.

[Creativ_Technologies]

On the custom connection string in the web.config did you change:

<add key="MM_CONNECTION_DATABASETYPE_test2" value="OleDb" />

to

<add key="MM_CONNECTION_DATABASETYPE_test2" value="SQLServer" />

[Guest]

The DB connection still seems to work in DW, but if I open a test page I get the following error:

System.ArgumentException: Keyword not supported: 'driver'.
at System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain)
at System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at DreamweaverCtrls.DataSet.NewDbConnection(String strConnection)
at DreamweaverCtrls.DataSet.DoInit()

Any other thoughts?

[Guest]

I think its related to this:

http://www.adobe.com/go/455d4ca0

Unfortunately, I've done the fix, and it still doesn't work.

[Creativ_Technologies]

Okay lets try this, modify your web.config at this key:

<add key="MM_CONNECTION_STRING_yourConName" value="Persist Security Info=False;Data Source=[serverIP];Initial Catalog=[databaseName];User ID=[username];Password=[password];" />

I don't know why you are having so much trouble connecting to this database. So you entire thing will be similar to:

<add key="MM_CONNECTION_HANDLER_test" value="sqlserver.htm" />
<add key="MM_CONNECTION_STRING_test" value="Persist Security Info=False;Data Source=[serverIP];Initial Catalog=[databaseName];User ID=[username];Password=[password];" />
<add key="MM_CONNECTION_DATABASETYPE_test" value="SQLServer" />
<add key="MM_CONNECTION_SCHEMA_test" value="" />
<add key="MM_CONNECTION_CATALOG_test" value="" />

[Creativ_Technologies]

Basically just create a new SQL Server connection from the application panel under databases. Then type in the IP in serverName field.

[Guest]

I changed to conn string to match what you suggested, now DW no longer sees any tables in the Application Pannel.

Here's the stuff from my web.config file:

<appSettings>
<add key="MM_CONNECTION_HANDLER_CUSTOM_CONNECTION" value="sqlserver.htm" />
<add key="MM_CONNECTION_STRING_CUSTOM_CONNECTION" value="Persist Security Info=False;Data Source=10.0.9.17;Initial Catalog=*********;User ID=********;Password=******;" />
<add key="MM_CONNECTION_DATABASETYPE_CUSTOM_CONNECTION" value="SQLServer" />
<add key="MM_CONNECTION_SCHEMA_CUSTOM_CONNECTION" value="" />
<add key="MM_CONNECTION_CATALOG_CUSTOM_CONNECTION" value="" />
</appSettings>

[Creativ_Technologies]

check your private messages

[Guest · Accepted solution]

OK, I found a sort of work around, so hopefully this helps. First off, I'm running winXP SP2 and the .NET 2.0 environment. Not sure if that plays a roll, but I thought I'd throw that out there anyway.

Here's a recap of my problem: I want to use the Application panel in DW8 to manage my DB connections. The connection string provided by GoDaddy did not work there. Meaning, it would say that it connected successfully to the sql server, but when I opened the DB it did not show any tables. If I uploaded the code anyway and hand coded a dataset, it worked.

In playing with my connection string, I realized that the problem lies with this:

Provider=SQLOLEDB;

If I remove the provider info, then DW8 has no problem viewing tables in my SQL DB. However, the webserver could not connect to the DB with out it. So what I decided to do is have two copies of the web.config file. The version on the server will include the provider line. On my local machine it will not.

I'm sure there are better ways to accomplish this, but this is the only working solution I could find. Honestly it seems like a DW issue to me, so that's where I put the blame, but I really don't care now.

[Newsgroup_User]

IMHO if you are serious about doing work in the .Net2.0 framework then you
should forget DW for anything other than designing the pages. It can't
render most of the controls, and its Data access is extremely limited.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"InPlayTech" <webforumsuser@macromedia.com> wrote in message
news:es9rdp$8ll$1@forums.macromedia.com...
> OK, I found a sort of work around, so hopefully this helps. First off,
> I'm
> running winXP SP2 and the .NET 2.0 environment. Not sure if that plays a
> roll,
> but I thought I'd throw that out there anyway.
>
> Here's a recap of my problem: I want to use the Application panel in DW8
> to
> manage my DB connections. The connection string provided by GoDaddy did
> not
> work there. Meaning, it would say that it connected successfully to the
> sql
> server, but when I opened the DB it did not show any tables. If I
> uploaded the
> code anyway and hand coded a dataset, it worked.
>
> In playing with my connection string, I realized that the problem lies
> with
> this:
>
> Provider=SQLOLEDB;
>
> If I remove the provider info, then DW8 has no problem viewing tables in
> my
> SQL DB. However, the webserver could not connect to the DB with out it.
> So
> what I decided to do is have two copies of the web.config file. The
> version on
> the server will include the provider line. On my local machine it will
> not.
>
> I'm sure there are better ways to accomplish this, but this is the only
> working solution I could find. Honestly it seems like a DW issue to me,
> so
> that's where I put the blame, but I really don't care now.
>

[Leonidas1]

I have the same problem with DW 8, .NET 2.0 framework (XP SP2), SQL SERVER 2005 express.
In the application panel I try to set up an OLE DB connection.
I use the "build" option.
My sql server instance is local.
I check the "Use Windows NT Integrated Security" button.
I am able to select my database from the drop down list.
On testing the connection it works.
On exiting the build panel , the OLE DB Connection panel has the following connection string:

Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=systemPractices;
Data Source=TOSHIBA-USER\SQLEXPRESS

On testing I get the following error:
"Cannot open database "systemPractices" requested by the login. The login failed."

Removing: "Provider=SQLOLEDB.1;" gives me the following error message:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

On returning to the Application panel no tables are displayed.

Is this a show stopper for DW 8 in the .NET2.0 framework/XP SP2/SQL SERVER 2005 environment?