Programming codes: 11-Tutorial 10: Using SQL to Search and Modify an Access Database In Visual C++.NET 2008, 2010

Tutorial 10: Using SQL to Search and Modify an Access Database In Visual C++.NET 2008, 2010 (Updated 12/11/2011)

Click here for the source code to the Concatenating Program in Visual C++.NET for the 2010 version or click here to download the 2008 version. This program shows how to create a simple database program, which uses odbcad32, and an oleDataAdapter to hook up to a Access 2010 database using SQL commands. This program will not use the Data Sources window found in Visual Basic, which probably doesn't exist in Visual C++.NET 2010. First, open Microsoft Access (if you do not have Access 2007 use Access 2005, or a supplied database) and select new blank database. You can download a free redistributable version of Access at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10910 You may also need the Access database engine at http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d . You may not be able to directly make tables with what is provided in the links, but you will be able to access my database in the sample project. Depending on what version of Windows you have you may have to make some adjustments. There is an issue with 64 bit vs. 32 bit Windows7 (refer to http://support.microsoft.com/kb/942976) which says if you use a 64 bit operating system you must have a 32 bit datasource (database). The code here works in the 32 bit environment. If you wish to go to the 64 bit environment, it has been reported that if you use an older mdb database that you may be able to overcome this issue. Both PhoneBook.accdb written in Access 2010 format and PhoneBook.mdb written in a 2003 format are included in this project as well as all the graphics.

Create a directory called download on your c: drive in the root and save the file as PhoneBook.accdb

To create fields in access for the first name and last name be careful. If you choose a key word in Access the program won't. Specifically it turns out first and last have become key words. For a list of key words that you can't use as column names refer to http://sqlserver2000.databases.aspfaq.com/what-are-reserved-access-odbc-and-sql-server-keywords.html . So make the columns LastName, FirstName, Phone, and fill in as follows:

Try to exit Acess after you have entered your data and it will present the following, save this as is as Table1

Alternative: Save the program as a 2002-2003 mdb database:
This should look as follows:

When you finish entering your data go to file and save the table as Table1 (the default). Say yes to creating a primary field. Since it matters where you put your database, it is easiest to put it in the root of your C:\ drive after you have created it. Also be careful, databases may be case sensitive. Close Access and your database before going on!
Open My Computer or Windows Explorer, navigate to the C:\windows\system32 and search for odbcad32.exe. Right click on this file as follows and select >send to>desktop.

Double click on the odbcad32.exe icon which will start the ODBC Data Source Administrator. Select the System DSN tab and Add

In Create New Data Source select Microsoft Access Driver (*.mdb, *.accdb) as shown and click Finish.

In the ODBC Microsoft Access Setup Window enter PhoneBook under Data Source Name, leave description blank, and click Select.

Navigate to and select your phonebook.mdb in the root of your c: drive and click OK.

Click OK under ODBC Microsoft Access Setup

This completes configuring the ODBC Administrator so your program will be able access the database. Design a form that looks as follows called NamesDBProgram3 (the large white field in the program below is a textbox with it multi-line function set to true).

Go to the tool box and under data items right click, and select choose items as shown below. Add an oleDataAdapter to your form as follows:

Under the .NET Framework Components select OleDbDataAdapter as shown below

This will add a OleDbDataAdapter to the toolbox. Add the OleDBDataAdapter to your form which will trigger the following window. Select New Connection.

This will trigger the appearance of the following dialog window:

Select Change, which will cause the following window to appear and select “Microsoft Access Database File” and then OK.

This will cause you to return to the Add Connection Dialog which looks as follows.

Click Browse and navigate to and select phonebook.accdb and click Open.

Don’t include a password but leave Admin as shown. Click on test connection and make sure a connection to the database exists. Then click OK. Back at the wizard click OK.

In the Data Adapter Configuration Wizard click Next.

In the following dialog window select Use SQL statements.

Data Adapter Configuration Wizard...Use SQL

In the next window click on Query Builder…

In the next dialog window select Add, then close this “Add Table Window and then click close.

Click close to Add Table, and in Query Builder select All Columns, ID, lastName, firstName, and Phone and click on OK.

This should lead to the dialog box. Click Finish.

Your design window in Visual C++ should now look as follows:

If something goes wrong in setting up your database, it is very, very difficult to fix. If you do have to change something, you may have to go to view, select Server Explorer, right click on the database you just configured and select delete. Then remove the related objects on your form. If this doesn’t clean things out so you get the above window you may have to close your project, open a new one and start over. It may even be necessary to reboot your computer.

Add the following lines of code. I have high lighted in yellow some lines that are easy to miss. Make sure to get the code to the form load event and the buttons.

#pragma once

