Posts by Reverend Jim

If you set up a specific folder for files to process then users can deposit completed files into that folder. Once you have processed a file you can move it out of that folder and archive or delete it. Sometimes you just have to train the users to provide data in the proper format in a file with a properly formatted name. I find a cattle prod works well.

I created a DataGridView control with 2 columns and added a few records, then set row 2 to read only and modified the background colour with the following.

Private Sub btnPopulate_Click(sender As System.Object, e As System.EventArgs) Handles btnPopulate.Click

    grdView.Rows.Add({"George", "Jones"})
    grdView.Rows.Add({"Fred", "Flintstone"})
    grdView.Rows.Add({"John", "Franks"})
    grdView.Rows.Add({"Arthur", "Green"})

    grdView.Rows(2).ReadOnly = True

    For Each cell As DataGridViewCell In grdView.Rows(2).Cells
        cell.Style.BackColor = Color.LightGray
    Next

End Sub

I would put the reorder test in a separate block. You seem to have a handle on the database part so I will describe it in pseudo-code

'select all records where the inventory count has reached or exceeded the reorder limit

select * from Product where Inventory <= ReOrderLimit

'if any items have reached the reorder limit then send an email notification

if recordcount > 0 then

    create new email

    for each record in recordset
        add inventory item to email message
    next

    send email

end if    

Does anybody know how to implement parallel.for for summing up multidimensional array.

So you want one number which is the sum of all of the numbers in a three dimensional array. Why do you have it inside another loop? If the containing loop is running for 5000 iterations then you are going to calculate the sum 5000 times. The only reason I can imagine for putting it inside that loop is if the numbers in the 3D array are changing over the 5000 iterations in which case you can't use a separate thread (parallel for) to calculate the sum. If the values in the 3D array are independent of the loop then you could take the summation code and put it in a separate thread. I have never done threads so I am a little lacking in expertise in this area.

If I am missing something then you will have to be clearer on the definition of the problem.

Then do what I suggested and declare the Button variables at the class level and use Rbut.Enabled = False and it should work. I do it frequently.

I created a listview with three columns for FirstName, LastName and Age. To insert all rows of the listview into mytable I could code

For Each row As ListViewItem In ListView1.Items
    Dim query As String = "insert into mytable (FirstName,LastName,Age) Values(" &
                          "'" & row.SubItems(0).Text & "'," &
                          "'" & row.SubItems(1).Text & "'," &
                                row.SubItems(2).Text &
                          ")"
    'execute query here
    Debug.WriteLine(query)
Next

Naturally, you would execute the query instead of writing the query to the debug output. This code does no error checking (Try/Catch) for possible problems like duplicate records, etc. Note that in the database, the first two fields would be stared as strings and the third as a number. That is why the third field does not have single quotes around it in the query.

Can you right click on the folder then select "Take Ownership"? I have safely deleted a number of these folders on my D and E drive with no problem.

If I might add my recommendation:

My laptop has two internal hard drives. The first is partitioned as C (60 gig) and D (400 gig) (approximately). I have Windows 7 Pro and all my applications installed on C. I store no data on C that I am not willing to lose. I use Acronis to iimage my C partition. Acronis is a commercial product but I understand that Macrium Reflect comes in a free version and has been recommended by several sites. This is my process for setting up.

• Wipe C partition
• Install and configure Windows 7 (activate)
• Install all driver software
• Install all Windows/driver updates
• Install disk imaging software
• Create basic system image
• Install anti-virus
• Install most used applications
• Apply all outstanding anti-virus/application updates
• Freeze Windows updates (details below)
• Install and run CCleaner to remove all temporary files
• Create final system image

At this point you have two images. The basic system image is your "I want to go back to a completely clean system. This one likely won't be used much. The second image is the important one. At some point you may find yourself infected with a trojan/virus/etc. Or your system may just be running slow. I find Windows does that after many months of use. Or you may have installed so much software that your system has gotten too cluttered. As long as you have kept all of your data on another drive (D, for example) it is a simple matter to refresh …

