如何使用 vb.net 将 SQL Server 数据库中的选定项目发送到电子邮件(Outlook)

2021-09-16 00:00:00 sql vb.net sql-server

我的表单中有 2 个表,RegisteredScheduleTodaySchedule.当用户在我的表单中输入 Name、Gender、SchedueleDate 和 Incharge 等数据并单击保存"按钮时,数据将转到 RegisteredSchedule 表,如果 ScheduleDate 等于日期 Now,该记录将显示在 TodaySchedule 表中,我希望从 SQL Server 中选择的数据通过电子邮件发送 - 是可能的?请帮助我我是新手.

I have 2 tables in my form, RegisteredSchedule and TodaySchedule. When the user inputs data such as Name, Gender, SchedueleDate and Incharge to my form and clicks the "Save" button, the data will go to the RegisteredSchedule table, and if the ScheduleDate set by users is equal to date Now, that record will show in TodaySchedule table and I want that selected data from SQL Server to be sent in an email - is that possible? Please help me I am a newbie.

这是我的代码,如果用户设置的 ScheduleDate 是 = DateNow .我希望这个 select 语句也通过电子邮件发送,而不仅仅是在 table2 中显示.

Here is my code to if the ScheduleDate set by user is = DateNow . I want this select statement to be send in email also, not just show in table2.

Public Sub OnSchedule()
    Dim conn As New SqlConnection("SERVER=x\x;database = 
 x; user=x;pwd=x; ")

    conn.Open()
    Dim cmd As SqlCommand = conn.CreateCommand
    cmd.CommandText = String.Format("select PatientName,Gender,ScheduleDate,PersonInCharge from " _
    & "Schedule where ScheduleDate = CONVERT(date,getdate()) order by ScheduleDate")
    Dim dr As SqlDataReader = cmd.ExecuteReader()
    If dr.HasRows Then
        Dim dtSerial As New DataTable
        dtSerial.Load(dr)
        dgvOnSchedule.DataSource = dtSerial
    Else
        MsgBox("no data")

    End If
    dr.Close()
    conn.Close()
End Sub

这是我的电子邮件中的代码,我试图将我的选择查询放在 oMail.TextBody 中,但没有奏效.请提出建议.

here is my code in my email, i tried to put my select query in oMail.TextBody but didn't work. please suggest.

    Public Sub sendEmail()
    Dim oMail As New SmtpMail("TryIt")
    Dim oSmtp As New SmtpClient()
    oMail.To = New AddressCollection("x@x.co.th")
   oMail.Cc = New 
   AddressCollection("x@x.co.th,x@x.co.th")
   oMail.Subject = "test email from VB.NET project"

   'code below not work, what should i do to my oMail.textbody to show the 
   select condition ?

   oMail.TextBody = "On SChedule Date" & OnSchedule()



 Dim oServer As New SmtpServer("x.x.x.th")
   Try
        oSmtp.SendMail(oServer, oMail)
        MessageBox.Show("success")
    Catch ex As Exception
        MessageBox.Show("no success")
    End Try     
End Sub

推荐答案

将表格创建为 HTML 对象,可以使用以下代码

For creating the table as HTML object, you can use following code

Dim sb As New StringBuilder("<table>")
For Each row As DataGridViewRow In DataGridView1.Rows
    sb.Append("<tr>")
    For Each cell As DataGridViewCell In row.Cells
        sb.Append("<td>")
        sb.Append(cell.Value)
        sb.Append("</td>")
    Next
    sb.Append("</tr>")
Next
sb.Append("</table>")

这将创建一个类似于以下示例的 HTML 表格脚本

This will create an HTML table script similar to following sample

<table><tr><td>1</td><td>Pre-School A</td></tr><tr><td>2</td><td>Pre-School B</td></tr><tr><td></td><td></td></tr></table>

相关文章