Posts by Reverend Jim

I should have thought of this earlier. If the listbox items are unique then we can just use the listbox item as the dictionary key. Let me do a quick rewrrite and get back to you.

Try this code and see how it compares to yours

Public Class Form1

    Public ctrlArray As New Dictionary(Of Integer, PropertyGrid)
    Public currgrid As PropertyGrid

    Private Sub btnAddItem_Click(sender As System.Object, e As System.EventArgs) Handles btnAddItem.Click

        'create new listbox item and associated property grid

        Dim newitem As String = "item " & ctrlArray.Count
        Dim newprop As New PropertyGrid

        newprop.Location = New System.Drawing.Point(140, 13)
        newprop.Size = New System.Drawing.Size(300, 260)
        newprop.Visible = False

        'add the new property grid to the control array, then add new listitem.

        Me.Controls.Add(newprop)
        ctrlArray.Add(ctrlArray.Count, newprop)
        ListBox1.Items.Add(newitem)

    End Sub

    Private Sub ListBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

        'hide the current visible grid

        If Not currgrid Is Nothing Then
            currgrid.Visible = False
        End If

        'make the newly selected grid visible

        currgrid = ctrlArray(ListBox1.SelectedIndex)
        currgrid.Visible = True

    End Sub

End Class

Just start with a button and listbox and nothing else.

Is it possible to zip the entire project and attach it to your post so I can see the whole thing and look at it in the IDE on my PC?

If you are getting that error it is because you haven't assigned a PropertyGrid instance to ctrlArray(j). When you get that error in the IDE, check the calue of ctrlArray(j) and also (j-1) and see if you are "off-by-one". If all of ctrlArray is unassigned then check where you do the assignment to see if you are doing it properly. If you haven't incremented the index properly then perhaps all your assignments are going to the same index.

I don't have access to a GridView class in VB 2010. What I suggest is to run the app in the IDE and when it stops on the line with the error, put a watch on rw1 and look for the property containing the cell value. Then change your code to use that property instead of "Value".

Declare ctrlarray at the class level as

Public Class form1
 
    Public ctrlArray() As PropertyGrid

Does your code work now?

In line 8 you have

listBox1.SelectedIndex = count - 1

But you execute this before you have added the new control to the control array. Changing the SelectedIndex triggers the event before you have added the control so when you try to access it in the array it is not there yet. Try creating the control and adding it to the array before you add the listbox entry and change the selected index.

--- first sip of morning coffee ---

On rereading I see it's complaining about index 0. Before I have a deeper look I have a question. Isn't it the new panel you want to bring to the front in which case the statement should be

ctrlArray(i).BringToFront()

instead of

Panel1.Controls.Item(i).BringToFront()

You don't need to have two separate loops depending on the relative number of rows. You can do it in one loop as

Private Sub btnCompare_Click(sender As System.Object, e As System.EventArgs) Handles btnCompare.Click

        For Each rw1 As DataGridViewRow In DataGridView1.Rows

            For Each rw2 As DataGridViewRow In DataGridView2.Rows
                If rw1.Cells(0).Value = rw2.Cells(0).Value Then
                    If Not rw1.Cells(0).Value Is Nothing Then
                        MsgBox(rw1.Cells(0).Value & "  found in grid 2")
                        Exit For
                    End If
                End If
            Next

        Next

    End Sub

I'm using Visual Studio 2010 so I have to use DataGridView instead of GridView but the method should be the same. The test for

If Not rw1.Cells(0).Value Is Nothing Then

is to prevent a match on the empty last row in the two grids.

Surely possible. Let's say you have exactly 832 items in the listbox (or listview). That means the first item is accessed by

ListBox1.Items(0)

and the last item by

ListBox1.Items(831)