A ListView (in details view) consists of a collection of items (or rows) where each item contains subitems (or columns). In the following example I have defined ListView1 as containing 6 columns. If I add some sample data as follows

For row As Integer = 1 To 6
    Dim newitem As New ListViewItem(CStr(row))
    For col As Integer = 1 To 5
        newitem.SubItems.Add(CStr(row * col))
    Next
    ListView1.Items.Add(newitem)
Next

The next code block multiplies column 4 by column 5 and places the result in column 6 (because subitems are zero relative the calculation is actually col5 = col3 * col4)

For Each row As ListViewItem In ListView1.Items
    Dim arg1 As Integer = CInt(row.SubItems(3).Text)
    Dim arg2 As Integer = CInt(row.SubItems(4).Text)
    row.SubItems(5).Text = CStr(arg1 * arg2)
Next

However, you may not already have data in the last column. In the following code I am only going to add data in the first 5 columns. In that case, the calculation will have to add a new subitem to store the results.

For row As Integer = 1 To 6    
    Dim newitem As New ListViewItem(CStr(row))
    For col As Integer = 1 To 4
        newitem.SubItems.Add(CStr(row * col))
    Next
    ListView1.Items.Add(newitem)
Next

For Each row As ListViewItem In ListView1.Items
    Dim arg1 As Integer = CInt(row.SubItems(3).Text)
    Dim arg2 As Integer = CInt(row.SubItems(4).Text)
    row.SubItems.Add(CStr(arg1 * arg2))
Next

I hope this is what you are looking for.

You said you wanted to disable it, not remove it. If you want it to disappear I suggest you use the "Visible" property. This will effectively remove it from the form but leave it available in case you want to restore it. If you really want to remove it then

Me.Controls.Remove(Rbut)

will do it

Can you give us something specific like the type of the array, the number of dimensions, perhaps some sample data and the results you are trying to generate from that data?

Rbut.Enabled = False

