Email product name if it reaches a particular value

[poojavb]

Hello Friends,

One help needed....

I have a product database....in which I have productid,prodname,inventoryvalue,reordervalue

I have to design a email system that as soon as the product reaches the reorder level it shud generate an email....

but the condition is there will be many products in the database....and on one button click it should send only one email but with all product names those reached the reorder level....

I tried the below thing but completely a waste...

Please do help me....

    Private Sub btnEmail_Click(sender As System.Object, e As System.EventArgs) Handles btnEmail.Click
       'Open Connection
        Dim reorder As Integer
        Dim inventory As Integer
        Dim prodname As String
        Dim prodcount As Integer


        'get the total number of product in database
        Try
            Dim myCommand1 As SqlCommand
            myCommand1 = New SqlCommand("SELECT count(*) as 'Total' FROM [Product]", Connection)
            Dim reader1 As SqlDataReader = myCommand1.ExecuteReader
            While reader1.Read()
                prodcount = reader1("Total")
            End While
            Debug.Print(prodcount.ToString)
            reader1.Close()

        'get the values of product names in a array
            Dim ds As New DataSet
            Dim sqlstring As String
            sqlstring = "select ProdName from Product"
            Dim da As SqlDataAdapter = New SqlDataAdapter(sqlstring, Connection)
            da.Fill(ds, "Product")
            Dim temp As Integer = ds.Tables("Product").Rows.Count
            Dim arr1() As String
            For i = 0 To temp - 1
                arr1(i) = ds.Tables("Product").Rows(i).Item("ProdName")
            Next

            For i As Integer = 1 To prodcount
                prodname = arr1(i)
                Debug.Print("in for: " + i.ToString)
                Dim myCommand As SqlCommand
                myCommand = New SqlCommand("SELECT * FROM [Product] where ProdName='" + prodname + "'", Connection)
                Dim reader As SqlDataReader = myCommand.ExecuteReader
                reader.Read()
                prodname = reader("ProdName")
                reorder = reader("ReOrderLimit")
                inventory = reader("Inventory")
                Debug.Print("prodname: " + prodname)
                Debug.Print("Inventory: " + inventory.ToString)
                Debug.Print("reorder: " + reorder.ToString)

                If inventory <= reorder Then
                    Dim body As String = "Please send these medicines:  " + prodname
                    Dim SmtpServer As New SmtpClient()
                    Dim mail As New MailMessage()
                    SmtpServer.Credentials = New Net.NetworkCredential("[e-mail]", "abc123def")
                    SmtpServer.Port = 28
                    SmtpServer.Host = "mail.abc.com"
                    mail = New MailMessage()
                    mail.From = New MailAddress("[e-mail]")
                    mail.To.Add("[e-mail]")
                    mail.Subject = "Lack of List of medicines"
                    mail.Body = body
                    SmtpServer.Send(mail)
                    MsgBox("mail sent sucessfully for reorder")
                End If
                reader.Close()
            Next

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Close connection
    End Sub

[Reverend Jim]

I would put the reorder test in a separate block. You seem to have a handle on the database part so I will describe it in pseudo-code

'select all records where the inventory count has reached or exceeded the reorder limit

select * from Product where Inventory <= ReOrderLimit

'if any items have reached the reorder limit then send an email notification

if recordcount > 0 then

    create new email

    for each record in recordset
        add inventory item to email message
    next

    send email

end if    

[poojavb]

Actually I dont know much about arrays....so I want how to send all the product names in one email whosever have reached the reorder level....

Can anyone tell me thru coding....or correct what I have done in my coding....

@Jim - I understood what u have said...but I want the product names....

[poojavb]

@Jim - can u tell me how to do this part

for each record in recordset
add inventory item to email message
next

Check my code below....just a temp code....

        'Open Connection
        Dim count As Integer
        Dim myCommand As SqlCommand
        myCommand = New SqlCommand("SELECT count(*) as count1 FROM [Product]  where Inventory < ReorderLimit", Connection)
        Dim reader As SqlDataReader = myCommand.ExecuteReader
        While reader.Read
            count = reader("count1")
        End While
        If count > 0 Then
            MsgBox("Greater than 0")
        Else
            MsgBox("No")
        End If
        'Close Connection

[poojavb]

Could not edit my previous post....

@Jim - check my code below it gives different email for different product

    Private Sub btnEmail_Click(sender As System.Object, e As System.EventArgs) Handles btnEmail.Click
        Dim Openconnection As String
        Openconnection = Open_DB_Connection("form7 - Button1 click")
        Dim count As Integer
        Dim productname As String
        Dim myCommand As SqlCommand
        myCommand = New SqlCommand("SELECT count(*) as count1 FROM [Product]  where Inventory < ReorderLimit", Connection)
        Dim reader As SqlDataReader = myCommand.ExecuteReader
        While reader.Read
            count = reader("count1")
        End While
        reader.Close()
        If count > 0 Then
            ' MsgBox("Greater than 0")
            Dim ds As New DataSet
            Dim sqlstring As String
            sqlstring = "select ProdName from Product where Inventory < ReorderLimit"
            Dim da As SqlDataAdapter = New SqlDataAdapter(sqlstring, Connection)
            da.Fill(ds)
            Dim rows(ds.Tables(0).Rows.Count) As String
            ' Loop through all rows in Categories table
            For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                rows(i) = ds.Tables(0).Rows(i).Item(0).ToString
                Debug.Print(rows(i))
                'Next
                'For i As Integer = 1 To count
                productname = rows(i)
                Debug.Print("Prodname:" + productname)

                Dim subject As String = "Please send these medicines:  " + productname
                Dim SmtpServer As New SmtpClient()
                Dim mail As New MailMessage()
                SmtpServer.Credentials = New Net.NetworkCredential("abc.def.com", "abc123def")
                SmtpServer.Port = 29
                SmtpServer.Host = "mail.abc.com"
                mail = New MailMessage()
                mail.From = New MailAddress("abc.def.com")
                mail.To.Add("abc.def.com")
                mail.Subject = "Lack of List of medicines"
                For j As Integer = 1 To count
                    subject = subject & Environment.NewLine & productname(j) & Environment.NewLine
                Next
                mail.Body = subject
                SmtpServer.Send(mail)
                MsgBox("mail sent sucessfully for reorder")
            Next
        Else
            MsgBox("No")
        End If
        Dim Closeconnection As String
        Closeconnection = Close_DB_Connection("form7 - Button2 click")
    End Sub

[poojavb]

Hey I got the answer....I am so happy after so many days of trying today I got the answer....

Dim SmtpServer As New SmtpClient()
    Dim mail As New MailMessage()
    Dim subject As String
    Private Sub btnEmail_Click(sender As System.Object, e As System.EventArgs) Handles btnEmail.Click
        Dim Openconnection As String
        Openconnection = Open_DB_Connection("form7 - Button1 click")
        Dim count As Integer
        Dim prodname As String
        Dim myCommand As SqlCommand
        myCommand = New SqlCommand("SELECT count(*) as count1 FROM [Product] where Inventory < ReorderLimit", Connection)
        Dim reader As SqlDataReader = myCommand.ExecuteReader
        While reader.Read
            count = reader("count1")
        End While
        reader.Close()
        If count > 0 Then
            Dim myCommand2 As SqlCommand
            myCommand2 = New SqlCommand("SELECT ProdName from Product where Inventory < ReorderLimit", Connection)
            Dim reader2 As SqlDataReader = myCommand2.ExecuteReader
            subject = "The below medicines have reached its reorder level. Please place the required orders "
            While reader2.Read()
                prodname = reader2("ProdName")
                subject = subject & Environment.NewLine & prodname
            End While
            reader2.Close()
            SmtpServer.Credentials = New Net.NetworkCredential("abc.def.com", "abc")
            SmtpServer.Port = 29
            SmtpServer.Host = "mail.abc.com"
            mail = New MailMessage()
            mail.From = New MailAddress("abc.def.com")
            mail.To.Add("abc.def.com")
            mail.Subject = "Medicines Exceeding Reorder Level"
            mail.Body = subject
            SmtpServer.Send(mail)
            MsgBox("Mail sent sucessfully")
        Else
            MsgBox("No medicines have reached its reorder value")
        End If
        Dim Closeconnection As String
        Closeconnection = Close_DB_Connection("form7 - Button2 click")
    End Sub

