For Loop

[xuexue]

Hi! The for loop works and it reads the value of recno(). However, the sql code itself is being executed once only.
Is there any tweak I need to do for DB2 iSeries codes?

For k = 0 To recno.Length - 1
    sqlUpdate = "UPDATE shipment SET status=@status WHERE recno=@recno"

    With cmdUpdate
        .Parameters.AddWithValue("@status", "A")
        .Parameters.AddWithValue("@recno", recno(k))

        .CommandText = sqlUpdate
        .Connection = conn
        .ExecuteNonQuery()
    End With
Next

Thanks!

[Reverend Jim]

What is recno.Length and what is its value? Please post the code associated with recno.

[xuexue]

recno is a string. Let's say recno=4 meaning recno.length = 4 just assume values.

As i said, the code works for the variable but not for the sql execution. What I want is that the sql be executed on the number of times of recno.length = 4

[Mr.M]

Try closing the connection because you keep trying to open the connection while it still opened so close the connection so that it will re open the connection.

[Reverend Jim]

If recno is a string and has the value "4" then recno.Length evaluates to 1 which is the length of the string. Perhaps you want CInt(recno) which will give you the number 4.

[Santanu Das]

I am agreed with Mr.M. Close or Dispose the CommandBuilder after updating your data

[Mr.M]

I will also agree with JIM you are also saying something different and you are doing something different then what you said. Do it like this

Dim recno As Integer
recno = 4
Dim k As Integer
For k = 0 to recno.Tostring
MsgBox("This is k: " & k)
Next

This will produce a message from 0 to 4 that means it will produce 5 messages, then what you can do is replace the

MsgBox("This is k: " & k)

With your code to access sql also don't forget to close the connection.

Hope this help you.

[Minimalist]

@xuexue: If the loop only executes one time just see how often it executes if you put: For k = 0 To 4. If this works then there is something wrong with recno.Length.

@Mr.M
1) For k = 0 to recno.Tostring how is this supposed to work?

[Mr.M]

I've tested it working. Test it and see. The

 K = 0 To recno.ToString

Means the for each k number from 0 to the value that is held by recno. Keep in mind that the Variable is already declared as Integer so that counting will be possible as it is not possible if its a string so the .Tostring means to the value that is contained by the variable. Test it then judge.

[Mr.M]

Note that I've Declared k as integer

[cgeier]

Two possibilities that I see:

Possibility 1: recNo.Length is only allowing 1 iteration (as others have stated)

Try outputing the value of k: MessageBox.Show("k: " + k);

Possibility 2:
Receiving error because SqlParameter(s) is/are already declared. This should produce an error. However, if one did the following (or some version of it):

Try

...code...

Catch ex as Exception

End Try

The error may not show--because exception handling was added but basically the exception was discarded (no code exists to handle the exception).

Solution to possibility 2:

With cmdUpdate
    .Parameters.AddWithValue("@status", "A")
    .Parameters.Add("@recno")
End With

For k = 0 To recno.Length - 1
    sqlUpdate = "UPDATE shipment SET status=@status WHERE recno=@recno"

    'for debugging purposes
    MessageBox.Show("k: " & k & " recno val: " & recno(k))

    With cmdUpdate
        .Parameters("@recno").Value = recno(k)

        .CommandText = sqlUpdate
        .Connection = conn
        .ExecuteNonQuery()
    End With
Next

Additionally, eliminate any discarded exceptions--ensure that some sort of error message is issued when an exception occurs.

[Reverend Jim]

Note that I've Declared k as integer
K = 0 To recno.ToString

It doesn't matter how you declare k. recno.ToString does not result in an integer value and in any case it is not a valid statement because it is missing the For keyword.

[Santanu Das]

@xuexue: As per your 2nd posting if recno is a string type variable, you write
recno has the value "4", then recno.Length always evalutes 1.

If you declare recno as an array i.e. Dim recno(4) as string then recno.Length evalutes the total number of elements i.e. 5. The rotation number of your loop is 5.

For k=0 To recno.Length - 1
'' Statements
Next

[Mr.M]

@Jim ok fine, why don't you copy the code as I've posted and test it, you will see the results from the message box. Please capture for me the output and post it back than we can argue about it.

[Minimalist]

@Mr.M
here is the output from your code. Also I don't know why you would insist on a mistake being correct? Have you tried to run it? I only feel sorry for the OP as her question got side tracked.

[Mr.M]

Well now I've spotted from your image you just posted, the error is because you have turned on the "Option Strict On" here on my VS I didn't turn that on so why don't you comment it and run it. The error is clear, and if I had turned that on I would have posted it together with the codes. Nothing wrong here, you just turned the Option Strict On" and claim the code is wrong, mmmm making sense and I must say I don't usually turn that on, but it work perfectly.

[Reverend Jim]

I admit that the following

For i = 0 to "4"

is effectively the same as

For i = 0 to 4

however, it requires an implicit conversion from string to integer and is a bad thing to do code wise. It is the equivalent of a farmer counting the feet and dividing by four to get the number of sheep. If it obscures the true intent of the code then it is probably a bad idea.

[xuexue]

Guys!

As I said on my post, the code works well for the variables. If I'm going to use a Msgbox to check the value of k, it loops perfectly. But the problem is that the SQL Statement is not being executed as to how many is the amount of k.

Let's say, recno='test' hence, recno.length = 4 so k=0 to 4. The loop works, it iterates up to 4x, but the SQL Statement is only being executed once.

I tried to close the connection. Am I doing it right? But nothing happens.

.CommandText = Nothing
.Connection = Nothing

Thanks!

[xuexue]

Hi!

I tried this code and it worked while I am testing the value of recno(k). This clears everything!

For k = 0 To recno.Length - 1
                sqlUpdate = "UPDATE shipment SET status='A' WHERE recno=" & recno(k)

                With cmdUpdate
                    .Parameters.AddWithValue("@status", "A")

                    .CommandText = sqlUpdate
                    .Connection = conn
                    .ExecuteNonQuery()
                End With
            Next

Thanks to all!

[cgeier]

Doing that could result in SQL injection. Use parameterized queries as in my post above. Also, things can sometimes be written with fewer lines of code, but that doesn't necessarily mean that it is good code.