Just make sure that Rbut has the proper scope (ie don't declare it in the sub that creates it)

Public Class Form1

    Public Rbut As Button
    Public Lbut As Button

    etc.

If you store the player name as part of the structure then you have to do a search to find the record corresponding with a particular name. If you use a dictionary then you can get at any player by indexing with the player name.

    Structure PlayerInfo
        Dim Age As String
        Dim Score As String
        Dim datePlayed As String
    End Structure

    Private Sub btnPlayers_Click(sender As System.Object, e As System.EventArgs) Handles btnPlayers.Click

        Dim players As New Dictionary(Of String, PlayerInfo)
        Dim details() As String = "Jim,53,11209,2012-06-02".Split(",")

        Dim info As New PlayerInfo
        info.Age = Trim(details(1))
        info.Score = Trim(details(2))
        info.datePlayed = Trim(details(3))
        players.Add(Trim(details(0)), info)

    End Sub

To refer to the stats for a particular user you can do

    MsgBox(players("Jim").Age)

You can check if a player exists by

    If players.ContainsKey("Jim") Then

With the following data

    VendorID   VendorName
    1          Sam
    2          George
    3          Fred
    4          Orville


    VendorID   TruckID
    1          101
    2          143
    3          212
    4          971

The following query

    select tblvendor.VendorName,tblvendor.VendorID,tblbill.truckid
      from tblbill inner join tblvendor 
        on tblbill.VendorID = tblvendor.VendorID

produces the following output

        VendorName   VendorID   TruckID
        Sam          1          101
        George       2          143
        Fred         3          212
        Orville      4          971

OK. I think I have restored my original reply. Now perhaps my second post will make more sense.

I see that I totally screwed up my previous post. It is gone and I'll try to get it back.

Any database access will be awkward and inefficient depending on how the data is to be accessed. Databases are typically organized to be the most efficient for the most likely access. In the case of a database with millions of records this is going to be a consideration. For the given application I doubt that this will be a consideration. Given that, I think the most reasonable organization is the one that I have given. It maps to what I think is the most likely display, a table showing the employee name/id, sign in time and sign out time.

It is also more suitable for casual scanning of the database using something like SQL Server Management Studio where a simple "select * from Attendance" shows all of the data.

First a few comments.

• It is a bad idea to have field names that contain spaces.
• it is preferable (for sorting) to keep both the date and time in one field
• What is to prevent an employee from signing in under another employee's number?

To address the first two points I suggest a table structure like

Attendance
    EmployeeNo        Int        Not Null
    SignIn            DateTime   Not Null
    SignOut           DateTime   Null

The primary key can be a combination of the first two fields. The first two fields cannot be null, first of all because you can't have a null key and second of all because when you insert the record you will know the value of both fields. The last field must allow nulls because you can't supply the value until the employee logs out.

To address the last point you would need an employee table that contains the names of all employees and their employee numbers (primary key). You would also need a password table which contains the employee number (primary key) and their password. To login to the app they would have to provide their password.

Based on my assumptions, the query on signin would be something like

Dim query As String = "insert into Attendance (EmployeeNo, SignIn) Values("
                    & empno & ",'" & Now() & "')"

where empno is the number selected from the combobox. If the attendance "sheet" is being maintained by someone other than the employee then a password is not required.

As I said, as long as you use a lock (I suggest LockPessimistic) there should be no problem. When you use a lock, the recommendation is to have the lock for only as long as is absolutely necessary.

A query to retrieve a job name where status = "FOR KE" will return (using your data) five records. In your example, "get" is ambiguous. Do you mean "get a record from the database" or do you mean "generate the next available job name"?

Let's assume you mean "generate". In that case, the query that will create the new record with the new job name should be done using a lock type of LockPessimistic. This will prevent two concurrent insertions.

I've been watching The Firm and I had to laugh when the hacker was trying to decrypt the hard drive and he claimed that "parts of it were coming into focus". Also the comment that a particular hacking tool could get you 90% of the way to determining an encryption key. That's sort of like being able to get your wife 90% pregnant.

If I understand you, you want to select everything between "[" and "]". If that is the case then you can select just that text by

Dim ss As Integer = InStr(RichTextBox1.Text, "[")
Dim se As Integer = InStr(RichTextBox1.Text, "]")

RichTextBox1.SelectionStart = ss
RichTextBox1.SelectionLength = se - ss - 1

MsgBox(RichTextBox1.SelectedText)

But for this to work you must have "[" and "]" in the text. If you don't it will fail.

I think the OP is saying that if (for example) Buffalo has already been added to the listview then it should not be added again.

Unfortunately, there is no easy method to use for this. The Contains and ContainsKey (or even Find) methods do not search the listview for a given string. However, you can roll your own as follows:

Private Function InListView(lvw, text) As Boolean

    For Each item As ListViewItem In lvw.items
        If item.Text = text Then Return True
    Next

    Return False

End Function

If you pass this function a listview control and a string it will return True if that string appears in any cell in the first column, False otherwise. For example

Dim found As Boolean = InListView(MyListView, "Buffalo")

You can access individual cells in a datagridview via the rows and cells properties. For example, to clear the cell at (2,2) you would do

DataGridView1.Rows(2).Cells(2).Value = ""

There are some useful constants defined in VB. Three of them are vbCr, vbLf and vbCrLf where Cr = carriage return and Lf = line feed. For the following example I created a RichTextBox control then pasted in a few paragraphs. Double clicking on any paragraph will select that paragraph. It does this by starting at the current cursor position (set when you click in the control), scanning backwards for the previous vbLf (or start of text), then scanning forward for the next vbLf (there will always be one because I add one to the end of a local copy of the text.

    Private Sub RichTextBox1_MouseDoubleClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles RichTextBox1.MouseDoubleClick

        'scan backwords from the cursor position to either the start of text or the start
        'of a paragraph (vbLf)

        Dim cp As Integer = RichTextBox1.SelectionStart     'current cursor position
        Dim ss As Integer = cp                              'selection start        
        Dim se As Integer = cp                              'selection end          

        Dim text As String = RichTextBox1.Text & vbLf       'note the added vbLf

        'find the previous and next line feed characters relative to the cursor position

        ss = InStrRev(text, vbLf, cp) + 1
        se = InStr(Mid(text, ss), vbLf) + ss

        'select the paragraph

        RichTextBox1.SelectionStart = ss - 1
        RichTextBox1.SelectionLength = se - ss

    End Sub

The datatype should not be varchar. Storing numbers as numbers allows you to to calculations, perform aggregate functions and to compare and sort results correctly. If you store numbers as strings you lose all that functionality. Plus, using strings will likely use more storage. It's easy enough to format the data for display after retrieval from the database.

You can check to see if a record exists by doing a SELECT on the primary key then checking to see if a record was returned. If the query returns a record then you can modify it using an UPDATE query. If a record is not returned then you can do an INSERT query.

I've done a ton of vbScript (most of my apps at work from 2000-2008 were glue, data mining and system maintenance apps) and I used ON ERROR RESUME NEXT a lot. Most of my apps ran unattended and it was not acceptable for them to abort. However, I would not recommend it for VB apps. This may be against the general concensus but my recommendation is to use code to test for possible errors prior to doing an operation where possible and use TRY-CATCH sparingly. For example, if you are planning to do a numeric operation on the contents of a textbox, my preference would be to do something like

If IsNumeric(txtMycontrol.text) Then
    'do regular stuff
Else
    'do error stuff
End If

With regular expressions and such it is fairly simple to validate textboxes to ensure the text is valid. For operations that are more difficult to pre-validate (database operations, for example) I would use a TRY-CATCH.

But that's just my preference.

ON ERROR RESUME NEXT is a holdover from the days before TRY-CATCH. It is still used in vbScript where the TRY-CATCH is not available. It was the only way to prevent your program from crashing on an error and typically resulted in endless error checking tests (ugly) such as

on error resume next
avg = total / numitems

If err.Number <> 0 Then
    'error processing code
    err.Clear   ' maybe
End If

You could still unzip it and look at the vb files. In any case here is a simple example I just coded up for you. I hope this helps. Please remember to mark this thread as solved when yoou get what you need. Feel free to ask any follow up questions.

'         
'  Name:  
'         
'    DynamicTextBoxes  
'                      
'  Description:        
'                      
'    Simple example of how to create and use textboxes at run time  
'        
'  Audit
'        
'    2012-05-19  rj 
'                   

Public Class Form1

    Private boxes(5) As TextBox

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim newbox As TextBox

        For i As Integer = 1 To 5

            'create a new textbox and set its properties

            newbox = New TextBox
            newbox.Size = New Drawing.Size(100, 20)
            newbox.Location = New Point(10, 10 + 25 * (i - 1))
            newbox.Name = "TextBox" & i
            newbox.Text = newbox.Name

            'connect it to a handler, save a reference to the array and add it to the form controls

            AddHandler newbox.TextChanged, AddressOf TextBox_TextChanged
            boxes(i) = newbox
            Me.Controls.Add(newbox)

        Next

    End Sub

    Private Sub TextBox_TextChanged(sender As System.Object, e As System.EventArgs)

        'when you modify the contents of any textbox, the name of that textbox and
        'its current contents will be displayed in the title bar

        Dim box As TextBox = DirectCast(sender, TextBox)
        Me.Text = box.Name & ": " & box.Text

    End Sub

End Class

There is a simple way to add multiple textboxes to a form at runtime and store references to them in an array so that they can be accessed by index. I created an example a few months ago to demonstrate this technique using buttons but you can easily use any other type of control. The technique basically boils down to

1) create an array of the given control to hold the references
2) code a loop to create the controls in which you
3) create a new control
4) set the control properties (location, size, etc)
5) assign a handler to handle events for that control
6) add the control to Me.Controls
7) assign the control to your array