Where 831 is ListBox1.Items.Count - 1. If you have a class level index (let's call it currindex) then it is initialized to 0. Once you start your timer then the timer handler will

increment currindex

'if past the end of the list then wrap back to the first item

if currindex = ListBox1.Items.Count then
    currindex = 0
end if

copy text from ListBox1.Items(currindex) to the textbox

I find it almost always helps if you write out the problem steps in English (or whatever language suits you) and only then translate that into actual VB code. If you leave your pseudo-code in place you can just prefix all lines with ' and they become your comments in the code.

Here's a sample project using Excel

You'd have to have Excel installed otherwise you won't have access to the Eccel objects. I don't think they get installed if you just have (for example) Microsoft Word. Another option would be to export as a CSV file. If you do that then I recommend ensuring that all fields are enclosed in " as in

"fld1","fld2","fld3"
etc

That's to prevent problems if any fields contain a comma. You could modify the code slightly and replace the Excel parts with code to generate the text strings.

Try this

Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click

        SaveFileDialog1.Title = "Save Excel File"
        SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xslx"
        SaveFileDialog1.ShowDialog()

        'exit if no file selected

        If SaveFileDialog1.FileName = "" Then
            Exit Sub
        End If

        'create objects to interface to Excel

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        'create a workbook and get reference to first worksheet

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

        'step through rows and columns and copy data to worksheet

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

        For Each item As ListViewItem In lvwToExport.Items
            For i As Integer = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next

        'save the workbook and clean up

        xls.ActiveWorkbook.SaveAs(SaveFileDialog1.FileName)
        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

It is certainily possible to create an array of controls. There are several ways to keep track of them. You could use a dynamic array or a dictionary (to name two). Just declare the array as follows

Dim ctrlarray(ListView1.Items.Count - 1) As PropertyGrid

You create a new instance as

ctrlarray(i) = New PropertyGrid

If you need to handle events for it you will have to use AddHandler to connect the Sub to that instance. The first Dim assumes that the number of items in the listview does not change (or at least does not increase). If the number rof items is dynamic you will have to declare the initial size as () and use ReDim as items are added. Another option is to use a dictionary as in

Dim ctrlarray as New Dictionary(Of Integer, PpropertyGrid)

And you reference properties and methods of the array items as

ctrlarray(i).etc

What am I missing here? I'm seeing a lot of brute force code to simply export a listview. What are you trying to export the listview to? What viewmode is the listview in? I've written a few examples of exporting listviews and gridviews to Excel spreadsheets and none of them used brute force. If you are asking what I think you are asking I should be able to help you out but I need more details.

You need a timer. You will also need a class variable to keep track of which item in the listbox is the next one to copy. When it gets to the last item the index will be reset to the first item. You don't need a loop. The iteration will be handled on each tick of the timer. If for any reason you do not want to create a class variable for the index you could alwyas store it in the Tag property of the listbox or textbox control.

Another possibility (fewer layers) is to use ADO as in

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

con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
rec.Open("select count(*) from admin where username='John'", con, CursorTypeEnum.adOpenForwardOnly)

If rec(0).Value = 1 Then
    'record exists
Else
    'record does not exist
End If

rec.Close()
con.Close()

A table should have a primary key defined. In the above example, the primary key is "username". Any attempt to add a duplicate username will result in an error. Because the primary key is unique, you only need to check that field to determine whether or not you can add a new record. You could easily encapsulate the duplicate check in a function as in

user = "John")

If KeyExists(user) Then
    MsgBox("User " & user & " already exists in database")
Else
    'do ADD code here
End If

Glad to help. Please mark this thread as solved.

That's why I suggested saving the original string in the Tag property. You don't have to worry about bogus Changed events like when the value is originally set, or if the user modifies a field, then undoes the mod. As long as the text is the same at the start and the end you don't really care if it was changed in between. By comparing the Tag to the Text all you have to do is

'pseudo code

changed = False

for each text control
    if control.Text <> control.Tag then
        changed = True
        exit for
    end if
next

If changed Then
    prompt to save changes
Else
    no changes made
End If

Easier and cleaner than having to add extra handlers and you can use a spare property in the control (Tag) instead of creating local variables.

It looks to me like your problem is

Public myOfficerIDFolder As String = myFilesFolder & "" & frmMain.lblID.Text & ""

You are declaring and initializing the string that contains the folder name at the class level which means that it has its value set before the form even loads (uses the default value of lblID.Text). You can declare the string at the class level, but don't assign it a value until just before you actually create the folder as in

If login is successful then    'pseudo code
    myOfficerIDFolder = myFilesFolder & "" & frmMain.lblID.Text & ""
    x.CreateIDfolder()
End If

Possibly silly question, but if you are creating the folder in the form load handler, then how can you have access to the ID number used to log in? I would assume that the user would have to log in first, and the log in area would not be available to the user until after the form load event has completed. In which case, you should create the folder just following the code that handles a successful log in.

Follow-up possibly silly question. What is the "x" in x.CreateIDfolder()? It's late here and my brain may have preceded my body to sleep.

Once the form has loaded and the text fields have been populated, you could loop through the text boxes and make a copy of the contained text in the Tag property of each control. Of form close you could compare the values of TextBox#.Text to TextBox#.Tag to look for changes.

Here is an example

