c# and Microsoft access

-Od|n- · 08-29-2009, 12:35 AM

I was wondering If its an obligation to create the Queries and then link the c# program on to access or if I can start with linking the program to the database without creating the queries for now?

Magos · 08-30-2009, 04:47 AM

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.

-Od|n- · 09-03-2009, 01:41 AM

Quote:

Originally Posted by Magos

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.

Sorry for late replies I was busy and not structuring my sentence properly, first of all thanx very much for your advice. I am however having problems with my database, I save it as access 2007 in debug -bin folder and when i connect by OleDb it cant open the files when i use .mDB as path, but it opens up when I save the database file in access 2000.

do u know what i can do to open up access 2007?

Magos · 09-03-2009, 01:44 AM

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

-Od|n- · 09-07-2009, 01:10 PM

well im getting this probrlem now i connected my database with c# and am getting "ExecuteNonQuery: connection property has not been initialized"

Code:

public partial class frmUpdateSupplier : Form
    {
        private OleDbConnection mDB;
        public frmUpdateSupplier()
        {
            InitializeComponent();
           
        }
        
        
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            
            string supplierId;
            string companyName;
            string firstName;
            string lastName;
            string street;
            string country;
            string zipCode;
            string phoneNo;
            string mobileNo;
            string emailAddress;
            string sql;
            OleDbCommand cmd;
          
            supplierId = txtSupplierId.Text;
            companyName = txtCompanyName.Text;
            firstName = txtFirstName.Text;
            lastName = txtLastName.Text;
            street = txtStreet.Text;
            country = txtCountry.Text;
            zipCode = txtZipCode.Text;
            phoneNo = txtPhoneNo.Text;
            mobileNo = txtMobile.Text;
            emailAddress = txtEmailAdd.Text;

            try
            {
                 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);



                cmd = new OleDbCommand(sql, mDB);


            cmd.ExecuteNonQuery();



       


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            
            



        }
        private string toSql(string stringValue)
        {

            return "'" + stringValue.Replace("'", "''") + "'";
        }
        private string toSql(int intValue)
        {
            return intValue.ToString();
        }
        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Magos · 09-07-2009, 02:45 PM

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();
}

-Od|n- · 09-09-2009, 05:24 PM

hmph i dont really understand what you typed there sorry im kinda noob, instead of

Code:

private OleDbConnection mDB;

I went and created an object

Code:

OleDbConnection mDB = new OleDbConnection();

between can u tell me exactly what this code does

Code:

cmd = new OleDbCommand(sql, mDB);


            cmd.ExecuteNonQuery();

Everything is in this book im using but its kinda hard to understand some parts of it.. from what I get it "cmd =.... " instantiates an OleDbcommand object and passes it to the SQl statement.. and ExecuteNonQuery is a method....

Between thanx again for all the help u are giving me...

StainedBlue · 09-09-2009, 08:38 PM

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.

-Od|n- · 09-10-2009, 05:31 PM

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.

StainedBlue · 09-11-2009, 08:19 AM

>>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).

08-29-2009, 12:35 AM	#1
-Od\|n- Registered User Join Date: Jul 2009 Posts: 8	c# and Microsoft access I was wondering If its an obligation to create the Queries and then link the c# program on to access or if I can start with linking the program to the database without creating the queries for now?
-Od\|n- is offline

08-30-2009, 04:47 AM	#2
Magos Confused Join Date: Sep 2001 Location: Sweden Posts: 3,122	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. __________________ MagosX.com Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Magos is offline

09-03-2009, 01:44 AM	#4
Magos Confused Join Date: Sep 2001 Location: Sweden Posts: 3,122	Check your connection strings, it seems Access 2000 and Access 2007 uses different strings: ConnectionStrings.com - Forgot that connection string? Get it here! __________________ MagosX.com Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Magos is offline

09-07-2009, 02:45 PM	#6
Magos Confused Join Date: Sep 2001 Location: Sweden Posts: 3,122	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(); } __________________ MagosX.com Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Magos is offline

09-09-2009, 05:24 PM	#7
-Od\|n- Registered User Join Date: Jul 2009 Posts: 8	hmph i dont really understand what you typed there sorry im kinda noob, instead of Code: private OleDbConnection mDB; I went and created an object Code: OleDbConnection mDB = new OleDbConnection(); between can u tell me exactly what this code does Code: cmd = new OleDbCommand(sql, mDB); cmd.ExecuteNonQuery(); Everything is in this book im using but its kinda hard to understand some parts of it.. from what I get it "cmd =.... " instantiates an OleDbcommand object and passes it to the SQl statement.. and ExecuteNonQuery is a method.... Between thanx again for all the help u are giving me...
-Od\|n- is offline

09-10-2009, 05:31 PM	#9
-Od\|n- Registered User Join Date: Jul 2009 Posts: 8	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. Last edited by -Od\|n-; 09-10-2009 at 05:34 PM.
-Od\|n- is offline

09-11-2009, 08:19 AM	#10
StainedBlue ...and never returned. Join Date: Aug 2009 Posts: 41	>>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). Last edited by StainedBlue; 09-11-2009 at 08:24 AM.
StainedBlue is offline

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode