Update a sql table with arraylist

Question

User-1767698477 posted

I have an array list which contains the DocIDs which need to be updated. How do you cycle thorugh the array with a for each ? When I do this, It gives the error that The variable name '@DocID' has already been declared. Variable names must be unique within a query batch or stored procedure. This is from the intellisense.

So there were 2 items in the array. It is throwing this on the second time through the loop?

Protected Function SentFilestoProcessor(ByVal DocIDs As ArrayList) As Boolean
For i = 1 To DocIDs.Count - 1
Response.Write(DocIDs([i]))
Next
Dim sqlquery As String = "UPDATE Documents SET [DateSenttoProcessor] = @DateSenttoProcessor WHERE DocID=@DocID"
Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)

Dim cmdupdate As New SqlCommand(sqlquery, cn)

For Each docid In DocIDs
cn.Open()
For i As Integer = 0 To DocIDs.Count - 1
cmdupdate.Parameters.AddWithValue("@DocID", DocIDs(i))
cmdupdate.Parameters.Add(New SqlParameter("@DateSenttoProcessor", Date.Now))
Next
cmdupdate.ExecuteNonQuery()
cn.Close()
cn.Dispose()
Next
Return True
End Function

Answer 1

User-1767698477 posted

Since posting this code about 15 minutes ago, I was able to get it working with this:

Protected Function SentFilestoProcessor(ByVal DocIDs As ArrayList) As Boolean
For i As Integer = 0 To DocIDs.Count - 1
Dim sqlquery As String = "UPDATE Documents SET [DateSenttoProcessor] = @DateSenttoProcessor WHERE DocID=@DocID"
Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString)
Dim cmdupdate As New SqlCommand(sqlquery, cn)
cn.Open()
For j As Integer = 0 To DocIDs.Count - 1
cmdupdate.Parameters.Clear()
cmdupdate.Parameters.Add(New SqlParameter("@DocID", DocIDs(i)))
cmdupdate.Parameters.Add(New SqlParameter("@DateSenttoProcessor", Date.Now))
Next
cmdupdate.ExecuteNonQuery()
cn.Close()
cn.Dispose()
Next
Return True
End Function

This code is working for me, but is it really how it should be done? I'm clearing all the parameters and adding new ones on each iteration of the loop. I could not get it work any other way. It seems to me there should be another way to do this without closing and disposing the connection each time. If there was a way to replace the value in the DodID parameter on each cycle of the loop, that would be great. Is there a way to do that?

Answer 2

User-1330468790 posted

Hi sking,

  

This code is working for me, but is it really how it should be done? I'm clearing all the parameters and adding new ones on each iteration of the loop. I could not get it work any other way.

I think you should not use for loop twice as you only need to iterate the array list once. There is another way that you could use store procedure to do multiple update.

  

It seems to me there should be another way to do this without closing and disposing the connection each time.

Yes, you could use "Using" statement which will release the resource automatically.

 

If there was a way to replace the value in the DodID parameter on each cycle of the loop, that would be great. Is there a way to do that?

As far as I can see, you already have updated the value on each cycle of the loop in your code though there are still some errors.

I would rewrite your code and you could see if it is what you want.

   

More detail, you could refer to below code.

.aspx page:

<form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" ></asp:GridView>
        </div>
       <div>
           <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Update Table" />
       </div>
    </form>

Code behind:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindGridView()
        End If
    End Sub

    Private Sub BindGridView()
        Dim sql As String = "SELECT * FROM [Documents]"
        GridView1.DataSource = SelectFromDatabase(sql, Nothing)
        GridView1.DataBind()
    End Sub

    Public Shared Function SelectFromDatabase(ByVal sql As String, ByVal parameters As SqlParameter()) As DataTable
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

        Using con As SqlConnection = New SqlConnection(constr)

            Using cmd As SqlCommand = New SqlCommand(sql, con)

                If parameters IsNot Nothing Then
                    cmd.Parameters.AddRange(parameters)
                End If

                Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Dim sql As String = "UPDATE [Documents] SET [DateSenttoProcessor] = @DateSenttoProcessor WHERE DocID = @DocID"
        Dim DocIDs As ArrayList = New ArrayList() From {
            1,
            2,
            3,
            4,
            5
        }

        Using conn As SqlConnection = New SqlConnection(constr)

            Try
                conn.Open()

                Using cmd As SqlCommand = New SqlCommand(sql, conn)

                    For j As Integer = 0 To DocIDs.Count - 1
                        cmd.Parameters.Clear()
                        cmd.Parameters.Add(New SqlParameter("@DocID", DocIDs(j)))
                        cmd.Parameters.Add(New SqlParameter("@DateSenttoProcessor", DateTime.Now))
                        cmd.ExecuteNonQuery()
                    Next
                End Using

            Catch ex As SqlException
                Debug.WriteLine(ex.ToString())
            Finally
                conn.Close()
            End Try
        End Using

        BindGridView()
    End Sub

Demo:

  

Hope this can help you.

Best regards,

Sean