Access 2007 Connection

[JackBurton07]

Hi All,

I just cant seem to get this to work??

private void button1_Click(object sender, EventArgs e)
{
//The connection string is used to describe the type of database, the security information and the location to the database.
string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\resources\pw.accdb;Jet OLEDBatabase Password=password";
//Create a new connection object and assign the ConString Connection String above
OleDbConnection DBCon = new OleDbConnection(ConnStr);

string strUsername = this.txtUN.Text;
string strPassword = this.txtPW.Text;


if (strUsername == "" || strPassword == "")
{
MessageBox.Show("You are missing information. Please make sure that both the username and password fields are filled out.", "Missing Info");
this.txtUN.Focus();
return;
}
// Conn String to Access 2007 DB

string strsql = "SELECT [UserID], [Password] FROM USES WHERE [UserID]='" + this.txtUN + "'[Password]="+ this.txtPW+"";

Form1.Show

 

[jmeckley]

access uses double quotes not single quotes.
you should also review the best practices for using ado.net.
1. always wrap connections and commands with a using statment (or try/finally blocks) to close/dispose objects
2. always use parameterized queries. they provide better preformance, are safe from sql injection attacks and would negate the problem you are currently having.
3. store the connection string is a central location (web.config) for a connection factory object.

it also good practice to validate input before instantiaing objects you may not need.if you're going to store passwords in the database they should be hashed to protect the information.

This is how i would approach the problem.

void ButtonClick(object sender, eventargs e)
{
   string userName = UserNameTextBox.Name;
   string password = EncryptTheString(PasswordTextBox.Password);
   if(IsTheLoginCorrect(userName, password))
   {
      //do something with the valid user
   }
   else
   {
      //notify the credentials are incorrect
   }
}

bool IsTheLoginCorrect(string userName, string password)
{
   string connectionString = ConfigurationManager
      .ConnectionStrings["Default"]
      .ConnectionString;

   using(IDbConnection connection = new new OleDbConnection(connectionString ))
   using(IDbCommand command = connection.CreateCommand())
   {
      command.CommandText = "select count(1) from [users] where userid = @user and password = @password";

      IDbParameter user = command.CreateParameter();
      user.Name = "user";
      user.Value = userName;
      command.Parameters.Add(user);

      IDbParameter pwd= command.CreateParameter();
      pwd.Name = "password";
      pwd.Value = password;
      command.Parameters.Add(pwd);

      object output = command.ExecuteScalar();
      return (int)output == 1;
   }
}

string EncryptTheString(string input)
{
   string seed = "an arbitrary value";
   byte[] hashedDataBytes = new SHA1CryptoServiceProvider()
      .ComputeHash(new UTF8Encoding()
      .GetBytes(seed + phrase));
   return ByteArrayToString(hashedDataBytes);
}

string byteArrayToString(byte[] inputArray) 
{
   StringBuilder output = new StringBuilder();
   for(int i = 0; i < inputArray.Length; i++) 
   {
      output.Append(inputArray[i].ToString("X2"));
   }
   return output.ToString();
}

this is off the top of my head, so i'm sure there are syntax errors.
you can also refactor the Encryption members into their own object so you don't repeat the code in other areas. the ado.net code can also be refactored into a simple set of DAL objects.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JackBurton07]

Thanks for this -

Do I put my connection string here replacing "Default"?

string connectionString = ConfigurationManager
.ConnectionStrings["Default"]
.ConnectionString;

 

[jmeckley]

Default" is the ID of the connection string, it's an abritrary key. This relates to the connection string section in the web/app.config file.

Jason Meckley
Programmer
Specialty Bakers, Inc.