Click here to Skip to main content
12,396,230 members (63,625 online)
Rate this:
 
Please Sign up or sign in to vote.
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()
Posted 21-Jul-12 1:43am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You can Add the parameters before the loop, using Parameters.Add instead of AddWithValue, and the set the value inside the loop.

But more significantly, INSERT statements do not use a Reader...so you can throw that line away!
  Permalink  
Comments
Gary Heath 21-Jul-12 8:17am
   
Ha Ha, oh yeah, can't see the wood for the trees sometimes !!! Thanks ... works just great now.
OriginalGriff 21-Jul-12 8:23am
   
:D Happens to us all!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 21 Jul 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100