vb.net - Unable to execute SQL command in windows service -

i'm working on creating windows service send emails customer when within month of having submission expire. i'm using vb.net in visual studios 2008

because it's windows service it's difficult debug. trying narrow down error created "senddebugemail" method sends me email if gets line. emails never make past "dr = cmd.executereader()"

i'm wondering doing wrong. sql statement should work fine. i've tested in sql server database.

i've created dummy_database made in sql server well. added insert sql statement dummy table have in there see if access database. table takes in line number , time sent. when run windows service database updates fine.

any appreciated. thanks

dim conn new sqlconnection(connstring2)      senddebugemail("134")      sql = "select email _customer custid in (select custid _onlinecustomer expirationdate <= '6-20-12' , expirationdate >= '6-10-12')"     dim cmd new sqlcommand(sql, conn)       ssubject = "hello"     sbody = "this test data"     dim dr sqldatareader      senddebugemail("143")      try         dr = cmd.executereader()   // stops          senddebugemail("147")          while dr.read              senddebugemail("152")              try                 loginfo("service woke up")                 dim integer = 0                 ' prepare e-mail fields                 sfrom = "test@gmail.com"                 sto = "test1@gmail.com"                 scc = "test2@gmail.com"                 dim omailmsg mailmessage = new mailmessage                 omailmsg.from = sfrom                 omailmsg.to = sto                 omailmsg.cc = scc                 ' call stored procedure process current item                 ' success message                 omailmsg.subject = ssubject + "(success)"                 omailmsg.body = sbody + "email has been sent successfully."                 ' send message                 if not (omailmsg.to = string.empty)                     smtpmail.send(omailmsg)                 end if              catch obug exception                 logevent(obug.message)                          end try         end while     catch ex exception              dr.close()         cmd.dispose()         conn.close()         conn.dispose()     end try end sub 

/////////////////////////////////////////////////////////////////////////////////

problem solved: set connection never opened it.
needed conn.open()

the thing helped me adding code last catch statement:

senddebugemail(ex.message & vbcrlf & ex.stacktrace) 

it send me email of stacktrace , made easy debug

are trapping , swallowing exceptions? if are, stop. let exceptions service crash service: exception logged in event log. exceptions should trap can recover (though valid catch exception, log , rethrow via throw;).

have instrumented code log4net (http://logging.apache.org/log4net/), or similar? should be, daemon windows service — how else (or operations) going diagnose problems service when occur (as will).

edited note:

1. you should using using statements: ado.net objects idisposable. makes cleaner code.

2. consider using sqldataadapter fill datatable or dataset results set. pattern you're using:

   read row sql while read successful   send email   read row sql 

   will lead blocking in database. talking mail server has potential high latency. if mail server doesn't answer, or have network congestion, or of number of other reasons, you're going left hanging until mail sent or exception thrown due timeout. , sql query going sitting there read locks on table , indices you're reading data, blocking people attempting updates, inserts or deletes. production dbas be...vexed. want keep locks moving , locks released quick can.