VS 2010 Reading Numbers In an Array and evaluating them against a number in a column-VBForums
Results 1 to 17 of 17

Thread: Reading Numbers In an Array and evaluating them against a number in a column

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Reading Numbers In an Array and evaluating them against a number in a column

    Hello All,
    I am working on a small program that gets it data from a SQL database. My program is using Stored Procedures and reading the database every so many seconds. If it finds a row with a matching ID the data is displayed into a listview with a data reader. The issue is every time the procedure runs it pulls in the same row until that row is deleted by another program. The row contains a unique # and I get this number out of the data reader and place it in an array. I know the numbers are going in the array because I display them in another text box just to make sure, I need to compare the unique # and tell the program to just skip that row if it has already been placed in my listview. I have the number going into the array on a new line each time a new number is added. Most of the time there will only be one number but it could be up to 5-6 that it has to read and evaluate. I guess my question is should this be done in the SQL procedures or in the program code? Hopefully it makes sense. Thanks in advance.

    Matt

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,370

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    Are you wedded to using a stored procedure? What you need to do may be a bit more complex if you are.

    Generally speaking, you might want to use an IN clause, e.g.
    SQL Code:
    1. SELECT * FROM MyTable WHERE Id NOT IN (2, 4, 6)
    where the numbers come from your array. If you use inline SQL code in your app then creating that is easy:
    vb.net Code:
    1. Dim command As New SqlCommand("SELECT * FROM MyTable WHERE Id NOT IN (" & String.Join(", ", myArray) & ")", connection)
    You should generally avoid string concatenation when building SQL code but there's no risk here.

    If you do want to use a stored procedure then it gets more complex because you can't send a list as a single parameter, so you'd have to send the numbers as a string and then break it up and construct the list in your SQL code.

    A better way may be to add a LastModified column to your database table. When you retrieve a record, you get its LastModifed value and then, on subsequent queries, you only look for records that have a LastModified value greater than that. That way, you only have to use a single value in your WHERE clause regardless of how many records there are.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    So I went back and looked at the DB where the data is coming from( I didn't design it), there is a column labeled LastUpdateWhen and it is a DateTime column. Would something like that work?

    I would like to stick to the stored procedure but I could write the SQL code in if that would be the best way?

    Thanks for taking time to provide some insight and help.

  4. #4
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    894

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    be careful using the IN clause in stored procedures, there is a limit to how many variables are allowed and its quite small, its a safety feature i came accross using azure DB's but it can be overridden..... i cant remember exactly but i think the limit is 1000 on azure normally..... and i cannot remember the command to override it but its easy to find on google.

    just a heads up
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  5. #5
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    894

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    on another note, if your trying to catch new records added into the database you might want to look into 'Triggers', i havent used them myself but i dont think they would be to difficult to setup. probably set when the trigger fires and tell it what procedure to run....

    This would be setup on the SQL server.
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,370

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    Quote Originally Posted by GBeats View Post
    on another note, if your trying to catch new records added into the database you might want to look into 'Triggers', i havent used them myself but i dont think they would be to difficult to setup. probably set when the trigger fires and tell it what procedure to run....

    This would be setup on the SQL server.
    That's not going to work because the database can't notify the application of the change via the trigger. There is the SqlDependency class in the Framework that can be used to receive notifications when changes occur, as long as the number of dependencies is small.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,370

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    Quote Originally Posted by mattgriffin901 View Post
    So I went back and looked at the DB where the data is coming from( I didn't design it), there is a column labeled LastUpdateWhen and it is a DateTime column. Would something like that work?
    If that column is populated with the current date and time when a record is inserted or updated then you can filter on that column to exclude any records that you have already retrieved unless they have been updated since. You would normally want to refresh updated records but not necessarily always. If you do retrieve updated records then you'd have to update the item in the ListView accordingly. If you were to ditch the ListView and use a DataGridView then you could simply bind a DataTable to it and refresh that each time, which would refresh the grid automatically.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    jmcilhinney,
    So I was just gonna go with coding the SQL SELECT statement directly but I keep getting errors no matter how I change things. Here is what I'm doing:

    Dim incnuarray As ArrayList = New ArrayList()
    Do While SQLDR.Read = True
    incnuarray.Add(SQLDR("IncNo").ToString)
    Loop
    I think I am doing something wrong when adding numbers to the array.

    I can display the numbers in a text box using either the Environment.NewLine or just separate them via a "," but I keep getting either "The multi part identifier"Systems.Collections.ArrayList" Could Not Be Bound, or I get a syntax error ,),


    Thanks.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,517

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    An ArrayList is a bit odd, and is out of date... you should generally be using a List(Of T) instead, eg:
    Code:
    Dim incnuarray As New List(Of String)
    Do While SQLDR.Read = True
       incnuarray.Add(SQLDR("IncNo").ToString)
    Loop
    I used the data type String as your loop uses .ToString on the value, but it may be more appropriate for you to change to a numeric data type instead (perhaps Integer), along with an appropriate conversion for it instead of .ToString (perhaps CInt() ).

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,370

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    I don't see how you would be getting the error message you provided from the code you provided. That error must be being generated elsewhere, so that would be the code you need to show us.

    Also, does the IncNo column really contain strings? You were talking about numbers earlier, so surely you should be using a numeric data type.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    si_the_geek, thanks for the insight, I have updated my code to use List(of) as you suggested and it appears to be working ok.
    jmcilhinney, yes the column properties for my IncNo column is nvarchar

    Now that I have the IncNo in the List I want to compare them to the column in my listview that holds numbers as well. Basically as I stated earlier just compare and if the number in my List(of) is found to already be in the listview just skip over it and keep going on with my code.

    Basically I have a timer that fires off every so many seconds and reads a table in the DB, if any rows have the matching ID them rows are selected and the information is what I build my listview with. Once I have a row of information I do not need it be accessed again, I want it to stay until the user clears it off. That is why i need to compare the IncNo column, that way if that IncNo is already in my listview the program just goes on. Hope it is making sense and as always thanks for the insight!!!

    Matt

  12. #12

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    Ok, so I'm still looking for some help on this one. I will post some code below. Again what I'm trying to do is compare the values from the two list and if values match just skip over adding the row of data to my listview again. I realize I could just clear the listview but I want the data to stay in the listview until the user clears it. I have been trying to experiment with multiple For Each loops but still cant get the results I'm looking for. Which leads me to wonder could I add the values in my list as Parameters to pass in SQL statement so it just skips the rows when returning the data. Anyway any help would be appreciated. Let me know if you need more info.

    Thanks.
    Matt Griffin
    VS 2010

    Code:
       'Load Incident Numbers from DB and Sort them in Reverse Order to Match LV Items Incident Numbers
            For Each DR As DataRow In SQL.DBDT.Rows
                incnulist.Add(DR("IncNo"))
            Next
            incnulist.Reverse()
    
            'Exit Sub if no incident #'s are found
            If incnulist.Count = 0 Then Exit Sub
    
            'Add Incident Numbers From the Listview
            For Each item As ListViewItem In ListView1.Items
                lvinculist.Add(item.SubItems(3).Text)
            Next
    
            'Compares the Incdent Numbers and skips calls already in the listview
            For Each inum As String In incnulist
                For Each lvinum As String In lvinculist
                    If ListView1.Items.Count = 0 Then
                        GetActiveCalls()
                        Exit For
                    ElseIf lvinum.ToString = inum.ToString Then
                        skip = True
                    Else
                        skip = False
                        GetActiveCalls()
                    End If
                Next
            Next
    Last edited by si_the_geek; Dec 19th, 2017 at 02:55 PM. Reason: added Code tags

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,517

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    When you post code please put it inside code tags so it is displayed in a more readable way - either using the Code/VBCode buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code] (I have added them to your post)


    If I have understood your code properly (including the bits you didn't show), then altering the last part of that code to the following should do it:
    Code:
            'Compares the Incdent Numbers and skips calls already in the listview
            For Each inum As String In incnulist
                skip = False
                For Each lvinum As String In lvinculist
                    If lvinum.ToString = inum.ToString Then
                        skip = True
                        Exit For
                    End If
                Next
                If skip = False Then 
                    GetActiveCalls()
                End If
            Next
    The changes here are based on: if "ListView1.Items.Count = 0" is true then there will be no items in lvinculist so the inner loop wont run, and in order to see if an item is not in a list you need to check if it is in any position before deciding.

    Note however that as I don't know the details of GetActiveCalls, I can't tell if this will make it work.

  14. #14

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    Si,
    Will do on any code I add in the future, sorry about that.

    GetActiveCalls() is the procedure that runs on the DB and puts my data into my listview.

    I edited my code as you show but the values in lvinum.tostring are not moving to the next value in the list. The inum.tostring values move to the next value in the list. i know I'm missing something somewhere????

    Thanks.

  15. #15
    Addicted Member
    Join Date
    Sep 2017
    Posts
    239

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    I do this sort of thing in a few applications. Elaborating on JMC's recommendation (I had some time to kill before leaving for the day) Here is a somewhat elaborate way to do just what he is suggesting, whereas this could be simplified. Also I dont use storedprocs nor listviews.

    Code:
    Option Strict On
    Imports System.Data.SqlClient
    Public Class CheckRows
        WithEvents DBTimer As New Timer With {.Interval = 1000}
        Dim DSET As New DataSet
        Dim LastDate As DateTime = Nothing
    
        Private Sub CheckRows_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using Conn As New SqlConnection("Data Source=**\*;Initial Catalog=***;Integrated Security=True")
                Conn.Open()
                Dim RDR As SqlDataReader = New SqlCommand("SELECT * FROM TestTable ORDER BY LastModify DESC", Conn).ExecuteReader
                DSET.Tables.Add(New DataTable With {.TableName = "TestTable"})
                DSET.Tables("TestTable").Load(RDR)
                If DSET.Tables("TestTable").Rows.Count > 0 Then
                    If Not IsDBNull(DSET.Tables("TestTable")(0)("LastModify")) Then
                        LastDate = Convert.ToDateTime(DSET.Tables("TestTable")(0)("LastModify"))
                    End If
                End If
                DataGridView1.DataSource = DSET.Tables("TestTable")
                Conn.Close()
            End Using
            DBTimer.Start()
        End Sub
    
        Private Sub DBTimerTick() Handles DBTimer.Tick
            Using Conn As New SqlConnection("Data Source=**\*;Initial Catalog=*;Integrated Security=True")
                Conn.Open()
                Dim TempRDR As SqlDataReader = New SqlCommand("SELECT TOP 1 * FROM TestTable ORDER BY LastModify DESC", Conn).ExecuteReader
                Dim TempTable As New DataTable
                TempTable.Load(TempRDR)
                If TempTable.Rows.Count > 0 Then
                    If Convert.ToDateTime(TempTable(0)("LastModify")) > LastDate Then
                        DSET.Tables("TestTable").Rows.Clear()
                        Dim RDR As SqlDataReader = New SqlCommand("SELECT * FROM TestTable ORDER BY LastModify DESC", Conn).ExecuteReader
                        DSET.Tables("TestTable").Load(RDR)
                        LastDate = Convert.ToDateTime(TempTable(0)("LastModify"))
                    End If
                End If
                Conn.Close()
            End Using
        End Sub
    
        Private Sub Button_AddRow_Click(sender As Object, e As EventArgs) Handles Button_AddRow.Click
            Using Conn As New SqlConnection("Data Source=**\*;Initial Catalog=**;Integrated Security=True")
                Conn.Open()
                Dim CMD As New SqlCommand("INSERT INTO TestTable (Col1,LastModify) VALUES (@Col1, @LastModify)", Conn)
                CMD.Parameters.Add("@Col1", SqlDbType.VarChar).Value = "TestData"
                CMD.Parameters.Add("@LastModify", SqlDbType.DateTime).Value = Now
                CMD.ExecuteNonQuery()
                Conn.Close()
            End Using
        End Sub
    End Class

  16. #16

    Thread Starter
    New Member
    Join Date
    Nov 2017
    Posts
    9

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    si_the_geek,
    After working some more and re reading, your post of the corrected code did work!!!! I had my "Next" in the wrong place and just discovered this morning. Thank you.

    Matt Griffin

  17. #17
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,350

    Re: Reading Numbers In an Array and evaluating them against a number in a column

    kpmc is close to what I was thinking.... but I'd make a couple changes...Specifically since you have LastModify, use it to get only the rows that have modified since the last pull, and only add those rows, rather than re-loading everything.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.