Question Multi-criteria lookup from table

Midavalo

New member
Joined
May 26, 2015
Messages
3
Location
New Zealand
Programming Experience
Beginner
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
10000102030405060
20000100101102103104105
30000110115120125130135
4000020040060080010001200


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
100004
40
10000
5
50
10000
6
60
20000
1
100
20000
2
101
200003
102
20000
4
103
200005
104
20000
6
105
30000
1
110
30000
2
115
300003
120
30000
4
125
30000
5
130
30000
6
135
40000
1
200
400002
400
40000
3
600
400004
800
40000
5
1000
40000
6
1200

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

Cheers,
Mike.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,726
Location
Sydney, Australia
Programming Experience
10+
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

New member
Joined
May 26, 2015
Messages
3
Location
New Zealand
Programming Experience
Beginner
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)
VB.NET:
[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.
 
Top Bottom