[RESOLVED] Help with Database to a Program Array

[Alfarata]

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'')'.

[RunsWithScissors]

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:

(' '600035-1125','086446-2920','400025-0006','700079-0005','700000-0632','056888-0002','056889-0001','600016-0017','080352-1210','600004-0006','300644-0001','800030-0002'')'

Also, I am note certain how ADODB would handle THIS line, but it IS the source of the problem above:

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:

        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.





[/CODE]

[RunsWithScissors]

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.

[Alfarata]

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

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()

[RunsWithScissors]

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.

'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

[Alfarata]

Runswithscissors I was doing this from a module not a form is that a problem???

[RunsWithScissors]

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!

[RunsWithScissors]

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?

[Alfarata]

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.

     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 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):

    '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 . . .