Thread: c# and Microsoft access

If you're talking about connecting to the database (like using OleDbConnection) and building a query (like "SELECT * FROM ...") then you can do it in any order you want. To actually run the query you have to be connected to the database first, of course.

Check your connection strings, it seems Access 2000 and Access 2007 uses different strings:
ConnectionStrings.com - Forgot that connection string? Get it here!

mDB seems to be null. Create a connection object (OleDbConnection) to it before using it in the command.
I suggest a using-statement, so you won't have to manually clean it up:

Code:

using(var c = new OleDbConnection(...))
{
  c.Open();

  var cmd = new OleDbCommand(...);
  ...
  smd.ExecuteNonQuery();
}

Code:

cmd = new OleDbCommand(queryString, connection)

It's a constructor for the command class. It takes your query i.e. (SELECT something FROM someTable) and the connection (in your case mDB).

Code:

cmd.ExecuteNonQuery() # returns no value

executes the command. There are other methods for commands such as:

Code:

cmd.ExecuteScalar() #returns a single value
cmd.ExecuteReader() # returns a record-set (multiple values)

Here are some examples of each:

Code:

OleDbConnection dbCon = new OleDbConnection(connectionString);
OleDbCommand cmd = dbCon.CreateCommand();
cmd.CommandText =
  @"UPDATE Students SET Address=@Address"; // the first @ symbol denotes a string literal
                                                                         // the second denotes a parameter

cmd.Parameters.AddWithValue("@Address", "1 Park Ave. New York, NY. 00000");

dbCon.Open();
cmd.ExecuteNonQuery();
dbCon.Close();

The above uses the ExecuteNonQuery() method because it is an UPDATE statement, and therefore returns no values. The same would be used for INSERT statements as well. Notice the parameterized query (Address=@Address). Parameterizing your queries like this is good practice, it not only prevents sql injection (which I will explain further), but it offers a clear way to organize your code (thus improving maintainability).

Sql injection can occur when users can explicitly enter data into some field in your form (namely textboxes). Consider the following code snippet:

Code:

cmd.CommandText = "DELETE FROM Students WHERE Name='" + myTextBox.Text + "'";

if I enter the following into the textbox:

Code:

Fred OR 1=1

I will delete all the records in the Student table (while no student named 'Fred' might exist, 1 will certainly always equal 1!). Parameterizing your queries prevents this type of malicious activity.

Some commands will return values. To return a single value, use ExecuteScalar()

Code:

OleDbConnection dbCon = new OleDbConnection(connectionString);
OleDbCommand cmd = dbCon.CreateCommand();
cmd.CommandText =
  @"SELECT COUNT(*) FROM Students"; // will count the total number of students in the Students table

dbCon.Open();
int numStudents = Convert.ToInt32(cmd.ExecuteScalar());
dbCon.Close();

and sometimes it's useful to return entire record sets of data:

Code:

OleDbConnection dbCon = new OleDbConnection(connectionString);
OleDbCommand cmd = dbCon.CreateCommand();

cmd.CommandText = @"SELECT StudentID,Name,Address FROM Students WHERE Class='CS101'";

dbCon.Open();
OleDbDataReader studentInfo = cmd.ExecuteReader();

while(studentInfo.Read()) 
{
  // Display all the students information enrolled in CS101
  Response.Write(studentInfo["StudentID"].ToString() + " " +
    studentInfo["Name"].ToString() + " " + studentInfo["Address"].ToString() + "<br />");
}
studentInfo.Close();
dbCon.Close();

In your code:

Code:

sql = "UPDATE Supplier SET "
                 
                 + "Company = " + toSql(companyName) + ","
                 + "FirstName = " + toSql(firstName) + ","
                 + "LastName = " + toSql(lastName) + ","
                 + "Street = " + toSql(street) + ","
                 + "Country = " + toSql(country) + ","
                 + "ZipCode = " + toSql(zipCode) + ","
                 + "PhoneNo = " + toSql(phoneNo) + ","
                 + "MobileNo =" + toSql(mobileNo) + ","
                 + "EmailAddress =" + toSql(emailAddress)

                 + "WHERE SupplierID = " + toSql(supplierId);

Try Parameterizing all those values like:

Code:

@"UPDATE Supplier SET
     Company=@Company,FirstName=@FirstName,LastName=@LastName,Street=@Street,
     Country=@Country,ZipCode=@ZipCode,PhoneNo=@PhoneNo,MobileNo=@MobileNo,
     EmailAddress=@EmailAddress
  WHERE SupplierID=@SupplierID";

// then add the parameters:
cmd.Parameters.AddWithValue("@Company", toSql(CompanyName));
cmd.Parameters.AddWithValue("@FirstName", toSql(firstName));
cmd.Parameters.AddWithValue("@LastName", toSql(lastName));
cmd.Parameters.AddWithValue("@Street", toSql(street));
cmd.Parameters.AddWithValue("@Country", toSql(country));
cmd.Parameters.AddWithValue("@ZipCode", toSql(zipCode));
cmd.Parameters.AddWithValue("@PhoneNo", toSql(phoneNo));
cmd.Parameters.AddWithValue("@MobileNo", toSql(mobileNo));
cmd.Parameters.AddWithValue("@EmailAddress", toSql(emailAddress));
cmd.Parameters.AddWithValue("@SupplierID", toSql(supplierID));

much more readable that way, and less error-prone since you don't have to worry about
concatenating the query string all those times!



EDIT: In my examples above, I used the SQL way of parameterizing queries, apparently ms access does things differently (big surprise).
Instead of:

Code:

@"SELECT Name FROM Students WHERE Class=@Class"; // Just for example

I think it has to be written like this in access:

Code:

@"SELECT Name FROM Students WHERE Class=?";

then:

Code:

cmd.Parameters.Add("Class", someValueForClass);

urgh. ms access sux.

Hey guys thanx a lot its much clearer now.

my friends said that mysql was harder than ms access..anyway after this im going to try it on mysql.

>>my friends said that mysql was harder than ms access..anyway after this im going to try it on mysql.

Well, you're using "SQL" anyway, you're just using MS Access' flavor of SQL. SQL is not a physical thing, it's a programming language (more accurately, a "querying language"). So the structure of your program will not change, in fact, your program is simple enough, you can change anywhere it says "OleDb" to "Sql".

Like:

Code:

using System.Data.OleDb;
// To:
using System.Data.SqlClient;

// And:
OleDbConnection oledbcon = new OleDbConnection(oledbconString);
// To:
SqlConnection sqlcon = new SqlConnection(sqlconString);

// And:
OleDbCommand oledbcmd // ...
// To:
SqlCommand sqlcmd // ...

// And the query strings will remain the same.

Well, that's the magic of ADO.NET. It has little to do with the DB's themselves.

But I actually think Access is harder, because it's implementation of SQL is so broke. There are many syntactical differences, and many functions are just non-existent. SQL is an ANSI standard, and all the other Sql databases (MySql, Oracle, MS Sql Server) implement that standard very well. This means no matter which DB platform you're using, you can write the exact same SQL statements and 99% of the time you will produce the same exact output. MS Access is no where near this. So when you run into trouble, don't expect much help with Access, the documentation and knowledge base is simply not there.

With that said, there's nothing wrong with Access, but Access is for much simpler business logic (few tables, few records, little interaction). but if you continue to work with DB's, you will have a much easier time dealing with a standards-compliant one. I suggest MS SQL Server Express, it's free, it's extremely powerful, and comes with a very nice GUI (Management Studio).