Dim amin As DateTime = CDate("08:00:00")
Dim amout As DateTime = CDate("12:05:00")
Dim pmin As DateTime = CDate("13:30:00")
Dim pmout As DateTime = CDate("16:43:00")

Dim amhours As TimeSpan = amout - amin
Dim pmhours As TimeSpan = pmout - pmin
Dim total As TimeSpan = amhours + pmhours

MsgBox(total.ToString)

Here is another method that uses the NorthWind database as an example. Probably not the best solution but it IS "a" solution.

Dim con As New ADODB.Connection
con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=NorthWind;Trusted_Connection=yes;")

Dim rec As New ADODB.Recordset
rec.Open("select top 1 * from Categories", con, CursorTypeEnum.adOpenForwardOnly)

For i As Integer = 0 To rec.Fields.Count - 1
    MsgBox(rec.Fields(i).Name)
Next

rec.Close()
con.Close()

Each iteration through the loop displays the name of the next field.

Dim filename As String = "vbExcel-" & Now().ToString("yyyy-MM-dd-HH-mm-ss") & ".xlsx"
xlWorkSheet.SaveAs("C:\Users\Space Era\Documents\" & filename)

Or you could use a dictionary.

Dim ClientRecords As New Dictionary(Of Integer, BankAccountClass)
Dim AClient As BankAccountClass

'add 5 new items

For i As Integer = 1 to 5
    AClient = New BankAccountClass
    AClient.FirstName = InputBox("Enter first name", "Enter")
    AClient.LastName = InputBox("Enter last name", "Enter")
    AClient.AccountNumber = InputBox("Deposit Amount", "Bank Account Program")
    AClient.Add(i,AClient)
Next

You can index ClientRecords like an array. So you have the advantages of a list (no ReDimming) and the access of an array.

Instead of doing repeated ReDims, you could just declare a list of the required type then "Add" new items.

Dim ClientRecords As New List(Of BankAccountClass)

Dim AClient As New BankAccountClass
AClient.FirstName = InputBox("Enter first name", "Enter")
AClient.LastName = InputBox("Enter last name", "Enter")
AClient.AccountNumber = InputBox("Deposit Amount", "Bank Account Program")

ClientRecords.Add(AClient)

If you want to use a different file name then you have to generate a new name for the SaveAs. For example, you might want to generate numbers 0001, 0002, etc and append to "vbexcel" to create vbexcel0001.xlsx, vbexcel0002.xlsx, etc. Or you could generate a name like "vbexcel-yyyy-mm-dd-hh-mm.xlsx" using the current date & time. Using the date/time tag should ensure you don't have duplicate file names (but it still wouldn't hurt to check before you save). You can generate a name as

Dim filename As String = "vbExcel-" & Now().ToString("yyyy-MM-dd-HH-mm-ss") & ".xlsx"

GU58 and GU67 do not appear in my copy of tnt.xls or in my combobox.

Windows 7 Pro, Visual Studio 2010 and MS SQL Server. I didn't intend to rag on you about the names. I just thought I'd mention it. I see it wasn't necessary.

If there is the possibility of duplicate entries in the spreadsheet you might try "select distinct" and see if you get the same number of records returned. Although, as I said, I didn't see any duplicates.

You use the CONVERT function. The first parameter is the type you want to convert to (in your case, string), the seconbd parameter is the field to retrieve. The third parameter determines the format. For example

select CONVERT(varchar(50),checktime,101)

Allowable formats are

100 or 0	mon dd yyyy hh:miAM (or PM)
101	        mm/dd/yy
102	        yy.mm.dd
103	        dd/mm/yy
104	        dd.mm.yy
105	        dd-mm-yy
106	        dd mon yy
107	        Mon dd, yy
108	        hh:mm:ss
109 or 9	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	        mm-dd-yy
111	        yy/mm/dd
112	        yymmdd
113 or 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	        hh:mi:ss:mmm(24h)
120 or 20	yyyy-mm-dd hh:mi:ss(24h)
121 or 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	        yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130	        dd mon yyyy hh:mi:ss:mmmAM
131	        dd/mm/yy hh:mi:ss:mmmAM

I ran it here and the values matched exactly. No extra values that were not in the table.

You might want to ask a moderator to remove the Excel table that you attached. It looks like it contains actual user data (names etc) and they might not appreciate having their data posted. Even just posting a list of names might be an oops. Next time you might want to overwrite the names with dummy data. Just a suggestion.

The combobox will be populated with the data in column 1. When an item in the combobox is selected, the corresponding value from column 2 will be displayed in the textbox.

