Visual Basic

Re: Linq, I get an extra blank record on GroupBy, How to select it out

28-Jan-17 2:58

Assuming FSALESPN and FSALESREP represent the same thing, you could add a filter to your query:

SQL

SELECT
   h.FSALESPN, 
   h.FPERIOD, 
   h.FCSAMT
FROM 
    ARINV01H.dbf h
WHERE 
    h.FSHIPDATE >= @FSTART
AND 
    h.FSHIPDATE <= @FSTOP
AND
    h.FSALESPN Is Not Null

Or you could add a filter to your LINQ query:

VB.NET

Dim gSalesReps As List(Of model_salesChampions) = invoices
    .Where(Function(sR) sR.FSALESREP IsNot Nothing)
    .GroupBy(Function(sR) sR.FSALESREP, Function(salesRep, cl) New model_salesChampions() With {
    .salesrep_ID = salesRep,
    .salesrep_Name = String.Empty,
    .P1 = cl.Where(Function(p1) p1.FPERIOD = rT & "01").Sum(Function(p1) p1.FCSAMT),
    ...
}).ToList()

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

jkirkerx30-Jan-17 7:13

jkirkerx

30-Jan-17 7:13

I don't why I didn't think of that. Or perhaps I did but tried to use it after the GroupBy resulting in an error.
Thanks Richard!

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

bindingsource.filter not working

Member 1108983127-Jan-17 1:03

27-Jan-17 1:03

All-

I have a datagridview on a form, with associated textboxes and such - as the user selects a row in the dgv, the textboxes show the appropriate record - for updating information on that record.

On a main menu form - I have a production line, and model year value that determines what data the user sees.

I am applying a filter to the bindingsource for the dgv to limit the information based on the production line and model year for which the user works on.

my filter looks like this -

me.TblPCRBindingSource.Filter = "ModelYear = " & (myear) And "Line = " & CInt(txtLine.Text)

where myear is a double as we can have 2017.5 model year (for mid year changes) and line I'm reading a textbox, but the datatype in the table is integer - so I convert.

If I do filter for one field - it works for that field. as soon as I add "AND" and the other field - they cancel each other out and I get ALL records.

I'm baffled as to why - banging head on wall.

please help-

thank you -

Eddy Vluggen27-Jan-17 1:15

Eddy Vluggen

27-Jan-17 1:15

Member 11089831 wrote:
"ModelYear = " & (myear) And "Line = " & CInt(txtLine.Text)

Shouldn't there be a space before the "Line?

Otherwise the resultfilter contents becomes "ModelYear = 2017Line = 0", and 2017Line will be an invalid value for a integer-field in the table, making the filter invalid.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Member 1108983127-Jan-17 4:26

27-Jan-17 4:26

yes, there should be a space - this is how I currently have it -

me.TblPCRBindingSource.Filter = "ModelYear = " & (myear) And "Line = " & CInt(txtLine.Text)

it however, does not work.
it's like it turned the filter off.

if I make them separate lines like this:

Me.TblPCRBindingSource.Filter = "Line = " & CInt(txtLine.Text)
       Me.TblPCRBindingSource.Filter = "ModelYear = " & (myear)

then only 1 filter works - usually the last one - as it follows order of operations.

the msdn documentation tell you to use And as I have above, but when I do with different data types .. they don't filter at all.

Richard Deeming27-Jan-17 2:07

27-Jan-17 2:07

Shouldn't the And be inside the string?

me.TblPCRBindingSource.Filter = "ModelYear = " & (myear) & " And Line = " & CInt(txtLine.Text)

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Member 1108983127-Jan-17 4:23

27-Jan-17 4:23

If I do add the "and" within the statement, I get an end of statement expected error.

Me.TblPCRBindingSource.Filter = "ModelYear = " & (myear) "And Line = " & CInt(txtLine.Text)

so this, doesn't work.

Richard Deeming27-Jan-17 4:26

