Posts by Reverend Jim

You set the SQL command and opened the connection to the database but you didn't execute the query.

SQLCmd = New MySqlCommand(strQuery, dbCon)
dbCon.Open()
c.StdMsg("Record Saved.")

Try changing

strQuery = "INSERT INTO login (username, password) values (" & txtUser.Text & "', '" & txtPwd.Text & "');"

to

strQuery = "INSERT INTO login (username, [password]) values ('" & txtUser.Text & "', '" & txtPwd.Text & "')"

Looks like you missed the single quote before the first value. You might want to read up on parameterized entry and how to avoid SQL injection attacks. Also, password is likely a reserved word. Note I used [ and ] around password in the rewritten query.

You have another table which uses that primary key as a foreign key. If you delete that record then the other table will have an orphan (a record that doesn't point back anywhere). If you are using MS SQL you should be able to set up something called cascading referential integrity. This causes all dependent records in other tables to be automatically deleted when the primary record is deleted. I've never used it but you can read up on it here.

I'm not that familiar with Access.

The following query does the calculation and orders the results from top marks to lowest marks

SELECT StudentID, Physics+English+Chemistry+Biology+History AS totalMarks
  FROM tblClass1
 ORDER BY totalMarks DESC

To pick the top five you can use

SELECT TOP 5 StudentID, Physics+English+Chemistry+Biology+History AS totalMarks
  FROM tblClass1
 ORDER BY totalMarks DESC

but keep in mind that you may have more than five students with the top five marks.

May I know where can I add the cellClick event handler in the code that i provided above?

Select the DataGridView control in design view. Go to the events tab of the properties and double click CellContentClick. An empty event handler will be added to your code that you can modify as needed.

You haven't said if you are having a problem with

• determining which cell was clicked
• determining the value in that cell
• calling another form
• passing a value to that form

In the CellClick event handler of the DataGridView you can determine the row and column clicked by

e.RowIndex and e.ColumnIndex

You can get the value by

Dim grd As DataGridView = sender
MsgBox(grd(e.ColumnIndex,e.RowIndex).Value)

Use that value when the new form is displayed.

Do a query to select the record with that ID. If it returns a record then you know that it exists.

Dim myID As String = "123"
query = "SELECT * FROM myTable WHERE company_ID = '" & myID & "'"

or even

query = "SELECT COUNT(*) FROM myTable WHERE company_ID = '" & myID & "'"

To return only the number of records found. How you execute the query depends on the method used to access the database (SQL, ADO, OLEDB).

We'd have to see the structure of the table. Is there one record per student? Are there multiple records where the multiple records have to be added before comparing to other students? Are the grades stored as numbers or letters? The structure of the table will determine the format of the query.

It is also important to know what database you are using. For example, MS SQL supports the RANK OVER clause which returns ranking numbers. This is important because if you want to know the top five students you can't just do an ORDER BY and pick the top 5. You may have (because of ties) more than five people with the top five grades. RANK OVER will account for that.

Unfortunately you have one side that has demonstrated it will do anything to win including obstructing government, voter registration fraud, lying and bankrupting the country. Also unfortunately, you have another side that still thinks it is possible to "play nice" with the other side.

Maybe if I explain to him rationally that we all have to get along to make this a better world the schoolyard bully will let me keep my lunch money. Hasn't ever worked in my experience.

Does this help

Dim newheader As New System.Windows.Forms.ColumnHeader
newheader.Text = "my text"
newheader.Width = 60
ListView1.Columns.Add(newheader)

When I run the following code

Private Sub Button_Click(sender As System.Object, e As System.EventArgs) Handles btnCol1.Click
    For i = 1 To 100
        ComboBox1.Items.Add(i)
    Next
End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    ComboBox1.Items.Clear()
    For i = 1001 To 1010
        ComboBox1.Items.Add(i)
    Next
End Sub

I get what I would expect. After btnCol1.Click I get a combobox with items 1-100. After Button1.Click I get a combobox with items 1001-1010. No large but mostly empty list. Everything looks fine. Try doing a Debug.WriteLine when you are adding the items and see if you notice anything unusual.

If you go into design view, right click on the listview and select "Edit Columns" you will see the names of all the columns (you can change these names). You can use these names to modify the header text at runtime as in

ColumnHeader1.Text = "new header text"

If you need to add column headers at runtime, I've found the best way to find out how this is done is to create the headers at design time, then look at the contents of Form1.Designer.vb in your project folder (replace Form1 with your actual form name if different). This file contains the code to create the controls. You can use the same code at runtime.

"Password" is a reserved word in SQL. Try

mystring = "UPDATE ClientLogIn SET [Password] = '" & PasswordTextBox.Text & "' WHERE  ClientID = '" & ClientIDTextBox.Text & "' AND Username = '" & UsernameTextBox.Text & "'"

First suggestion is to declare the parameter types in AddUserFun as

Function AddUserFun(user As String, pass As String, acclvl As Integer) As Integer

and at the top of this function you can add (for now)

Debug.WriteLine("ADD user=" & user & "  pass=" & pass & "  acclvl=" & acclvl.ToString())

If all of the acclcl values are coming in as 0 then your problem is in Button_Click where you are setting the value of Acc_Lvl, in which case you will have to do some debugging in your Select/Case clause. It would help to add an Else clause as in

Select Case var
    Case "Level 1 ( ACCESS EVERYTHING)"
        Acc_Lvl = 1
    Case "Level 2 (CAN NOT ADD AND DELETE USER)"
        Acc_Lvl = 2
    Case "Level3 (CAN ONLY USE THE ORDERS PAGE AND VIEW THE BILLS)"
        Acc_Lvl = 3
    Case Else
        MsgBox("unexpected value " & var)
End Select

It might help prevent typos is you did your Select/Case on only the first few chars of the string as in

Select Case var.Substring(0,7)

I also couldn't help but notice your strings start with

"Level 1"
"Level 2"
"Level3"

note the lack of a blank in the third entry. Typos like this can screw up the code. That's why you need the "Case Else" to catch these types of errors.

Can you please explain in more detail?

On form load you can do

txtDayRate.Text = My.Settings.Dayrate

If the value changes you can save it back as

My.Settings.DayRate = txtDayRate.Text

It means you are trying to use an object before it was created. When you declare

Dim con As SqlConnection

you are saying "I want to create a variable that can be used to refer to a SqlConnection object". That doesn't actually create the object., however if you do

Dim con As New SqlConnection

That creates the reference AND creates the object. It is shorthand for

Dim con As SqlConnection
con = New SqlConnection

The first statement creates a variable (con) that can refer to the object. The second statement creates the object (allocates memory) and assigns the address of that object to con.

It's like going to the post office and saying "I want to reserve an address". That doesn't automatically cause a house to be built. However, if you then build a house at 123 Elm Street, then go to the post office and register that address you can now get mail sent to that location.

"Object reference not set to an instance of an object" would be the equivalent of "Return to sender - no such address".

It would have helped if you'd mentioned that at the start. Use

Dim con As New SqlConnection
Dim cmd As New SqlCommand

In the line

cmd.CommandText = "INSERT INTO table([Name], [Comment], [emailaddress]) VALUES([txtname], [txtcomment], [txtemail])"

you haven't supplied any values for the fields. The actual query needs data as in

cmd.CommandText = "INSERT INTO table([Name], [Comment], [emailaddress]) " &
                   VALUES('Fred','some guy','fred@hotmail.com')"

or

cmd.CommandText = "INSERT INTO table([Name], [Comment], [emailaddress]) VALUES(" &
                  "'" & txtName.Text    & "'," &
                  "'" & txtComment.Text & "'," &
                  "'" & txtEmail.Text   & "')"

although to be safe you should use parameterized entry to avoid SQL injection attacks as in

cmd.CommandText = "INSERT INTO table([Name], [Comment], [emailaddress]) VALUES(@name,@comment,@email)"
cmd.Parameters.AddWithValue("@name","Fred")
cmd.Parameters.AddWithValue("@comment","some guy")
cmd.Parameters.AddWithValue("@email","fred@hotmail.com")

Add the wrapper then save it to a temporary file and read it back into the xmldocument object.

Ah. I see. Look at these two code samples

Dim student As Integer

For student = 1 To 3
    'do stuff here
Next

and

For student As Integer = 1 To 3
    'do stuff here
Next

In the first case, student has scope outside the loop where it isn't used. In the second case, student is only defined within the loop. It's similar to the practice of making a variable global only if it is used globally. The scope of a variable should reflect its use.

Give me a specific question. If I go into more detail overall then I am basically doing your homework for you.

If you combine this with your other thread then you could just run unzip.exe in the spawned shell.

The following code implements a GUI front end for a command shell. As it stands, you type the command into the textbox at the bottom then click "Execute". You can bypass this and just feed commands to the shell directly as in

Submit("dir d:\temp")

If you clear txtStdOut and txtStdErr at the top of the Submit sub then you can scan either or both for results and errors after the command has executed.

'                                                                           '
'  Name:                                                                    '
'                                                                           '
'    CommandShell.vb                                                        '
'                                                                           '
'  Description:                                                             '
'                                                                           '
'    GUI front end for a command line shell                                 '
'                                                                           '
'  Notes:                                                                   '
'                                                                           '
'    Type all the regular DOS commands in the text box input and they are   '
'    executed with the results displayed in the top portion of the display. '
'                                                                           '
'  Audit:                                                                   '
'                                                                           '

Public Class Form1

    'Note - because the spawned process is running in a different thread, the only way
    'for it to write to a control in this process is to use a delegate.

    Private WithEvents MyProcess As Process

    Private Delegate Sub AppendStdOutDelegate(ByVal text As String)
    Private Delegate Sub AppendStdErrDelegate(ByVal text As String)

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

        Me.AcceptButton = ExecuteButton

        MyProcess = New Process

        With MyProcess.StartInfo
            .FileName = "CMD.EXE"
            .UseShellExecute = False
            .CreateNoWindow = True
            .RedirectStandardInput = True
            .RedirectStandardOutput = True
            .RedirectStandardError = True
        End With

        MyProcess.Start()

        MyProcess.BeginErrorReadLine()      'start async read on stderr
        MyProcess.BeginOutputReadLine()     'start async …

I think what is happening is the blanks in the path are screwing things up. There are two things you could try. One is to use the 8.3 version of the path. You can get this by doing "dir /x". The other possibility is to add another set of double quotes around both arguments. You should be able to verify this by trying

cmd /K ""C:\Users\Administrador\documents\visual studio 2012\Projects\SWF Builder\SWF Builder\bin\Debug\RABCDAsm\rabcasm.exe"" ""C:\Users\Administrador\documents\visual studio 2012\Projects\SWF Builder\SWF Builder\bin\Debug\Source\3.3.0-1\3.3.0-1.main.asasm""

Now try opening a cmd shell and pasting

cmd /K "C:\Users\Administrador\documents\visual studio 2012\Projects\SWF Builder\SWF Builder\bin\Debug\RABCDAsm\rabcasm.exe" "C:\Users\Administrador\documents\visual studio 2012\Projects\SWF Builder\SWF Builder\bin\Debug\Source\3.3.0-1\3.3.0-1.main.asasm"

Let's ignore the error checking for now and just get the logic right. In pseudo-code we have

clear output string

for 3 students

    get name
    set total to 0

    for 3 scores
        add next score into total
    next

    calculate average as total / 3
    convert average to letter grade
    add line to output area

next

display output

For loop counters that you don't need after the loop completes it is better to declare them for the duration of the loop only as in

For students As Integer = 1 to 3

The same applies for the test scores as

For scores As Integer = 1 to 3

Your code says you want to Exit if a blank score is entered. Fair enough. You want to flag non-numeric scores. You should also flag invalid numbers like -5 and 110. The following loop will cycle until a valid score is entered or a null score is entered. If we drop out the bottom of the loop then we have a valid score. It would be convenient to make this into a separate function.

Do
    get next score as string

    If score is null Then
        display message
        Exit
    End If

    If score is a number from 0 to 100 Then
        Exit Loop
    End If

    display error message

Loop

calculate average

To convert from a number to a letter consider that you have five cases and you want to select one case. That's a hint. You …

Here is a sample of traversing a simple xml structure

    Dim myxml = <root>
                    <level id="1">
                        <prop n="PROPERTY" v="VALUE"/>
                    </level>
                    <level id="2">
                        <prop n="PROPERTY" v="VALUE"/>
                    </level>
                </root>

    For Each level In myxml.Elements
        Debug.WriteLine("id=" & level.@id.ToString)
        For Each prop In level.Elements
            Debug.WriteLine("n=" & prop.@n.ToString & "  v=" & prop.@v.ToString)
        Next
    Next

Post the string again so I can see the new value. It might be easier to just do

Debug.WriteLine myProcess.StartInfo.Arguments

then copy and paste the text

After line 3 add the following

MsgBox(myProcess.StartInfo.Arguments)

and post the results here

I would make the above code into a function and pass it

exefile - the program to run
args    - the arguments
timeout - how long to wait for completion

and it could return true (if it ran to completion) or false (if it timed out and was killed). There are also ways in which you can redirect stdin, stdout and stderr so that you can get feedback (capture output and errors). You might get some ideas from the code I posted here.

I'd start by changing the code (for now) as follows

    Dim myProcess As Process = New Process()

    myProcess.StartInfo.FileName = "cmd.exe"
    myProcess.StartInfo.Arguments = "/K " & "rabcasm.exe " & "\Source\" & "3.3.0-1\3.3.0-1.main.asasm"

    myProcess.StartInfo.WindowStyle = ProcessWindowStyle.Normal
    myProcess.StartInfo.CreateNoWindow = False
    myProcess.Start()

    myProcess.WaitForExit(60000)
    If Not myProcess.HasExited Then
        myProcess.Kill()
    End If
    myProcess.Close()

Note the changes as follows

• using /K instead of /C to keep the window open
• using ProcessWindowStyle.Normal and CreateNoWindow = False
• WaitForExit(60000)

Those changes will allow you to see what is happening. It is possible your process is getting hung up somewhere. Making the window visible and keeping it open will give you a chance to see if that is the case. Your WaitForProcess was only set to one second which means you were likely killing the process before it completed.

Public Sub writeFile(filePath As String, noLine As Integer, newText As String)
    Dim lines() As String = IO.File.ReadAllLines(filePath)
    lines(noLine) = newText
    IO.File.WriteAllLines(filepath,lines)
End Sub

Read the entire file into a text array (System.IO.File.ReadAllLines). Replace the specific entry in the text array, then write the array back to the file (System.IO.File.WriteAllLines).

Look at my code

SQLstr = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
         "   FROM VAC_JSINFO " &
         "  WHERE (COLLECTDATE > '" & fstrtTime & "') " &
         "    AND (COLLECTDATE < '" & fendTime & "') " &
         "    AND (KYUERR1 >= 0) " &
         "  ORDER BY RM DESC"

and look at your code

Dim SQLstr As String = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
SQLstr &= " FROM VAC_JSINFO "
SQLstr &= " WHERE (COLLECTDATE > '" & fstrtTime & "') AND (COLLECTDATE < '" & fendTime & "') AND (KYUERR1 >= 0) "
SQLstr &= " ORDER BY RM DESC "

see the difference? Try this

Public Function SumYC1() As String

    Dim SQLstr As String
    Dim fstrtTime As String
    Dim fendTime As String
    Dim res1 As String

    Dim DBConn As New SqlConnection("server=xxxx;uid=xxx;pwd=xxx;database=xxx")
    DBConn.Open()    

    Dim DBCmd As New SqlCommand
    DBCmd.Connection = DBConn

    Dim i As Integer = 0

    Do
        i -= 1
        fstrtTime = Date.Now.AddDays(i).ToString("yyyy/MM/dd") & " " & "06:15:00"
        fendTime  = Date.Now.AddDays(i).ToString("yyyy/MM/dd") & " " & "18:15:00"

        SQLstr = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
                 "   FROM VAC_JSINFO " &
                 "  WHERE (COLLECTDATE > '" & fstrtTime & "') " &
                 "    AND (COLLECTDATE < '" & fendTime & "') " &
                 "    AND (KYUERR1 >= 0) " &
                 "  ORDER BY RM DESC"

        DBCmd.CommandText = SQLstr
        res1 = DBCmd.ExecuteScalar().ToString()

    Loop …

Build the entire query inside the loop as

SQLstr = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
         "   FROM VAC_JSINFO " &
         "  WHERE (COLLECTDATE > '" & fstrtTime & "') " &
         "    AND (COLLECTDATE < '" & fendTime & "') " &
         "    AND (KYUERR1 >= 0) " &
         "  ORDER BY RM DESC"

Is there still a question pending?

Or more concisely

Dim folder As String = "D:\temp"

For Each file As String In My.Computer.FileSystem.GetFiles(folder, FileIO.SearchOption.SearchTopLevelOnly, "*.csv")
    Debug.WriteLine(file)
    'do work
Next

Use My.Settings. It's a lot easier. Instead of all that file I/O and wondering where the file is (or if it's been moved or deleted or in the wrong format) you basically just read/write a variable.

Instead of creating dynamic controls I think you should display the data in a DataGridView or some such control and just update it (if changed) on the timer tick. It would help if you

1. commented your code to explain what you are trying to do
2. removed code from the post that is not pertinent to the problem

Does it really matter whether or not today is Monday? Just do the loop as

set start date to today

do
    subtract one from start day
    get the records for start day
loop while record count = 0

If today is (for example) Thursday then it will kick out after one iteration (Wednesday). If it is Monday then it will kick out when it finds a day with records which should be the previous working day.

There are a number of ways to save values between sessions

1. save the value in a database
2. save the value in a file
3. save the value in a My.Settings variable

For a single value the easiest is probably the last method. Go to the project properties page and click the "Settings" vertical tab. Enter the following for the fields

Name:   finerate (or another name of your choice)
Type:   single
Scope:  Application
Value:  1.50 (or whatever your daily rate is)

In your Form Load event handler you get the value by

finerate = My.Settings.finerate

If at any point you change this value you can save it back to My.Settings by

My.Settings.finerate = finerate

You don't need to put the finerate in a column because the value doesn't change from row to row. It is a constant. The equation I gave returns the actual fine if the book is past due and zero if it is not.

You could put the due date in one column then calculate the DATEDIFF in days from today. Multiply that by the fine per day. If the result is negative or zero then the book is not overdue and the fine is zero.

fine = finerate * Math.Max(0.0, DateDiff(DateInterval.Day, duedate, Now))

This statement calculates the number of days between now and the due date. Math.Max ensures that negative values (book is not yet overdue) are replaced with zero. That number is multiplied by the daily fine rate to give the fine owing.

Too many commas. Remove the one before the WHERE clause as in

da = New MySqlDataAdapter("update tbluser set  FirsName='" & txtUF.Text & "', LastName='" & txtUL.Text & "', ContactNum='" & txtUC.Text & "', Username='" & txtUser.Text & "', Password='" & txtPass.Text & "'  where UserID='" & txtUid.Text & "'", conn)

After line 65, please add

MsgBox(SqlStatement)

and post the results here. Also, what database engine are you using?

In order for that to happen the two instances have to have some type of communication. As was pointed out earlier, this could be through something as simple as a shared file, however, this is not a good method because it requires constant polling for file changes and coordination of file access. Another option is a named pipe but I think the simplest will be the MessageQueue although I don't know how you can make this run asynch without introducing timeouts and polling. Why does it have to be two copies of one program? Why not just use one program and have it display two identical forms? That way, changes to the textbox on one form can be automatically propogated to another on the second form.

Looks like System Messaging is what you want. Have a look at the following code

'for more details please see
'http://www.informit.com/articles/article.aspx?p=23273

'For this code to work you must enable Microsoft Message Queue (MSMQ) Server
'Under Windows 7, go to Control Panel -> Programs and Features then click on
'"Turn Windows Features on or off" in the left panel. Select the box labeled
'Microsoft Message Queue (MSMQ) Server

Imports System.Messaging    'add reference to System Messaging (.NET tab)   

Public Class Form1

    'only private queues are available unless you are on a domain

    Const QNAME = ".\Private$\myQueue"
    Private myQueue As MessageQueue

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

        'create the queue if it does not exist

        If Not MessageQueue.Exists(QNAME) Then
            MessageQueue.Create(QNAME, False)
        End If

        'get a reference to the queue and set the formatting

        myQueue = New MessageQueue(QNAME, False)
        myQueue.Formatter = New BinaryMessageFormatter

    End Sub

    Private Sub btnSend_Click(sender As System.Object, e As System.EventArgs) Handles btnSend.Click

        myQueue.Send("this is my message queued at " & Now())

    End Sub

    Private Sub btnReceive_Click(sender As System.Object, e As System.EventArgs) Handles btnReceive.Click

        'if there is no message in the queue then the program will wait
        'here until a message is available.

        Dim s As String = CType(myQueue.Receive.Body, String)
        MsgBox(s)

    End Sub

End Class

Build the application using the above code. First try running just one instance. Do a send, then do a receive. Now try running two instances. Do a send from one, then a receive from the other. Now try clicking …

Have a look at how to use named pipe for full duplex inter-process communication in vb 2010

Also see System.Messaging