CODEWITHSUNDEEP
c#sqldatagridview
Deadphoenix
Deadphoenix

Reputation: 88

Pulling Data from MS Access DataBase to DataGridView using Variable

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,

Upvotes: 1

Views: 214

Answers (2)

Steve
Steve

Reputation: 216293

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

Upvotes: 1

Francis
Francis

Reputation: 94

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

Upvotes: 0

Related Questions