27-Jan-17 4:26

You need a space before the And:

VB.NET

Me.TblPCRBindingSource.Filter = "ModelYear = " & (myear) & " And Line = " & CInt(txtLine.Text)

Otherwise, your filter will end up as:

plain

ModelYear = 2017And Line = 42

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

modified 27-Jan-17 13:36pm.

Member 1108983127-Jan-17 7:20

27-Jan-17 7:20

This:

me.TblPCRBindingSource.Filter = "Line = " & CInt(txtLine.Text) And " ModelYear = " & (myear)

gives me everything.

and this:

e.TblPCRBindingSource.Filter = "Line = " & CInt(txtLine.Text) " And ModelYear = " & (myear)

gives me and end of statement error at the And ModelYear. It wants it like the first example - yet the first example isn't working.

this is coded in VB 2015

Richard Deeming27-Jan-17 7:37

How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

27-Jan-17 7:37

The first one is completely wrong.

If you compile with Option Strict On, you'll get a compiler error - BC30512 Option Strict On disallows implicit conversions from 'String' to 'Long'.
If you compile with Option Strict Off, you'll get a runtime error - Conversion from string "line = 42" to type 'Long' is not valid.

The second one is missing the & between CInt(...) and " And ...":

VB.NET

e.TblPCRBindingSource.Filter = "Line = " & CInt(txtLine.Text) & " And ModelYear = " & (myear)

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Member 1186689324-Jan-17 5:29

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

24-Jan-17 5:29

I am attempting to build a command line tool that will create shortcuts for an application on the start menu and desktop. I have existing code that I use elsewhere to create the shortcuts. However, the program looks at two different files one file has only the site code and the other has the other information needed to created the shortcut by site for a whole region. The idea is to go through both lines until it finds the 3 letter abbreviation for the site in both files. For example one file only contains BEC (sites2.txt - the city) and the other file (R03Sites2.csv - Region) contains 50 lines with a different cities and all the pertinent information.

Sub Main()
        '--------------------------------------------------------------------------------------------------------
        'Declare Variables
        Dim strSiteCode
        Dim strVistaFQDN
        Dim strPort
        Dim arrStr

        '--------------------------------------------------------------------------------------------------------
        'Start processing

        Using sr As New StreamReader("c:\dell\R03Sites2.csv")
            Dim line As String
            ' Read and display lines from the file until the end of
            ' the file is reached.
            Do
                line = sr.ReadLine()
                Dim Sites As String = My.Computer.FileSystem.ReadAllText("c:\dell\Sites2.txt")
                If Not (line Is Nothing) Then
                    Console.WriteLine("Start processing loop")
                    arrStr = Split(sr.ReadLine, ",")
                    strSiteCode = arrStr(0)
                    strVistaFQDN = arrStr(1)
                    strPort = arrStr(2)
                    Console.WriteLine("Array populated...  Enter if then statement")
                    If Trim(Sites) = Trim(strSiteCode) Then
                        Console.WriteLine(Trim(Sites) & " , " & Trim(strSiteCode))
                        Console.WriteLine("s=" & strVistaFQDN & " p=" & strPort)

                        'build shorcuts here.  Code to build shortcuts is working correctly when ran
                        'statically but it never finds a match so it always moves to the else and writes
                        'Console.WriteLine("Error:  No Matching Information in the Sites and R03sites files!")
                        'There is only 1 line is Sites and an array is created for the data in R03sites.
                        'My intention is to match the line in Sites to the portion of the array that correspondes
                        'to the site which is strSiteCode.  When it finds the match it is suppose to create the
                        'shortcut with the correct server name and port.


                        Console.WriteLine("Successfully processed!")
                        Exit Do
                    Else
                        Console.WriteLine("Error:  No Matching Information in the Sitecode and R03sites files!")
                    End If
                End If
            Loop Until line Is Nothing
        End Using
    End Sub

Here is the output:

\Dell>bcmass
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
Start processing loop