I've attached the RunTimeButtons project. The constant SQSIZE defines the square size of the button array. SQSIZE = 5 will generate a 5x5 grid of buttons. For more options on laying out dynamic controls you can use the TableLayoutPanel control.

It's not a query problem. It's a data problem. You are trying to insert a record with a primary key that already exists. It's like trying to store two different values into an array with the same index, except that in the array mode you just end up overwriting the existing value. At least SQL is nice enough to prevent you from doing that.

Glad I could help. Please remember to flag this thread as solved.

The easiest way to modify Excel from VB is probably by using the Excel Application object. To do this,

1) start a new project
2) add a reference to "Microsoft Excel ##.# Object Library"
3) add "Imports Excel = Microsoft.Office.Interop.Excel" at the top of your code

You create the Excel object by

Dim xls As New Excel.Application

Open an existing workbook by

xls.Workbooks.Open("d:\my documents\points-jim.xls")

Create a reference to a particular sheet (for convenience) by

Dim sheet As Excel.Worksheet
sheet = xls.Workbooks(1).Worksheets(1)

You can access individual cells as

Dim row As Integer = 1
Dim col As Integer = 1

MsgBox(sheet.Cells(row, col).value)

I've attached a sample project which exports the data in a ListView control to an Excel spreadsheet. That should get you started.

