How to save query result in a variable vb.net

Question

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.

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

Case 1

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)

Case 2

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??

the sqlComm.ExecuteScalar() returns integer value,total number of rows for the first case.

I need the second case to return all the rows from the table.Also how to send using the smtp email

Public Sub Main()

    ''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("[email protected]", "pwdddd") ''this is the line added
    mySmtpClient.Credentials = value
    mySmtpClient.EnableSsl = True



    For Each row As DataRow In dtDataTable.Rows


        myHtmlMessage = New MailMessage("[email protected]", "[email protected]", "Table data is ", row("column1").ToString() + " - " + row("column2").ToString() + System.Environment.NewLine)


    Next row



    mySmtpClient.Send(myHtmlMessage)



    Dts.TaskResult = ScriptResults.Success


End Sub

my requiremnet is ,to send the result set as an email and the email body should be

column1 column2

aa 11

bb 323

cvc 342

aa 11

Also bear in mind that one thing is VBScript, another thing is VBA/VB6 and another one is VB.NET. Please update your description/tags accordingly. — user2480047
– user2480047, Commented Aug 15, 2013 at 10:09

S.M · Accepted Answer · 2013-08-15 10:46:42Z

1

This block of code will give idea how to do this.

sqlComm = New System.Data.SqlClient.SqlCommand("select Column1, Column2, COUNT(1) CNT from table1  group by Column1, Column2", sqlConn)
Dim sqlReader As System.Data.SqlClient.SqlDataReader
sqlReader = sqlComm.ExecuteReader
Dim dtDataTable As New DataTable
dtDataTable.Load(sqlReader)

For Each row As DataRow In dtDataTable.Rows
  Dts.Events.FireInformation(0, "", row("Column1").ToString() + " - " + row("Column1").ToString(), "", 0, fireAgain)
Next row

rowsAffected = sqlComm.ExecuteScalar() change this line of code to

sqlReader = sqlComm.ExecuteReader
    Dim dtDataTable As New DataTable
    dtDataTable.Load(sqlReader)

myHtmlMessage = New MailMessage("email", "email", "This is a testing from VB script Task", rowsAffected.ToString) modify this line of code to

 For Each row As DataRow In dtDataTable.Rows
myHtmlMessage = New MailMessage("email", "email", "This is a testing from  VB script Task", row("CNT").ToString())
Next row

edited Aug 15, 2013 at 10:46

answered Aug 15, 2013 at 10:40

S.M

7763 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1254579 Over a year ago

For Each row As DataRow In dtDataTable.Rows myHtmlMessage = New MailMessage("email", "email", "This is a testing from VB script Task", row("CNT").ToString()) Next row this part is not working

S.M Over a year ago

replace with this statement ,For Each row As DataRow In dtDataTable.Rows Dts.Events.FireInformation(0, "", row("CNT").ToString(), "", 0, fireAgain) Next row , is this working, What is the error you are getting

user1254579 Over a year ago

No error ..geting only the last row,but i need full resultset..i have added the code above ..please look above...

user1254579 Over a year ago

added the code in the question section..it seems there is a problem in the last for loop

S.M Over a year ago

do you need to combine all records into single message body text for sending one email? if so, declare string variable "strCombineRecordsCountMessage" and inside the loop, add those records to the variable <strCombineRecordsCountMessage + row("CNT").ToString()> and after this loop ends, add this variable to myHtmlMessage. eg. myHtmlMessage = New MailMessage("email", "email", "This is a testing from VB script Task", strCombineRecordsCountMessage.ToString())

Collectives™ on Stack Overflow

How to save query result in a variable vb.net

I need the second case to return all the rows from the table.Also how to send using the smtp email

my requiremnet is ,to send the result set as an email and the email body should be

column1 column2

aa 11

bb 323

cvc 342

aa 11

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I need the second case to return all the rows from the table.Also how to send using the smtp email

my requiremnet is ,to send the result set as an email and the email body should be

column1 column2

aa 11

bb 323

cvc 342

aa 11

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related