title: How to do I send an email a target group based on stored procedure @Error message?
body: <p>Please forgive me for asking too many questions.</p>
  
  <p>I have been working on this all day and can't take it anymore.</p>
  
  <p>I have a stored proc called sp_signup()</p>
  
  <p>The stored proc first checks to see if the user has already signed up. If yes, then inform the user that s/he has already signed up for this class.</p>
  
  <pre><code>SET @ERROR = 'You have already signed up for this training'
  </code></pre>
  
  <p>This works a treat.</p>
  
  <p>If no, then check to see if there are still available seats.</p>
  
  <p>If seats are still available, sign the user up by inserting into training table and inform the user s/her has been signed up.</p>
  
  <pre><code> SET @ERROR = 'You have been registered for this class'
  </code></pre>
  
  <p>This works a treat.</p>
  
  <p>If no seats remain, then put the user on waiting list by inserting the user's registration info into waitingList table and inform the user s/he has been placed on waiting list.</p>
  
  <pre><code>SET @ERROR = 'Sorry, but this class is full. However, you have been placed on waiting list.'
  </code></pre>
  
  <p>This works a treat as well.</p>
  
  <p>So, the stored proc, when tested, works great.</p>
  
  <p>However, on our .net app that calls the stored proc, we are sending users emails informing them that they have either been signed up for the class or have been placed on waiting list depending on what the @Error message says.</p>
  
  <p>This is where we are having problem because the email is not going out.</p>
  
  <p>Here are the conditionals for sending email:</p>
  
  <pre><code>If Label1.Text = "You have been registered for this class" Then
  'Email code goes here and is sent to inform users they have been registered for class
  ElseIf Label1.Text = "Sorry, but this class is full. However, you have been placed on waiting list." Then
  'Email code goes here and is sent inform to users they have been placed on waiting list
  Else
  
  End If 
  </code></pre>
  
  <p>below is where the stored procedure call is made:</p>
  
  <pre><code>Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
  
  Dim username = Session("Username")
  
  Try
  
      Dim s As String
  
      s = "sp_signup"
      Dim connStr As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
      Dim conn As New SqlConnection(connStr)
      Dim cmd = New SqlCommand(s, conn)
      cmd.CommandType = CommandType.StoredProcedure
      cmd.Parameters.AddWithValue("@cosID", Request.QueryString("cosId"))
      cmd.Parameters.AddWithValue("@locID", Request.QueryString("locid"))
      cmd.Parameters.AddWithValue("@dat", Request.QueryString("iddate"))
      cmd.Parameters.AddWithValue("@UserName", username)
      cmd.Parameters.Add("@ERROR", SqlDbType.[Char], 500)
      cmd.Parameters("@ERROR").Direction = ParameterDirection.Output
      conn.Open()
      cmd.ExecuteNonQuery()
      message = DirectCast(cmd.Parameters("@ERROR").Value, String)
      Dim cmdGetKey As New SqlCommand("SELECT @@IDENTITY", conn)
      Dim skey As Integer = cmdGetKey.ExecuteScalar()
      Session("TrainingId") = skey
      conn.Close()
      btnSendEmail_Click()
  
      'Display some feedback to the user to let them know it was processed
      Label1.ForeColor = System.Drawing.Color.Green
      Label1.Text = message
  
  Catch
  
      'If the message failed at some point, let the user know
      Label1.ForeColor = System.Drawing.Color.Red
      Label1.Text = message
  
  End Try
  End Sub
  </code></pre>
  
  <p>The email code has been tested and works without the stored proc and is below:</p>
  
  <pre><code>Protected Sub btnSendEmail_Click()
      Dim skey As Integer = Session("TrainingId")
      'Response.Write(skey)
      'Response.End()
      Dim Conn As SqlConnection
      'Dim param As SqlParameter
      'Dim cmdcommand As SqlCommand
      Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString)
      Conn.Open()
  
      If Label1.Text = "You have been registered for this class" Then
          Dim emailcmd As New SqlCommand("select distinct lg.Email, lg.fullname, c.CourseName, l.location, d.trainingDates, d.trainingTime, i.instructorName from tblTrainings t Inner Join tblCourses c on t.courseId = c.courseId " &amp; _
                                                 " Inner Join tblLocations l on t.locationId = l.LocationId " &amp; _
                                                 " Inner Join tblTrainingDates d on t.dateid=d.dateid " &amp; _
                                                 " Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " &amp; _
                                                 " Inner Join tblInstructors i on ic.instructorId = i.instructorId " &amp; _
                                                 " Inner Join tblLogin lg on t.username = lg.username where lg.username = '" &amp; Session("username") &amp; "' AND t.CourseID = " &amp; Request.QueryString("cosId") &amp; "  AND t.LocationID = " &amp; Request.QueryString("locid") &amp; " AND t.dateId = " &amp; Request.QueryString("iddate") &amp; " AND TrainingId = " &amp; skey &amp; ";", Conn)
          Dim dr = emailcmd.ExecuteReader()
          If dr.Read() Then
              email = dr.GetString(0)
              fullname = dr.GetString(1)
              courses = dr.GetString(2)
              Loc = dr.GetString(3)
              tdates = dr.GetDateTime(4)
              ttime = dr.GetString(5)
          End If
          'code for other email requests
          Dim objSmtpClient As SmtpClient = New SmtpClient("relay.smtp", 25)
          Dim objSender As MailAddress = New MailAddress("name.emailadd.com", "name.emailadd.com")
          Dim objMail As MailMessage = New MailMessage("name.emailadd.com", "name.emailadd.com")
          objMail.Bcc.Add("name.emailadd.com")
          objMail.To.Add(email)
          'objMail.CC.Add("name.emailadd.com")
          objMail.Subject = "About Your Training: " &amp; courses &amp; ""
          objMail.Body = " Dear " &amp; fullname &amp; " &lt;br&gt;You have just signed up for &lt;b&gt;" &amp; courses &amp; "&lt;/b&gt; training. &lt;br&gt;&lt;br&gt;This training will be held at &lt;b&gt;" &amp; Loc &amp; "&lt;/b&gt; on &lt;b&gt;" &amp; tdates &amp; "&lt;/b&gt; starting from &lt;b&gt;" &amp; ttime &amp; "&lt;/b&gt;.&lt;br&gt;&lt;br&gt; For more details about this training, please visit &lt;a href='Training/'&gt; Training/&lt;/a&gt;. "
          objMail.IsBodyHtml = True
          objSmtpClient.Send(objMail)
          dr.Close()
      ElseIf Label1.Text = "Sorry, but this class is full. However, you have been placed on waiting list." Then
          Dim waitcmd As New SqlCommand("select distinct lg.Email, lg.fullname, c.CourseName, wl.location, d.trainingDates, d.trainingTime, i.instructorName from tblWaitingList wl Inner Join tblCourses c on wl.courseId = c.courseId " &amp; _
                                     " Inner Join tblLocations l on wl.locationId = l.LocationId " &amp; _
                                     " Inner Join tblTrainingDates d on wl.dateid=d.dateid " &amp; _
                                     " Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId " &amp; _
                                     " Inner Join tblInstructors i on ic.instructorId = i.instructorId " &amp; _
                                     " Inner Join tblLogin lg on wl.username = lg.username where lg.username = '" &amp; Session("username") &amp; "' AND wl.CourseID = " &amp; Request.QueryString("cosId") &amp; "  AND wl.LocationID = " &amp; Request.QueryString("locid") &amp; " AND wl.dateId = " &amp; Request.QueryString("iddate") &amp; " AND TrainingId = " &amp; skey &amp; ";", Conn)
          Dim dr = waitcmd.ExecuteReader()
          If dr.Read() Then
              email = dr.GetString(0)
              fullname = dr.GetString(1)
              courses = dr.GetString(2)
              Loc = dr.GetString(3)
              tdates = dr.GetDateTime(4)
              ttime = dr.GetString(5)
          End If
          'code for other email requests
          Dim objSmtpClient As SmtpClient = New SmtpClient("relay.smtp", 25)
          Dim objSender As MailAddress = New MailAddress("name.emailadd.com", "name.emailadd.com")
          Dim objMail As MailMessage = New MailMessage("name.emailadd.com", "name.emailadd.com")
          objMail.Bcc.Add("name.emailadd.com")
          objMail.To.Add(email)
          'objMail.CC.Add("name.emailadd.com")
          objMail.Subject = "About Your Training: " &amp; courses &amp; ""
          objMail.Body = " Dear " &amp; fullname &amp; " &lt;br&gt;You have been placed on the waiting list for &lt;b&gt;" &amp; courses &amp; "&lt;/b&gt; training. &lt;br&gt;&lt;br&gt;This training will be held at &lt;b&gt;" &amp; Loc &amp; "&lt;/b&gt; on &lt;b&gt;" &amp; tdates &amp; "&lt;/b&gt; starting from &lt;b&gt;" &amp; ttime &amp; "&lt;/b&gt;.&lt;br&gt;&lt;br&gt; Should a seat become available, notification will be based on first-come, first-served bases.&lt;br&gt;For more details about this training, please visit &lt;a href='http://#/'&gt; http://Training/&lt;/a&gt;. "
          objMail.IsBodyHtml = True
          objSmtpClient.Send(objMail)
          dr.Close()
      Else
  
      End If
  End Sub
  </code></pre>