[dba-SQLServer] Internet MDB connection method

Jim,

Use the OLEDB connection to connect. Syntax is similar to the SQL Connection
object. The database connection string in this case will be stored in
WEB.CONFIG.

---Sample Code---

Sample Call:

sql = "SELECT TOP 1 MailServerName, MailFromAccount, SMTPAuthentication,
SMTPUser, SMTPPassword, Attachment1, Attachment2 FROM tblMailSettings";
sqlParameters.Clear();
sqlConnect = "AccessMDB.Connect";

Hashtable dbMailProperties = new Hashtable();
//make sure that it's empty before we add and data into it
dbMailProperties.Clear();
dbMailProperties = Data.GetOleDbSingleRecord(sql, connectionString,
		MailServer, 
		MailFrom,
		SMTPAuthentication.ToString(),
		SMTPUser,
		SMTPPassword,
		Attachment1,
		Attachment2);

---Public Module in Data class that returns a Hashtable---

NOTES: Hashtable returns key, value pair. You can then walk through the
hashtable and grab the key, value pair to populate your web controls.

		public static Hashtable GetOleDbSingleRecord(string sql,
string sqlConnect, params string[] fieldParams)
		{
			Hashtable dbFields = new Hashtable();
			int recordCount = 0;
			string ConnectionString =
ConfigurationSettings.AppSettings[sqlConnect];
			OleDbConnection sqlConn = new
OleDbConnection(ConnectionString); 
			sqlConn.Open(); 
			OleDbCommand sqlCmd = new OleDbCommand(sql,
sqlConn); 
			OleDbDataReader sqlDR = sqlCmd.ExecuteReader(); 
			//make sure that there's nothing in the hashtable
			dbFields.Clear();
			while (sqlDR.Read()) 
			{ 
				//walk through each supplied parameter and
associate the values retrieved from the table
				//NOTE: the order of fields is important and
must match the order of fields in the table
				//the assignment of the returned field
values will align with the parameters passed to this function
	
//System.Web.HttpContext.Current.Response.Write("Parameters: " +
fieldParams.Length + "<BR>");
				for (int x = 0; x <= fieldParams.Length - 1;
x++)
				{
					//grab the first parameter passed to
the function
					//and assign the first field value
from the recordset
					fieldParams[x] =
sqlDR[x].ToString();
					//populate the hashtable with the
key, value pair
					//in this instance we use the
recordcount and the value assigned to each parameter
					dbFields.Add(recordCount,
fieldParams[x].ToString());
					recordCount += 1;
				}
			} 
			//clean up
			sqlDR.Close(); 
			sqlConn.Close(); 
			//return the hashtable
			if (recordCount == 0)
				return null;
			else
				return dbFields;
		} //end GetOleDbSingleRecord

---Connection string stored in WEB.CONFIG file---
<configuration>
    <appSettings>
		<add key="AccessMDB.Connect"
value="Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\inetpub\wwwroot\WebAppFolder\Databases\AccessDatabase.mdb"  />
		<add key="Impersonate.User" value="Username" />
		<add key="Impersonate.Password" value="Password" />
		<add key="Impersonate.Domain" value="Domain" />
    </appSettings>

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Wednesday, February 13, 2008 10:36 PM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] Internet MDB connection method

Hi All:

What connection method and code do you use to connect to an MDB DB on your
web sites? (I have only used MS SQL in the past and that is fairly straight
forward.) 

My client had originally hard coded the MDB location in a Global file
variable and used the DSN-less Database Connection method but that will not
work now that he is planning on migrating to a public internet hosting
company. Is there a way to pre-setup an ODBC Database Connection that would
work on any location?

Any insights and coding samples would be greatly appreciated.

TIA
Jim    

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com