Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.
Solved
Need help building and Access (mdb) table from an existing table in VB.NET 2010
Posted on 2012-12-21
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
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
[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
-
4
2
6 Comments
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
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
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.ExecuteRead
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
I am having a problem however at line 13
Dim rdrNozzles = cmdReadNozzles.ExecuteRead
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
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.
If not either put your database there with that name or change line 6 so it can find your database.
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
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
Featured Post
Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
- Shell Scripting
- Programming Languages-Other
- Scripting Languages
- Programming
- Notepad
- *AutoHotkey, *Scripts
Suggested Courses
Course of the Month6 days, 1 hour left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.