Posts by Reverend Jim

In the button click handler you can identify the button either by name or by label text (the label text is probably more informative depending on how you name your controls)

Dim button As Button = DirectCast(sender, Button)
MsgBox(button.Text & " " & button.Name)

wsh is reserved. If you execute the following line

wscript.echo wsh

the result will be the output of the line

Windows Script Host

use another variable name.

If you want to update a field automatically (like to display the time) you have to add a Timer control to your form and set it to run at a convenient interval. For example, if you add Timer1 to your form, you can set the interval to the number of milliseconds between events (1000 = one second). If you set it to 1000 you can update the time field once a second with the current time. Set the Enabled property of Timer1 to True to start the timer.

If it doesn't work then you must be doing something wrong because I use this feature frequently.

Stubborn browser toolbar addons can be removed/disabled using HijackThis (free) at http://free.antivirus.com/hijackthis/

You can do this with application settings. I don't know what version of Visual Studio you have so I'll describe the procedure for VS 2010.

Start with your project open

Go to the project properties settings

Select the "Settings" tab down the left side of the panel

Add an item, let's name it "LastButtonText", make it of type "String" and scope "User". Don't enter a value.

Go to your Form Load event handler and add the line (I'll assume your button control is named btnMyButton - change it to the correct name)

If My.Settings.LastButtonText <> "" Then
    btnMyButton.Text = My.Settings.LastButtonText
End If

That will load the button text on startup. Now you have to save the last displayed text at app exit. For that you have to add the following code to the FormClosing handler.

My.Settings.LastButtonText = btnMyButton.Text

That should do it.

Glad to help. Have a Merry Christmas yourself.

You can't do a loop for i = 0 to i because you are using the same variable for your loop index as for the end value. To do the padding for each line in the text box try:

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

    Dim s As String = ""

    For Each line As String In RichTextBox1.Lines
        s &= ZeroPad(line, 9) & vbCrLf
    Next

    RichTextBox1.Text = s
 
End Sub

Private Function ZeroPad(num As Integer, width As Integer) As String
    Return num.ToString.PadLeft(width).Replace(" ", "0")
End Function

Private Function ZeroPad(str As String, width As Integer) As String
    Return str.PadLeft(width).Replace(" ", "0")
End Function

There are versions of ZeroPad for either string or numeric parameters. If you are wondering why I edited this message three times it is because I have a new kitten that insists on introducing typos by pawing at the keyboard.

Although you may need more than one leading zero. Try

Dim i As Integer = 123
Dim s As String = i.ToString.PadLeft(9).Replace(" ", "0")
MsgBox(s)

which pads to 9 digits. You can roll your own function to pad to a variable width. Also, the OP said that the data doesn't always come in a spreadsheet.

If you are going to do a copy and paste then you will get what you get. What is the format of the cells in the spreadsheet (is it an Excel spreadsheet). Numbers don't usuallt display with a leading zero.

If the cells you want to copy can be identified in code I can show you how to copy automatically,

The simplest answer would be that another process is using the file. Try the Handle utility available at http://technet.microsoft.com/en-gb/sysinternals/bb896655 and see what shows up.

Put a breakpoint at the line with the Clear (line 13 in my previous post) and see if you are actually getting there. Check the contents of the listview at the break then step one line and check again. I tested the subjListView.Items.Clear() line and verified that it does what you want.

It is generally not a good practice to put code in a construct that does not depend on the construct. You have

subjListView.Items.Clear()

in both case clauses therefore it should be moved out of the select. It belongs in fillListView.

Try

Sub fillListView()
 
    With sqlcmd
        .CommandText = strsql
        .Connection = sqlconn
    End With
 
    With sqlda
        .SelectCommand = sqlcmd
        .Fill(dTbl)
    End With

    subjListView.Items.Clear()

    For i = 0 To dTbl.Rows.Count - 1
        With subjListView
            .Items.Add(dTbl.Rows(i)("name"))
            With .Items(.Items.Count - 1).SubItems
                .Add(dTbl.Rows(i)("age"))
            End With
        End With
    Next

End Sub

Try ListView1.Items.Clear() instead of ListView1.Clear(). That will clear the items only, not the column headers.

The code I provided earlier was before I knew what your data structures were. It was intended to show you how to import and export the lists.

I don't see the problem. I ran your code with my mods and it does what you said you wanted.

And once again, why worry about sorting when the combobox does that for you?

I am not familiar with CRUD.

OK. I handled how to add a Trade to tradeslist in an earlier example. My example overloaded the New method so I could do

tradelist.Add(New Trade(frmEditTrade.txtName.Text, frmEditTrade.txtSalary.Text)

That won't add the trade in sorted order. However, because the combobox has a Sorted property so that the entries ARE sorted it doesn't really matter if the list and text file are not sorted.

Assuming you want to do this to all text controls on the form, add the following to subs

Private Sub Text_Enter(sender As System.Object, e As System.EventArgs)
    DirectCast(sender, TextBox).BackColor = Color.Yellow
End Sub

Private Sub Text_Leave(sender As System.Object, e As System.EventArgs)
    DirectCast(sender, TextBox).BackColor = Color.White
End Sub

Then add the following for the form load event handler

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

    For Each ctrl As Control In Me.Controls

        If TypeOf (ctrl) Is TextBox Then
            AddHandler ctrl.Enter, AddressOf Text_Enter
            AddHandler ctrl.Leave, AddressOf Text_Leave
        End If

    Next

End Sub

The first two subs will supply the handler code for the enter and leave events. The form load code will add those handlers to all textboxes.

I have a new kitten (as of 4 days ago) who is making these postings somewhat difficult, although on the bright side, she is creating some very cryptic passwords as she chases the cursor while dancing on my keyboard.

For example, I created a form named frmEditTrade. In your frmAdministration I added

Private Sub btnTCNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTCNew.Click
    If frmEditTrade.ShowDialog() = Windows.Forms.DialogResult.OK Then
        MsgBox(frmEditTrade.txtName.Text & " " & frmEditTrade.txtSalary.Text)
    End If
End Sub

You can replace the MsgBox with the code to add a new object to tradeslist. You should check before you add it to verify that it is not already in the list. The data entry form I provided is minimal. It does no checking to verify that the salary is numeric and reasonable.

And I can't emphasize enough about the comments. If I seem to be nagging about that it is because I spent over 30 years as a programmer, a lot of which was maintaining end enhancing other people's code. I was also a marker in university. Bad or no comments = bad grade.

You have to create a data entry form for Trade and Equipment. The data entry form will be called as a modal dialog in the btnTCNew_Click handler. That form will do the data entry and validation on the new values and call the tradelist.Add method if the validation succeeds and the user cliks an Add or OK button on that form. You might want to reread my hint in the previous posting. Your comments are sorely lacking. They should be written BEFORE the code.

Not really. Why don't you zip and post the project and I can have a look. I really hope you have commented your code (that was a hint).

What I meant was that I didn't know what the makeup of "Trade" was. tradelist is a List of Trades but because I didn't know for sure what a Trade looked like I made an assumption that it was a class with two properties. I don't know what the third value is in your second list.

As an interim guess, you can read/write the tradeslist as follows (I assumed a default definition for Trade)

Public Class Form1

    Public tradelist As New List(Of Trade)

    Public Class Trade

        Public Name As String
        Public Wage As Double

        Public Sub New(name As String, wage As Double)
            Me.Name = name
            Me.Wage = wage
        End Sub

        Public Sub New(name As String, wage As String)
            Me.Name = name
            Me.Wage = CDbl(wage)
        End Sub

    End Class

    Private Sub btnImport_Click(sender As System.Object, e As System.EventArgs) Handles btnImport.Click

        Dim trades() As String = ReadFile("d:\temp\trades.txt")

        For Each line As String In trades
            Dim fields() As String = line.Split("|")
            If UBound(fields) = 1 Then
                tradelist.Add(New Trade(fields(0), fields(1)))
            End If
        Next

    End Sub

    Private Function ReadFile(filename As String) As String()

        Dim text As String = My.Computer.FileSystem.ReadAllText(filename)
        text = text.Replace(vbCr, "")
        Return text.Split(vbLf)

    End Function

    Private Sub WriteFile(filename As String, text As String)

        My.Computer.FileSystem.WriteAllText(filename, text, False)

    End Sub

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

        Dim text As String = ""

        For Each trade As Trade In tradelist
            text = text & trade.Name & "|" & trade.Wage & vbCrLf
        Next

        WriteFile("d:\temp\trades.txt", text)

    End Sub

End Class

Unless text files are prohibitively large I prefer to read and write them in one chunk rather than use streams. Error checking is up to you.

Sort of the same question as before but further into the stack - now can you please define what "Trade" is. Please don't say "it's a class". Define it so I can code you an example and I don't want to make any assumptions.

Easiest one first. set the Sort property of the ComboBox to True and the items will display sorted.

The comboboxes are populated via a list and the list is populated by reading a text file for each combobox.

What is the type of your list? A "list" can be many different things. It can be a ListBox, a ListView, any one of the Microsoft supplied list types or even a custom list class. Without a declared type I have no way of answering the list question.

Ditto for the "how to write a list to a text file". I can't definitively answer this without knowing the form of your list.

Let's replace the variables with tokens. For example, let's replace ServerAdressTextBox.Text with the string SERVER, etc, and see how your connection strings look at that point. Your three examples boil down to

"Data Source=SERVER;Initial Catalog=DATABASEIntegrated Security=SSPI" & ";"

"Data Source=SERVER\MSSQL;Initial Catalog=DATABASE;User Id=USERNAME;Password=PASSWORD;"

"Data Source=SERVER;Initial Catalog=DATABASETrusted Connection=True;"

When you look at the resulting strings you can see that you are missing the delimiter ";" following the DATABASE name in examples 1 and 3. Correct that first and see where it gets you.

If you are using integrated security then you are logged in to SQL Server based on your Windows logon ID. That ID has to be defined to SQL Server as having access rights to the databases that you want to connect to.

Your first example uses Integrated Security on SQL Server. Your second example uses a non-Windows userid and password and NOT on SQL Server. So please decide what it is you want to connect to and how, and we'll try to help.

I usually use ADO and I use the connection string

"Driver={SQL Server};Server=" % server & ";Database=" & database & ";Trusted_Connection=yes;"

The code I posted was an example of how to use a dictionary relating to your code. The actual variable "code" was just to show how to relate the airport code to the city and region. In actual use it would be

Public Class Form1

    Private city As New Dictionary(Of String, String())

    Private Sub btnDetermineRoute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDetermineRoute.Click

        Dim fromCode As String = lstFrom.Text       'get airport code for source city
        Dim toCode As String = lstTo.Text           'get airport code for destination city

        'check that a valid airport code was entered for source and destination

        If Not city.ContainsKey(fromCode) Then
            lblWarning.Text = "You must select a source city"
            lblWarning.Visible = True
            Exit Sub
        End If

        If Not city.ContainsKey(toCode) Then
            lblWarning.Text = "You must select a destination city"
            lblWarning.Visible = True
            Exit Sub
        End If

        'check that source and destination cities are different

        If (fromCode = toCode) Then
            lblWarning.Text = "You must selected different cities!"
            lblWarning.Visible = True
            Exit Sub
        End If

        'get the city and zone for the source and destination

        Dim fromCity As String = city(fromCode)(0)
        Dim fromZone As Integer = city(fromCode)(1)

        Dim toCity As String = city(toCode)(0)
        Dim toZone As Integer = city(toCode)(1)

        'set base ticket price based on zone and coach

        Dim ticketPrice As Double = IIf(fromZone = toZone, 250, 400)

        'increase ticket price for business or first class

        If radBusinessClass.Checked Then
            ticketPrice *= 1.3
        ElseIf radFirstClass.Checked Then
            ticketPrice *= 1.6
        End If

        'display ticket summary

        txtMessage.Text = "Your ticket from " …

If I might make a suggestion. Have you considered using a dictionary? It would simplify your code considerably. Here's an example:

Dim city As New Dictionary(Of String, String)

city("AUS") = "Austin"
city("BOS") = "Boston"
city("BWI") = "Baltimore"
city("DFW") = "Dallas"
city("JFK") = "New York"
city("LAX") = "Los Angeles"
city("MIA") = "Miami"
city("ORD") = "Chicago"
city("SFO") = "San Francisco"

'if you want to get the corresponding city for a given code you just index
'the dictionary with the code. For example

Dim code As String = "ORD"

If city.ContainsKey(code) Then
    MsgBox("The city corresponding to " & code & " is " & city(code))
Else
    MsgBox("There is no corresponding city for the code " & code)
End If

There are other useful properties/methods including ContainsValue. You can define the dictionary at the class level. You can add more codes and not have to add corresponding ElseIfs. You can define the types of values to include string arrays where the first element is the city and the second is the zone as in:

Dim city As New Dictionary(Of String, String())

city("AUS") = {"Austin", "3"}
city("BOS") = {"Boston", "2"}
city("BWI") = {"Baltimore", "2"}
city("DFW") = {"Dallas", "3"}
city("JFK") = {"New York", "2"}
city("LAX") = {"Los Angeles", "1"}
city("MIA") = {"Miami", "2"}
city("ORD") = {"Chicago", "3"}
city("SFO") = {"San Francisco", "1"}

'if you want to get the corresponding city for a given code you just index
'the dictionary with the code. For example

Dim code As String = "ORD"

If city.ContainsKey(code) Then
    MsgBox("The …

Clearly I missed the boat on this one. I think I'll reread everything through a few dozen times and go through the code until I get it ;-)

I see my previous code has some fluff. Typically I step through a collection of nodes using prev, curr and a NextNode method but because Insert requires a node index rather than an actual node I changed a while loop to a for loop but forgot to take out the "prev" code. Usually when looking for a place to insert a node you keep track of the previous node because you don't know where to insert until you have gone one nodoe too far. Thus, you never have to decide "do I insert before or after". You just insert using prev. That's why I am having a problem with the question. Using "prev" you always insert AFTER, like in the example I posted (minus the fluff).

I still maintain that the algorithm (or portion that was posted) makes no sense, at least as it is (incompletely) stated.

I hate feeling like an idiot. Can you please explain to me the difference between what he is asking and what I think he is asking because it is obvious I am completely missing something here.

I believe my earlier example does just that. It creates one level of nodes under the root containing the entered words in sorted order.

The exact algorithm is extremely important because if you don't know it then you can't program the boundary conditions of the recursion. Your tree starts out with one-digit items at the first level and two-digit items at the second level, then breaks this pattern by having three and four-digit (or character) items at the third level. If you start putting in special cases then it gets messy.

And what do you do if, for example, the first item you want to add is 123? At that point there is no 1 or 12 node to add it under. These things have to be specified completely. I once had a professor who was fond of saying "if you don't know what your program is supposed to do then you'd better not start writing it".

I'm sure we can do it but I have to know how you decide where to put an item. For example, you add item 4 after item 3 but you add item 5 under item 4. I don't understand how you decide to add after as opposed to under.

It seems to me that the easiest way to handle multiple tree levels is to use recursion, but, again, that would rely on there being some way of determining what would require a new level of nesting. For example, why are items 2, 3 and 4 at one level but 5, and 6 (and 6.1 for that matter) at another level?

You step through the tree and keep track of the current and previous nodes (I use variables named curr and prev). When you find a node with a value greater than the value of the node you want to insert then you can use the Insert method on the prev node. Try this example. Create a new project. Add a button (btnAdd), a text box (txtAdd) and a treeview (trvTree). Make sure you define a root node (root). Now add the following code

Public Class Form1

    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click

        If txtAdd.Text = "" Then Exit Sub

        Dim newnode As New TreeNode
        newnode.Text = txtAdd.Text

        AddNode(trvTree.Nodes(0), newnode)

    End Sub

    Private Sub AddNode(root As TreeNode, newnode As TreeNode)

        Dim curr As TreeNode = Nothing
        Dim prev As TreeNode = root
        Dim i As Integer

        For i = 0 To root.Nodes.Count - 1
            curr = root.Nodes(i)
            If curr.Text > newnode.Text Then Exit For
            prev = curr
        Next

        root.Nodes.Insert(i, newnode)

    End Sub

End Class

Type a word in the textbox and click Add. Keep adding words. They will be placed in the tree in order. This is a simplified example. For the tree in your example there seems to be no obvious algorithm to determine the level of nesting. My example has only one level of nesting.

What are "-ve" values?

Then just do the checking with a row index of 2 (third row zero relative)

I'm not sure what you are asking. You see how to access the cells. What you do once you have a match is up to you. By the way, because you have already determined that the value in the cell is a string you don't have to convert. You can use it directly so cell.Value.ToString is redundant. You can just use cell.Value as a string.

Glad to help. If that is what you were looking for then please come back to this thread and mark it as solved.

Assuming you have data in the given row (in the example, I use row 2 (first row is row zero), you can access all of the cells in that row by

For col As Integer = 0 To dgvTest.ColumnCount - 1
    MsgBox(dgvTest.Rows(2).Cells(col).Value)
Next

Try this. I created a form with five labels and one listbox.

Public Class Form1

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

        AddHandler Label1.Click, AddressOf Label_Click
        AddHandler Label2.Click, AddressOf Label_Click
        AddHandler Label3.Click, AddressOf Label_Click
        AddHandler Label4.Click, AddressOf Label_Click
        AddHandler Label5.Click, AddressOf Label_Click

    End Sub

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

        Dim label As Label = sender
        ListBox1.Items.Add(label.Text)

    End Sub

End Class

You could also define a click event for just one of the controls in the set then use AddHandler on form load to add the other controls to that handler. It would save a lot of clicking. You would have to modify the original handler to distinguish between the controls. Or, if you don't mind some cut and paste you could add them as follows:

Private Sub lbl01_Click(sender As System.Object, e As System.EventArgs) Handles _
            lbl01.Click,lbl02.Click,lbl03.Click,lbl04.Click,...

Just extend the line for as many controls as you have. This gets a little messy, however, if you have a lot of controls. I recommend the AddHandler method.

Please put CODE tags around code to preserve formatting. After pasting the code, select all code and click on the CODE tool at the top of the edit winidow.

We can't help without a few details such as what happens when you try to run this code. What errors do you get and on what line?

You don't use SaveAs (or even Save) on a WorkSheet. You use it on a WorkBook. For example, the following code copies items from a listview into a new Excel WorkBook/WorkSheet then saves it to a file specified by the SaveFileDialog.

'browse for a file to save the data into

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

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

'create an Excel WorkBook

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

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

'save the listview items to the Excel 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 += 1
    Next
    row += 1
    col = 1
Next

'save the WorkBook to a file and exit Excel

xls.ActiveWorkbook.SaveAs(SaveFileDialog1.FileName)
xls.Workbooks.Close()
xls.Quit()

You should use the Excel methods rather than SendKeys. You can select a sheet my index (1-relative) or by name such as

sheet = xls.ActiveWorkbook.WorkSheets(3)
sheet = xls.ActiveWorkbook.WorkSheets("Sheet3")

I'll leave that as an exercise for the student. Read up on the ListView control and play around a bit and you'll find out how to get the column names from the control. Or, if you already know the column names you should be able to write the code to add them to the spreadsheet.

Create a new form. Add a reference to "Microsoft Excel 11.0 Object Library". At least that's the name on my PC. The version number may be different on yours.

Add two buttons named btnPopulate and btnExport. Add a listview named lvwToExport and set it to details view. Make sure it has three columns. Add the following code to your form and run it (you may need to change the path of the Excel file). Click on "Populate" to add several items to the listview, then "Export" to create an Excel spreadsheet and copy the listview items. By default, the Excel.Application is created invisible. If you want to see it add the line

xls.Visible = True

and comment out the xls.Close and xls.Quit lines (or the spreadsheet will disappear almost immediately after it is created).

Imports Microsoft.Office.Interop
Imports System.Windows.Forms

Public Class Form1

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

        Dim item As ListViewItem

        'add two rows (of three columns) to the listview

        item = New ListViewItem("one")
        item.SubItems.Add("two")
        item.SubItems.Add("three")
        lvwToExport.Items.Add(item)

        item = New ListViewItem("four")
        item.SubItems.Add("five")
        item.SubItems.Add("six")
        lvwToExport.Items.Add(item)

    End Sub

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

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

        'by default a new Excel Application has no workbooks. Add one. Also
        'by default, a new workbook has three worksheets.

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

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

        'copy the listview rows and columns to the worksheet cells

        For …

Assuming you have an Excel file on D: named myfile.xls, you can access it as follows:

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

xls.Workbooks.Open("D:\myfile.xls")
sheet = xls.Workbooks(1).Worksheets(1)

sheet.Cells(1,1) = "abc"
sheet.Cells(1,2) = "def"
sheet.Cells(1,3) = "ghi"

xls.SaveWorkspace()
xls.Quit

That will set the first three columns of the first row to the respective values, save the file then exit Excel. The actual Excel interface is hidden.