C# Get certain data from AccessDatabase 2010 and display in a combobox C#

Question

0.00/5 (No votes)

See more:

I have tried posting this question on MSDN forums, however, i had no success.

I am trying to make a C# Windows Forms Application for a business that will manage its employees. The application uses an Access 2010 Database. The edit Employee page is what I am having trouble with. I want the Employees to be able to be selected from a combobox in the format of:

FirstName LastName, EmployeePosition

The database name is EmployeeInformation.accdb
The fields that I want from the Database are:

- FirstName
- LastName
- EmployeePosition

The name of the Combo Box is cboSelectEmp

The current code i am using to try and get this data is

C#

private void LoadDataToCbo()
        {
            string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EmployeeInformation.accdb";
           // string query = @"SELECT FirstName from Employees";
            string query =
                @"SELECT EmpID, LastName + ', ' + FirstName + ' (' + EmployeePosition + ')' as Name FROM Employees";
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
            DataTable source = new DataTable();
            dAdapter.Fill(source);
            cboSelectEmp.DataSource = source;
            //cboSelectEmp.ValueMember = "FirstName";
            //cboSelectEmp.DisplayMember = "FirstName";

            cboSelectEmp.DisplayMember = "Name";
            cboSelectEmp.ValueMember = "EmpID";

        }

Posted 11-Dec-13 11:44am

User 9512297

Add a Solution

Comments

Dave Kreskowiak 11-Dec-13 18:34pm

So what's the problem?

[no name] 11-Dec-13 18:35pm

it doesn't work
it throws exceptions all over the place.
sorry i forgot to mention this in the question

Dave Kreskowiak 11-Dec-13 18:36pm

You also forgot to mention the exception messages!!!

[no name] 11-Dec-13 18:37pm

i just posted it

[no name] 11-Dec-13 18:37pm

it throws an exception on dAdapter.Fill(Source);

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

sorry i forgot to mention this in the question.

Dave Kreskowiak 11-Dec-13 19:56pm

Ah, OK. The error is a bit misleading but understandable once you figure it out.

The word 'Name' is a reserved word in Access. It's not recommended that you use that as a table or column name. In order to use it, you have to surround the word with square brackets, like this:

SELECT EmpID, LastName + ', ' + FirstName + ' (' + EmployeePosition + ')' as [Name] FROM Employees

[no name] 11-Dec-13 20:23pm

i still get the same exception with your query

[no name] 11-Dec-13 18:38pm

I am very unfamiliar to SQL querys and OleDB usage

5 solutions

Solution 2

Try This:

C#

string query =
               @"SELECT EmpID, FirstName as [Name], LastName , EmployeePosition   FROM Employees";

Posted 11-Dec-13 13:35pm

Sudhakar Tillapudi

Updated 11-Dec-13 13:44pm

v2

Comments

[no name] 11-Dec-13 19:39pm

That gives an exception

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

Sudhakar Tillapudi 11-Dec-13 19:44pm

i think name is reserved word in access try to encose it in square brackets [], see my edited answer.

[no name] 11-Dec-13 19:51pm

Using that query still gives the exception

Solution 3

Check this line in documentation under remarks This overload of the OleDbDataAdapter constructor uses the selectConnectionString parameter to set the SelectCommand property. However, it does not open the connection. You still must explicitly open the connection.

http://msdn.microsoft.com/en-us/library/2f8y4737(v=vs.110).aspx

I think that is your problem

Try this

C#

string query =
               @"SELECT EmpID, FirstName as [Name], LastName , EmployeePosition   FROM Employees";
using (var odb = new OleDbConnection(connectionString))
                       {
                           odb.Open();
                           if (odb.State == ConnectionState.Open)
                           {
                               var dset = new DataTable();
                               var adapter = new OleDbDataAdapter();
                               var newCmd = new OleDbCommand(query, odb)
                                   {
                                       CommandType =  CommandType.Text
                                   };
                               adapter.SelectCommand = newCmd;
                               adapter.Fill(dset);
                               odb.Close();
                             
                           }
}

Posted 11-Dec-13 14:54pm

Premith Unnikrishnan

Updated 11-Dec-13 16:40pm

v2

Comments

[no name] 11-Dec-13 22:03pm

Again this throws an exception on adapter.Fill(dset);
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

Solution 4

Try query string in solution2 by Sudhakar Tillapudi

Posted 11-Dec-13 17:21pm

Premith Unnikrishnan

Solution 6

hey nattyman its easy task

write your code in catch block and then debug now do necessary changes it will solve

Posted 16-Dec-13 20:08pm

Gaurav Makwana

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 9512297 · Accepted Answer · 2013-12-11T19:54:00

I fixed it using this code:

C#

private void LoadDataToCbo()
       {
           string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EmployeeInformation.accdb";
           string query =
                          @"SELECT (FirstName + ' ' + LastName + ', ' + EmployeePosition) as FirstName from Employees";
           OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
           DataTable source = new DataTable();
           dAdapter.Fill(source);
           cboSelectEmp.DataSource = source;
           cboSelectEmp.DisplayMember = "FirstName";


       }

it looks like it was the ValueMember property was the thing that was messing with me.

Thanks everyone for all your help

C# Get certain data from AccessDatabase 2010 and display in a combobox C#

5 solutions

Solution 5

Solution 2

Solution 3

Solution 4

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us