Posts by Reverend Jim

This error can happen when you have a parameter in your connection string that is not valid for the given provider. Try changing

Persist Security Infor=False

to

Persist Security Info=False

and see what happens. What type of database are you trying to open?

I think you can clear out saved text by clicking the dropdown box and moving the cursor over an option. When it is highlighted, press the DELETE key.

Which index is it complaining about? You have two

• currentTimer - 1
• List.Items(currentTimer - 1).SubItems.Count

Try replacing the code with

Debug.WriteLine("index 1 = " & (CurrentTimes - 1).ToString)
Debug.WriteLine("index 2 = " & (List.Items(currentTimer - 1).SubItems.Count).ToString)
Debug.WriteLine("# items = " & List.Items.Count)
List.Items(currentTimer - 1).SubItems(List.Items(currentTimer - 1).SubItems.Count).Text = ""

The obvious problem is that you are either indexing past the number of items or past the number of subitems. You can't determine that just by looking at the code. You have to see the actual values of the indices.

Please display the value of sqlupdate (Debug.WriteLine would be good) and post the results here. And before someone else says it, you should be using parameterized values instead of just concatenating to create your query.

For numeric input I suggest you use a NumericUpDown control. It will accept only numbers and you can set maximum and minimum values for the control (and alter them at runtime).

You set the messagebox style to Critical or YesNoCancel but only ever check for Yes, No or Cancel. Try setting it up as follows:

Select Case MsgBox(msg, MsgBoxStyle.YesNoCancel, title)

    Case MsgBoxResult.Yes
        .
        .
        .
    Case MsgBoxResult.No
        .
        .
        .
    Case MsgBoxCancel
        .
        .
        .
End Select    

You retest where it is not necessary. Your code basically looks like

If num = x Then
    .
End If

If num < x Then
    .
End If

If num > x Then
    .
End If

If the first case is true then you don't need to do the following two tests. Using the NumericUpDown control with a Select Case gives you

Select Case NumericUpDown1.Value

    Case Is = X
        Me.Text = "you guessed it"

    Case Is < X
        Me.Text = "too low"

    Case Is > X
        Me.Text = "too high"

End Select

You want a UNION rather than a JOIN. Example:

SELECT * FROM Males   WHERE Mark > 50
 UNION
SELECT * FROM Females WHERE Mark > 50

will return one recordset

It's probably easier to use an application setting instead of the registry. If you are using VB 2010, go to Project -> Properties, then go to the Settings page. You'll have to pick an appropriate variable type. You can access it by

My.Settings.varname

You can keep track of elapsed time by using a TimeSpan or unsigned int.

You can either set the ListView MultiSelect property to False, or you can change the code to

ListView_LOP.SelectedIndices.Clear()
ListView_LOB.Focus()

For i = 0 To ListView_LOB.Items.Count - 1
    If ListView_LOB.Items(i).SubItems(1).Text = TextBox1.Text Then
        ListView_LOB.Items(i).Selected = True
    End If
Next

Surely if Windows can turn on/off the power to various devices such as monitor, hard drive, etc based on user settings then there must be some way for an application to do the same with the proper API call.

can't we even make a commercial depicting people being happy

Many years ago our local paper decided to stop running the cartoon Tumbleweeds. The reason given was that First Nations people had complained because it depicted them as stupid. It didn't matter that the cartoon depicted everyone as stupid.

How do you that the jamaicans like the commercial?

An article in the Washington Post. Obviously I haven't talked to all Jamaicans. Just one. And he liked it.

Got it. Thanks.

Fortunately I no longer have to worry about this. Although it would have been a good thing to know in pre-retirement when I had to do this sort of stuff (a euphemism) for a living. One more clarification if you please. Why

