Using excel data via linked sql server- unexcpected outcome

I used excel file with 103 records with email and name columns to send out emails using vb.net 2010.
I created sql server linked server to get excel data and sql server procedure simple as select * from LinkName...MydataSheet
Worked perfect in sql query window. 103 records.

In my vb code I populate data table (dt) with this data using the procedure and use the loop to go to each record, get email address from the field and send email, something like this:

for i = 1 to dt.rows.count - 1

'code to send email to this address
row(i).item(0)

Next


when debugging it goes 102 times and exits the loop.
However each email box from the file got all other 102 emails with another addresses!!!!

so for instance I have 3 records:
a@email.com
b@email.com
c@emailcom

so a,b,c got each all 3 emails!


I used that code before many times and it never failed me like that! I couldn't find the problem while debugging. The only difference between previous projects and this one is the data source. Usually I use sql server table and not excel. So I dumped the data to the sql server table and it worked correctly, no more multiple emails. Each email address got it's own email.
I then looked at the excel file, checked for anything that might have caused that strange behavior and found no formulas, no macros, just one file with 103 records on the first sheet.
Can you please help me to shed some light what happens when excel used and why it generates emails that are NOT coming out from the code when debugging????!!!!
Alina OAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
the problem could be in your scripts that send out emails. Can you ensure you create a new email object as per each loop? Or at least clear the recipient in your email object before you reuse it again?

post your scripts to send out emails here would be great for troubleshooting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alina OAuthor Commented:
Ryan,
I wasn't creating new mail object in the loop, but I did assigned new address to the mailto in the loop.
Dim strMailTo As String = dt.Rows(i).Item(0).ToString()
                    mail.To.Add(strMailTo)
                    SmtpServer.Send(mail)

                Next
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.