Posts by Reverend Jim - Page 29

Can you please post a sample plant info file?

It's certainly possible. How you do it depends on a few things. For example

1. Is each plant description in a separate file?
2. How are the fields identified

My preference is to read files all at once. This avoids the cumbersome process of setting up stream readers, opening, closing, etc. Let's make a few assumptions

1. the plant info is in a separate file for each plant (where the file is named as plantname.txt)
2. the folder containing the plant info files is stored in PLANTINFO
3. the name of the selected plant has been copied to a string variable, plant

then reading all of the info for a plant is as simple as

Dim infofile As String = System.IO.Path.Combine(PLANTINFO, plant & ".txt")

If My.Computer.FileSystem.FileExists(infofile) Then
    Dim info() As String = System.IO.File.ReadAllLines(infofile)
Else
    MsgBox("No file found for " & plant)
    Exit Sub
End If

Now, of course, you have to go through the various lines just read and get the plant properties. This depends on how the file is arranged. For example, if the lines look like

DESCRIPTION: some text
LIGHT:       some more text
ZONE:        still more text
HEIGHT:      yet more text

Then you can parse out the fields by

For Each line As String In info
    If Trim(line) <> "" Then
        Dim fields() As String = line.Split(":")
        Select Case UCase(fields(0))
            Case "DESCRIPTION"
                'copy fields(1) to textbox
            Case "LIGHT"
                'copy fields(1) to textbox
            Case "ZONE"
                'copy fields(1) to textbox
            Case "HEIGHT"                    
                'copy fields(1) to textbox
            Case …

The default value for what? When you run the following code

Private Sub ComboBox2_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged

    myConnection = New SqlConnection(connectionstring)
    myConnection.Open()

    myCommand = New SqlCommand(" Select  Name FROM [Product_Name_List] Where P_Id='" & ComboBox2.Text & "'", myConnection)
    Dim dr As SqlDataReader = myCommand.ExecuteReader

    ComboBox2.Items.Clear

    While dr.Read()
        Debug.WriteLine(dr(0))
        TextBox8.Text = Val(dr(0)).ToString
    End While

    dr.Close()
    myConnection.Close()

End Sub

what is the output in the debug window?

Try adding "HDR=No" to the extended properties.

What happens when you run the following code

Dim con As New OleDb.OleDbConnection
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Persist Security Info = False; Data Source = | DataDirectory | \ db.mdb"

For Each item In ListBox1.Items

    dim cmd_sel OleDb.OleDbCommand con.CreateCommand = ()
    cmd_sel.CommandText = "SELECT Field1, Field2 FROM WHERE Tabla Field1 LIKE '" & item & "'"
    Dim oledbReader OleDb.OleDbDataReader

    con.Open ()
    oledbReader cmd_sel.ExecuteReader = ()

    While oledbReader.Read ()
        Debug.WriteLine(oledbReader.Item(1))
        ListBox3.Items.Add (oledbReader.Item(1))
    Loop

oledbReader.Close ()
con.close ()

I want to know what appears in the debug output window. Also, what's the idea of the frequent appearance of "the" in your code as in

The Dim oledbReader OleDb.OleDbDataReader

and

While the oledbReader.Read ()

You can use SelectedIndexChanged

Go the the machine on which the code works. On the line of code just prior to opening the connection, add the following line (I'm going to assume your connection object is called conn. If not then replace it with the actual object name)

Debug.WriteLine(conn.ConnectionString)

Post the output of that statement back here. Once we see the string we will be able to show you what parts need to be modified for the other system. You only want to have to enter the parameters once per system so I suggest you use Settings variables to store them between sessions. You will likely need three (four, if the database name is different on the other machine). If you don't know how to create Settings variables there are many examples on this forum. I suggest

ServerName   String
UserName     String
Password     String

You access the values as (for example)

My.Settings.ServerName

Just treat them like any other variable. Check the values at form load and if the values are blank you can prompt the user to enter the appropriate strings. Just make sure you save them back into the My.Settings.ServerName, etc. variables so they get preserved between sessions.

I used to do it the hard way too until someone else here did to me what I did to you ;D

I don't have access to VB while my computer is in the shop but let me make a suggestion (you'll have to verify the syntax). You can check all of the textboxes on the form by

For Each tbx As TextBox In Me.Controls.OfType(Of TextBox)()
    If tbx.Text <> "" Then
        'process textbox with content
    End If
Next

If some of the textboxes do not need to be checked then you can either put all the ones to check in a groupbox and just check within that group, or you could place some value in the Tag property of each textbox to check and ignore the ones without that value.

So lets say i added content to the 1st textbox/selected textbox.
I want it to save just that.

