how to fetch values from procedure in vb.net

See more:

VB

Hello to all Expertisers of programming,

I'm Tarun from India ... a core programmer.

Here, I want a solution of my codes where i'm getting an exception. The case is that: I've an e-book portal, where user also can order for COD(Cash-On-Delivery). And, user also get 10% discount on every order. So, I want to mention discount amount and total amount with discount in e-mail body. I've done almost work on this but on the last step, my code is interuppting in an exception. So, please help me.. Portal is running on VB.Net platform


Getting an error during fetching values from stored procedure in a sendmail function.

Error: No data exists for the row/column.

My Code:

Procedure:
--------------------------------
sp_helptext GetTotalAmount 

CREATE Procedure GetTotalAmount XPuhThwi 
(  
@OrderId varchar(100)  
)  
As    
Begin    
 select TotalAmount from OrderAmount where OrderId=@OrderId  
End

------------------------------------

Here, I'm getting error

Private Sub prcSendMail()

        Dim Discount As Int64
        Dim Amount As String
        Try
            'Dim GetAmountCmd As OleDbCommand
            'Dim AmountReader As OleDbDataReader

            'GetAmountCmd = New OleDbCommand("GetTotalAmount", Con)
            'GetAmountCmd.CommandType = CommandType.StoredProcedure
            'GetAmountCmd.Parameters.AddWithValue("@OrderId", Session("SesID"))
            'AmountReader = GetAmountCmd.ExecuteReader

            CmdTmp2 = New OleDbCommand("GetTotalAmount", Con)
            CmdTmp2.CommandType = CommandType.StoredProcedure
            CmdTmp2.Parameters.AddWithValue("@OrderId", Session("SesID"))
            Rs = CmdTmp2.ExecuteReader
            Amount = Rs("TotalAmount").ToString
            Discount = CInt(Amount) * 0.1
        Catch ex As Exception

        End Try

        Str = "Dear " & txtUName.Text & "," & vbCrLf & vbCrLf & "Your VPP Order placed successfully" & _
                 vbCrLf & vbCrLf & "The login details are:" & vbCrLf & vbCrLf & "User Name : " & txtUName.Text & _
                 vbCrLf & "Password : " & Session("pass") & vbCrLf & vbCrLf & _
                 vbCrLf & "VPP Order Detail: " & vbCrLf & _
                 vbCrLf & VppOrderDetail & vbCrLf & vbCrLf & _
                 vbCrLf & "Total Amount: Rs" & Amount.ToString & vbCrLf & vbCrLf & _
                 vbCrLf & "Discount: Rs" & Discount.ToString & vbCrLf & vbCrLf & _
                 vbCrLf & "Total Amount with discount: Rs" & (Amount - Discount).ToString & vbCrLf & vbCrLf & _
                 vbCrLf & "We will inform you about your shipping detail." & vbCrLf & vbCrLf & _
                 vbCrLf & vbCrLf & "We do this in good faith-that when the goods arrive to your shipping address, you will take the delivery of the parcel against full payment." & vbCrLf & "In this case, you need not send the payment in advance. You have to make the full payment to the person who delivers parcel to you. We can send this VPP consignment through courier, by VPP-Value Payble Registered Post or through our representative, depending upon the city and the location of delivery." & vbCrLf & vbCrLf & _
                 "Regards," & vbCrLf & "Jaypee Brothers.com"
        Try
            Dim mail As New MailMessage()
            mail.To.Add(txtEMail.Text)                  'User ID            
            'mail.Bcc.Add("errors.jaypee@gmail.com")   'To Check
            mail.From = New MailAddress("sales@Jaypeebrothers.com")     'Jaypee ID
            mail.Subject = "VPP Order Detail"
            mail.Body = Str
            'mail.Body = "Body Comes Here"
            'SmtpMail.SmtpServer = "localhost"   'your real server goes here             
            'Dim SmtpMail As New SmtpClient("mail.jaypeebrothers.com")
            Dim SmtpMail As New SmtpClient("mail.jaypeebrothers.com")
            Dim cr As New System.Net.NetworkCredential("sales@jaypeebrothers.com", "passw0rd")
            SmtpMail.Credentials = cr
            SmtpMail.Send(mail)
            Response.Write("<script>alert('MAIL SENT SUCCESSFULLY!')</script>")
        Catch e1 As Exception

        End Try
    End Sub

