Updating SQL 2005 in a loop dont work

Discussion in 'ASP .Net' started by Fendi Baba, May 23, 2007.

  1. Fendi Baba

    Fendi Baba 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.

    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
    Fendi Baba, May 23, 2007
    #1
    1. Advertising

  2. Fendi Baba

    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.
    >


    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


    > 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").Connection
    > String
    > 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, May 23, 2007
    #2
    1. Advertising

  3. "Fendi Baba" <> wrote in message
    news:...
    >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))
    Alexey Smirnov, May 23, 2007
    #3
  4. Fendi Baba

    Hans Kesting Guest

    > "Fendi Baba" <> wrote in message
    > news:...
    >
    >> 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))



    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, May 24, 2007
    #4
  5. Fendi Baba

    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.
    >
    > 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
    Hans Kesting, May 24, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alexandre
    Replies:
    4
    Views:
    470
    Lasse Reichstein Nielsen
    Apr 12, 2005
  2. Fendi Baba
    Replies:
    2
    Views:
    286
    Fendi Baba
    May 24, 2007
  3. Replies:
    2
    Views:
    346
    Kevin Handy
    Sep 20, 2007
  4. news.rcn.com
    Replies:
    2
    Views:
    1,108
    Roedy Green
    Dec 10, 2007
  5. Isaac Won
    Replies:
    9
    Views:
    354
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page