You can't check only the selected textbox because by clicking the button you deselect the textbox.

You can put the common code in a module then just make a call to that code in the form closing handler for each form.

The test

If TextBox1.Text.Contains(Str(i + 1)) Then

is not necessary. If the first string is not present then the replace won't do anything.

You could also use a dictionary as in

Dim transform As Dictionary(Of String, String)

and populate it where the key is the original char and the value is the replacement. Then you can do the replacement by

For Each key as String In transform.Keys
    TextBox4.Replace(key,transform(key))
Next

Caution - this will fail if any of the replacement strings contains any of the chars you are replacing.

I like the new feature but I am concerned that this will lead to people posting "bump" and other such empty content just to get their names in the bar.

try str.SubString

Or you could do str = str.Replace("-eng","")

The point is to make a worthwhile contribution. My following comment is not meant to point out any similarity but only to make a point. A few months ago we had a member (now banned) who was making dozens of posts daily in Thoughts of the day. Most of the posts made no sense and the only reason I could see for making them was to boost his activity points. The big problem with posts like that is that it dumbs down the other content by sheer volume.

how do i subtract and add values to each fields for every single transaction on the same table

That is very ambiguous question which I tried to answer. Are you saying you want to modify each field (doesn't make sense but that's what you said)?

Where do i exaclty put the Select Title?

After line 117.

If your table has the fields (for example)

myBooks
    ISBN
    Title
    Copies
    Borrowed

You can get a list of the number of available copies of all books by

SELECT Title, Copies-Borrowed AS Available FROM myBooks

If a book gets borrowed and you need to update the table you can do

UPDATE myBooks SET Borrowed=Borrowed+1 WHERE ISBN='some value'

And if a book is returned change "+" to "-"

What line is throwing the error?

Why bother converting all of the control values to integers and dates when all you are doing is putting them back into a string?

Don't put single quotes around values in the query when the corresponding database field is numeric.

I don't follow. The value that you enter (if any) when you create the Settings variable is the default. A default is, by definition, the value that is used if no other value is entered.

That's much more manageable. I can have a look at it this afternoon. A couple of comments quickly

1. It's never a good idea to declare variables like "n" at the class level. Variable names like n, i, j, etc are typically used for short duration such as for loop indices and should not have scope beyond that. Things should have the smallest scope possible. Never declare at the class level purely for convenience.

2. You declare myConnection at the class level and instantiate it in the form load event. But then you create another myConnection in the Save sub. This is not necessary. The old one is still hanging around. Use it. In this case it makes sense to declare your connection at the class level. However, when using database connections the general rule is to open the connection at the last possible moment and close it as quickly as possible.

3. You declare both myConnection and sqlconn at the Class level as SQLConnection. Why do you need two connection objects?

I'll look in more detail after I walk the dog and have some lunch.

It may be slightly different depending on which version of Visual Studio you are running, but in 2010, you open the project properties and click on "Settings" about halfway down the left panel. Enter a variable name, type and scope then enter an initial value. To load the value into an app variable you can do (let's assume a string setting named LastFile)

Dim lastfile As String = My.Settings.LastFile

To save that value back (to preserve the value between successive runs) just reverse the assignment.

I can't get your application to run. I don't have the time to go through it line by line to figure out what it is supposed to do. You haven't commented the code and you haven't named any of your controls to indicate what their function is.

Any time. I'll mark this as solved.

Don't use SUM. That is used to return the result of adding up several records. For example, if you had a table of sales data and you wanted to know the total sales for the Eastern region you would code

SELECT SUM(Sales) AS TotalSales 
  FROM Sales_Table
 WHERE Region = 'Eastern'

What you want to do is calculate the Total of the individual marks for each record. If we ignore the test for NULL values the query is

UPDATE tblClass1 SET Total = Physics+English+Chemistry+Biology+History

You don't need (or want) to do either of the following (both of which are invalid)

UPDATE tblClass1 SET Total = SUM(Physics+English+Chemistry+Biology+History)
UPDATE tblClass1 SET Total = SUM(Physics,English,Chemistry,Biology,History)

I posted code in Retrieve and save to another excel workbook a while back which should get you started.

INSERT adds a new record. What you want to do is UPDATE a field in an existing record. Tp update all the records at once you do

"UPDATE tblClass1 " & _
"   SET Total = IIF(Physics   IS NULL,0,Physics)   + " & _
"               IIF(English   IS NULL,0,English)   + " & _
"               IIF(Chemistry IS NULL,0,Chemistry) + " & _
"               IIF(Biology   IS NULL,0,Biology)   + " & _
"               IIF(History   IS NULL,0,History)"

or if you just want to update specific records you can add a WHERE clause at the end as in

"UPDATE tblClass1 " & _
"   SET Total = IIF(Physics   IS NULL,0,Physics)   + " & _
"               IIF(English   IS NULL,0,English)   + " & _
"               IIF(Chemistry IS NULL,0,Chemistry) + " & _
"               IIF(Biology   IS NULL,0,Biology)   + " & _
"               IIF(History   IS NULL,0,History)   + " & _
" WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "    

Incidentally, I missed a couple of line continuation characters at the ent of lines in the previous post (I put them back in). I'm sure you caught that when you tried it.

PS:I thought its good to join this here rather than starting a new thread.

No problem.

So if the text the user enters is a filename you want to display the file. I get that. And if it is not a file you want to compare it to a list of builtin codes. How about something like this

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

        If My.Computer.FileSystem.FileExists(TextBox2.Text) Then
            DisplayFile(TextBox2.Text)
        ElseIf LCase(TextBox2.Text).StartsWith(".sys\") Then
            ExecuteCustomCommand(TextBox2.Text)
        Else
            MsgBox("file " & TextBox2.Text & " not found")
        End If

    End Sub

    Private Sub ExecuteCustomCommand(command As String)

        Select Case LCase(command)
            Case ".\sys\clock"
                TextBox1.AppendText(vbCrLf & "It's " & DateTime.Now)
            Case ".\sys\user"
                TextBox1.AppendText(vbCrLf & "You are " & Environment.UserName)
            Case Else
                MsgBox("invalid .sys\ command")
        End Select

    End Sub

    Private Sub DisplayFile(filename As String)
        'your code here
    End Sub

I tried the code that I posted against the PUBS database (one of the sample databases you can download from Microsoft for SQL Server). It worked just fine. If you zip your entire project and post it here I'll load it up and give it a look.

It's been a while since I did this from vbScript but this works. Save this code in sampleapp.vbs and create a test widget.xls file to play with.

set xls = CreateObject("Excel.Application")
xls.WorkBooks.Open "d:\script\dani\widget.xls"

With xls.WorkBooks(1).WorkSheets(1)
    .Cells(2,1) = "Malcolm"
    .Cells(2,2) = "Delarge"
    .Cells(2,3) = 855321
End With

xls.WorkBooks(1).SaveAs "d:\script\dani\widget2.xls"
xls.Quit

quick note - if you get errors while playing you may end up with one or more Excel.exe tasks running. Just use task manager to kill them. Personally, I use pskill.exe (free sysinternals suite app)

Took me five minutes to find it here

I didn't realize until I looked it up that you can only use NZ if the Expression Service is available. And the Expression Service is only available from within an Access application instance. Which means that queries from VB have to use the following instead

"SELECT StudentID, IIF(Physics   IS NULL,0,Physics)   + " & _
                 " IIF(English   IS NULL,0,English)   + " & _
                 " IIF(Chemistry IS NULL,0,Chemistry) + " & _
                 " IIF(Biology   IS NULL,0,Biology)   + " & _
                 " IIF(History   IS NULL,0,History) AS total " & _
"  FROM tblClass1 " & _
" WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

That's just retarded.

If you want to get some idea of the complexity, google

mozilla thunderbird source code download

and get the source code for Thunderbird.

Try this instead

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Public connectionstring As String = "Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;"
    Public sqlDataset As New DataSet
    Public recnum As Integer

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

        Dim conn As New SqlConnection(connectionstring)
        Dim da As New SqlDataAdapter("SELECT * FROM Authors ", conn)

        conn.Open()
        da.Fill(sqlDataset, "Vb_table")
        conn.Close()

        recnum = 0
        nav()

    End Sub

    Private Sub nav()

        If recnum < sqlDataset.Tables("Vb_table").Rows.Count Then
            TextBox1.Text = sqlDataset.Tables("Vb_table").Rows(recnum).Item(1)
            TextBox2.Text = sqlDataset.Tables("Vb_table").Rows(recnum).Item(2)
            TextBox3.Text = sqlDataset.Tables("Vb_table").Rows(recnum).Item(3)
        Else
            MsgBox("end of table")
        End If

    End Sub

    Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click
        recnum += 1
        nav()
    End Sub

End Class

Also, SUM doesn't add up the values inside the parentheses.

SELECT SUM(Physics) FROM tblClass1

Will give you the total of all the Physics marks for all students. It SUMS down the rows rather than across the columns.

You are talking about an incredibly complex program (Outlook) with many thousands of lines of code requiring intimate knowledge of several technologies and it's only your third project in VB? I think you are biting off way more than you can chew. In any case, you are asking for far more assistance than is appropriate for this forum.

You could also set the default value for any mark field to 0 so that NULLS are never encountered.

Try the following. I don't have a sample database in Access so I haven't tried this query. I used SQL with the COALESCE function (does the same thing as NZ), then did a copy/paste.

"SELECT StudentID, NZ(Physics,0) + NZ(English,0) + NZ(Chemistry,0) + " & _
"                  NZ(Biology,0) + NZ(History,0) AS total " & _
"  FROM tblClass1 " & _
" WHERE StudentID = '" & Trim(Me.txtSearch.Text) & " ' "

For something a little fancier try playing with the following. Each time a digit is pressed, it saves the current (before inserting the new digit) in the Tag property. This is used to undo the newest digit if the resulting value exceeds some threshhold.

    Private Sub TextBox1_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
        Dim txt As TextBox = sender
        txt.Tag = txt.Text
        e.Handled = InStr("1234567890", e.KeyChar) = 0 And Asc(e.KeyChar) <> Keys.Back
    End Sub    

    Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged
        Dim txt As TextBox = sender
        If CInt(txt.Text) > 1500 Then
            txt.Text = txt.Tag
            MsgBox("too big")
        End If
    End Sub

If TextBox1.Text > 1500 Then

Doesn't work because you can't directly compare strings and numbers. Also

If TextBox1.Text > "1500" Then

won't work because "2" > "1500". If you limit your textbox to entering numbers only then you can do

If CInt(TextBox1.Text) > 1500 Then

To restrict a textbox to digits only you add

Private Sub TextBox1_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
    e.Handled = InStr("1234567890", e.KeyChar) = 0 And Asc(e.KeyChar) <> Keys.Back
End Sub

This allows only digits, backspace, delete and left/right cursor.

As far as I know, Access does not support temporary tables. However, you can create a table on the fly with SELECT INTO as in

SELECT * INTO temptable FROM Customers WHERE Address  LIKE '*C*'

Just remember to drop the table when you are done.

What do you mean by "nothing happened"? Were both objects defined or not?

What version of VB are you using? Mine is Visual Studio 2010. Perhaps yours does not support using literal arrays. If that is the case you might try

System.IO.File.WriteAllText(filename, _
    empIDbox.Text & vbCrLf & _
    fnamebox.Text & vbCrLf & _
    lnamebox.Text & vbCrLf & _
    salbox.Text   & vbCrLf)

And if you prompt for input and the user enters ".sys\" then what? Tell me what you are trying to do (never mind the code) and I'll try to help.

The obvious guess would be either TextBox1 isn't defined or sqlDataset isn't defined. Put a breakpoint at the line where you get the error and try to examine both objects.

VB 2010 uses imiplicit line continuation. Try

System.IO.File.WriteAllLines(filename, _
    {empIDbox.Text,fnamebox.Text,lnamebox.Text,salbox.Text})

If the file format is not fixed then I suggest you change it to

ID;fname;lname;salary

Then you can read the entire file into an array. Instead of searching the array you can use Filter to locate the desired record. For example, if your file contains

10211;Michael;Phelps;37.25
90210;Gwen;Philpott;23.71
40104;Sam;Beckett;73.45

then once the file is read into the array "empdata" you can locate a particular employee by

Dim rec() As String = Filter(empdata,"90210")

If Ubound(rec) <> -1 Then
    Dim flds() As String = rec(0).Split(";")
    'flds(0) = empID
    'flds(1) = fname
    'flds(2) = lname
    'flds(3) = salary
Else
    'not found
End If

I have no way of knowing where your code is being executed. Is any of the code inside the timer Tick event handlers? For what you are trying to do I would put the following code in the Timer2_Tick handler

counter += 1
Label1.Text = counter

If counter = 180 Then
    counter = 0
    Timer2.Enabled = false
    Timer3.Enabled = true
End If

then in Timer3_Click I would put

counter += 1
label2.Text = counter

If counter = 80 Then
    Timer3.Enabled = False
End If

It looks like you pretty much have it except for the Else (and you should use & instead of + to concatenate strings).

If TextBox2.Text = ".\sys\clock" Then
    TextBox1.Text = TextBox1.Text & Environment.NewLine & "It's " & DateTime.Now
Else
    Textbox1.text = "You didn't use the .SYS\ code"
End If

The easiest way to write out the text is

System.IO.File.WriteAllLines(filename,
    {empIDbox.Text,fnamebox.Text,lnamebox.Text,salbox.Text})

To read it back

Dim text() As String = System.IO.File.ReadAllLines(filename)

If text(0) = txtUpdateID.Text Then
    txtUpdateFirstName.Text = text(1)
    txtUpdateLastName.Text = text(2)
    txtSalupdate.Text = CInt(text(3)).ToString("C2")