Updating SQL 2005 in a loop dont work

[Fendi Baba]

I wrote a code to loop through an array and only the first item is
updated. I think the sqlcommand is not updated properly in my code but
I am not sure how to go about this.

This is my code:

Partial Class admin_batch
Inherits System.Web.UI.Page

Protected Sub ButtonUpdate_Click(ByVal sender As Object, ByVal e
As System.EventArgs) Handles ButtonUpdate.Click
Dim strconnectionString As String =
WebConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
Dim con As New SqlConnection(strconnectionString)
Dim s As String
Dim numAff As Integer
Dim value As DateTime
Dim deliveryvalue As String
value = CalIssuedDate.SelectedDate
Deliveryvalue = "True"
Dim words As String = TextBoxUpdate.Text
Dim split As String() = words.Split(Chr(13))

Dim sql5 As String = "Update gown_orders SET
DeliveryStatus=@DeliveryStatus, IssueDate=@DateIssued where
StudentID=@StudentID"
Dim cmd5 As New SqlCommand(sql5, con)


For Each s In split
If s.Trim() <> "" Then
Try
con.Open()
cmd5.Parameters.Clear()
cmd5.Parameters.AddWithValue("@DeliveryStatus",
deliveryvalue)
cmd5.Parameters.AddWithValue("@DateIssued", value)
cmd5.Parameters.AddWithValue("@StudentID",
Trim(s))

numAff = cmd5.ExecuteNonQuery()

Catch ex As Exception
Label1.Text = ex.Message

Exit Try


Finally

Label1.Text = "Successfully Updated"
con.Close()
End Try






End If
Next s

TextBoxUpdate.Text = ""
End Sub
====================================================

Any ideas why the rest of the records are not updatable?

Thanks

 

[Hans Kesting]

I wrote a code to loop through an array and only the first item is

updated. I think the sqlcommand is not updated properly in my code but
I am not sure how to go about this.

Not sure it will help, but you could try to change your parameter handling
a bit:
there is no need to clear all parameters and add them back.
You can use a separate SqlParameter for the studentID. Add all parameters
to the command-object *before* the loop (including this studentID param)
and *in* the loop only change the Value of that parameter.

Can you verify (with a profiler) that the expected update commands
arrive at SqlServer? (expected number of updates and expected text
of the command)

Hans Kesting

 

[Guest]

I wrote a code to loop through an array and only the first item is
updated. I think the sqlcommand is not updated properly in my code but
I am not sure how to go about this.

Try to test

Response.Write ("array has " & split.Length & " items")
Response.Write ("split(1)=" & split(1))
Response.Write ("split(2)=" & split(2))

 

[Hans Kesting]

Try to test

Response.Write ("array has " & split.Length & " items")
Response.Write ("split(1)=" & split(1))
Response.Write ("split(2)=" & split(2))

A tip: I usually use something like
Response.Write ("split(1)=[" & split(1) & "]")
where extra brackets (or quotes) are used, so I can see additional spaces
etc that prevent a match.

By the way: in C# I would start at split(0), I don't know if that is the
same in VB?


Hans Kesting

 

[Hans Kesting]

I wrote a code to loop through an array and only the first item is

updated. I think the sqlcommand is not updated properly in my code but
I am not sure how to go about this.

This is my code:
[snip]
Dim sql5 As String = "Update gown_orders SET
DeliveryStatus=@DeliveryStatus, IssueDate=@DateIssued where
StudentID=@StudentID" [snip]
cmd5.Parameters.AddWithValue("@StudentID",
Trim(s))

A guess: is StudentID a numerical column in the database?
this @StudentID parameter will be a string! This might or might not give
problems.
You can create a SqlParameter with a specified type.

Hans Kesting