We help IT Professionals succeed at work.

DSN only Connection String?

Realmrat
Realmrat asked
on
8,044 Views
Last Modified: 2012-05-04
I'm currently building a website that uses DB functionality.  I'm wanting to use MS Access, because I can do so for free.  :)  I can create a DSN for the hosted DB, but I do not know how to build the connection string to connect to it.  As far as I know, I can only use a DSN.

I'm writing the program using ASP.NET, with C# codebehind.

Thanks for the help!

->Joe
Comment
Watch Question

Commented:
This is a great source for connection strings:

http://www.able-consulting.com/ADO_Conn.htm
If you have defined the DSN on the Web Server, then the connection string is simply

Dim cn

set cn = Server.CreateObject(ADODB.Connection)
cn.ConnectionString = "DSN=<your DSN>"

where you replace <Your DSN> with the name of the DSN that you created to point to the database in question.

AW
in c# it would be
<AIR CODE WARNING> // I do not have .NET installed on this box//

uses System.Data.OLEDBClient  
.
.

Connection cn;
cn =  new Connection(DSN="<Your DSN>");
cn.Open;

</AIR CODE WARNING>

AW

Author

Commented:
Arthur,

Do you know what that code would be in C#?  Or even VB.NET?

That looks like VBScript.

->Joe

Author

Commented:
lol I didn't hit refresh soon enough.  Will try out that code

->Joe

Author

Commented:
I tried:
OleDbConnection conn = new OleDbConnection("DSN=pppMSA");

I got:
An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'

So I added:
Provider=Microsoft.Jet.OLEDB.4.0;

Now I get:
Could not find installable ISAM.

Any suggestions?

->Joe

Author

Commented:
Here's my entire code.  Basically, when the page loads, get all the information from the DB, then fill the DataGrid with that info.

protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected string cmdText = "SELECT * FROM products ORDER BY id";
   
private void Page_Load(object sender, System.EventArgs e)
{
           // Run the query and get some data to display
            DataTable data = ExecuteQuery(cmdText, connString);

            // Bind the data to the grid
            DataGrid1.DataSource = data;
            DataGrid1.DataBind();
}

DataTable ExecuteQuery(string cmdText, string connString)
{
            OleDbConnection conn = new OleDbConnection("DSN=pppMSA;Provider=Microsoft.Jet.OLEDB.4.0;");
            OleDbCommand cmd = new OleDbCommand(cmdText, conn);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            DataTable data = new DataTable();
            adapter.Fill(data);
            return data;
}

->Joe
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
AW,

connString was from a previous version of the program.  I hard coded the connection string as per your example, just for quickness.

I do have Microsoft Access 2002 installed on my computer.

The connection string:
protected string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..//..//mypath//ppp.mdb;User Id=admin;Password=;";

DOES work.  But on my live website, the folder used for DB's is located bellow the folder used for the web,  I don't know the exact folder hierarchy used on the server.


RustyTulip,

I'm using ASP.NET and C#.  That looks like VB or VB script...  Nevertheless, I tried using the connection string you provided.  I get the same error.



Doing this with MySQL is easy.  Simply: "DSN=ppp;DESC=MySQL ODBC 3.51 Driver DSN;"  I was hoping to do something similar with Access, because that is all I have available on my live website.  Thinks are not looking so great though.

->Joe

Author

Commented:
Alright, I have figured out something that works on the site, but does not use the DSN provided by the site.  I'll just have to "hide" the database in a hidden folder on the site.  This is what worked, using a little different approach.

public void Page_Load(object sender, EventArgs e)
{
      DataSet _data = new DataSet();

      string _conn = "Provider=Microsoft.Jet.OLEDB.4.0; data source=" + Server.MapPath("mypath/db1.mdb");
      string _query = "SELECT * FROM users";
      OleDbDataAdapter _adapter = new OleDbDataAdapter( _query, _conn );
      _adapter.Fill(_data);

      Repeater1.DataSource = _data.Tables[0].DefaultView;
      Repeater1.DataBind();
}

I'll split the points between AW & RustyTulip ... anyone have any retorts?

->Joe
Works for me, thank you and glad to be of assistance.

AW

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.