Private Sub ComboBox1_SelectedValueChanged(sender As System.Object, e As System.EventArgs) Handles ComboBox1.SelectedValueChanged

    txtExcel.Text = ComboBox1.SelectedValue.ToString

End Sub

Private Sub btnLoadExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadExcel.Click

    Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\temp\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
    con.Open()

    Dim cmd As New OleDb.OleDbCommand("select * from [Sheet1$]", con)
    Dim da As New OleDb.OleDbDataAdapter(cmd)
    Dim dt As New DataTable
   
    da.Fill(dt)

    ComboBox1.DataSource = dt
    ComboBox1.DisplayMember = "EMP_ID"
    ComboBox1.ValueMember = "SPIDOM"

End Sub

Just pretty decent at proofreading ;)

Also, in lines 7 & 8, "combobox" should be "combobox1"

Actually, the lines can have a variable number of fields. If the fields contain embedded commas then I suggest you do the replacement as

line = line.Replace(""",""",newdelim)    'replace "," with new delimiter
line = line.Replace("""","")             'remove leading and trailing "

Juse set newdelim to some character not used in the text

First question next

Dim lines As String() = IO.File.ReadAllLines("d:\test.txt")
Dim data(lines.Count - 1)
For i As Integer = 0 To lines.Count - 1
    Dim line As String = lines(i).Replace("""", "")
    data(i) = line.Split(",")
Next

if test.txt contains

"Item1","Item2","Item3"
"LineItem1","LineItem2","LineItem3"
"NetxItem1","NextItem2","NextItem3"

then

data(0)(0) = "Item1"
data(2)(2) = "NextItem2"
etc

This assumes

1) all lines contain 3 fields
2) no fields contain a comma

If you can't rely on #2 then you have to massage line to replace the comma with a separator that does not appear in the data.

Last question first. ReadAllLines reads the entire file into a stringt array where each element is one line of the file. ReadAllText reads the entire file into one long string.

