how to pass sql dataadapter value from aspx page to a html file

I have this below code which get the list of users from database and stores it in sql dataadapter...

Now on button click I want to send email to all the list in this sql dataadapter....in the below format

Hello <username>(This has to be from the sqldataadapter)

Is your phone number <phonenumber>(phone number column from sql adapter) correct?

The above email should be a html file..which should act as a body of the email...here I dont know how to get this username and phonenumber in this html file from aspx page's sql dataadapter.

My code for getting data in dataadapter

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT userName,Email,Phone from Users ", con)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)

How to get to send email to each row? and how to attach html email to this body

Re: how to pass sql dataadapter value from aspx page to a html file

Create an HTML page for email body that holds "Place Holders" for UserName and PhoneNumber, then in your above code, loop thru each row in DataTable that you fetch the user details and replace the Place Holders in HTML with actual UserName and PhoneNumber
to send email. Here is a sample code on similar scenario:

Dim mailFrom = "me@test.com"
Dim subject = "Confirm Contact"
Dim dt As New DataTable("UserTable")
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT userName,Email,Phone from Users ", con)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
End Using
End Using
End Using
For Each row As DataRow In dt.Rows
Dim messageBody = File.ReadAllText(Server.MapPath("~/HTML_FILE_PATH/HtmlFile1.htm"))
messageBody = messageBody.Replace("<username>", Convert.ToString(row("UserNameCol"))).Replace("<phonenumber>", Convert.ToString(row("PhoneNumberCol")))
Dim mailMessage As New MailMessage()
With mailMessage
.From = New MailAddress(mailFrom)
.Subject = subject
.Body = messageBody
End With
mailMessage.IsBodyHtml = True
'-- write further code to send email
'-- write further code to send email
Next

Server Error in '/' Application.
Column 'UserFirstNameCol' does not belong to table UserTable.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Column 'UserFirstNameCol' does not belong to table UserTable.
Source Error:
Line 41: For Each row As DataRow In dt.Rows
Line 42: Dim messageBody = File.ReadAllText(Server.MapPath("~/email1.html"))
Line 43: messageBody = messageBody.Replace("<%UserFirstName%>", Convert.ToString(row("UserFirstNameCol"))).Replace("<%UserPhonenumber%>", Convert.ToString(row("UsertelephoneCol")))
Line 44:
Line 45: Dim mailMessage As New MailMessage()

Re: how to pass sql dataadapter value from aspx page to a html file

Lexi85

Column 'UserFirstNameCol' does not belong to table UserTable.

Remove "Col".

The error is describing Column 'UserFirstNameCol' does not belong to table UserTable, which simply means you are using row("UserFirstNameCol") which does not exists in your DataTable. Refer to your Select query and then use appropriate column names.

Re: how to pass sql dataadapter value from aspx page to a html file

A recipient must be specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: A recipient must be specified.
Source Error:
Line 53: SmtpClient.DeliveryMethod = SmtpDeliveryMethod.Network
Line 54: SmtpClient.Credentials = New System.Net.NetworkCredential(username, password)
Line 55: SmtpClient.Send(mail)
Line 56: Next
Line 57: End Sub

Though there are 2 records with valid emails addresses in the database

Re: how to pass sql dataadapter value from aspx page to a html file

Lexi85

Exception Details: System.InvalidOperationException: A recipient must be specified.

Error pretty clear describes that recipient is not specified in your email sending code. Please debug and check if correct (not blank) email address is coming and you are setting it in code in "TO" to send email.

Re: how to pass sql dataadapter value from aspx page to a html file

Your database table column for email is - useremail, that you can access similarly how you are accessing userfirstname and userphonenumber from DataTable and assign it to mail.To like
.To = Convert.ToString(row("UserEmail"))
in below code.

Re: how to pass sql dataadapter value from aspx page to a html file

I do not see any code requirement to use ArrayList if you are getting UserEmail field value from database table into DataTable in your previous code. Did you try setting the UserEmail from DataTable row like specified in my previous post?

Re: how to pass sql dataadapter value from aspx page to a html file

Server Error in '/' Application.
A recipient must be specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: A recipient must be specified.
Source Error:
Line 76: smtpclient.DeliveryMethod = SmtpDeliveryMethod.Network
Line 77: smtpclient.Credentials = New System.Net.NetworkCredential(username, password)
Line 78: smtpclient.Send(mail)
Line 79: Next
Line 80: