Bummer, I have used the grid extensively to fill up with data and have the user group and sort the information. Love it for that, but this is first time trying to take a virtually empty grid and grab just the selected data from it. I've tried setting the value to <> Empty, Nothing, DBNull and many other things. Their sampls show them using a grid similarly for an odering application so I'm trying now to follow the code to see if I can locate how they check for a null value in a selected row cell. I'm also looking at how the settings for the myriad of properties may be different than the default. If any ideas pop up please let me know, Larry

using vb.net 2008 and latest version of JanusSystem GridEx. When I use the Try..Catch..Finally I get an error message when I am trying to get the data from the cell in a row. There can be up to 5 cells that the user has entered data into that I need to pull out if the SelectedRow is checked. If there is data in the cell I can extract it and store it to the table. If I check the next column and it is empty I get the error message below:

A first chance exception of type 'System.NullReferenceException' occurred in Janus.Windows.GridEX.v3.dll
System.NullReferenceException: Object reference not set to an instance of an object.
at Janus.Windows.GridEX.GridEXCellCollection.get_Item(GridEXColumn column)
at Janus.Windows.GridEX.GridEX.GetValue(GridEXColumn column)
at Janus.Windows.GridEX.GridEX.GetValue(String columnKey)
at JWI.frmLOL.SaveTab1(Object& _frmGuid) in C:\JWI\JWI\JWI\frmLOL.vb:line 708

If I use the old VB6 On Error Resume Next then I don't even pick up the data that is in the cells, the code just goes right over the lines:

strJnts = GridEX1.GetValue("E") 'or "B1" or "B2" etc

And I never hit the DoSaveData function. Not sure what the solution is or even the problem. I've tried at least a dozen diferrent codings for IsNotDBNull and anything I could find in the MSDN and online but nothing seems to help. I have a message into the JanusSystem forum but they won't be answering until mid morning Monday and I would like a solution later tonight or Sunday if possible. The entire function code follows:

Public Function SaveTab1(ByRef \_frmGuid As Object) As Boolean
    Dim rowNdx As Integer = 0 'Set to zero index value 
    Dim strJnts As String
    Dim \_MATGuid As Object
    Try
        'On Error Resume Next
        GridEX1.BoundMode = Janus.Windows.GridEX.BoundMode.Unbound
        'allows us to use it in AddItem mode 
        GridEX1.KeepRowSettings = True
        'keeps settings same when row scrolls out of sight 
        Dim checkedRows() As Janus.Windows.GridEX.GridEXRow
        checkedRows = GridEX1.GetCheckedRows()
        'if the user didn't check any row, you will get an empty array 
        If checkedRows.Length = 0 Then
            'no materials on Tab1 Tubing so we need to check Tab2 Pipe
            'SaveTab2()
        Else
            Dim row As Janus.Windows.GridEX.GridEXRow
            For Each row In checkedRows
                'get th

I liked the link- useful. Right on, I had started back with just two tables and worked my way up from there. I finally decided to add another ID field to one of the tables (breaking normalcy rules) but it helped make it an easier sql select statement. I only had to change a small bit of code to make sure that everything gets saved properly to the tables. So it works now- thanks. Larry

I rried to post a little bit ago but that must have bombed off. I'm using Access 2007 in query design mode At this point I have gotten to where I show the 24 test records for Material Inventory in the correct storage yards. The SQL statement is:

SELECT MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID;

Now I need to show where two 2 items of material in each storage yard came from a DIFFERENT CostCenterID than the CostCenterID of the storage yard. The storage yards are costed based upon the lease they are loacted on. There can be several hundred wells for each lease- all with the same CostCenterID. SOMETIMES, material from one well on one lease (one CostCenterID) may be stored in another leases storage yard (another CostCenterID). I need to be able to track it and point it out. So I add in the table Well and relate the fields Well.CostCenterID to MatInv.CostCenterID and I get 36000+ records. Not the original 24 test records. So obviously the Query designer isn't what I need so I am trying to manipulate things in SQL view- STILL without any luck. Trying to follow info on Access help that is just about useless. Current SQL statement is:

SELECT Well.CostCenterID, Well.Well, MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Well INNER JOIN (Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID) ON Well.CostCenterID = MatInv.CostCenterID;

Any assisance is appreciated, Larry

