How to create function that returns a single value and single row from MS Access Database in C#

Hakan
Hakan used Ask the Experts™
on
Hello,

I would like to create a function and return a single value like an example;

public static string GetValueFromAccess(string uniqueID) {....}

As in example i'd like to get result "DF556-33446" when i set uniquID = 30075

I'm trying to edit this code to implement it but didn't succeeded.

        private void AccessReader_Click(object sender, RoutedEventArgs e)
        {
            string connetionString = null;
            OleDbConnection cnn;
            OleDbCommand cmd;
            string sql = null;

            connetionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=E:\SampleData\ASampleDatabase.accdb";

            sql = "Select Count(*) from [Asset Items]";

            cnn = new OleDbConnection(connetionString);
            try
            {
                cnn.Open();
                MessageBox.Show("Connection Opened ");
                cmd = new OleDbCommand(sql, cnn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cnn.Close();
                MessageBox.Show(" ExecuteNonQuery in OleDbConnection executed !! :");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! " + ex.ToString());
            }
        }

Open in new window


You can find sample database on attachment also.

AccessExample.pngASampleDatabase.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
David Johnson, CD
Top Expert 2016

Commented: 
SELECT [Asset Items].[Asset No]
FROM [Asset Items]
WHERE ((([Asset Items].[Serial No])="DF556-33446"));

Open in new window

ASampleDatabase-DWJ.accdb
Retired
Distinguished Expert 2017
Commented:
Hi Hakan;

The following code will do that for you.
        private void AccessReader_Click(object sender, RoutedEventArgs e)
        {
            string connetionString = null;
            OleDbConnection cnn;
            OleDbCommand cmd;
            string sql = null;

            // Holds the return value from the database
            string retValue = null;
            // Record to find in the database
            string findPK = "30075";

            connetionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=E:\SampleData\ASampleDatabase.accdb";
           
            // I modified your query
            sql = "Select [Serial No] from [Asset Items] Where [Asset No] = '" + findPK + "'" ;

            cnn = new OleDbConnection(connetionString);
            try
            {
                cnn.Open();
                MessageBox.Show("Connection Opened ");
                cmd = new OleDbCommand(sql, cnn);
                // Get the value from the database
                retValue = (string) cmd.ExecuteScalar();
                cmd.Dispose();
                cnn.Close();
                MessageBox.Show(" ExecuteNonQuery in OleDbConnection executed !! :");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! " + ex.ToString());
            }

            // YOU NEED TO DO SOMETHING WITH THE VALUE RETURNED FROM THE DB
        }

Open in new window

Hakan

Author

Commented:
Thank you for contributions :) Now i got the idea. Fernando it's worked! and David it's also useful to get reverse value for me :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial