Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-CE VB.NET
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 at 21-Jul-12 8:17am
   
Ha Ha, oh yeah, can't see the wood for the trees sometimes !!! Thanks ... works just great now.
OriginalGriff at 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
0 mhegazy94 460
1 Sergey Alexandrovich Kryukov 450
2 Kornfeld Eliyahu Peter 305
3 Ravi Bhavnani 200
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 6,890
1 OriginalGriff 6,761
2 CPallini 5,350
3 George Jonsson 3,609
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 21 Jul 2012
Copyright © CodeProject, 1999-2014
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