Insert into SCLCE Table from VB.net Array

See more:

SQL-CE

VB

SQL-Server

I'm confused, hopefully somebody can sort me out ... I have validated a Text file and the data is all loaded into an Array, ready to be added to my SQLCE Table.

This is my code, but I am having a problem, in that the variable "myTeam" isn't recognised as it outside the "For Each" loop ... however, if I move the "AddWithValue" lines inside the For "Each loop" it fails with the error "The SqlCeParameter with this name is already contained by this SqlCeParameterCollection" ... I could probably add a little counter and only process these 2 lines the first time through, but I'm sure there's a better way of doing it than that, a *proper" way !!!

Using mySqlCmd As New SqlCeCommand("INSERT INTO Teams(TeamName, LeagueID) VALUES(@myValTeamName, @myValLeagueID)", mySqlConn)
    If mySqlConn.State = ConnectionState.Closed Then mySqlConn.Open()
    mySqlCmd.Parameters.AddWithValue("@myValTeamName", myTeam) << This Line
    mySqlCmd.Parameters.AddWithValue("@myValLeagueID", myLeagueID)
    For Each myTeam In myTeamList
        Using mySqlRdr As SqlCeDataReader = mySqlCmd.ExecuteReader()
            mySqlCmd.ExecuteNonQuery()
        End Using
    Next
End Using
mySqlConn.Close()