Unhandled Exception: System.IndexOutOfRangeException: Index was outside the bounds of the array.
at System.Array.InternalGetReference(Void* elemRef, Int32 rank, Int32* pIndices)
at System.Array.GetValue(Int32 index)
at Microsoft.VisualBasic.CompilerServices.Symbols.Container.GetArrayValue(Object[] Indices)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.InternalLateIndexGet(Object Instance, Object[] Arguments, String[] Argument
Names, Boolean ReportErrors, ResolutionFailure& Failure, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateInvokeDefault(Object Instance, Object[] Arguments, String[] Argum
entNames, Boolean ReportErrors, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateIndexGet(Object Instance, Object[] Arguments, String[] ArgumentNames)
at BCMASS.Module1.Main()

Richard Deeming24-Jan-17 6:02

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

24-Jan-17 6:02

You're getting the exception because you've read a line from the file which doesn't contain two commas.

That's probably caused by the fact that you're skipping every other line:

VB.NET

line = sr.ReadLine() ' Read a line
If Not (line Is Nothing) Then
    arrStr = Split(sr.ReadLine, ",") ' Read another line

You need to change that to re-use the line you've already read. You also need to check that the array returned from Split contains enough elements:

VB.NET

line = sr.ReadLine()
If line IsNot Nothing Then
    arrStr = Split(line, ",")
    If arrStr.Length >= 3 Then
        strSiteCode = arrStr(0)
        strVistaFQDN = arrStr(1)
        strPort = arrStr(2)
        ...

You should also move the code that reads the contents of Sites2.txt outside of the loop. As it stands, you're reading the entire file for every line in Sites2.csv, even though you're not changing it.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Member 1186689324-Jan-17 8:13

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

24-Jan-17 8:13

Thanks for the response. I made the suggested changes and am still having issues. This is what I have now:

Sub Main()
        '--------------------------------------------------------------------------------------------------------
        'Declare Variables
        Dim strSiteCode
        Dim strVistaFQDN
        Dim strPort
        Dim arrStr
        Dim Sites As String = My.Computer.FileSystem.ReadAllText("c:\dell\Sites2.txt")
        '--------------------------------------------------------------------------------------------------------
        'Start processing

        Using sr As New StreamReader("c:\dell\R03Sites3.csv")
            Dim line As String
            ' Read and display lines from the file until the end of
            ' the file is reached.

            Do
                line = sr.ReadLine()
                Console.WriteLine("line = " & line)
                Console.WriteLine("Start processing loop")
                If line IsNot Nothing Then
                    arrStr = Split(line, ",")
                    If arrStr.Length >= 3 Then
                        strSiteCode = arrStr(0)
                        strVistaFQDN = arrStr(1)
                        strPort = arrStr(2)
                        Console.WriteLine(Trim(Sites) & " , " & Trim(strSiteCode))
                        Console.WriteLine("s=" & strVistaFQDN & " p=" & strPort)
                        Console.WriteLine("Array populated...  Enter if then statement")
                        If Trim(Sites) = Trim(strSiteCode) Then
                            'Console.WriteLine(Trim(Sites) & " , " & Trim(strSiteCode))
                            'Console.WriteLine("s=" & strVistaFQDN & " p=" & strPort)
                            Console.WriteLine("Successfully processed!")
                            Exit Do
                        Else
                            Console.WriteLine("Error:  No Matching Information in the Sitecode and R03sites files!")
                        End If
                    End If
                End If
            Loop Until line Is Nothing
        End Using
    End Sub

Here is the output (I cut the R03 file down to 2 entries):

C:\Dell>bcmass
line = BEC,test.beckley.com,19233
Start processing loop
BEC,BEC
, BEC
s=test.beckley.com p=19233
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
line = BIR,test.birmingham.com,19230
Start processing loop
BEC,BEC
, BIR
s=test.birmingham.com p=19230
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
line =
Start processing loop

The bold entries should read BEC,BEC and BEC,BIR instead of BEC,BEC,BEC and BEC,BEC,BIR. I'm not sure how make the if then statement read only the first BEC for the Sites2.txt file and the Site code out of R03sites3.csv and then continue processing/reading the matching information.

Richard MacCutchan24-Jan-17 8:38

Richard MacCutchan

24-Jan-17 8:38

The reason you are getting the extra data in the output is because of the line:

Console.WriteLine(Trim(Sites) & " , " & Trim(strSiteCode))

You need to split the Sites data the same as the other lines, so you can compare BEC to BEC. Also, you only read the Sites data once, so how do you expect it to be different on subsequent passes through the loop?

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

Member 1186689324-Jan-17 9:07

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

24-Jan-17 9:07

Thanks for the response. You're suggestion helped but the if then statement still doesn't work. Also, Sites data never changes each computer at each site has a Sites data file with the appropriate entry. It only has one line in the file and it is formatted like this: BEC,BEC. Here is the new code:

Do
                line = sr.ReadLine()
                Console.WriteLine("line = " & line)
                Console.WriteLine("Start processing loop")
                If line IsNot Nothing Then
                    arrStr = Split(line, ",")
                    siteArr = Split(Sites, ",")
                    Site1 = siteArr(0)
                    If arrStr.Length >= 3 Then
                        strSiteCode = arrStr(0)
                        strVistaFQDN = arrStr(1)
                        strPort = arrStr(2)
                        Console.WriteLine(Trim(Site1) & " , " & Trim(strSiteCode))
                        Console.WriteLine("s=" & strVistaFQDN & " p=" & strPort)
                        Console.WriteLine("Array populated...  Enter if then statement")
                        If Trim(Sites) = Trim(strSiteCode) Then
                            'Console.WriteLine(Trim(Sites) & " , " & Trim(strSiteCode))
                            'Console.WriteLine("s=" & strVistaFQDN & " p=" & strPort)
                            Console.WriteLine("Successfully processed!")
                            Exit Do
                        Else
                            Console.WriteLine("Error:  No Matching Information in the Sitecode and R03sites files!")
                        End If
                    End If
                End If
            Loop Until line Is Nothing

Here is the output:

\Dell>bcmass
line = BEC,test.beckley.com,19233
Start processing loop
BEC , BEC
s=test.beckley.com p=19233
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
line = BIR,test.birmingham.com,19230
Start processing loop
BEC , BIR
s=test.birmingham.com p=19230
Array populated... Enter if then statement
Error: No Matching Information in the Sitecode and R03sites files!
line =
Start processing loop

Richard MacCutchan24-Jan-17 9:37

Richard MacCutchan

24-Jan-17 9:37

If Trim(Sites) = Trim(strSiteCode) Then

That should be

If Trim(Site1) = Trim(strSiteCode) Then ' Site1 contains the first field of the Sites data

You need to slow down, think what you are trying to achieve, and look at exactly what each line is doing. Rather than using Console.WriteLine to see what is going on, you should use the debugger step by step, so you can see exactly what is happening.

And in the second loop (as I explained previously) BEC will never be equal to BIR.

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

Member 1186689324-Jan-17 10:03

Re: How to compare 2 different text file lines and then process information if a match is in both file lines - Please help

24-Jan-17 10:03

That totally makes sense. I just didn't change sites to site1 in all instances. I know that BEC will never equal BIR, R03sites has numerous sites and what I wanted to do was loop through all the sites in R03sites and write an error to the console on non-matching items and process the match. I just don't know how to do what I want a better way. I am sure you could make the loop stop when it finds a match but I don't know how to do it yet. I am still new to programming and have never worked with 2 text files at once, I have mostly just used text files to read computer names or ip addresses and then do something straightforward with it or write text to a log file. I appreciate the help. I left work but will give this a try tomorrow.

Member 1186689325-Jan-17 5:42