I have a program that creates an array of part numbers. I also have a database that holds part numbers and all associated information I need for other calculations. How can I search an MSAccess database table against all of the part numbers in the array?
The Array will never have more than 100 items in it, the table has over 7000 records to search from. I've posted my nonworking code hoping someone can point me in the tight direction.
Code:
Dim rs2 As New ADODB.Recordset
Dim Test1(7000) As String
Dim Test2(7000) As String
Dim X As Integer
'CBOpHwPn(CBOpHwCount) is the Array with multible Part Numbers
'CBOpHwCount is the number in the array
X = 1
'rs2.Open("select * from tblMain where OrderNumber = '" & CBOpHwPn(CBOpHwCount) & "'", strConn, 2, 2)
rs2.Open("select * from [tblMain]", strConn2)
If Not rs2.EOF Then
If CBDrHDPLTPn(X) > "" And Mid(rs2.Fields("Part_Number").Value, 1, 11) = CBDrHDPLTPn(X) Then
'X = X + 1
If Not rs2.EOF Then
Test1(X) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(X) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
' 'Until CBDrHDPLTPn(X) has completed its loop
X = X + 1
End If
End If
End If
rs2.Close()
The first thing you should do is ditch ADO and start using ADO.NET. If you're using VB.NET then there's really one one justifiable reason for using ADO: you've upgraded a VB6 app that already uses ADO to VB.NET. If you're writing new code then it should be ADO.NET from the start. You can follow the Database FAQ link in my signature for some ADO.NET resources. There are also other helpful links in my signature to walkthroughs and videos.
The best way to do this would be to create a query that looks for values in a list, e.g.
SQL Code:
SELECT *
FROM tblMain
WHERE OrderNumber IN ('Order1', 'Order2', 'Order3')
Now, you could build a string containing that list and insert it into the query using string concatenation, e.g.
vb.net Code:
Dim orderNumbers As String() = {"Order1", "Order2", "Order3"}
Dim orderNumberList As String = String.Join("', '", orderNumbers)
Dim query As String = String.Format("SELECT * FROM tblMain WHERE OrderNumber IN ('{0}')", orderNumberList)
That carries with it all the issues associated with SQL statements and string concatenation though. A better way is to use parameters that you add dynamically. If you follow the CodeBank link in my signature you'll find a thread of mine dedicated to building IN queries with parameters.
The first thing you should do is ditch ADO and start using ADO.NET
A. What jmc said. It takes about a minute to absorb the new sytax, but you will never regret moving to .NET once you give it a minute.
B. Since it looks like you are working entirely within vb6, here is the vb6 version of what jmc proposed (ok, only somewhat- I am not using Parameters for this. In fact, I have been "away" from vb6 long enough that I realized how rusty I am with some of these basic functions.
C. This entire process would be much more elegant in .NET, as you can see from jmc's proposed solution.
This is a little crude, but it works (I used some local tables and such I had laying around in MS Access, so the table and field names are different, but you get the idea):
Code:
'A String variable into which you will build part of your
'WHERE clause using the SQL In Operator:
Dim strIN As String
'An iterator variable to loop through your array:
Dim str As Variant
'Use those two to concatenate your parameters from your array:
For Each str In strArray
strIN = strIN & ", " & str
Next
'Nip the first commo from the front of the
'clumsy concatenated string (there is probably a better way to
'handle this):
strIN = Replace(strIN, ", ", "", 1, 1)
'A thousand deaths for concatenating into the WHERE clause, but here goes:
rs.Open "SELECT CountyNameID, CountyName " & _
"FROM lkp_tblCountyName " & _
"WHERE COuntyNameID In(" & strIN & ")", cn
Still working on this problem. I now have a search string but even if I don't use the concatenated strIN as soon as it starts it EOF is TRUE. so I never see the rest of the code, not sure if the filling of the Test2 and Test2 Arrays will work.
Code:
Dim rs2 As New ADODB.Recordset
'A String variable into which you will build part of your
'WHERE clause using the SQL In Operator:
Dim strIN As String
Dim Test1() As String
Dim Test2() As String
Dim X As Integer
strIN = ""
'Use those two to concatenate your parameters from your array:
For X = 1 To CBDrHDPLTCount
strIN = strIN & "," & CBDrHDPLTPn(X)
Next
'Nip the first commo from the front of the concatenated string
'strIN = Replace(strIN, ",", "", 1, 1)
strIN = "600035-1125"
X = 1
rs2.Open("SELECT Part_Number, Part_Desc " & _
"FROM tblMain " & _
"WHERE Part_Number In(" & strIN & ")", strConn2)
While Not rs2.EOF
If CBDrHDPLTPn(X) > "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then
Test1(X) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(X) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
X = X + 1
End If
End While
rs2.Close(
The sample I provided was based on an integer ID. Note below, the addition of single quotes around each array element as it is added to the concatenated string. You may have to play with this, but the "In" function will expect a string to look like THIS after is has been assembled:
Ok the strIN looks ok, no comma in beginning. But I never find a match in the While loop.
If I look only for 1 part number I do not find it.
But if I search for the first record in the table I find it and get the 2 pieces of data, but the loop exceeds the (7000) limit yet I only have 3354 Records.
I'm at a loss. and yes this was a converted VB6 project.
I'm open to any and all suggestions.
Thanks
Code:
Dim rs2 As New ADODB.Recordset
'A String variable into which you will build part of your
'WHERE clause using the SQL In Operator:
Dim strIN As String
Dim Test1(7000) As String
Dim Test2(7000) As String
Dim X As Integer
strIN = ""
For X = 1 To CBDrHDPLTCount
strIN = strIN & ",'" & CBDrHDPLTPn(X) & "'"
Next
strIN = Replace(strIN, ",", "", 1, 1)
X = 1
rs2.Open("SELECT Part_Number, Part_Desc " & _
"FROM tblMain " & _
"WHERE Part_Number In(" & strIN & ")", strConn2)
While Not rs2.EOF
If CBDrHDPLTPn(X) > "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then
Test1(X) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(X) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
End If
X = X + 1
End While
rs2.Close()
Yes this a VB.net project that was converted to VB.net. I did not do the conversion.
I do not know how to do the code for this using the newer VB.net, But I will accept any help in doing it correctly.
I could post some code here that would work for what you are trying to do, but you may benefit more from following jmc's suggestion first, and THEN return with any questions you may have. ANy code I put up ewould be next to meaningless until you take a gander at the basics.
The Data Access in ADO.NET takes a little getting used to, as does some of the minor syntactical changes. However, once you become even a LITTLE familiar with it, it really is much easier to work with.
The da stuff will seem extra-verbose at first, but keep doing it, you'll see.
Additionally, the code JMC posted is actually most of what you need. Go visit the FAQ section of this forum, and/or follow the link jmc suggests, and then post back with any questions you have.
I have been trying to fix this for 3 days and I just can not figure it out. I have tried muliple queries as you can see from the commented code. On all but the last try the query does not error, when looking at the line "While Not rs2.EOF" it shows as true. So I am thinging it's at the End of File.
I have checked and rechecked the database name, tablename, field names all are OK. I have even copied them to make sure nothing was missing.
I have included the database, I will tell you some of the part numbers in the query using "strIN" are not in the database. But the individual number are at the top of the table.
Here is the error I get when trying the code from jmcilhinney I modified.
any help here would be Greatly Appreciated
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'OrderNumber IN (''600035-1125','086446-2920','400025-0006','700079-0005','700000-0632','056888-0002','056889-0001','600016-0017','080352-1210','600004-0006','300644-0001','800030-0002'')'.
Code:
Sub Data_In()
Dim rs2 As New ADODB.Recordset
Dim strIN As String
Dim Test1(7000) As String
Dim Test2(7000) As String
Dim X As Integer
Dim query As String
strIN = ""
For X = 1 To CBDrHDPLTCount
strIN = strIN & ",'" & CBDrHDPLTPn(X) & "'"
Next
strIN = Replace(strIN, ",", "", 1, 1)
query = String.Format("SELECT * FROM tblMain WHERE OrderNumber IN ('{0}')", strIN)
X = 1
rs2.Open(query, strConn2)
'rs2.Open("SELECT Part_Number, Part_Desc FROM tblMain WHERE Part_Number = 100000-0773", strConn2)
'rs2.Open("SELECT * FROM [tblMain] WHERE Part_Number =100000-0773", strConn2)
'Part_Number In(" & strIN & ")"
'rs2.Open("SELECT * FROM [tblMain] WHERE Part_Number IN (100000-0773)", strConn2)
While Not rs2.EOF
If CBDrHDPLTPn(X) <> "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then
Test1(X) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(X) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
End If
X = X + 1
End While
rs2.Close()
End Sub
I found one problem I missed I had ordernumber instead of Part_Number in the query.
Now I get this error.
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Part_Number IN (''600035-1125','086446-2920','400025-0006','700079-0005','700000-0632','056888-0002','056889-0001','600016-0017','080352-1210','600004-0006','300644-0001','800030-0002'')'.
THe error message is showing you (albiet indirectly) where a potential problem is with your SQL; you are ending up with some extra single quotes, as shown in red below:
Also, I am note certain how ADODB would handle THIS line, but it IS the source of the problem above:
Code:
query = String.Format("SELECT * FROM tblMain WHERE OrderNumber IN ('{0}')", strIN)
Skip the StringFormat (for the moment anyway) and instead, make certain your SELECT statement is being put together correctly. Since We have already assembled the Single-quote enclosed, comma-delimited list in the looped structure, THIS should provide what you need:
Code:
query = "SELECT * FROM tblMain WHERE OrderNumber IN (" & strIN & ")"
I'm going to spend a minute throwing this together in a project, and play with both the ADODB and a 100% ADO.NET version, and I will post back. In the meantime, play with the above.
I can do my best to make something work, and walk you through what I did. However, it would be very handy to know where the array of partnumbers to search for is coming from. To guess from your variable name, is it a ComboBox?
If you could possibly explain what you are trying to acheive, it will be easier to help find a solution. To this point, all we know is that you need to quesry an access database based on the elements of an array (which seems like an odd approach . . .!).
Any additional info you could provide would be helpful.
Ok
First off the last change to eliminate the extra ticks worked, so the sql part is OK.
Now what I'm doing or trying to do: This is a big program with 30 child Forms in a Midi Main form, 18 modules of engineering code to build products in a manufacturing plant.
I did not start this project it was originally in Fortran and everthing was originally built to 64K size limit for DOS. Then it was converted to COBOL with the same 64 K Limit.
I converted it to VB 6.0 then another person got hold of it after I was done with my part and converted it to VB.net.
Now I was asked to finish it up.
So I have little or no experience with VB.net.
Ok so far?
Now this program has extensive use of CSV files to hold all the data needed for the engineering math and to build the product. (64K Limit)
So one of the main items they want is to have a Bill of Material. Origianally it woud use arrays to gather all of the data and build a BOM. The problem lies in the original 64K limit many, many of the csv files contain the same information.
To update these csv files is murder.
So get around this I am going to hard code the part numbers in the vb.net code, and rather than do the search and find it in an array from a csv file.
Now, If I do this then I can take this one Array of Part Numbers and do a search on one Database table and bring the information into the program.
So as it stands I can create the Query with the Part Numbers. But I need to run this against the database and bring the rest of the information back into an Array to let the program finish up the code that is already done.
I know this is stupid but I am only the cleanup person. and with the amount of changes engineering is making to the parts the CSV file will kill them.
So here is the code, and I posted the Database earlier to use.
I know the code in the While not EOF is not correct I just do not know how to compare and move the data I need.
Thanks so Much
Code:
Dim rs2 As New ADODB.Recordset
Dim strIN As String
Dim Test1(7000) As String
Dim Test2(7000) As String
Dim X As Integer
Dim Y As Integer
Dim query As String
Dim Bill As String = ("10001-0007,10001-0001,10001-0003,600035-1125") 'these are good numbers
Dim temp As String
strIN = ""
For X = 1 To CBDrHDPLTCount
strIN = strIN & ",'" & CBDrHDPLTPn(X) & "'"
Next
strIN = Replace(strIN, ",", "", 1, 1)
query = String.Format("SELECT * FROM tblMain WHERE Part_Number IN ({0})", strIN)
X = 1
rs2.Open(query, strConn2)
'*********************Different versions I tried*************************
'rs2.Open("SELECT Part_Number, Part_Desc FROM tblMain WHERE Part_Number = 100000-0773", strConn2)
'rs2.Open("SELECT * FROM [tblMain] WHERE Part_Number =100000-0773", strConn2)
'Part_Number In(" & strIN & ")"
'rs2.Open("SELECT * FROM [tblMain] WHERE Part_Number = 100000-0773", strConn2)
'rs2.Open("SELECT * FROM [tblMain]", strConn2)
For Y = 1 To CBDrHDPLTCount
While Not rs2.EOF
temp = rs2.Fields("Part_Number").Value ' ********** this never seem to change
If CBDrHDPLTPn(X) <> "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then
Test1(X) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(X) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
End If
End While
X = X + 1
Next Y
rs2.Close()
I did this before I read your new post, so this may or may not be usefule to you.
YOU check this out, while I check out your latest post, and see if we can't kill two birds with one stone:
A. Solve your semi-pressing problem!
B. Begin familiarizing you with thei ADO.NET stuff . . .
I put together the fully ADO.NET version of the code. Bear in mind that while I am getting fairly adept with some of this, JMC is actually the one to pay attention to in the bigger picture. Some of what I do below, he would advise taking a step further (e.g. The whole String Concatenation in SQL. I did NOT parameterize this, but in the long run, that's is how it should be done). THis is very basic, but it both "gets the job done", and it may be a useful introduction . . ..
However, it SOUNDS like this may end up being more of a one-off utility that will be used to facillitate a larger upgrade?
Anyway, check this out. Some of it may not make a lot of senseat first, but explore the links in JMC's signature, and use MSDN and yourlocal help.
I willl take a look at your upload, and see what can be done. Let ME know if the code, as posted, even remotely solves you problem:
This is basically a FORM, with two methods:
A. I just used the form Load event to execute a basic version of your query.
B. The Function FOundParts is the workhorse. You pass it an array, and it will return an ADO.NET DataTable populated with the results of your query.
C. It should be a very short step to output the contecnt of the populated dataTable to .csv, xml, what have you.
Code:
'The Imports statements allow you to avoid using the long, fully-qualified
'names for object from these namespaces. They are usually included in the
'default Project-level Imports statement, but I use them at the module-level anyway
Imports System.Data
'MS Access requires the OleDb .NET Provider
Imports System.Data.OleDb
Public Class Form1
Private Function FoundParts(ByVal Parts() As String) As DataTable
'In ADO.NET, a DataTable is an "in-memory" representation of
'a database table (or query return). If you are accustomed to ADODB, the
'best approximation would be the "disconnected" recordset. However,
'Whereas in ADODB the "Disconnected" recordset (for me, anyway) was an exception rather than the rule,
'ADO.NET is designed to utilize more of a disconnected architecture. Hence,
'the dataTable. Takes some getting used to, but it is actually
'a lot more flexible:
Dim dt As DataTable
'STRINGS are handled a little differently in .NET. jmc suggested the use
'of the String.Format fucntion, and I am not yet so familiar with that, so I
'am not going to try to provide advice on it (I really need to learn it though).
'Here, I am using a StringBuilder object to handle a string concatenation
'of unknown size. Look up STRINGS and STRINGBUILDER in your .NET documentaiton
'or on MSDN. in .NET, strings are OBJECTS, and do not behave the way you are
'accustomed to in vb6.
'Initialize an empty stringbuilder object (this should return a zero-length string
'if the array is empty):
Dim sb As New System.Text.StringBuilder("")
'Iterate through the Array elements and build the contents of your IN list. Skip this
'if there are NO elements in the Array, and the Zero-Length String will ensure that NO
'records are returned from the query (One might also want to protect against a Parameter
'that is passed as NOTHING, but I didn't in this case):
If Parts.Count > 0 Then
For Each PartNo As String In Parts
sb.Append(", '" & PartNo & "'")
Next
sb.Remove(0, 1)
End If
Dim strSQL As String = "" & _
"SELECT Part_Number, Part_Desc " & _
"FROM tblMain " & _
"WHERE Part_Number In(" & sb.ToString & ")"
'The USING statement creates an instance which goes out of scope
'at the end of the Using Block. The Using structure takes care of
'object disposal and such.
'The COnnection String is stored in the project settings file. Double CLick on the "MyProject" icon in the
'Solution explorer, and go to the "Settings" tab . . . You'll need to set YOUR project up with the
'proper connection string to your database. Select Setting type (connection String) in the Dropdown, name is,
'and then click in the Rightmost column. You should get the Connection String wizer, or you can type it in.
Using cn As New OleDbConnection(My.Settings.cxnPartsDb)
Using cmd As New OleDbCommand(strSQL, cn)
dt = New DataTable
cn.Open()
'The Try/Catch block is the basic exception-handling tool in .NET.
'This is MUCH cooler than vb6 Error handling via "On Error/GoTo" bs . . .
Try
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
dt.Load(dr)
Catch ex As Exception
'Something horrible has happened; Throw up a message box to tell the user:
MsgBox(ex.Message)
End Try
'Right HERE, .NET starts packing it in for the Command (and then the Connection) objects.
End Using
End Using
'Return a populated DAtaTable to the calling procedure:
Return dt
End Function
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Since I have NO IDEA what the source of your array is, I am just manually constructing one
'to make sure this works . . .
Dim str() As String = {"100000-0773", "100001-0001", "100001-0003", "000000"}
'Declare a local DataTable variable (you could just as easily declare a private
'memebr variable at the module level, but for this, I just used a local), and then
'Use the Function to return a new instance, populated based on the Array as a parameter:
Dim dt As DataTable = Me.FoundParts(str)
'Since I have no idea what you are DOING with the data once you retreive it,
'I'm just gonna read it out into the output window of the vs2008 IDE:
'Delare a generic DataTableREader, and read the contents
'of your newly populated DataTable Variable:
Dim dr As New DataTableReader(dt)
While dr.Read
Dim strPartNo As String = "Part No: " & dr("Part_Number")
Dim strPartDesc As String = "Desc: " & dr("Part_Desc")
'THis will write the output to the "Output" window of your IDE:
Console.WriteLine(strPartNo & " / " & strPartDesc)
End While
End Sub
End Class
I need to get some coffee before I dig in here, and this is stepping back into your original code, and not related to the code I just poasted. However, I think your Loop is never advancing through the recordset:
Try some variations on THIS (see RED items for changes):
Code:
'I think there is a more efficient, and less convoluted way to do this. I will look into it when I get
'back from getting some coffee . . . That said, experiment with the changes
'I made with the Loop structure, especially at then end (see comment below).
For Y = 1 To CBDrHDPLTCount
If Not rs2.EOF Then
Do Until rs2.EOF
temp = rs2.Fields("Part_Number").Value ' ********** this never seem to change
If CBDrHDPLTPn(X) <> "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then
Test1(X) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(X) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
End If
'SOME omcbination of changes to these
X = X + 1
rs2.MoveNext
Loop
End If
rs2.MoveFirst
'(?) Note sure. you may have to re-open the recordset at this point, or change it to a Dynamic recordset when
'you first open it in order to step back to the beginning . . .
Next Y
ALso check out the manner in which I iterate through the returned DataTable object in the more ".NET" version in my earlier post.
I'll try to come up with both methods when I return . . .
Hope this is helpful . . .
Last edited by RunsWithScissors; Dec 5th, 2009 at 04:43 PM.
Runswithscissors I was doing this from a module not a form is that a problem???
No. Any code I am posting is an EXAMPLE. The module is probably a better idea. Even BETTER would be a class. However, all of this is a good example of why it is helpful to provide as much info as you can regarding the problem you are trying to solve!!
Check my last poat, related to your original code, and now that I have a better idea what you are trying to do, I will try to come up with something that does what you need.
This is interesting . . .I appreciate the opportunity to help, though it can get convoluted sometimes!!
Be back in a few minuttes, after some much needed caffination!
Is it safe to assume that one goal of the project would be to get the data OUT of the .csv files, eliminate diuplicates, and then consolidate into an Access Database? Hardcoding the part numbers into the vb code seems like a gauranteed way to cause pain down the road . . .
Tell me if I would be on the wrong track to suggest we read all the .csv data (duplicates and all) into a table, then parse out duplicate data, and then consolidate into a final table?
OK. Ignore my very last post. I think I get it now. You need to build the arrays Test1(7000) and Test2(7000) in order to pass them on to the rest of the program, which is already created, in vb6, now ported to vb.NET. So the rest of the program is expecting these arrays. .
HOT DOG Hot DOG It's working here are the changes I made. I know this is Mickey Mouse programming but until I can do better this is GREAT.
Runswithscissors Thanks for your help. and to everyone else.
Thank you so much.
Code:
Dim rs2 As New ADODB.Recordset
Dim strIN As String
Dim Test1(7000) As String
Dim Test2(7000) As String
Dim X As Integer
Dim Y As Integer
Dim query As String
Dim Bill As String = ("10001-0007,10001-0001,10001-0003,600035-1125") 'these are good numbers
Dim temp As String
strIN = ""
For X = 1 To CBDrHDPLTCount
strIN = strIN & ",'" & CBDrHDPLTPn(X) & "'"
Next
strIN = Replace(strIN, ",", "", 1, 1)
query = String.Format("SELECT * FROM tblMain WHERE Part_Number IN ({0})", strIN)
X = 0
Y = 1
rs2.Open(query, strConn2)
'*********************Different versions I tried*************************
'rs2.Open("SELECT Part_Number, Part_Desc FROM tblMain WHERE Part_Number = 100000-0773", strConn2)
'rs2.Open("SELECT * FROM [tblMain] WHERE Part_Number =100000-0773", strConn2)
'Part_Number In(" & strIN & ")"
'rs2.Open("SELECT * FROM [tblMain] WHERE Part_Number = 100000-0773", strConn2)
'rs2.Open("SELECT * FROM [tblMain]", strConn2)
For Y = 1 To CBDrHDPLTCount
X = X + 1
If Not rs2.EOF Then
Do Until rs2.EOF
temp = rs2.Fields("Part_Number").Value ' ********** this never seem to change
If CBDrHDPLTPn(X) <> "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then
Test1(Y) = rs2.Fields("Part_Number").Value 'Part number from Database
Test2(Y) = rs2.Fields("Part_Desc").Value 'First of the items to pull from the data base
'Y = Y + 1
X = X + 1
Exit Do
End If
rs2.MoveNext()
Loop
End If
rs2.MoveFirst()
Next
If Y > CBDrHDPLTCount Then
rs2.Close()
End If
RunsWithScissors
I missed one of your posted but that is exactly correct. There is more than the part Number and Part desciption to bring into the array but I only needed 2 items to test with.
I have been fighting this for days because I do not do much Database work. Heck I don't usually do VB I'm an automation programmer Allen Bradley, Mitsubshi, and Beckhoff. But it pays for my train collection.
Thanks again so much
If you do indeed find a cleaner way I'd be glad to give it shot.
Just for giggles, and if it isn't too much trouble, I would LOVE to know if the code below works for what you are doing. It is not as elegant as it could be, but it may simplify what you are trying to do (or NOT- who knows? ;-) ).
If you are able, try running it, and let me know if I was on ther right track here. I am self-taught at all this, and it would be a giant victory to learn that I got it right!!
Nice job. Hope it contniues to work, and btw, welcome to .NET land!
I moved the relevant code to a MODULE. Uses the same FUnction-returns-dataTable to retreive the
Part info from the database, then runs through a modified iteration to populate your arrays Test1 and Test2:
Code:
'The Imports statements allow you to avoid using the long, fully-qualified
'names for object from these namespaces. They are usually included in the
'default Project-level Imports statement, but I use them at the module-level anyway
Imports System.Data
'MS Access requires the OleDb .NET Provider
Imports System.Data.OleDb
Module modFindParts
'Call THIS from wherever it is you are calling your current Sub:
Public Sub LoadMyPartsArrays(ByVal SomeArrayOfPartNumbers() As String)
Dim Test1(7000) As String
Dim Test2(7000) As String
'Use the function (added to this module now . . .) the retrieve all of the parts
'identified in the current array:
Dim dt As DataTable = FoundParts(SomeArrayOfPartNumbers)
'Unless I missed something, there is no need for both an X and a Y
'loop counter:
Dim X As Integer = 1
'Loop through the rows in the DataTable:
For Each Row As DataRow In dt.Rows
'Add the appropriate element to Each Array:
Test1(X) = Row("Part_Number")
Test2(X) = Row("Part_Desc")
#If DEBUG Then
Console.WriteLine(Test1(X) & " : " & Test2(X))
#End If
'Increment your element counter:
X = X + 1
Next
End Sub
Private Function FoundParts(ByVal Parts() As String) As DataTable
'In ADO.NET, a DataTable is an "in-memory" representation of
'a database table (or query return). If you are accustomed to ADODB, the
'best approximation would be the "disconnected" recordset. However,
'Whereas in ADODB the "Disconnected" recordset (for me, anyway) was an exception rather than the rule,
'ADO.NET is designed to utilize more of a disconnected architecture. Hence,
'the dataTable. Takes some getting used to, but it is actually
'a lot more flexible:
Dim dt As DataTable
'STRINGS are handled a little differently in .NET. jmc suggested the use
'of the String.Format fucntion, and I am not yet so familiar with that, so I
'am not going to try to provide advice on it (I really need to learn it though).
'Here, I am using a StringBuilder object to handle a string concatenation
'of unknown size. Look up STRINGS and STRINGBUILDER in your .NET documentaiton
'or on MSDN. in .NET, strings are OBJECTS, and do not behave the way you are
'accustomed to in vb6.
'Initialize an empty stringbuilder object (this should return a zero-length string
'if the array is empty):
Dim sb As New System.Text.StringBuilder("")
'Iterate through the Array elements and build the contents of your IN list. Skip this
'if there are NO elements in the Array, and the Zero-Length String will ensure that NO
'records are returned from the query (One might also want to protect against a Parameter
'that is passed as NOTHING, but I didn't in this case):
If Parts.Count > 0 Then
For Each PartNo As String In Parts
sb.Append(", '" & PartNo & "'")
Next
sb.Remove(0, 1)
End If
Dim strSQL As String = "" & _
"SELECT Part_Number, Part_Desc " & _
"FROM tblMain " & _
"WHERE Part_Number In(" & sb.ToString & ")"
'The USING statement creates an instance which goes out of scope
'at the end of the Using Block. The Using structure takes care of
'object disposal and such.
'The COnnection String is stored in the project settings file. Double CLick on the "MyProject" icon in the
'Solution explorer, and go to the "Settings" tab . . . You'll need to set YOUR project up with the
'proper connection string to your database. Select Setting type (connection String) in the Dropdown, name is,
'and then click in the Rightmost column. You should get the Connection String wizer, or you can type it in.
Using cn As New OleDbConnection(My.Settings.cxnPartsDb)
Using cmd As New OleDbCommand(strSQL, cn)
dt = New DataTable
cn.Open()
'The Try/Catch block is the basic exception-handling tool in .NET.
'This is MUCH cooler than vb6 Error handling via "On Error/GoTo" bs . . .
Try
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
dt.Load(dr)
Catch ex As Exception
'Something horrible has happened; Throw up a message box to tell the user:
MsgBox(ex.Message)
End Try
'Right HERE, .NET starts packing it in for the Command (and then the Connection) objects.
End Using
End Using
'Return a populated DAtaTable to the calling procedure:
Return dt
End Function
End Module
Last edited by RunsWithScissors; Dec 5th, 2009 at 06:05 PM.
In the code in my last post, you need only add feilds to the query string, and they will be returned into the data table. You can then access the infoo from the data table in the same manner as I retrieved the Part_Number and Part_Desc.
re:
Heck I don't usually do VB I'm an automation programmer Allen Bradley, Mitsubshi, and Beckhoff. But it pays for my train collection.
Nice!
I don't get paid for any programming (although I SHOULD, given the amount I have to do at my work-alas, it is not part of my job there!).
For some it Trains, and for others, it is Gibson Les Pauls and Marshal Amplifiers!
[RESOLVED] Help with Database to a Program Array
Reply With Quote
Originally Posted by RunsWithScissors