Please reread my original response. Taken out of context I have no idea what PERSON_EXCEL_DATA and other objects are. Some more details on what you are trying to do would help.

How are you opening/modifying/saving the data? Please post your code so we can tell you where you are going astray. Are the duplicate rows being inserted or are they being appended to the worksheet?

If you set the forms KeyPreview property to True you can use the following code

Public Class Form1

    Private Sub Form1_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles Me.KeyPress

        If Me.ActiveControl.GetType = GetType(TextBox) Then

            Me.Text = e.KeyChar

            Select Case e.KeyChar
                Case "0" To "9"
                Case Chr(Keys.Back)
                Case Else
                    e.Handled = True
            End Select

        End If


    End Sub

End Class

This will restrict all textboxes on the form to 0-9 and backspace. If you need to add non-affected textboxes you can always set the Tag property on the restricted ones and test for that.

Dim tokens() As String = "17:55:88".Split(":")

This will give you an array with indices from 0 to 2 where

tokens(0) = "17"
tokens(1) = "55"
tokens(2) = "88"

If there is a mathematical formula that generates the sequence 0, 12, 345, 6789, ... then you can use it. Otherwise you can use

    Dim numberOfPasses As Integer = 3 'you would likely get the number from a control

    For Each s As String In {"0", "12", "345", "6789"}
        numberOfPasses -= 1
        if numberOfPasses < 0 Then Exit For
        label1.Text = s
    Next

Although I feel I should point out that there is no reason to do this in a loop. What is the point of "writing" each iterated value into the textbox when the next iteration will just overwrite the previous value? It would all happen so quickly that the previous values would never be seen. You'd be better off storing the numbers 0, 12, etc in an array, then indexing into that array with the user input "level" number (of course, checking beforehand to ensure that the user selected value falls within the array).

You could do

    For Each s As String In {"0", "12", "345", "6789"}
        Debug.WriteLine(s)
    Next

The following code might do it

    Private Function CheckPassword(password As String) As String

        If Len(password) < 8 Or Len(password) > 15 Then Return "not 8-15 chars"
        If Not Regex.IsMatch(password, "[a-z]") Then Return "no lower case letters"
        If Not Regex.IsMatch(password, "[A-Z]") Then Return "no upper case letters"
        If Not Regex.IsMatch(password, "[0-9]") Then Return "no digits"
        If Regex.Matches(password, "[!,@,#,$,%,^,&,*,?,_,~,-,L,(,)]").Count > 1 Then Return "more than one special char"

        Return "OK"

    End Function

    Private Sub txtTestStr_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles txtTestStr.Validating

        Dim result = CheckPassword(txtTestStr.Text)

        If result = "OK" Then
            e.Cancel = False
        Else
            MsgBox(result)
            e.Cancel = True
        End If
    End Sub

