title: How to save query result in a variable vb.net
body: <p>The first vb scripts work fine and can hold the total count in a variable say rowsAffected .
  This can be send using smtp  based email using the last statement.</p>
  
  <p>How can i implement the similar situvation in 2nd case?in the 2nd case ,i need to send the result set to email.what modification should i do to hold the result set in a variable ? also how to send that result set </p>
  
  <p>Case 1</p>
  
  <pre><code>Dim sqlComm As System.Data.SqlClient.SqlCommand
  Dim rowsAffected As Integer
  
  
  sqlComm = New System.Data.SqlClient.SqlCommand("select count(*) from table1", sqlConn)
  
  rowsAffected = sqlComm.ExecuteScalar()
  
  myHtmlMessage = New MailMessage("email", "email", "This is a testing from  VB script Task", rowsAffected.ToString)
  </code></pre>
  
  <hr>
  
  <p>Case 2</p>
  
  <pre><code>sqlComm = New System.Data.SqlClient.SqlCommand("select * from tabl1", sqlConn)
  
  rowsAffected = sqlComm.ExecuteScalar()   ''what modification needed here?
  
  myHtmlMessage = New MailMessage("email", "email", "This is a testing from  VB script Task", rowsAffected.ToString)  ''what modification here??
  </code></pre>
  
  <p>the sqlComm.ExecuteScalar()  returns integer value,total number of rows for the  first case.</p>
  
  <h2>I need the second case to return all the rows from the table.Also how to send using the smtp email</h2>
  
  <p>Public Sub Main()</p>
  
  <pre><code>    ''mail variables
  
      Dim myHtmlMessage As MailMessage
      Dim mySmtpClient As SmtpClient
      Dim value As NetworkCredential
  
      ''sql variables
      Dim fireAgain As Boolean = True
      Dim sqlConn As System.Data.SqlClient.SqlConnection
      Dim sqlComm As System.Data.SqlClient.SqlCommand
  
  
  
  
      Dim cm As ConnectionManager = Dts.Connections("cnn") ''Retrive the reference to the managed Connections
  
      '' Request an open connection
      sqlConn = cm.AcquireConnection(Dts.Transaction)
      Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, fireAgain)
  
      ''Do your work
      sqlComm = New System.Data.SqlClient.SqlCommand("SELECT  [Total_Row_inserted_by this_Load] Column1     ,[Filename] Column2 FROM [Testing-DB].[dbo].[Emailbody]", sqlConn)
  
  
  
      Dim sqlReader As System.Data.SqlClient.SqlDataReader
      sqlReader = sqlComm.ExecuteReader
  
      Dim dtDataTable As New DataTable
  
  
      dtDataTable.Load(sqlReader)
  
      ''Inform SSIS you're done your work
      cm.ReleaseConnection(sqlConn)
  
  
  
      For Each row As DataRow In dtDataTable.Rows
          Dts.Events.FireInformation(0, "", row("Column1").ToString() + " - " + row("Column2").ToString(), "", 0, fireAgain)
  
      Next row
  
  
      mySmtpClient = New SmtpClient("send.company.net")
      mySmtpClient.Port = 585
      value = New NetworkCredential("my.name@company.net", "pwdddd") ''this is the line added
      mySmtpClient.Credentials = value
      mySmtpClient.EnableSsl = True
  
  
  
      For Each row As DataRow In dtDataTable.Rows
  
  
          myHtmlMessage = New MailMessage("my.name@company.net", "my.name@company.net", "Table data is ", row("column1").ToString() + " - " + row("column2").ToString() + System.Environment.NewLine)
  
  
      Next row
  
  
  
      mySmtpClient.Send(myHtmlMessage)
  
  
  
      Dts.TaskResult = ScriptResults.Success
  
  
  End Sub
  </code></pre>
  
  <h2>my requiremnet is ,to send the result set as an email and the email body should be</h2>
  
  <h2>column1 column2</h2>
  
  <h2>aa       11</h2>
  
  <h2>bb       323</h2>
  
  <h2>cvc      342</h2>
  
  <h2>aa       11</h2>