That is essentially what I already have with the exisitng tables. Of course there is a lot more information in each table, the well table contains cost center codes, subdivision, longitute and lattitude and is a many to one relationship with the Lease table. The Locations table which I switched and tried to add to the well table still has a cost center and the address of the Location I have placed in the longitude/lattitude fields with the name of the storage yard in the Well field When I had both tables no problem pulling out the well (which is actually the well number in the particular lease generally coded like: 12-34, 9-1, or 7-50- this relates to the location on a acre gridlines which each square mile of oil field leases are divided into) or the location from the location table. It always seemed to succeed when I went from well to storage or storage to well. But well to well or storage to storage I never got any data - though I have manually created the data so I know I have records for testing purposes. I was thinking I needed to add something like:

w.well WHERE w.WellID = m.MovefromGUID AND w.well WHERE w.WellID = m.Move2GUID

somewhere in the INNER JOIN's so I am experimenting with that right now. Any other suggestions are certainly helpfull. Some of the questions we need to ask are: What materails were moved from storage to wells What materials moved from wells to storage What crews moved the materials Total quantity of each material moved by day by week, etc. So yeah there is a lot that needs to be gathered and displayed which is why I'm beginning to question my initial database design and wondering if I need to change it dramatically now to save more headaces later. Years ago with Access 2 and then Access 2000 I finally remember doing something like what I wanted. Using the hlp for Access 2007 is nigh on useless - it is more oriented toward promoting its features than offerring help. I found an old copy of Office 2000 and installed the help and went into it for Access and what I THINK I need is some form of UNION query. That should allow me to keep the seperate tables for Wells and Locations and still be able to pull out either data in wither field. If I run into problems I'll upload a sample of the code later. Larry

modified on Friday, June 25, 2010 10:07 PM

Using Visual Studio 2008 and Access 2007 I have tried several different ways to track Material Activity. Table MatActivity has a field for MoveFromGUID and MoveFromType as well as Move2GUID and Move2Type. The possible combinations are: From Well to Well; from Well to StorageYard; from StrorageYard to Well and from StorageYard to StorageYard. I tried it with two tables the Well table and a separate Location table but no luck. There are 4 types of wells so I have tried to add the storageyards into the well table and just use a different code for the welltype to indicate it as a storageyard. The following code gets me the sample data:

SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, w.WellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON w.WellID =m.MoveFromGUID;

The following code trying to set another relationship from the well table fails to pull any data:

SELECT m.MatActivityID, m.TasksID, m.MoveFromGUID, w.Well, m.MoveFromType, m.Move2GUID, m.Move2Type, m.MaterialsID, m.Units, wWellID, w.WellType FROM Well as w INNER JOIN MatActivity as m ON (w.WellID = MatActivity.Move2GUID) AND (w.WellID = m.MoveFromGUID);

Do I need to change the table MatActivity? It basically is to document from and to locations and the units of each material being moved. There can be anywhere from several hundred units of one type of material to 30-40 types of material with only a couple of units each. It is related to the Tasks table which provides info on the crew to move the material and the dates and time assigned and actually moved. The Material table provides the description of the material to be moved. If I need to rethink the process and change the entire table structure to be able to show the actual to and from locations and remainder of data now is the time before I get much further into the project. Any suggestions and assistance greatly appreciated

My code for storing the data:

                If Len(strJnts) > 0 Then
                    \_gblString = CreateGUIDString()

                    gblConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\JWI\\Data\\JWI.mdb;Jet OLEDB:Database Password=jWi;")

                    strData = "INSERT INTO LOLItems (LOLItemsID, LOLIFormID, Type, TextBox, MaterialsID, Units, DateCreated )" \_
                    & " VALUES ('" & \_gblString & "',  " \_
                    & "'" & \_frmGuid & "', '" \_
                    & 1 & "',' " \_
                    & strName & "',' " \_
                    & \_MATGuid & "',' " \_
                    & strJnts & "',' " \_
                    & Now() & "');"

                    gblCMD.Connection = gblConn
                    gblCMD.CommandText = strData
                    Dim cmd As New System.Data.SqlClient.SqlCommand

                    gblConn.Open()
                    gblCMD.ExecuteNonQuery()
                End If

The value of _MATGuid is gathered from the tag property of a label. In the immediate window: ? _MATGuid "55368562-a5c5-429d-a491-e1bb24f37836" ? strdata "INSERT INTO LOLItems (LOLItemsID, LOLIFormID, Type, TextBox, MaterialsID, Units, DateCreated ) VALUES ('{6b8da5a3-fbad-4140-af9a-2728a9e35af6}', '{a6a0e519-aa21-48b0-b26a-839caed800c7}', '1',' txtRodE_1',' 55368562-a5c5-429d-a491-e1bb24f37836',' 10',' 6/4/2010 9:47:40 PM');" In Access the value for MaterialsID for the record ended up being: {05F3C3FC-D57C-7047-0200-00000000E927} It is a Number field, ReplicationID, Indexed (Duplicates Allowed) When I saved 14 record at a time I got the following- It seems like they are GUIDS being generated by Access as they are so similiar. MaterialsID {05A5C29C-D57C-7047-0500-00000000EA35} {05A5C29C-D57C-7047-0100-000000009A3E} {05A5C29C-D57C-7047-0400-000000001A48} {05A5C29C-D57C-7047-0000-00000000BA28} {05A5C29C-D57C-7047-0200-000000009A30} {05A5C29C-D57C-7047-0200-000000009A43} {05A5C29C-D57C-7047-0400-000000003A34} {05A5C29C-D57C-7047-0000-00000000DA41} {05A5C29C-D57C-7047-0300-000000002A45} {05A5C29C-D57C-7047-0700-000000007A3B} {05A5C29C-D57C-7047-0300-00000000AA31} {05A5C29C-D57C-7047-0500-000000009A49} {05A5C29C-D57C-7047-0600-000000007A39} {05A5C29C-D57C-7047-0100-00000000BA2C} The values that the _MatGuid SHOULD have been are shown below: 55368562-a5c5-429d-a

Right, but I initially thought that I would have to cycle through EVERY textbox and if there was a value in it then store the data directly to the table then. I would be able to pick up the MaterialID from the GUID stored in the label for the row. I just thought that would be more time consuming and convoluted than this way. But I am open for suggestions. There are a lot more textboxes on the form than just the 65 for the materials so I have to be able to prevent any others from being searched. I beleive that can be done by cycling through the textboxes on tabLOLfrm.TabPage1 and tabLOLfrm.TabPage2 and tabLOLfrm.TabPage3. I beleive that the textboxes on the container are not recognized by the form so that might be one way. I remember that VB6 had the control array feature which was much easier to utilize. I have seen a number of attempts to create a control array in VB.Net but don't know exactly how to make a simple easy way to work. I've tried about 3 diferent ways from several different sources. Again, I appreciate your input and insight so far, any further assistance is greatly apreciated. I'm going to start working and this suggestion tonight and see what I can accomplish. Larry

Your right it is convoluted. Let me lay out part of the design specs. We are dealing with scrap material commodity selling. There will be 30-40 buyers/sellers working with the form and can make 20-30 buys/sells per hour. What makes it difficult is they want to top selling items from yesterday to show as the first item on the form today. In other words, if 3/8 inch rod traded more yesterday then today it needs to be at the top of the list. If today 5/8 inch rods sold the most then tomorow they need to be at the top of the list. So each day, the order in which the items show can change. I can calculate total number of sales of each item and then fill the items into a label which has the name of the item and then in the labels .tag property I store the GUID for the item. Then I name each of the textboxes acrosss the screen based upon the item name. Each item has 5 grades from Junk to New and it is possible that on one order the user can sell 20 joints of 3/8 inch rod Junk, 15 joints of 3/8 inch rod Good (B2) and 10 rods of 3/8 inch rod New. That is why I thought the best reason is to store each entry into a text box on the Lost_focus event into a Material collection class and then save the items to the tables from that class when they click on the Save button which will also close the form and allow them to go on to the next order. Anything anyone can come up with to make it easier will be greatly appreciated. Larry

Not sure about those events but will look into them. Thanks Lary

The Save works only when they click on the Save button to cycle through the Material Collection class. When the control loses focus then it STORES the necessary info into the Material collection class. I figured this would be easier than cycling through each text box when the Save button was clicked and then doing something like : if len(textbox.text)> 0 then Store necessary info to table end if and then to the next textbox and so on. My problems seems to be that the events don't fire in the sequence that I'm expecting. I can enter data into 2 or 15 text boxes and when I click on Save it always seems to handle the number of items in the collection and THEN go to at least one more textbox even though there is no data stored in it. If I enter data into just one text box then the Save routine runs before the STORE routine for the textbox and then that store routine runs and then the next textbox lost focus runs also. Not sure why? Thanks, Larry

I have a total of 65 text boxes which the user can fill in. They are 13 down and 5 across. Each row is related to a particular material (which can change daily -see earlier posts) the columns are for quality of material. In Debug mode stepping through the code this is what happens: If I pull up the form and enter data into one textbox and then click on the save button the program goes through the save routine, but there is nothing to save and then it hits the Lost_focus event of the text box and runs through and saves the data to the collection class but doesn't save the information. If I enter one text box and then tab to another the Lost_focus of the first textbox saves the data to the collection class and then click on the Save button the program saves the info to the tables properly and then runs the Lost_focus event of the second text box. I don't remember this happening in VB6 so I am sure it has to do with the new form collection class, etc. I am confused and wonder now what is the proper way to proceed. I need to be able to save for each form anywhere from 1 to 65 types of material on each form. The text_changed proeprty won't work as it fires on each digit being entered- 123 causes the event to fire 3 times. Here is the code for the SaveMaterial function from the save button, the StoreMaterial2COL function and the first couple of lost_focus Subs:

Private Function SaveMaterial() As Boolean

    Try
        ' The index of a zero-based collection is Count property minus 1.
        Dim iCnt As Integer
        iCnt = myMaterialsCollection.Count
        iCnt = iCnt - 1

        With myMaterialsCollection
            For Each aItem In myMaterialsCollection
                If iCnt = -1 Then
                    Exit For
                End If
                \_gblString = CreateGUIDString()

                gblConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\JWI\\Data\\JWI.mdb;Jet OLEDB:Database Password=jWi;")

                strData = "INSERT INTO LOLItems (LOLItemsID, LOLIFormID, Type, TextBox, MaterialsID, Units, DateCreated )" \_
                & " VALUES ('" & \_gblString & "',  " \_
                & "'" & \_frmGuid & "', '" \_
                & 1 & "',' " \_
                & aItem.txtBoxName & "',' " \_
                & aItem.MaterialID & "',' " \_
                & aItem.jnts & "',' " \_

Thanks, I seem to have that part working now. got another problwm which I don't seem to remember that in VB6. Started another post for it titled: "Lost_focus and save button"

Ok, I have the labels working properly on each tab. I can change them based upon the sequence of orders for each item placed the day before. (I am manually adjusting that now). I need to create the Function MaterialSave(). Each item on each tab has 5 textboxes for New (A), Good (B1), Good (B2), Good (C) or Junk (F). First textbox is txtRodF_0, txtRodC_0, txtRodB2_0 and next row is txtRodF_1, txtRodC_1, txtRodB2_1, etc. I need to store the materialID (a GUID) that is stored in the lblRods_x.Tag property, the name of the text box txtRodC_0.name and the number of units being traded. A person can trade/sell 15 units of txtRodF_0 and 20 units of txtRodB2_0 and any combination od trades/sells per tab per Material Transfer form. Each agent can do 100 or so forms per day. I tried doing a multi-dimensional array and then a collection and have bombed out on each of them. Then I was researching and came across maybe using a Material Class with 3 properties and I just add class member everytime a textbox loses focus after an amount has been entered. I can then save the class members one by one to the material table on the form_close event. When a new form is pulled up it creates a new material class all over. Any ideas or suggestions, better/faster way of doing this?

Thanks, the last two replies help me to understand what is going on a little bit better. I'm wanting an easier way to handle this. I have a tab on the form with 4 tabs and on each tab the number of items that will vary from day to day : 13 Rods, 11 Tubes, 10 Equip, and 9 Misc. So hard coding it is not really desirable. I wonder if it shows Null or nothing as a result of the labels being on the tab. When I ran this:

            For Each Row In dstbl.Rows
                If rowNdx = recCount Then
                    Exit For
                End If
                For Each c As Control In Me.TabPage1.Controls
                    If c.Name = "lblRods\_" & lblNdx Then
                        c.Tag = dsMaterial.Tables(0).Rows(rowNdx)("MaterialsID").ToString()
                        c.Text = dsMaterial.Tables(0).Rows(rowNdx)("Material").ToString()
                    End If
                Next
                rowNdx = rowNdx + 1 'keep track of where we are at 
                lblNdx = lblNdx + 1
            Next

It did not work as For Each c As Control In Me.Controls Debug.Print showed me the names of each control but I just got tabLOL but not tabLOL.TabPage1 so when I hard coded it fills in properly. So I changed to having 4 separate functions: Private Function LoadTab1Data() As Boolean Private Function LoadTab2Data() As Boolean Private Function LoadTab3Data() As Boolean Private Function LoadTab4Data() As Boolean And it seems to be working when I change the statement:

        Dim mySelectQuery As String = "SELECT MaterialsID, Type, Sequence, Material " & \_
            "FROM Materials " & \_
            "WHERE Type = 2 " & \_
            "ORDER BY Type, Sequence;"

and change the Type. Would still like to modularize it more and make it one Function that goes through the data 4 times and fills in each tab page properly. Works clumsily now, but still hoping to refine it, Larry

I have no problem picking up the line number and col number from the bottom of the IDE. It very clearly shows it. What I am not understanding is WHY the code is not returning the controlType? This loop (line numbers added for your convienance) from lines 43 to 56 should pick up the type as being a Label from what was defined and pased into it. I would imagine that is what appears to be Null - it doesn't pick up that lblRods_ is a label even though I have tried both of these combinations: Dim Labels As Label() = ControlArrayUtils.getControlArray(Me, "lblRods") Dim lblRods As Label() = ControlArrayUtils.getControlArray(Me, "lblRods") and I don't know how many other combinations. My conclusion is an error in the original code- but that is because I am not certain. I would hope somebody with more experience than myself would be able to examine and determine if that is the case and possible suggest a solution. I'm assuming it would return the controlType as Label and not Null

43 If maxIndex > -1 Then
44
45 For i = 0 To maxIndex
46 Dim aControl As Control = _
47 getControlFromName(frm, controlName, i, separator)
48 If Not (aControl Is Nothing) Then
49 'Save the object Type (uses the last
50 'control found as the Type)
51 controlType = aControl.GetType
52 End If
53 alist.Add(aControl)
54 Next
55 End If
56 Return alist.ToArray(controlType)

I have a table of 13 items that I need to display in sequence. The sequence can change from day to day based upon the previous days usage. One day the number 1 item may be 3/4" concrete lined rod, the next day that may be 5th and 5/8" SMPC may be the number one item. I pull the material from the table in the sequence order and then I want t fill in the label with the material name and the label tag property with the MaterialID which is a GUID. I found some code in a google search for creating a control array but I'm just not understanding it enough to get it to work in my project. The link for that is as follows: http://vbnet.codenewbie.com/articles/vbnet/1555/Mimicking\_VB6\_Control\_Arrays\_in\_VBNET-Page\_1.html My labels are named lblRods_0 to lblRods_12 and the code for loading them is:

Private Function LoadTabData() As Boolean
    'We need to get the items for the frmLOL from the table Materials and load 
    'them into the text boxes in sequence. If user fills out anything we need to 
    'store it using the GUID stored in the tag property

    'SELECT Materials.MaterialsID, Materials.Type, Materials.Sequence, Materials.Material
    'FROM(Materials)
    'ORDER BY Materials.Type, Materials.Sequence;
    Try
        gblConn = New OleDbConnection(JWIConnStr)
        Dim daMaterial As New OleDbDataAdapter
        Dim dsMaterial As New DataSet
        Dim mySelectQuery As String = "SELECT MaterialsID, Type, Sequence, Material " & \_
            "FROM Materials " & \_
            "ORDER BY Type, Sequence;"
        Dim myConnection As New OleDbConnection(JWIConnStr)
        Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)
        myConnection.Open()
        daMaterial.SelectCommand = myCommand
        daMaterial.Fill(dsMaterial)
        If Not dsMaterial.Tables(0).Rows.Count > 0 Then
            MsgBox("Unable to locate MATERIALS. Please Cancel and retry later", MsgBoxStyle.OkOnly)
            Exit Function
        Else 'at least one record shows 
            Dim recCount As Integer
            Dim dstbl As New DataTable
            daMaterial.Fill(dstbl)
            recCount = dsMaterial.Tables(0).Rows.Count
            Dim rowNdx As Integer
            Dim lblNdx As Integer
            rowNdx = 0 'Set to zero index value for the dsMaterial tbl 
            lblNdx = 1
            For Each Row In dstbl.Rows