The last poster (whose handle I can't spell this early before my coffee) is right. You'd have to preserve the data somehow in case the user just restarts the app. This could be through a database, file, or through My.Settings variables.

You could declare some variables with class scope such as

Dim numFailed As Integer            'number of consecutive failed login attempts
Dim lastAttempt As Date             'date & time of last login attempt
Dim lastUser As String              'name of last user to try to login

In the form load event, initialize numFailed to 0 and lastUser to "". When the user tries to login, the logic would be as follows

Dim canLogin as Boolean = True

if currentUser = lastUser Then
    if numFailed = 3 Then
        if less than 10 minutes since lastAttempt Then
            canLogin = False
        end if
    end if
end if

if canLogin then
    if Login(currentUser) Then
        numFailed = 0
    else
        numFailed += 1
    end if
    lastUser = currentUser
    lastAttempt = Now()
else
    MsgBox("please wait a few minutes and try again"
end if

Dim prod As String = "P011112"

Dim mm As Integer = CInt(prod.Substring(1, 2))
Dim yy As Integer = CInt(prod.Substring(3, 2))

MsgBox(MonthName(mm, False) & ", " & yy + 2003)    'output is "January, 2014"

Assumes that the digits in the string are valid for conversion to date.

Try this and see what happens. Copy the following code and save it into a file with the extension "vbs". Replace the username and password values with whatever strings you expect to find in tour admin table. Then run it from the command line like

cscript somename.vbs

If you are getting a match from your table then that tells us one thing. If you don't then it tells us another.

username = "John"
password = "aardvark"

set con = CreateObject("ADODB.Connection")
set rec = CreateObject("ADODB.RecordSet")
set out = Wscript.StdOut

con.Open "Driver={SQL Server};Server=SUSHANT-PC;Database=USB_APP;Trusted_Connection=yes;"

if con.State <> 1 then
    out.WriteLine "could not open database"
    wscript.quit
end if

rec.Open "select username,password from admin where username = '" & username & "' and password = '" & password & "'",con,1
Dump rec

rec.Close
con.Close

Function Dump ( rec )

   'display all records (with fieldnames) in the given recordset

    dim field
    dim record
    dim maxlen
    dim name
    dim valu

    Dump   = 0
    maxlen = 0

    'get the length of the longest field name for output formatting

    for each field in rec.fields
       if len(field.name) > maxlen then maxlen = len(field.name)
    next

    'dump the records
	
    do until rec.EOF

        Dump = Dump + 1

        out.WriteLine vbcrlf & "Record: " & Dump
 
        for each field in rec.fields
            name = field.Name
            valu = field.Value & ""
            out.WriteLine "  " & name & ": " & space(maxlen-len(name)) & " " & valu
        next

        rec.MoveNext

    loop

    out.WriteLine vbcrlf & Dump & " record(s)"

End Function

Incidentally, when I try your …

I don't know if this is available in MySql but it is a feature of MS SQL (and in a slightly different form, Access). The COALESCE function can be used in a sql query to replace null values with a defailt value. For example

select lastName, firstName, COALESCE(title,'n/a') from table1

returns "n/a" as the value of the field, title, where the actual value of the field is NULL. You might see if MySql has a similar feature which would allow you to specify an appropriate default date in the query.

Try this

Private fadeout As Boolean
.
.
.
Private Sub btnFadeOut_Click(sender As System.Object, e As System.EventArgs) Handles btnFadeOut.Click
    fadeout = True
    Timer3.Enabled = True
End Sub

Private Sub btnFadeIn_Click(sender As System.Object, e As System.EventArgs) Handles btnFadeIn.Click
    fadeout = False
    Timer3.Enabled = True
End Sub

Private Sub Timer3_Tick(sender As Object, e As System.EventArgs) Handles Timer3.Tick

    If fadeout Then
        If Me.Opacity <= 0.0 Then
            Timer3.Enabled = False
        Else
            Me.Opacity -= 0.01
        End If
    Else
        If Me.Opacity >= 1.0 Then
            Timer3.Enabled = False
        Else
            Me.Opacity += 0.01
        End If
    End If

End Sub

My timer interval is set to 10.

'
'  Name:
'
'    ScanLogs
'
'  Description:
'
'    Displays the most recently modified log file in the given folder that contains
'    a specified string.
'
'  Audit:
'
'    2012-02-13  name - original code
'

Public Class Form1

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

        Dim myString As String = "Splunge"                  'string to search for                   
        Dim myFolder As String = "d:\temp"                  'folder containing log files to scan    
        Dim newestfile As String = ""                       'name of most recently modified log file
        Dim newestdate As Date = CDate("january 1 1900")    'date of most recently modified log file

        'scan a folder for all log files

        For Each file As String In My.Computer.FileSystem.GetFiles(myFolder, FileIO.SearchOption.SearchTopLevelOnly, "*.txt")

            'scan file for string (False = case insensitive)

            If FileContains(file, myString, False) Then

                Dim lastmoddate As Date = My.Computer.FileSystem.GetFileInfo(file).LastWriteTime

                'if newer log file then save file name and mod date

                If lastmoddate > newestdate Then
                    newestfile = file
                    newestdate = lastmoddate
                End If

            End If

        Next

        If newestfile <> "" Then
            txtQuery.Text = newestfile
        Else
            txtQuery.Text = "not found"
        End If

    End Sub

    Function FileContains(filename As String, searchTerm As String, caseSensitive As Boolean) As Boolean

        'returns True if the given file contains the given string

        Dim text As String = My.Computer.FileSystem.ReadAllText(filename)

        If caseSensitive Then
            Return InStr(text, searchTerm, Microsoft.VisualBasic.CompareMethod.Binary) > 0
        Else
            Return InStr(text, searchTerm, Microsoft.VisualBasic.CompareMethod.Text) > 0
        End If

    End Function

End Class

It's always worth noting that a browse through available methods can turn up some interesting time savers. For …

First, some pseudo code to get the logic.

'find the most recently modified log file containing a given string

mystring   = "ERROR"
newestfile = ""
newestdate = someolddate

for each file in the folder

    if this is a log file
        if the file contains mystring
            if this file is newer than newestdate
                newestfile = file_name
                newestdate = file_modified_date
            end if
        end if
    end if

next

if newestfile <> ""
    msgbox("the most recently modified log file containing " & mystring & " is " & newestfile)
else
    msgbox("no log file found containing " & mystring)
end if

The easiest way to get a list of files in a folder is to use native VB objects. Iin this case

For Each file As String in My.Computer.FileSystem.GetFiles(foldername)

And because you only want log files, you can filter on those by

For Each file As String In My.Computer.FileSystem.GetFiles(foldername, FileIO.SearchOption.SearchTopLevelOnly, "*.log")

An obvious candidate for a separate function is the code that scans a file for a string. You could name is something like FileContains, pass it the file name and the string and have it return True or False. I'll post more in a few minutes.

I can't open your document. Don't assume everyone has Office 2010. Posting in txt format would be preferable.

The query syntax to insert a picture into SQL can be found here

Dead centre of Canada (at least east-west centre)

Easy for you. You're in Florida where it's warm. I'm in Manitoba, in winter. Everything is slower here ;)

Had to shoo the cat off the keyboard.