Dim worksheet As xl.Worksheet = CType(wb.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

instead of

Dim worksheet as Excel.Worksheet = wb.Worksheets("Sheet1")

I only heard about it after the fact (I only watched the last minute and a half of the game) and saw it online, but there was a Volkswagom commercial where a guy from Minnesota talks in Jamaican patois. Here's yet another example of people who have nothing better to do than be offended on behalf of other people. There's been a lot of talk about how that commercial is racist and that the actors migh as well have done it in blackface. Meanwhile, the commercial is quite popular in Jamaica and has gotten the official thumbs up from the Jamaican government.

I've been doing this although I don't know that it is guaranteed to work. I have never been able to find a definitive answer as to how to properly handle the Excel objects when it comes time to clean up. I suppose if it were possible to get a process id associated with the underlying Excel.exe it would be possible to do a process kill on form exit but that would be a rather ham-handed way of handling it.

    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
    .
    .
    .
    ReleaseObject(sheet)
    ReleaseObject(xls)
    .
    .
    .

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

When you write code to use the Excel application object it is not unusual to end up with multiple Excel.exe instances running. You should check with Task Manager for this.

This will help when installing the SQL Management Studio. The procedure is not obvious.

No problem. Glad we got it straightened out.

That's clearer. I thought you were still trying to assign a name to the StopWatch object. If you are creating all of the panels at the same time (ie in a loop inside a code block, then you can just use a local variable. If you are doing the creation at different times (ie different calls to a sub or function) then you will have to make the variable global. For the local case you get

for i = 1 to 5
    dim NextPanel as New Panel
    NextPanel.Name = "Panel" & i.Tostring()
    Me.Controls.Add(NextPanel)
next

for the global case you get

Public Form Form1

    Private panelnum As Integer = 0
    .
    .
    .

    Private Sub MySub()

        for i = 1 to 5
            panelnum += 1
            dim NextPanel as New Panel
            NextPanel.Name = "Panel" & panelnum.Tostring()
            Me.Controls.Add(NextPanel)
        next

Is that more what you had in mind?

If you are just looping through a listview then why do you need to exit the loop using a buttor or menu? Surely it will exit after all of the items have been processed. Mayybe you should post the code so we can make a more informed suggestion.

If you have a block of code that is stuck in a very long loop that you want to exit prematurely then you'll need two things.

1. a global condition that you can test to do an Exit
2. a call to My.Application.DoEvents inside that loop

For example

Public Class Form1

    Private GetOut As Boolean
    .
    .
    .
    Private Sub MySub(...)
    .
    .
    .
        GetOut = False

        Do While some condition
            .
            .
            .
            If GetOut Then Exit Do
            .
            .
            .
            My.Application.DoEvents

        Loop

Then the code under (for example) btnEscape would include

GetOut = True

If you don't have the DoEvents call then your application will lock up.

Other thread

Dim NextPanelStopwatch As New Stopwatch

This thread

Dim Stopwatch As New Stopwatch ' <-- How can I assign the name [Stopwatch1]?

Both threads create a StopWatch object at runtime and in the last thread I explained that there is no Name property of the StopWatch object. How is that not a duplicate thread? You need to pay attention to the answer or state the question more clearly. You said How can I assign the name [Stopwatch1]?. What do you want to assign that name to?

If I am missing something obvious here I apologize but it's 02:25 AM and I'm more than a little tired from not sleeping.

The StopWatch object does not have a name property. Please do not post duplicate threads.

You hard coded the connection string a dozen or more times. The actual connection string should appear only once. If you ever have to change it you don't want to have to change it in multiple places. If you do you are likely to miss one.

You don't name your controls. They should never be named Button1, Button2, etc except for trivial examples. When working with a large block of code, using a name like btnRefresh makes the code much easier to follow.

You haven't added any comments to your code. This, in and of itself, is cause for justifiable flogging. Here I am speaking as a 30+ year veteran as a maintenance programmer where I had to work with untold thousands of lines of other people's code. If you are doing this as an assignment, some markers can be brutal in taking of marks for uncommented code. I know. I was one of them. And I learned that less the hard way when I lost marks on early assignments.

I made the following additions at the top of Main

Public Class Main

    Const DATABASE = "D:\Downloads\EmployeeRecords\EmployeeRecords\bin\Debug\Employeedb.mdb"
    Public ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DATABASE & ";"
    Public conn As New OleDbConnection(ConStr)

You can now use the same connection object (conn) everywhere in your project. However, in the login form you must now use Main.conn instead of just conn. The following is the replaacement code for Button5.Click. You can take out the MsgBox once you see it …

Oh. OK.

Any chance you could also post a sample database?

I could have a look, but as I said, I don't know a lot about the new layers such as DataTables, DataAdapters, etc. I'll offer any advice I can.

I don't know from DataTables and DataAdapters. As Scotty says, "the fancier they make the plumbing the easier it is to plug up the drains." I use mostly ADO when I want to do any database work, but what I suggested is an easy way to build the query. What in particular are you having trouble with in my code? I'll try to explain further.

I did a minor edit after you responded but before I posted and saw your reply. The process is really not that compliciated. All I am doing is building up the query a bit at a time. In people speak it's like:

• I want all employees
• oh yeah, I just want regular employees
• oh yeah, I also want contractual employees

Every time you find a checkbox you add a bit to the end of the query. Then when you are done you lop off the last two chars to prevent a dangling "OR". You can get sneaky when you want to avoid a lot of repetitive typing or complex If statements.

Compose the query first then do the DB access later as in

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
Dim cmd As New OleDbCommand("", con)

Dim query As String = "SELECT * FROM Employees   "
Dim where As String = ""

If cbox.Checked Then where &= " status = 'Regular'     OR"
If rbox.Checked Then where &= " status = 'Contractual' OR"
If mbox.Checked Then where &= " gender = 'Male'        OR"
If fbox.Checked Then where &= " gender = 'Female'        "

If where <> "" Then query &= "WHERE" & where

cmd.CommandText = query.Substring(0, Len(query) - 2)

etc.

Inside the loop you can save some typing by

ListView1.Items.Add(New ListViewItem({ _
    numberrow.ItemArray.GetValue(0).ToString.ToUpper, _
    numberrow.ItemArray.GetValue(1).ToString.ToUpper, _
    numberrow.ItemArray.GetValue(2).ToString.ToUpper, _
    numberrow.ItemArray.GetValue(3).ToString.ToUpper, _
    numberrow.ItemArray.GetValue(4).ToString.ToUpper})

You can also add an entire row to a listview (in details view) in one step by

ListView1.Items.Add(New ListViewItem({"col1data","col2data","col3data"}))

In your case the parameter(s) would be

{reader.GetString(0),reader.GetString(1),reader.GetString(2)}

For details on how to use parameterized queries please refer to this code snippet. It also suggests how to adjust your code formatting to make queries easier to read.

I didn't know that RichTextBox has .Save and .Load features

Neither did I when I first started using it. That's why I like to browse the intellisense methods from time to time. If this gives you what you need then please mark this thread solved.

As tinstaafl says, you didn't say what line was throwing the error. Also, you should never (never) build queries by concatenation. That leads you open to SQL injection attacks. Build your queries using parameters. In your case that looks like

strsql = "UPDATE tbl_tec_temp                 " _
       & "   SET tem_we_position = @position, " _
       & "       tem_we_company  = @company,  " _
       & "       tem_we_salary   = @salary,   " _
       & "       tem_we_date     = @date,     " _
       & "       tem_we_rl       = @rl,       " _
       & "       tem_we_attach   = @attach    " _
       & " WHERE temp_no = @no"

Dim cmd As New SqlCommand(strsql)
cmd.Parameters.AddWithValue("@position", TextBox8.Text)     
cmd.Parameters.AddWithValue("@company ", TextBox10.Text)
cmd.Parameters.AddWithValue("@salary  ", TextBox21.Text)
cmd.Parameters.AddWithValue("@date    ", DateTimePicker1.Text)
cmd.Parameters.AddWithValue("@rl      ", TextBox22.Text)
cmd.Parameters.AddWithValue("@attach  ", TextBox36.Text)
cmd.Parameters.AddWithValue("@no      ", TextBox39.Text)

Please note the formatting of the statement that builds the query. Spreading the query across multiple lines makes it easier to read and adding a few blanks as padding to line fields up makes it easier to spot typos.

Please post the code you are using to load and save the text. The usual way to load and save rich text is as follows:

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
    RichTextBox1.SaveFile("d:\temp\myfile.rtf")
End Sub

Private Sub btnLoad_Click(sender As System.Object, e As System.EventArgs) Handles btnLoad.Click
    RichTextBox1.LoadFile("d:\temp\myfile.rtf")
End Sub

In that case

TextBox1.Text = ""

Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
Dim cmd As New SqlCommand

con.Open()

cmd.Connection = con
cmd.CommandText = "select count(*) as count,Name from supplies group by Name order by count desc"

Dim rdr As SqlDataReader = cmd.ExecuteReader

Do While rdr.Read() And ListView1.Items.Count < 3
    TextBox1.Text &= rdr("Name") & " "
Loop

rdr.Close()
con.Close()

For the following code you need to create ListView1 in details view with two columns. It will add up to three rows to the listview with the most frequent items appearing first.

ListView1.Items.Clear()

Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
Dim cmd As New SqlCommand

con.Open()

cmd.Connection = con
cmd.CommandText = "select count(*) as count,Name from supplies group by Name order by count desc"

Dim rdr As SqlDataReader = cmd.ExecuteReader

Do While rdr.Read() And ListView1.Items.Count < 3
    ListView1.Items.Add(New ListViewItem({rdr("count"), rdr("Name")}))
Loop

rdr.Close()
con.Close()

I'm personally not interested and I don't qualify in any case, but if you want to attract interest I suggest you provide some details as to the type of project, time commitment, timeline, scope, required skillsets, etc.

On the following table

Supplies
  ID
  Name

Containing the following rows

1   paper
2   book
3   book
4   book
5   pen
6   pencil
7   pencil
8   pen

The following query

select count(*) as count,Name from supplies group by Name order by count desc

returns

3   book
2   pen
2   pencil
1   paper

Then you can pick the three most frequent items from most to least frequent by stepping through the returned recordset until you have three items or you run out of records.

Fiscal cliff, my a$$.

The government kept everyone in fear following WWII with the "Red Menace". The cold war provided the threat to keep the patriotic fires stoked. This allowed HUAC to proceed with a modern version of the Salem witch trials. When the Soviet Union collapsed they needed a new threat. We got it with the "War Against Terrorism". That gave the government carte blanche to pass all kinds of otherwise unconstitutional laws under the guise of the Patriot Act. Now the powers that be can do warrentless wiretaps and detain people indefinitely. So you have lost the right to privacy and due process. But to totally gut the financial system they needed a different kind of threat so they created the fiscal cliff and kept moving the deadline a few weeks at a time. With financial armageddon always right around the corner all kinds of bad legislation is going to be passed. Say goodbye to social security.

We have a friend who likes to go on about the coming apocalypse. In 2011 it was all about the Book of Revelation. Last year it was all 2012. I finally found a way to shut her up. Last year I asked her if she was convinced the world would end in December of 2012. She said "yes". So I asked her if she would sign a contract whereby she would sell me her house for $10,000 as long as I agreed to sell it back for the same price in January of 2013. She refused. I told her it was a no lose deal. She could take that end-of-the-world cruise and not have to worry about paying for it. She still refused. Didn't hear anything more about it after that.

The reason the Mayan calendar ended when it did is the same reason our calendar ends on December 31.

Use autoruns from Sysinternals Suite. Free.

I don't have my dev machine so I can't test this but I believe the format for a date value is like

select * from tble where Invoiced > #01/05/2012#

so try

    "SELECT * FROM invoiced_filter " & _
    " WHERE Invoiced > #" & fromdte & "# AND < #" & todte & "#"

Both your tables have a field named ID, but in the Employee table it is the Employee ID and in the Employer table it is the Employer ID. Each record in the Employee table has to be associated with an employer. You do this by ensuring that each record contains the Employer ID.

You will also have to define EmployerID in the Employee table as a foreign key and you can define a contraint to ensure that it exists in the Employer table. How you do this depends on your DBMS. I use MS SQL but because my dev system is in for repairs I can't give you instructions on how to set this up at the moment.

You will also have to decide which fields are required and deselect the Allow NULLS option.

You'll have to add the EmployerID field to the Employee table in order to link the two tables. Also, what are the definitions of Firstname & Lastname as applied to an employer? For example, I used to work for Manitoba Hydro, which would be entered under CompanyName (which is not a required field???). No one person owns that company so what gets entered for Firstname/Lastname? Only Lastname is a required field. Firstname is not. Yet in the employee table only the ID is required which is pointless because an employee must have a name. You still have some thinking to do.

You haven't posted the structure of the tables so a definitive answer isn't possible, however I suspect you haven't related the tables. Your Employer table will need at least two fields such as

Employer
EmployerID (identity)
EmployerName (varchar)

And the Employee table will need the same type of fields plus it must contain the field, EmployerID so that each EmployeeName record relates to a record in the Employer table. The EmployerID field in the Employer table will be either an integer (auto-increment) or a GUID value. In a real-world situation, EmployerName would actually be broken down into separate fields such as FirstName, MiddleName and LastName.

What happens if you supply the IP address as part of the server name? For example, on my regular computer my SQL Server instance is

JIM-PC\SQLEXPRESS

But I imagine (I can't verify this because my computer is in the shop) I could also use

\\192.168.1.104\SQLEXPRESS

I don't recall the show, but one character dissed the new-age beliefs of another and received a vigorous slap in return. He replied, "ah, yes. The sound of one hand clapping".