Solved

Need help building and Access (mdb) table from an existing table in VB.NET 2010

Posted on 2012-12-21
6
1 solution
522 Views
Last Modified: 2012-12-26
I have an Access (MDB) database that contains a table named NOZZLES and need to create a new table caalled NOZZLECOMBO. All of this need to be done programatically in VB.NET 2010.
I can create the template for the NOZZLECOMBO table but need help getting the data in.
Basically I have a NOZZLES table that consists of various size flow nozzles (sometimes the same size appears mor than once but needs to be treated separately since its exact measurement may differ from the other nozzle of the same nominal size.

Ultimately my progarm will use the data in the NOZLECOMBO table to determine which nozzle combination to use for a specific task.
Here is the layout of the NOZZLES table

NozID      NozSize      LOLO      LO      HI      HIHI
Noz1      3      100      150      275      300
Noz2      4      200      260      600      700
Noz3      5      500      650      900      1000
Noz4      6      700      800      1100      1300
Noz5      6      700      800      1100      1300


And here is a possible layout of the NOZZLECOMBO TABLE that needs to be built:

Comb      Noz1      Noz2      Noz3      Noz4      Noz5      LOLO      LO      HI      HIHI
1      3                              100      150      275      300
2            4                        200      260      600      700
3                  5                  500      650      900      1000
4                        6            700      800      1100      1300
5                              6      700      800      1100      1300
6      3      4                        300      410      875      1000
7      3            5                  600      800      1175      1300
8      3                  6            800      950      1375      1600
9      3                        6      800      950      1375      1600
10            4      5                  700      910      1500      1700
11            4            6            1100      1360      2250      2600
12            4                  6      900      1060      1700      2000
13                  5      6            1200      1450      2000      2300
14                  5            6      1200      1450      2000      2300
15                        6      6      1400      1600      2200      2600
16      3      4      5                  800      1060      1775      2000
17      3            5      6            1300      1600      2275      2600
18      3      4            6            1000      1210      1975      2300
19      3            5      6            1300      1600      2275      2600
20            4      5      6            1400      1710      2600      3000
21                  5      6      6      1900      2250      3100      3600
22      3            5      6      6      2000      2400      3375      3900
23      3      4      5      6      6      2200      2660      3975      4600


Some combinations may seem to be duplicate but as I said the actual size of a nozzle may differ from one of the same nominal size (there may be two 6 inch nozzles but the first may actually be 5.95 and the other may be 6.1 and that will change the flow capacity.

My program will always try to use numbers between LO and HI but I will allow it to got down as low as LOWLOW and as high as HIHI

So what I need is help building the NOZLECOMBO table - making sure all of the possible combinations exist and end up in ascending order based on the LOLO data for each combination.
One final point: these may be as many as 12 different nozzles (depending on the application) but I think if I can get help with 5 nozzles then 12 will jest be more of the same.

Thanks, Charlie
0
Comment
Question by:charlieb01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 4
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Beartlaoi earned 500 total points
ID: 38714184
I came up with a method that treats each nozzle as a bit within an integer, then to get all combinations simply increment.
This automatically handles any number of nozzles up to 31

Imports System.Data.OleDb

Module Module1
    Sub Main()
        Dim sPath = System.IO.Path.GetDirectoryName(Process.GetCurrentProcess.MainModule.FileName)
        Dim sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & "\Nozzles.accdb;Persist Security Info=False;"
        Dim Conn = New OleDbConnection(sConnect)
        Conn.Open()

        Dim lNozzles = New List(Of Nozzle)

        Dim cmdReadNozzles = New OleDbCommand("SELECT NozID, NozSize, LOLO, LO, HI, HIHI FROM Nozzles", Conn)
        Dim rdrNozzles = cmdReadNozzles.ExecuteReader()
        While rdrNozzles.Read()
            Dim Noz = New Nozzle()
            Noz.NozID = rdrNozzles.GetString(0)
            Noz.NozSize = rdrNozzles.GetInt32(1)
            Noz.LOLO = rdrNozzles.GetInt32(2)
            Noz.LO = rdrNozzles.GetInt32(3)
            Noz.HI = rdrNozzles.GetInt32(4)
            Noz.HIHI = rdrNozzles.GetInt32(5)
            lNozzles.Add(Noz)
        End While
        rdrNozzles.Close()
        cmdReadNozzles.Dispose()

        Dim NozzleArray = lNozzles.ToArray()
        Dim NumNozzles As Integer = lNozzles.Count
        Dim lCombos = New List(Of NozzCombo)
        ' To get every combination, just make each nozzle a bit, then count from 0 to 2^NumNozzles-1
        Dim LastNozzle As Integer = 2L ^ NumNozzles - 1L
        For iCombo As Integer = 1 To LastNozzle
            Dim Combo = New NozzCombo
            For iNozz As Integer = 0 To NumNozzles - 1
                If (iCombo And (2L ^ iNozz)) > 0 Then
                    Combo.Add(NozzleArray(iNozz))
                End If
            Next
            lCombos.Add(Combo)
        Next

        lCombos.Sort()

        Try
            Dim cmdDropTable = New OleDbCommand("DROP TABLE NOZZLECOMBO", Conn)
            cmdDropTable.ExecuteNonQuery()
            cmdDropTable.Dispose()
        Catch
        End Try

        Dim SB = New Text.StringBuilder()
        SB.Append("CREATE TABLE NOZZLECOMBO(Comb INTEGER")
        For iNozz As Integer = 0 To NumNozzles - 1
            Dim NozID = NozzleArray(iNozz).NozID
            SB.Append(", ")
            SB.Append(NozID)
            SB.Append(" INTEGER")
        Next
        SB.Append(", LOLO INTEGER, LO INTEGER, HI INTEGER, HIHI INTEGER)")
        Dim cmdCreateTable = New OleDbCommand(SB.ToString(), Conn)
        cmdCreateTable.ExecuteNonQuery()
        cmdCreateTable.Dispose()

        Dim SB2 = New Text.StringBuilder()
        Dim iComb As Integer = 1
        For Each Cbo In lCombos
            SB.Clear()
            SB2.Clear()
            SB.Append("INSERT INTO NOZZLECOMBO(Comb, ")
            SB2.Append(iComb.ToString())
            SB2.Append(", ")
            iComb = iComb + 1
            For Each Noz In Cbo.lNozzles
                SB.Append(Noz.NozID)
                SB.Append(", ")
                SB2.Append(Noz.NozSize.ToString())
                SB2.Append(", ")
            Next
            SB.Append("LOLO, LO, HI, HIHI) VALUES(")
            SB2.Append(Cbo.LOLO.ToString())
            SB2.Append(", ")
            SB2.Append(Cbo.LO.ToString())
            SB2.Append(", ")
            SB2.Append(Cbo.HI.ToString())
            SB2.Append(", ")
            SB2.Append(Cbo.HIHI.ToString())
            SB2.Append(")")
            SB.Append(SB2.ToString())
            Dim cmdInsert = New OleDbCommand(SB.ToString(), Conn)
            cmdInsert.ExecuteNonQuery()
            cmdInsert.Dispose()
        Next

    End Sub

    Class Nozzle
        Public NozID As String
        Public NozSize, LOLO, LO, HI, HIHI As Integer
    End Class

    Class NozzCombo
        Implements IComparable(Of NozzCombo)

        Public lNozzles = New List(Of Nozzle)
        Public LOLO, LO, HI, HIHI As Integer
        Public Sub Add(Noz As Nozzle)
            lNozzles.Add(Noz)
            LOLO = LOLO + Noz.LOLO
            LO = LO + Noz.LO
            HI = HI + Noz.HI
            HIHI = HIHI = Noz.HIHI
        End Sub

        Public Function CompareTo(other As NozzCombo) As Integer Implements System.IComparable(Of NozzCombo).CompareTo
            If LOLO = other.LOLO Then
                Return 0
            ElseIf LOLO < other.LOLO Then
                Return -1
            Else
                Return 1
            End If
        End Function
    End Class
End Module

Open in new window

0
 

Author Comment

by:charlieb01
ID: 38716574
This appears to be a very complete program and a lot of effort

I am having a problem however at line 13

Dim rdrNozzles = cmdReadNozzles.ExecuteReader()

The error:
ExecuteReader requires an open and available Connection. The connection's current state is closed.

I don't know what to do with this.

Any help
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 38717493
Do you have a file named Nozzles.accdb in the same folder as your exe?
If not either put your database there with that name or change line 6 so it can find your database.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Download the report
 

Author Comment

by:charlieb01
ID: 38717529
My database is an older version *.mdb (for compatability reasons) and I missed a line when changing that part of the code.
Also, my real airflow numbers are Double not Integer so I had to make a few changes to accomodate that but OTHERWISE:

This was the BEST assistance I have ever received since joining experts-exchange. I had no idea how I was going to do this and you came up with the perfect program. Fot that, I am extremly grateful. You are truly a master.

Thanks,
Charlie
0
 

Author Closing Comment

by:charlieb01
ID: 38717530
Fantastic Solution
0
 

Author Comment

by:charlieb01
ID: 38721963
Just a follow-up,
On Line 111, the second = sign should be a + sign
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

See Our New Courses
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an Add-to Combo Box
Article by: Helen
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Delivering a Word Template for Use in Merging Data from Access
Article by: Helen
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Introduction to C++: Functions - Passing by Value vs Reference
Video by: Ian
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
Bar Graphs in an Access Query
Video by: crystal
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Advertise Here

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here