Knowledgebase » Knowledgebase » Database » Other Database issues

Sample Connection String for ASP.NET

Mark — Sun, 31 May 2009 14:25:26 GMT

We support several types of databases such as MSSQL, MySQL, Excel, Excel 2007 and Access in ASP.net application. Here are some sample connection strings.

MSSQL

ASP.net connection

"Server=DB Server;Database=DB NAME;uid=DB ID;pwd=DB Password;" providerName="System.Data.SqlClient"

OLE DB connection string (DSNless Connection):

"Provider=SQLOLEDB;Data Source=DB Server;User Id=DB ID;Password=DB Password;Initial Catalog=DB NAME;"

ODBC connection string

"DSN=ODBC Name;User Id=DB ID;Password=DB Password;Initial Catalog=DB NAME;"

Please replace the word in red color into your database information. If you don't know your database information, you can read this article.
For example:
ODBC Name : The name of ODBC you created in control panel > database > ODBC
DB Server : sql3xx.mysite4now.com
DB Name : mydemodb_12345
DB ID : mydemoid_12345
DB Password : mydemopw

MySQL

OLE DB connection string (DSNless Connection):

"DRIVER={MySQL}; SERVER=DB Server; DATABASE=DB Name; USER=DB ID; PASSWORD=DB PASSWORD; OPTION=3;"

ODBC connection string (Requires a ODBC to be created first):

"DSN=ODBC Name;User=DB ID;Password=DB PASSWORD;Database=DB NAME;"

Pleasereplace the word in red color into your database information.
For example:
ODBC Name : The name of ODBC you created in control panel > database > ODBC
IP Address : mysqlxxx.mysite4now.com
DB Name : mysqldemo_12345
DB ID : mysqldemo_12345
DB Password : mysqlpw

Excel

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path of excel;Extended Properties=Excel 8.0"

Replace path of excel by the actual path of your excel file.
Example : d:\hosting\member\memberID\myexcel.xls

PS. If you need to write data into your excel database, please grant write permission to the excel file.

Excel 2007

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path of excel;Extended Properties=Excel 12.0"

Replace path of excel by the actual path of your excel 2007 file.
Example : d:\hosting\member\memberID\myexcel2007.xlsx

PS. If you need to write data into your excel database, please grant write permission to the excel file.

Access

For Access DB WITHOUT password protection:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path of access db"

For Access DB WITH password protection:

"Provider=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=path of access db;password=password of db"

Replace path of access db by the actual path of your excel 2007 file.
Example : d:\hosting\member\memberID\myAccessDB.mdb

Replace password of db by the database password of your Access database.

Why my DSN-less connection cannot locate my ACCESS DB?

Mark — Wed, 24 Dec 2008 11:53:53 GMT

To find the path of your DB, you will have to hardcode the FULL path instead of using a Mappath("mydb.mdb").

If you are using Dreamweaver, by default it will use relateive path.

Why is my ASP page not working when trying to make changes to MS Access DB?

Mark — Wed, 24 Dec 2008 11:33:13 GMT

Thishappens when your .MDB file does not have WRITE permissions. To fixthe problem, go to your Hosting Control Panel, click on the File Permission icon and assign Read + Write to user IUSR_memberID for the .MDB file.

If this still give you error, you'll have to give write permission to the ENTIRE folder that contains the database file.

How do I manage ODBC/DSN connections?

Mark — Wed, 24 Dec 2008 11:21:04 GMT

To manage ODBC/DSN connection, login to Hosting Control Panel -> Database -> ODBC Manager.

Creating MSAccess Connections

Microsoft Access databases must reside in your disk space. There is no AccessServer service. Therefore, before creating a connection, please upload your Access database to your site and then proceed. To create an ODBC/DSN connection:

1. ODBC/DSN NAME: Type the name of the new connection to create
2. Location of the MDB File: Add the full path location of your database
3. Click on on the Create It button and proceed

Creating ms SQL Connections

Our MS SQL Servers contain all your ms SQL databases. Before creating aconnection make sure create a MSSQL database at control panel. Once created, you need to remember your DB name, ID, Password and DB Server. With that information, you can continue to create your ODBC:

1. ODBC/DSN NAME: Type the name of the new connection to create
2. MSSQL Server IP Address: Add the database connection IP*
3. Database User ID: Add your database's userID*
4. Click on on the Create It button and proceed

*Look up this information in Control Panel -> Database -> MSSQL Admin

CreatingmySQL Connections

Our My SQL Servers contain all your my SQL databases. Before creating aconnection make sure create a my SQL database from the control panel. Oncecreated, need to remember your DB name, ID, Password and DB Server to connect. To create a mySQL ODBC:

1. ODBC/DSN NAME: Type the name of the new connection to create
2. MySQL Server IP Address: Add the database connection IP*
3. Database Name: Add the name of your existing my SQL database*
4. Database User ID: Add your database's userID*
5. Database Password: Add your database's password*
6. Click on on the Create It button and proceed

*Look up this information in Control Panel -> Database -> Mysql Admin

Deleting Already Created ODBC/DSN

The panel will show you all the ODBC/DSN connections that exist in your account. Please take notice that, once created, the system will NOT display the type of database associated with the connections, therefore it is recommended to keep such a list on hand. To delete your connection:

1. Select the connection you wish to delete
2. Click on on the Delete It button and proceed

What if my database is not Access, MySQL or MSSQL?

We recommend use DSNless connections in your scripts. If ODBC is essential please contact our support for help.

My page showing funny characters (%%%) when pulling data from my DB that's in another language

Mark — Wed, 24 Dec 2008 11:11:17 GMT

Please make sure you set your codepage in your script, so that ASP will know what language to show when pulling data from database.

Ex:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1256"%>

1256 is your codepage LCID.

To find out your locale id (LCID) , please visit:
http://www.microsoft.com/globaldev/reference/lcid-all.mspx

Why is my ASP page returning ...must be updateable query.. messages when trying to make changes to MS Access DB?

Mark — Wed, 24 Dec 2008 11:09:23 GMT

This happens when your .MDB file does not have WRITE permissions. To fix the problem, go to your Hosting Control Panel, go to Security, go to File Permission on the left hand menu. In there, browse to the folder that contains your .mdb file and give the .mdb file Read + Write to user IUSR_MEMBERID for the .MDB file.

If this still give you error, you'll have to give write permission to the ENTIRE folder that contains the database .MDB file.

My page returns a ODBC not found error?

Mark — Wed, 24 Dec 2008 10:55:07 GMT

You get this error because you did NOT setup an ODBC connection yet. Login to Control Panel and click on the Database -> Create ODBC/DSN icon to create an ODBC connection.