--------------------------------------------------------------


Private Sub prcSaveData()
        Try
            'Add Author detail on 17th July 2015
            CmdTmp2 = New OleDbCommand("spTmpDetails2", Con)
            CmdTmp2.CommandType = CommandType.StoredProcedure
            CmdTmp2.Parameters.AddWithValue("@SID", Session("SesID"))
            Rs = CmdTmp2.ExecuteReader
            Dim TotalAmount As Int64 = 0
            While Rs.Read()
                CmdInsetCart = New OleDbCommand("spInsertCart", Con)
                CmdInsetCart.CommandType = CommandType.StoredProcedure
                CmdInsetCart.Parameters.AddWithValue("@SID", Session("SesID"))
                CmdInsetCart.Parameters.AddWithValue("@UN", txtUName.Text)
                CmdInsetCart.Parameters.AddWithValue("@PID", Rs("ISBN").ToString)
                CmdInsetCart.Parameters.AddWithValue("@Name", Rs("Title").ToString)
                CmdInsetCart.Parameters.AddWithValue("@Desc1", Rs("Author").ToString)
                CmdInsetCart.Parameters.AddWithValue("@Price", Rs("Price"))
                CmdInsetCart.Parameters.AddWithValue("@Qty", Rs("Qty"))
                CmdInsetCart.Parameters.AddWithValue("@Amt", Rs("Amt"))
                CmdInsetCart.Parameters.AddWithValue("@Status", "VPPOrder")
                CmdInsetCart.Parameters.AddWithValue("@PDate", Date.Now())

                TotalAmount = TotalAmount + CInt(Rs("Price"))

                CmdInsetCart.ExecuteReader()


                'VppOrderDetail = VppOrderDetail & vbCrLf & "ISBN: " & Rs("ISBN").ToString & vbCrLf & "Title: " & Rs("Title").ToString & vbCrLf & "Author: " & Rs("Author").ToString & vbCrLf & "Price: " & Rs("Price").ToString & vbCrLf & "QTY: " & Rs("Qty").ToString & vbCrLf & "Amount: " & Rs("Amt").ToString & vbCrLf & "Order Date: " & Date.Now()
                'Dim Temp As String
                'Temp = Rs("Amt")
                'Dim totalpaywithPromo As String
                'If (Session("PromoCode") = "") Then
                '    Dim promoDiscPrice As String
                '    promoDiscPrice = Session("promoDiscPrice")
                '    promoDiscPrice = Temp / 10
                '    totalpaywithPromo = Temp - promoDiscPrice
                'End If
                VppOrderDetail = vbCrLf & VppOrderDetail & "Order Id: " & Session("SesID") & vbCrLf & "ISBN: " & Rs("ISBN").ToString & vbCrLf & "Title: " & Rs("Title").ToString & vbCrLf & "Author: " & Rs("Author").ToString & vbCrLf & "Price: " & Rs("Price").ToString & vbCrLf & "QTY: " & Rs("Qty").ToString & vbCrLf & "Amount: " & Rs("Amt").ToString & vbCrLf & "Order Date: " & Date.Now()
            End While
            InsertTotalAmount(TotalAmount, Session("SesID").ToString)
        Catch ex As Exception
        End Try
    End Sub


----------------------------------------------


Private Sub InsertTotalAmount(ByVal TotalCurrentAmount As Int64, ByVal CurrentSessionId As String)
        Try
            Dim CmdInsertAmount As OleDbCommand
            CmdInsertAmount = New OleDbCommand("spInsertTotalAmount", Con)
            CmdInsertAmount.CommandType = CommandType.StoredProcedure
            CmdInsertAmount.Parameters.AddWithValue("@OrderId", CurrentSessionId)
            CmdInsertAmount.Parameters.AddWithValue("@TotalAmount", TotalCurrentAmount)

            CmdInsertAmount.ExecuteReader()
        Catch ex As Exception
            MyCLS.prcEMailOnError(ex, , , , System.Reflection.MethodBase.GetCurrentMethod.ToString())
        End Try
    End Sub