Pulling Data from MS Access DataBase to DataGridView using Variable

Question

I am designing a C# Application that pulls Asset Information from an existing MS Access Database.

string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\IT\IT Hardware\HWInv.accdb";
            string dbcmd = "SELECT tblInvCategory.Category, tblInvManufacturer.Manufacturer, tblInvModel.Model, tblInvMaster.SerialNumber, tblInvMaster.InvNumber, tblInvMaster.InvNumberExternal, tblInvCompany.CompanyName, tblInvCustomer.CustomerName, tblInvLocation.Location, tblInvMaster.OfficeNumber, tblInvTechs.TechName, tblInvMaster.TechDate, tblInvMaster.UpdatedBy, tblInvMaster.UpdatedDate, tblInvMaster.DeployDate, tblInvMaster.RetirePlanned, tblInvMaster.Status, tblInvMaster.PONumber, tblInvMaster.Notes, tblInvMaster.Audited FROM tblInvTechs INNER JOIN (tblInvModel INNER JOIN (tblInvManufacturer INNER JOIN (tblInvLocation INNER JOIN (tblInvCustomer INNER JOIN (tblInvCompany INNER JOIN (tblInvCategory INNER JOIN tblInvMaster ON tblInvCategory.CategoryID = tblInvMaster.CategoryID) ON tblInvCompany.CompanyID = tblInvMaster.CompanyID) ON tblInvCustomer.CustomerID = tblInvMaster.CustomerID) ON tblInvLocation.LocationID = tblInvMaster.LocationID) ON tblInvManufacturer.ManufacturerID = tblInvMaster.ManufacturerID) ON tblInvModel.ModelID = tblInvMaster.ModelID) ON tblInvTechs.TechID = tblInvMaster.TechID WHERE (((tblInvCustomer.CustomerName)="+ CustomerName + "))";

            OleDbConnection con = new OleDbConnection(conn);
            OleDbCommand cmd = new OleDbCommand(dbcmd, con);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable CustAssets = new DataTable();
            da.Fill(CustAssets);
            dataGridView1.DataSource = CustAssets;

I have an existing Query in MS Access for the Local MS Access Form here:

SELECT tblInvCategory.Category, tblInvManufacturer.Manufacturer, tblInvModel.Model, tblInvMaster.SerialNumber, tblInvMaster.InvNumber, tblInvMaster.InvNumberExternal, tblInvCompany.CompanyName, tblInvCustomer.CustomerName, tblInvLocation.Location, tblInvMaster.OfficeNumber, tblInvTechs.TechName, tblInvMaster.TechDate, tblInvMaster.UpdatedBy, tblInvMaster.UpdatedDate, tblInvMaster.DeployDate, tblInvMaster.RetirePlanned, tblInvMaster.Status, tblInvMaster.PONumber, tblInvMaster.Notes, tblInvMaster.Audited

FROM tblInvTechs INNER JOIN (tblInvModel INNER JOIN (tblInvManufacturer INNER JOIN (tblInvLocation INNER JOIN (tblInvCustomer INNER JOIN (tblInvCompany INNER JOIN (tblInvCategory INNER JOIN tblInvMaster ON tblInvCategory.CategoryID = tblInvMaster.CategoryID) ON tblInvCompany.CompanyID = tblInvMaster.CompanyID) ON tblInvCustomer.CustomerID = tblInvMaster.CustomerID) ON tblInvLocation.LocationID = tblInvMaster.LocationID) ON tblInvManufacturer.ManufacturerID = tblInvMaster.ManufacturerID) ON tblInvModel.ModelID = tblInvMaster.ModelID) ON tblInvTechs.TechID = tblInvMaster.TechID

WHERE (((tblInvCustomer.CustomerName)=[Forms]![frmInvSelectCustomerName]![CustomerName]));

The SQL command is failing when inserted as above. The Varible Customer Name is used to pull just those records for that user from the Access Database but it is failing when compiled and run.

The Call stack outputs this is the Text Visualizer:

Syntax error (missing operator) in query expression '(((tblInvCustomer.CustomerName)=Joe Smith))'.

It appears my issue is in the WHERE statment.

Any help is much appreciated.

Thanks,

Steve · Accepted Answer · 2014-02-25 23:03:11Z

1

If you use a parameterized query you avoid this kind of problems.
The error arises because, if the CustomerName field is a string every value used in a condition should be enclosed in single quotes.
However, manually setting the quotes is dangerous for possible Sql Injections and could cause other syntax errors if the string used as value contains itself a single quote.
Using a parameterized query avoid all of this

string dbcmd = "SELECT ...... " & _ 
               "WHERE (((tblInvCustomer.CustomerName)=?))";

And then

using(OleDbConnection con = new OleDbConnection(conn))
using(OleDbCommand cmd = new OleDbCommand(dbcmd, con))
using(OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
     cmd.Parameters.AddWithValue("@p1", CustomerName);
     DataTable CustAssets = new DataTable();
     da.Fill(CustAssets);
     dataGridView1.DataSource = CustAssets;
}

edited Feb 25, 2014 at 23:03

answered Feb 25, 2014 at 22:41

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Deadphoenix Over a year ago

dataGridView1.DataSource = CustAssets; is reporting that CustAssets are not exist in the current context.

Steve Over a year ago

Yes sorry, moved inside the using block

Deadphoenix Over a year ago

Thanks for that.. The modification to the dbcmd is still giving me trouble. it is a String so using & throws a IDE code error.

Steve Over a year ago

Can't explain such behavior, you accepted my answer, but did you resolve it?

Deadphoenix Over a year ago

I Finished. There was a Typo in the name of the DataGridView Control

|

Francis · Accepted Answer · 2014-02-25 22:56:43Z

0

here's a link leading to a post with a problem a bit similar to yours. you might want to check this out first:

http://social.msdn.microsoft.com/Forums/en-US/8ee73442-f28b-49ab-a4d9-8b550f841aa4/binding-ms-access-db-to-a-datagrid-view-in-c-windows-forms?forum=winformsdatacontrols

answered Feb 25, 2014 at 22:56

Francis

9410 bronze badges

Collectives™ on Stack Overflow

Pulling Data from MS Access DataBase to DataGridView using Variable

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related