[poojavb]

One more help...

Can anyone tell me how do I insert newline in the email body part...
I tried everything like Environment.Newline, vbcrlf, and even the html tags :) and "/n" and event other charatcers but failed

check my below code

   Dim SmtpServer As New SmtpClient()
    Dim mail As New MailMessage()
    Dim subject As String
    Dim body As String

    Private Sub btnEmail_Click(sender As System.Object, e As System.EventArgs) Handles btnEmail.Click
        Dim Openconnection As String
        Openconnection = Open_DB_Connection("form7 - Button1 click")
        Dim prodname As String
        Dim prodid As String
        Dim address As String
        address = ""
        Dim myCommand2 As SqlCommand
        myCommand2 = New SqlCommand("SELECT Prodid,ProdName from Product where Inventory <= ReorderLimit", Connection)
        Dim reader2 As SqlDataReader = myCommand2.ExecuteReader
        body = "The below medicines have reached its reorder level. Please place the required orders "
        If reader2.HasRows Then
            While reader2.Read()
                prodname = reader2("ProdName")
                Debug.Print("Prod name: " + prodname)
                prodid = reader2("Prodid")
                body = body + " " + "Product ID: " + prodid + " " + "Product Name: " + prodname & " "
            End While
            Debug.Print("body: " + body)
            reader2.Close()
            mail.Body = body
            subject = "Medicines Exceeding Reorder Level"
            System.Diagnostics.Process.Start("mailto:" & address & "?subject=" & subject & "&body=" & mail.Body)
        Else
            MsgBox("No medicines")
        End If

        Dim Closeconnection As String
        Closeconnection = Close_DB_Connection("form7 - Button2 click")
    End Sub

I want the email body output as

The below medicines have reached its reorder level. Please place the required orders

Product ID: PRD4
Product Name: Metacin

Product ID: PRD5
Product Name: Saframycin

Product ID: PRD6
Product Name: Glycodin

[Reverend Jim]

body &= vbCrLf should work. If your email format is HTML then try body &= "<br>"

[poojavb]

If I use a <br> tag then the output is as

The below medicines have reached its reorder level. Please place the required orders <br>Product ID: PRD4Product Name: Metacin<br>Product ID: PRD5Product Name: Saframycin<br>Product ID: PRD6Product Name: Glycodin

If I use vbCrLf the output is

The below medicines have reached its reorder level. Please place the required ordersProduct ID: PRD4Product Name: MetacinProduct ID: PRD5Product Name: SaframycinProduct ID: PRD6Product Name: Glycodin

I tried using so many tags but still its not working....

actually debug.print shows the correct output....but in the email the newline is not getting applied....this output is when I apply vbCrLf or Environment.Newline

The below medicines have reached its reorder level. Please place the required orders 
Product ID: PRD4
Product Name: Metacin

Product ID: PRD5
Product Name: Saframycin

Product ID: PRD6
Product Name: Glycodin

[poojavb]

Got it solved

'declaring variables

Dim subject As String
Dim body As String

Private Sub btnEmail_Click(sender As System.Object, e As System.EventArgs) Handles btnEmail.Click
        'Open Connection
        Dim myCommand2 As SqlCommand
        myCommand2 = New SqlCommand("SELECT * from HMS.dbo.Product where Inventory <= ReorderLimit", Connection)
        Dim reader2 As SqlDataReader = myCommand2.ExecuteReader
        body = "The below medicines have reached its reorder level. Please place the required orders " + Environment.NewLine + "Product Details" + Environment.NewLine
        If reader2.HasRows Then
            While reader2.Read()
                body = body + Environment.NewLine + "Product ID: " + reader2("Prodid") + Environment.NewLine + "Product Name: " + reader2("ProdName") & Environment.NewLine 
            End While
            reader2.Close()
            subject = "Medicines Exceeding Reorder Level"
            body = String.Format("body={0}", Uri.EscapeDataString(body))
            System.Diagnostics.Process.Start("mailto:" & "" & "?subject=" & subject & "&" & body)
        Else
            MsgBox("No medicines")
        End If
        'Close Connection
End Sub