namespace NamesDBProgram {

using namespace System;

using namespace System::ComponentModel;

using namespace System::Collections;

using namespace System::Windows::Forms;

using namespace System::Data;

using namespace System::Drawing;

using namespace System::Data::OleDb;

/// <summary>

/// Summary for Form1

///

/// WARNING: If you change the name of this class, you will need to change the

/// 'Resource File Name' property for the managed resource compiler tool

/// associated with all .resx files this class depends on. Otherwise,

/// the designers will not be able to interact properly with localized

/// resources associated with this form.

/// </summary>

public ref class Form1 : public System::Windows::Forms::Form

{

public:

static String ^findFirst="";

static String ^findLast="";

static bool foundJustUsed=false;

Form1(void)

{

InitializeComponent();

//TODO: Add the constructor code here

}

protected:

/// <summary>

/// Clean up any resources being used.

/// </summary>

~Form1()

{

if (components)

{

delete components;

}

protected:

private: System::Windows::Forms::Label^ label7;

private: System::Windows::Forms::Button^ button4;

private: System::Windows::Forms::Label^ label6;

private: System::Windows::Forms::Button^ button3;

private: System::Windows::Forms::Label^ label5;

private: System::Windows::Forms::Label^ label4;

private: System::Windows::Forms::Button^ button2;

private: System::Windows::Forms::TextBox^ textBox4;

private: System::Windows::Forms::Button^ button1;

private: System::Windows::Forms::TextBox^ textBox3;

private: System::Windows::Forms::Label^ label3;

private: System::Windows::Forms::TextBox^ textBox2;

private: System::Windows::Forms::Label^ label2;

private: System::Windows::Forms::TextBox^ textBox1;

private: System::Windows::Forms::Label^ label1;

private: System::Data::OleDb::OleDbCommand^ oleDbSelectCommand1;

private: System::Data::OleDb::OleDbCommand^ oleDbInsertCommand1;

private: System::Data::OleDb::OleDbCommand^ oleDbUpdateCommand1;

private: System::Data::OleDb::OleDbCommand^ oleDbDeleteCommand1;

private: System::Data::OleDb::OleDbDataAdapter^ oleDbDataAdapter1;

private: System::Data::OleDb::OleDbConnection^ oleDbConnection1;

private: System::ComponentModel::IContainer^ components;

private:

/// <summary>

/// Required designer variable.

/// </summary>

#pragma region Windows Form Designer generated code

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

void InitializeComponent(void)

{

System::ComponentModel::ComponentResourceManager^ resources = (gcnew System::ComponentModel::ComponentResourceManager(Form1::typeid));

this->label7 = (gcnew System::Windows::Forms::Label());

this->button4 = (gcnew System::Windows::Forms::Button());

this->label6 = (gcnew System::Windows::Forms::Label());

this->button3 = (gcnew System::Windows::Forms::Button());

this->label5 = (gcnew System::Windows::Forms::Label());

this->label4 = (gcnew System::Windows::Forms::Label());

this->button2 = (gcnew System::Windows::Forms::Button());

this->textBox4 = (gcnew System::Windows::Forms::TextBox());

this->button1 = (gcnew System::Windows::Forms::Button());

this->textBox3 = (gcnew System::Windows::Forms::TextBox());

this->label3 = (gcnew System::Windows::Forms::Label());

this->textBox2 = (gcnew System::Windows::Forms::TextBox());

this->label2 = (gcnew System::Windows::Forms::Label());

this->textBox1 = (gcnew System::Windows::Forms::TextBox());

this->label1 = (gcnew System::Windows::Forms::Label());

this->oleDbSelectCommand1 = (gcnew System::Data::OleDb::OleDbCommand());

this->oleDbConnection1 = (gcnew System::Data::OleDb::OleDbConnection());

this->oleDbInsertCommand1 = (gcnew System::Data::OleDb::OleDbCommand());

this->oleDbUpdateCommand1 = (gcnew System::Data::OleDb::OleDbCommand());

this->oleDbDeleteCommand1 = (gcnew System::Data::OleDb::OleDbCommand());

this->oleDbDataAdapter1 = (gcnew System::Data::OleDb::OleDbDataAdapter());

this->SuspendLayout();

// label7

this->label7->Location = System::Drawing::Point(273, 20);

this->label7->Name = L"label7";

this->label7->Size = System::Drawing::Size(150, 43);

this->label7->TabIndex = 32;

this->label7->Text = L"First do a find, then change First, Last, Phone as needed and press update";

// button4

this->button4->Location = System::Drawing::Point(317, 66);

this->button4->Name = L"button4";

this->button4->Size = System::Drawing::Size(84, 25);

this->button4->TabIndex = 31;

this->button4->Text = L"Update";

this->button4->UseVisualStyleBackColor = true;

this->button4->Click += gcnew System::EventHandler(this, &Form1::button4_Click);

// label6

this->label6->AutoSize = true;

this->label6->Location = System::Drawing::Point(150, 112);

this->label6->Name = L"label6";

this->label6->Size = System::Drawing::Size(126, 13);

this->label6->TabIndex = 30;

this->label6->Text = L"Enter First, Last to Delete";

// button3

this->button3->Location = System::Drawing::Point(153, 134);

this->button3->Name = L"button3";

this->button3->Size = System::Drawing::Size(84, 25);

this->button3->TabIndex = 29;

this->button3->Text = L"Delete";

this->button3->UseVisualStyleBackColor = true;

this->button3->Click += gcnew System::EventHandler(this, &Form1::button3_Click);

// label5

this->label5->AutoSize = true;

this->label5->Location = System::Drawing::Point(150, 66);

this->label5->Name = L"label5";

this->label5->Size = System::Drawing::Size(129, 13);

this->label5->TabIndex = 28;

this->label5->Text = L"Enter First, Last for Phone";

// label4

this->label4->AutoSize = true;

this->label4->Location = System::Drawing::Point(150, 14);

this->label4->Name = L"label4";

this->label4->Size = System::Drawing::Size(117, 13);

this->label4->TabIndex = 27;

this->label4->Text = L"Enter First, Last, Phone";

// button2

this->button2->Location = System::Drawing::Point(153, 82);

this->button2->Name = L"button2";

this->button2->Size = System::Drawing::Size(84, 25);

this->button2->TabIndex = 26;

this->button2->Text = L"Find";

this->button2->UseVisualStyleBackColor = true;

this->button2->Click += gcnew System::EventHandler(this, &Form1::button2_Click);

// textBox4

this->textBox4->Location = System::Drawing::Point(208, 176);

this->textBox4->Multiline = true;

this->textBox4->Name = L"textBox4";

this->textBox4->Size = System::Drawing::Size(131, 115);

this->textBox4->TabIndex = 25;

// button1

this->button1->Location = System::Drawing::Point(153, 36);

this->button1->Name = L"button1";

this->button1->Size = System::Drawing::Size(84, 25);

this->button1->TabIndex = 24;

this->button1->Text = L"Insert\r\n";

this->button1->UseVisualStyleBackColor = true;

this->button1->Click += gcnew System::EventHandler(this, &Form1::button1_Click);

// textBox3

this->textBox3->Location = System::Drawing::Point(55, 105);

this->textBox3->Name = L"textBox3";

this->textBox3->Size = System::Drawing::Size(75, 20);

this->textBox3->TabIndex = 23;

// label3

this->label3->AutoSize = true;

this->label3->Location = System::Drawing::Point(11, 108);

this->label3->Name = L"label3";

this->label3->Size = System::Drawing::Size(38, 13);

this->label3->TabIndex = 22;

this->label3->Text = L"Phone";

// textBox2

this->textBox2->Location = System::Drawing::Point(55, 72);

this->textBox2->Name = L"textBox2";

this->textBox2->Size = System::Drawing::Size(75, 20);

this->textBox2->TabIndex = 21;

// label2

this->label2->AutoSize = true;

this->label2->Location = System::Drawing::Point(22, 72);

this->label2->Name = L"label2";

this->label2->Size = System::Drawing::Size(27, 13);

this->label2->TabIndex = 20;

this->label2->Text = L"Last";

// textBox1

this->textBox1->Location = System::Drawing::Point(55, 39);

this->textBox1->Name = L"textBox1";

this->textBox1->Size = System::Drawing::Size(75, 20);

this->textBox1->TabIndex = 19;

// label1

this->label1->AutoSize = true;

this->label1->Location = System::Drawing::Point(23, 42);

this->label1->Name = L"label1";

this->label1->Size = System::Drawing::Size(26, 13);

this->label1->TabIndex = 18;

this->label1->Text = L"First";

// oleDbSelectCommand1

this->oleDbSelectCommand1->CommandText = L"SELECT Table1.*, ID AS Expr1, lastName AS Expr2, firstName AS Expr3, phone AS"

L" Expr4\r\nFROM Table1";

this->oleDbSelectCommand1->Connection = this->oleDbConnection1;

// oleDbConnection1

this->oleDbConnection1->ConnectionString = L"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\PhoneBook.accdb";

// oleDbInsertCommand1

this->oleDbInsertCommand1->CommandText = L"INSERT INTO `Table1` (`lastName`, `firstName`, `phone`, `lastName`, `firstName`, "

L"`phone`) VALUES (\?, \?, \?, \?, \?, \?)";

this->oleDbInsertCommand1->Connection = this->oleDbConnection1;

this->oleDbInsertCommand1->Parameters->AddRange(gcnew cli::array< System::Data::OleDb::OleDbParameter^ >(6) {(gcnew System::Data::OleDb::OleDbParameter(L"lastName",

System::Data::OleDb::OleDbType::VarWChar, 0, L"lastName")), (gcnew System::Data::OleDb::OleDbParameter(L"firstName", System::Data::OleDb::OleDbType::VarWChar,

0, L"firstName")), (gcnew System::Data::OleDb::OleDbParameter(L"phone", System::Data::OleDb::OleDbType::VarWChar, 0, L"phone")),

(gcnew System::Data::OleDb::OleDbParameter(L"Expr2", System::Data::OleDb::OleDbType::VarWChar, 0, L"Expr2")), (gcnew System::Data::OleDb::OleDbParameter(L"Expr3",

System::Data::OleDb::OleDbType::VarWChar, 0, L"Expr3")), (gcnew System::Data::OleDb::OleDbParameter(L"Expr4", System::Data::OleDb::OleDbType::VarWChar,

0, L"Expr4"))});

// oleDbUpdateCommand1

this->oleDbUpdateCommand1->CommandText = resources->GetString(L"oleDbUpdateCommand1.CommandText");

this->oleDbUpdateCommand1->Connection = this->oleDbConnection1;

this->oleDbUpdateCommand1->Parameters->AddRange(gcnew cli::array< System::Data::OleDb::OleDbParameter^ >(21) {(gcnew System::Data::OleDb::OleDbParameter(L"lastName",

System::Data::OleDb::OleDbType::VarWChar, 0, L"lastName")), (gcnew System::Data::OleDb::OleDbParameter(L"firstName", System::Data::OleDb::OleDbType::VarWChar,

0, L"firstName")), (gcnew System::Data::OleDb::OleDbParameter(L"phone", System::Data::OleDb::OleDbType::VarWChar, 0, L"phone")),

(gcnew System::Data::OleDb::OleDbParameter(L"Expr2", System::Data::OleDb::OleDbType::VarWChar, 0, L"Expr2")), (gcnew System::Data::OleDb::OleDbParameter(L"Expr3",

System::Data::OleDb::OleDbType::VarWChar, 0, L"Expr3")), (gcnew System::Data::OleDb::OleDbParameter(L"Expr4", System::Data::OleDb::OleDbType::VarWChar,

0, L"Expr4")), (gcnew System::Data::OleDb::OleDbParameter(L"Original_ID", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"ID", System::Data::DataRowVersion::Original, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"IsNull_lastName", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"lastName", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_lastName", System::Data::OleDb::OleDbType::VarWChar, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"lastName", System::Data::DataRowVersion::Original, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"IsNull_firstName", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"firstName", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_firstName", System::Data::OleDb::OleDbType::VarWChar, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"firstName", System::Data::DataRowVersion::Original,

nullptr)), (gcnew System::Data::OleDb::OleDbParameter(L"IsNull_phone", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"phone", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_phone", System::Data::OleDb::OleDbType::VarWChar, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"phone", System::Data::DataRowVersion::Original, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"IsNull_Expr1", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr1", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_Expr1", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr1", System::Data::DataRowVersion::Original, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"IsNull_Expr2", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr2", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_Expr2", System::Data::OleDb::OleDbType::VarWChar, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr2", System::Data::DataRowVersion::Original, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"IsNull_Expr3", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr3", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_Expr3", System::Data::OleDb::OleDbType::VarWChar, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr3", System::Data::DataRowVersion::Original, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"IsNull_Expr4", System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input,

static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr4", System::Data::DataRowVersion::Original, true, nullptr)),

(gcnew System::Data::OleDb::OleDbParameter(L"Original_Expr4", System::Data::OleDb::OleDbType::VarWChar, 0, System::Data::ParameterDirection::Input,

false, static_cast<System::Byte>(0), static_cast<System::Byte>(0), L"Expr4", System::Data::DataRowVersion::Original, nullptr))});

// oleDbDeleteCommand1

this->oleDbDeleteCommand1->CommandText = resources->GetString(L"oleDbDeleteCommand1.CommandText");

this->oleDbDeleteCommand1->Connection = this->oleDbConnection1;

this->oleDbDeleteCommand1->Parameters->AddRange(gcnew cli::array< System::Data::OleDb::OleDbParameter^ >(15) {(gcnew System::Data::OleDb::OleDbParameter(L"Original_ID",

System::Data::OleDb::OleDbType::Integer, 0, System::Data::ParameterDirection::Input, false, static_cast<System::Byte>(0),

static_cast<System::Byte>(0), L"ID", System::Data::DataRowVersion::Original, nullptr)), (gcnew System::Data::OleDb::OleDbParameter(L"IsNull_lastName",