I don't generally like doing things this way because it locks the user into a field until the criteria are satisfied. An alternative would be to add a label next to the password entry field and modify the text in the label to reflect the current password status (could be updated on each keystroke).

How about this then

Imports System.Text.RegularExpressions

Public Class Form1
.
.
.
    Private Function ValidPassword(password As String) As Boolean

        'checks for
        '
        '  invalid length
        '  no lower case letters
        '  no upper case letters
        '  no digits
        '  more than one special character

        If Len(password) < 8 Or Len(password) > 15 Then Return False 'invalid length
        If Not Regex.IsMatch(password, "[a-z]") Then Return False 'no lower case letter
        If Not Regex.IsMatch(password, "[A-Z]") Then Return False 'no upper case letter
        If Not Regex.IsMatch(password, "[0-9]") Then Return False 'no digit
        If Regex.Matches(password, "[!,@,#,$,%,^,&,*,?,_,~,-,L,(,)]").Count > 1 Then Return False 'too many special case

        Return True

    End Function
.
.
.
End Class

If you are using a recent version of SQL server (2005 or newer) you can use the new ranking functions. Your specific query to generate the above results would be

SELECT TOP 5 RANK() OVER (ORDER BY IAVERAGE) AS Rank,
       USERNAME,ICOUNT,IAVERAGE,DATE_LOGGED
  FROM MyTable   
 WHERE IAVERAGE > 0 AND DATE_LOGGED='05-07-2012'

RANK() will generate a ranking number which will repeat for equal averages (all IAVERAGE numbers that are equal will have the same ranking number). If you wanted to further subdivide the rankings, you could always include a PARTITION clause as follows (let's assume you have a field named CLASSNUM)

SELECT TOP 5 RANK() OVER (PARTITION BY CLASSNUM ORDER BY IAVERAGE) AS Rank,

For this result set, the ranking number would be reset to 1 for each class.

What do you mean by a special case?

8-15 lower case letters only

^[a-z]{8,15}$

More complete - also write an Excel file:

Imports ADODB
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset
        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Add()
        sheet = xls.ActiveWorkbook.ActiveSheet

        con.Open("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\temp\asset.xls;")
        rec.Open("select * from [Sheet1$] where [Name] = 'Rahul'", con, CursorTypeEnum.adOpenStatic)

        Dim row As Integer = 0

        Do Until rec.EOF
            row += 1
            sheet.Cells(row, 1) = rec("Name").Value
            sheet.Cells(row, 2) = rec("Emailid").Value
            sheet.Cells(row, 3) = rec("Asset").Value
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

        xls.ActiveWorkbook.SaveAs("D:\temp\test.xls")
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(xls)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

End Class

I'm going to post the first part regardless. It may be better than your solution or it may be worse.

Imports ADODB

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset

        con.Open("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\temp\asset.xls;")
        rec.Open("select * from [Sheet1$] where [Name] = 'Rahul'", con, CursorTypeEnum.adOpenStatic)

        Do Until rec.EOF
            Debug.WriteLine(rec("Name").Value & " " & rec("Emailid").Value & " " & rec("Asset").Value)
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

    End Sub

End Class

I'll have a look in a bit. I have to go pound nails for a while. To read the data you don't need interopt. You can do it using ADO and treating the source as a database. Should also be possible via oledb. I'll have a look later. If you don't want to use interopt to write, you could always write the output as a csv file which can be opened by Excel. Either way works.

What you are describing is more of an irregular expression. Your character classes need not be in any particular order. I believe you are far better off handling the validation in code. This will be clearer and more maintainable. Also, your requirements appear to be in conflict

atleast one uppercase character

and

only lower case characters

You can't have both. Also, a minimum must be a specific number. It cannot be a range. You can have a minimium of 8 but not of 8-15. I presume you mean the length must be between 8 and 15 characters.

Can you post an excel file with some sample data which causes the problem you are describing?