Question Multi-criteria lookup from table

[Midavalo]

Hi,

This is my first question here, hopefully I've put it in the correct place.

I'm trying to build a small app (for fun, at home) that looks up values based on two input values. I had thought I'd create a simple MS Access table and look up from that, but I just can't figure out how to connect to the Access table and return any values. I'm using Visual Studio 2013 and Access 2013.

Firstly, is an Access table the best place to store and lookup these values? I have previously pulled values from a my.settings file (in a different project), but there are no lookups so that was fairly easy to setup.

Secondly how do I perform the lookup and get the value returned?
There are 57 rows and 7 columns. First value I want to query is from the first column, second value determines which of the remaining 6 columns to select from (in the form of selection from 1-6), so using the table below, if I input 20000 in the first textbox and 4 in the second textbox it would return a value of 103.

Amount	V1	V2	V3	V4	V5	V6
10000	10	20	30	40	50	60
20000	100	101	102	103	104	105
30000	110	115	120	125	130	135
40000	200	400	600	800	1000	1200

Alternatively I could pivot it slightly, so would lookup 20000 from first column and 4 from second column would return 103 from third column.

Amount	Count	Value
10000	1	10
10000	2	20
10000	3	30
10000	4	40
10000	5	50
10000	6	60
20000	1	100
20000	2	101
20000	3	102
20000	4	103
20000	5	104
20000	6	105
30000	1	110
30000	2	115
30000	3	120
30000	4	125
30000	5	130
30000	6	135
40000	1	200
40000	2	400
40000	3	600
40000	4	800
40000	5	1000
40000	6	1200

Please let me know if there is any other info I need to supply.

Cheers,
Mike.

 

[jmcilhinney]

For a small amount of data, you could use any number of options but a database is fine and good practice for more complex projects. You would use ADO.NET to connect to and manipulate the database. Create an OleDbCommand containing the appropriate SQL code and then execute it with a data reader or data adapter. There's plenty of information around about ADO.NET in general and with Access in particular. Post back if you have specific issues.

 

[Midavalo]

OK thanks I'll have a look and see how I get on. ADO.Net is a new one for me, so wasn't part of my searches will try that.

Cheers,
Mike.

 

[Midavalo]

For a small amount of data, you could use any number of options but a database is fine and good practice for more complex projects. You would use ADO.NET to connect to and manipulate the database. Create an OleDbCommand containing the appropriate SQL code and then execute it with a data reader or data adapter. There's plenty of information around about ADO.NET in general and with Access in particular. Post back if you have specific issues.

Thanks jmcilhinney, with something extra to search on (ADO.Net) I was able to find some helpful pointers at https://msdn.microsoft.com/en-us/library/vstudio/dw70f090(v=vs.110).aspx which I was able to modify to work with my table and query Much appreciated.

Here's the code I copied from that site (pre-modification)

[COLOR=Blue]Option[/COLOR] [COLOR=Blue]Explicit[/COLOR] [COLOR=Blue]On[/COLOR] 
[COLOR=Blue]Option[/COLOR] Strict [COLOR=Blue]On[/COLOR] 

[COLOR=Blue]Imports[/COLOR] System
[COLOR=Blue]Imports[/COLOR] System.Data
[COLOR=Blue]Imports[/COLOR] System.Data.OleDb

[COLOR=Blue]Public[/COLOR] [COLOR=Blue]Class[/COLOR] Program
    [COLOR=Blue]Public[/COLOR] [COLOR=Blue]Shared[/COLOR] [COLOR=Blue]Sub[/COLOR] Main()

        [COLOR=Green]' The connection string assumes that the Access  [/COLOR]
        [COLOR=Green]' Northwind.mdb is located in the c:\Data folder. [/COLOR]
        [COLOR=Blue]Dim[/COLOR] connectionString [COLOR=Blue]As[/COLOR] [COLOR=Blue]String[/COLOR] = _
             [COLOR=#A31515]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="[/COLOR] _
            & [COLOR=#A31515]"c:\Data\Northwind.mdb;User Id=admin;Password=;"[/COLOR] 

        [COLOR=Green]' Provide the query string with a parameter placeholder. [/COLOR]
        [COLOR=Blue]Dim[/COLOR] queryString [COLOR=Blue]As[/COLOR] [COLOR=Blue]String[/COLOR] = _
            [COLOR=#A31515]"SELECT ProductID, UnitPrice, ProductName from Products "[/COLOR] _
            & [COLOR=#A31515]"WHERE UnitPrice > ? "[/COLOR] _
            & [COLOR=#A31515]"ORDER BY UnitPrice DESC;"[/COLOR] 

        [COLOR=Green]' Specify the parameter value. [/COLOR]
        [COLOR=Blue]Dim[/COLOR] paramValue [COLOR=Blue]As[/COLOR] [COLOR=Blue]Integer[/COLOR] = 5

        [COLOR=Green]' Create and open the connection in a using block. This [/COLOR]
        [COLOR=Green]' ensures that all resources will be closed and disposed [/COLOR]
        [COLOR=Green]' when the code exits. [/COLOR]
        [COLOR=Blue]Using[/COLOR] connection [COLOR=Blue]As[/COLOR] [COLOR=Blue]New[/COLOR] OleDbConnection(connectionString)

            [COLOR=Green]' Create the Command and Parameter objects. [/COLOR]
            [COLOR=Blue]Dim[/COLOR] command [COLOR=Blue]As[/COLOR] [COLOR=Blue]New[/COLOR] OleDbCommand(queryString, connection)
            command.Parameters.AddWithValue([COLOR=#A31515]"@pricePoint"[/COLOR], paramValue)

            [COLOR=Green]' Open the connection in a try/catch block.  [/COLOR]
            [COLOR=Green]' Create and execute the DataReader, writing the result [/COLOR]
            [COLOR=Green]' set to the console window. [/COLOR]
            [COLOR=Blue]Try[/COLOR]
                connection.Open()
                [COLOR=Blue]Dim[/COLOR] dataReader [COLOR=Blue]As[/COLOR] OleDbDataReader = _
                 command.ExecuteReader()
                [COLOR=Blue]Do[/COLOR] [COLOR=Blue]While[/COLOR] dataReader.Read()
                    Console.WriteLine( _
                        vbTab & [COLOR=#A31515]"{0}"[/COLOR] & vbTab & [COLOR=#A31515]"{1}"[/COLOR] & vbTab & [COLOR=#A31515]"{2}"[/COLOR], _
                     dataReader(0), dataReader(1), dataReader(2))
                [COLOR=Blue]Loop[/COLOR]
                dataReader.Close()

            [COLOR=Blue]Catch[/COLOR] ex [COLOR=Blue]As[/COLOR] Exception
                Console.WriteLine(ex.Message)
            [COLOR=Blue]End[/COLOR] [COLOR=Blue]Try[/COLOR]
            Console.ReadLine()
        [COLOR=Blue]End[/COLOR] [COLOR=Blue]Using[/COLOR] 
    [COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR] 
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Class[/COLOR]

Thanks again,
Mike.