[RESOLVED] [2005] SQL Update Question - with List variable

[stimbo]

I have a list(of String) variable that has 150 items in it. I basically just want to add those values to a column in my table and was wondering how to do it?

Here's the relevant code. Obviously this doesn't work and from the error message I got regarding parameter already existing (on the 2nd loop no doubt) I can see why. I've just included it to demonstrate what I want. Everything is connecting okay etc... so it's just the best way to add the range. I saw the Parameters.AddRange but can't find a decent example of what it's about so don't know if that's relevant.

Dim cmd As String = "INSERT INTO Pathology (PathologyValue) VALUES (@Path)"
        Dim insrt As New SqlCommand(cmd, conn)
 
        Try
            conn.Open()
 
            For i As Integer = 0 To myList.Count - 1
 
                insrt.Parameters.AddWithValue("@path", myList(i))
 
                insrt.ExecuteNonQuery()
 
            Next
 
            conn.Close()
 
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

[bmahler]

Try something like this

Dim insrt As SqlCommand
        Try
            For i As Integer = 0 To myList.Count - 1
                insrt = New SqlCommand(cmd, conn)
                conn.Open()
                insrt.Parameters.AddWithValue("@path", myList(i))
                insrt.ExecuteNonQuery()
                conn.Close()
                insrt = Nothing
            Next

[stimbo]

That's just a type0, here's the error message I get:

The variable name '@Path' has already been declared. Variable names must be unique within a query batch or stored procedure.

I took that to mean I can't use the Parameter like that, i.e. dupicate 'declaration' or addition of it in the same loop..

Taking out the For loop as a test and just having it add one item to the DB works fine (as you would expect):

insrt.Parameters.AddWithValue("@Path", myList(0))

[stimbo]

That's done the trick. It makes sense too. I should have thought of it.

Thanks very much.