Redirect to form for each datarow in datagrid that meets criteria

I have a web site done in ASP.NET with VB.NET on MS Visual Web Developer.  A customer selects products from my site and adds them to the shopping cart (DataTable).  As the customer goes to check out, if a product requires a prescription, they are first redirected to the pharmacy_order form.  The Pharmacy_Order form page displays the Order Number (and product SKU), Medication and Quantity.  Other fields are filled in by the customer.  When submitted, the form fields and variables (asp Labels) are saved to a database table and emailed to our pharmacy.  After the form is submitted, the customer is redirected back to the checkout form/page to fill out the fields needed to complete the order.  But I need it to check the other (next) item in the shopping cart(next row in the DataTable) to see if they also need a prescription, then repeat the pharmacy_order form process before going back to the check out page.  What am I missing?  Or what code do I have in the wrong sequence.  Help!
Check out page calls the PharmacyOrder function:
 
Function PharmacyOrder()
        For Each objDR In objDT.Rows
            strSKU = objDR("Sku")
            'strProduct = objDR("Product")
            
            Dim myCmd10 As New SqlCommand
            Dim myConn10 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            myCmd10.Connection = myConn10
            myCmd10.CommandType = CommandType.Text
            myCmd10.CommandText = "SELECT product.sku AS sku, product_main.product_name AS product, " & _
                "product.product_name as short_descr, product.prescription AS prescription, product.vaccine AS vaccine " & _
                "FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE " & _
                "(product.sku NOT LIKE '%-main') AND (product.sku = '" & strSKU & "')"
            myConn10.Open()
            Dim myReader As SqlDataReader = myCmd10.ExecuteReader()
            Dim Rx As String
            Do While myReader.Read
                If Not IsDBNull(myReader.Item("prescription")) Then
                    Rx = myReader.GetString(myReader.GetOrdinal("prescription"))
                    If Rx = "Y" Then
                        If Not IsDBNull(myReader.Item("sku")) Then
                            Session("PharmSKU") = myReader.GetString(myReader.GetOrdinal("sku"))
                        End If
                        If Not IsDBNull(myReader.Item("product")) Then
                            Session("PharmMedication") = myReader.GetString(myReader.GetOrdinal("product"))
                        End If
                        If Not IsDBNull(myReader.Item("short_descr")) Then
                            Session("PharmQuantity") = myReader.GetString(myReader.GetOrdinal("short_descr"))
                        End If
                        Response.Redirect("pharmacy_order.aspx")
                    End If
                End If
            Loop
            myConn10.Close()
        Next
    End Function
 
 
The Pharmacy_Order form page:
    upon Page_Load:
 
Dim orderNr As String = ""
        Dim myCmd As New SqlCommand
        Dim myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
        myCmd.Connection = myConn
        myCmd.CommandType = CommandType.Text
        myCmd.CommandText = "Select nextnumber from nextnumber"
        myConn.Open()
        Dim myReader As SqlDataReader = myCmd.ExecuteReader()
        Do While myReader.Read
            OrderNr = (myReader("nextnumber") + 1)
        Loop
        myConn.Close()
        Session("OrderNr") = orderNr
        lblOrderNr.Text = orderNr
        
        lblSKU.Text = Session("PharmSKU")
        lblMedication.Text = Session("PharmMedication")
        lblQuantity.Text = Session("PharmQuantity")
 
and when the Submit button is clicked, the PharmacyOrder sub is called:
 
Sub PharmacyOrder(ByVal s As Object, ByVal e As EventArgs)
        ' Set all the variables
        Dim myDate As String = System.DateTime.Now.ToString("MMM dd, yyyy")
        Dim myTime As String = System.DateTime.Now.ToString("yyyyMMddHHmmss")
        Dim strOrderNr As String = Session("OrderNr")
        Dim strSKU As String = Session("PharmSKU")
        Dim strClientName As String = txtClientName.Text
        'Dim clientname As String = String.Concat(txtClientFname.Text, txtClientLname.Text)
        Dim strPet As String = txtPet.Text
        Dim strSpecies As String = ddlSpecies.SelectedValue
        Dim strMedication As String = Session("PharmMedication")
        Dim strQuantity As String = Session("PharmQuantity")
        Dim strVname As String = txtVname.Text
        Dim strHospital As String = txtHospital.Text
        Dim strVphone As String = txtVphone.Text
        Dim strVfax As String = txtVfax.Text
        
        ' Insert them all into the database table
        Dim myCmd2 As New SqlCommand
        Dim myConn2 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
        myCmd2.Connection = myConn2
        myCmd2.CommandType = CommandType.Text
        myCmd2.CommandText = "INSERT INTO pharmacy_order (id, orderID, client_name, pet_name, species, medication, quantity, vet_name, hospital, vet_phone, vet_fax, last_updated) VALUES" & _
        " ('" & myTime & "','" & strOrderNr & "','" & strClientName & "','" & strPet & "','" & strSpecies & "','" & strMedication & "','" & strQuantity & "','" & strVname & "','" & strHospital & "','" & strVphone & "','" & strVfax & "','" & myTime & "')"
        myConn2.Open()
        myCmd2.ExecuteNonQuery()
        myConn2.Close()
        
        ' Now send the email to Pharmacy
        Dim mMailMessage As New MailMessage()
        Dim emailAddress As String = "pharmacy@omahavaccine.com"
        'mMailMessage.From = New MailAddress("online_order@omahavaccine.com")
        mMailMessage.To.Add(New MailAddress(emailAddress))
        'mMailMessage.Subject = "Prescription Request"
        mMailMessage.Body = "<table width=600 border=0 cellpadding=0 cellspacing=0>" & _
        "<tr><td align=center colspan=3><font face=Arial pointsize=12px>CSR Company, Inc.<br />11143 Mockingbird Drive<br />Omaha, NE 68137<br />Phone: 800-367-4444<br />Fax: 888-809-6040</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td align=center colspan=3><font face=Arial pointsize=12px><b>PRESCRIPTION REQUEST</b></font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td align=center colspan=3><font face=Arial pointsize=12px><b>Please sign and fax back to us toll free: 888-809-6040</b></font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td align=left colspan=3><font face=Arial pointsize=12px>The client listed below placed an order for their pet&acute;s medication. Please make any necessary changes to the medications requested. If the prescription is declined, please indicate so and fax back so we can expedite the client making an appointment with your hospital.</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150>&nbsp;</td><td width=300>&nbsp;</td><td align=left width=150><font face=Arial pointsize=12px>Thank you,<br />Lisa Haug, R Ph<br />Amanda Hug, R Ph<br />" & myDate & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Order Number: </font></td><td colspan=2>" & strOrderNr & "&nbsp;(sku #" & strSKU & ")</td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Client:&nbsp;</font></td><td colspan=2>" & strClientName & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Pet&acute;s Name:&nbsp;</font></td><td colspan=2>" & strPet & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Species:&nbsp;</font></td><td colspan=2>" & strSpecies & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Medication:&nbsp;</font></td><td colspan=2>" & strMedication & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Size/Quantity:&nbsp;</font></td><td colspan=2>" & strQuantity & "</td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Directions for use:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>__________________________________________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td colspan=3 width=450><font face=Arial pointsize=12px>_____________________________________________________________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Refills:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>&nbsp;0&nbsp;-&nbsp;1&nbsp;-&nbsp;2&nbsp;-&nbsp;3&nbsp;-&nbsp;4&nbsp;-&nbsp;5&nbsp;-&nbsp;PRN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date: _______________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Signature:&nbsp;</font></td>" & _
        "<td colspan=2 align=left><font face=Arial pointsize=12px>______________________________&nbsp;DVM License #:&nbsp;________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td colspan=3 align=left><font face=Arial pointsize=12px>Veterinarian&acute;s Name:&nbsp;" & strVname & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Hospital/Clinic:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>" & strHospital & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Vet&acute;s Phone:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>" & strVphone & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Vet&acute;s Fax:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>" & strVfax & "</font></td></tr>" & _
        "</table>"
        
        mMailMessage.IsBodyHtml = True
        mMailMessage.Priority = MailPriority.Normal
 
        Dim mSmtpClient As New SmtpClient()
        mSmtpClient.Send(mMailMessage)
        
        'lblSuccess.Text = "<strong>Thank you. We submitted your information to our pharmacy.</strong>"
        Response.Redirect("Checkout.aspx")
    End Sub

Open in new window

OVC-it-guyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
You need to put a check in the page load event of checkout page to see if there is any item in the datatable which needs redirection to pharmacy fillout. Keep redirecting while it does or allow to checkout.
Other, more reasonable, option is to change the pharmacy form page and supply it only the order number. This form should then keep asking information while there are items in the order which require this information.
0
OVC-it-guyAuthor Commented:
Gonna try the other, more reasonable option and see what it does (or doesn't do).
0
OVC-it-guyAuthor Commented:
Took out the SKU (except where the DataTable shows items in the Shopping Cart).  When I fill in the form fields and click Submit, it calls the PharmacyOrder sub which saved the data to the database table and sends the email to the pharmacy.  It may or may not have redirected me back to the Check_out page, but since I have a second item which requires a prescription, I'm taken again to the Pharmacy_order page.  The form fields are empty, but the variables displayed in asp:Label's still show the first item in the shopping cart.  I need it to step to the second item in the shopping cart.  See image.
RxRequired.jpg
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

OVC-it-guyAuthor Commented:
Do you think I need to pull the DataTable/SHopping Cart grid, as well (because it's got the sku, as well)?
0
OVC-it-guyAuthor Commented:
As for the other method you mentioned, CodeCruiser, what, specifically do you suggest?  I already run the "For Each objDR In objDT.Rows . . . Next " to query the database for each SKU to see if it requires a prescription.  So, I'm not sure what you mean by a check.  If you have an idea, this might be just what I'm missing.
For Each objDR In objDT.Rows
            strSKU = objDR("Sku")
            
            Dim myCmd10 As New SqlCommand
            Dim myConn10 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            myCmd10.Connection = myConn10
            myCmd10.CommandType = CommandType.Text
            myCmd10.CommandText = "SELECT product.sku AS sku, product_main.product_name AS product, " & _
                "product.product_name as short_descr, product.prescription AS prescription, product.vaccine AS vaccine " & _
                "FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE " & _
                "(product.sku NOT LIKE '%-main') AND (product.sku = '" & strSKU & "')"
            myConn10.Open()
            Dim myReader As SqlDataReader = myCmd10.ExecuteReader()
            Dim Rx As String
            Do While myReader.Read
                If Not IsDBNull(myReader.Item("prescription")) Then
                    Rx = myReader.GetString(myReader.GetOrdinal("prescription"))
                    If Rx = "Y" Then
                        If Not IsDBNull(myReader.Item("sku")) Then
                            Session("PharmSKU") = myReader.GetString(myReader.GetOrdinal("sku"))
                        End If
                        If Not IsDBNull(myReader.Item("product")) Then
                            Session("PharmMedication") = myReader.GetString(myReader.GetOrdinal("product"))
                        End If
                        If Not IsDBNull(myReader.Item("short_descr")) Then
                            Session("PharmQuantity") = myReader.GetString(myReader.GetOrdinal("short_descr"))
                        End If
                        Response.Redirect("pharmacy_order.aspx")
                    End If
                End If
            Loop
            myConn10.Close()
        Next

Open in new window

0
CodeCruiserCommented:
You need to use an additional field in the table to indicate whether the form has been filled for an item or not. When page loads, check to see which item needs form filling and its not filled yet. Then you can set all the labels and pointers to that row.
0
OVC-it-guyAuthor Commented:
I assume you mean the database table (not the DataTable), since I am runnig the query against each SKU in the DataTable (shopping Cart).  I'll have to give that a try and get back to you.
0
CodeCruiserCommented:
You can add that field to the datatable in memory rather than the database if that meets your requirements. The pupose is just to see which SKU has been process and which requires processing.
Also, What if you use a repeater control and provide input boxes for all the SKUs at once rather than one at a time?
0
OVC-it-guyAuthor Commented:
Adding the field (to either database table or DataTable) makes sense, so the app knows which product has already had a prescription form submitted (then repeat the query for any other product in the shopping cart).  Providing input boxes doesn't work, as veterinarians (and human doctors) must only put a single item (and its direction for use) on a prescription.
0
OVC-it-guyAuthor Commented:
Wait, before I go adding fields to database tables and adding another query or two to the routine, what do you thing about this - put in a row counter to step through the rows of the shopping cart/DataTable.  Then I don't have to add anything to any table:

For intCounter = 0 To objDT.Rows.Count - 1
            objDR = objDT.Rows(intCounter)
            ' run query here to see if prescription required
        Next

Your thoughts?
0
CodeCruiserCommented:
Yeah. Could be done. You can store the current rowindex in session and everytime the form is submitted, check if there is another product in order which needs presciption information then reset the form for that product.
0
OVC-it-guyAuthor Commented:
Let me give that a try then . . .
0
OVC-it-guyAuthor Commented:
Ok, maybe I'm too tired to be working on this anymore today.  Where on by Checkout page would I put the For intCounter = 0 To objDT.Rows.Count - 1 . . . routine?  Or, better yet, where do I put the query to check whether a product requires a prescription?  Checkout page code in snippet.

Function PharmacyOrder()
        For Each objDR In objDT.Rows
            strSKU = objDR("Sku")
            'strProduct = objDR("Product")
            
            Dim myCmd10 As New SqlCommand
            Dim myConn10 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            myCmd10.Connection = myConn10
            myCmd10.CommandType = CommandType.Text
            myCmd10.CommandText = "SELECT product.sku AS sku, product_main.product_name AS product, " & _
                "product.product_name as short_descr, product.prescription AS prescription, product.vaccine AS vaccine " & _
                "FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE " & _
                "(product.sku NOT LIKE '%-main') AND (product.sku = '" & strSKU & "')"
            myConn10.Open()
            Dim myReader As SqlDataReader = myCmd10.ExecuteReader()
            Dim Rx As String
            Do While myReader.Read
                If Not IsDBNull(myReader.Item("prescription")) Then
                    Rx = myReader.GetString(myReader.GetOrdinal("prescription"))
                    If Rx = "Y" Then
                        If Not IsDBNull(myReader.Item("sku")) Then
                            Session("PharmSKU") = myReader.GetString(myReader.GetOrdinal("sku"))
                        End If
                        If Not IsDBNull(myReader.Item("product")) Then
                            Session("PharmMedication") = myReader.GetString(myReader.GetOrdinal("product"))
                        End If
                        If Not IsDBNull(myReader.Item("short_descr")) Then
                            Session("PharmQuantity") = myReader.GetString(myReader.GetOrdinal("short_descr"))
                        End If
                        Response.Redirect("pharmacy_order.aspx")
                    End If
                End If
            Loop
            myConn10.Close()
        Next
    End Function

Open in new window

0
OVC-it-guyAuthor Commented:
Trying again . . .  I add the For intCounter = 0 To objDT.Rows.Count - 1 and set the variable strSKU to the sku # for the row, then run the query to determine if they need to submit a prescription, but I can't get off the Pharmacy_order page.  After clicking the Submit button, which saves the record in the database and email the info to our pharmacy, I redirect back to the Checkout page, but it just sends me back to the Pharmacy_order page.
 For intCounter = 0 To objDT.Rows.Count - 1
            objDR = objDT.Rows(intCounter)
            strSKU = objDR("Sku")
           
            Dim myCmd10 As New SqlCommand
            Dim myConn10 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            myCmd10.Connection = myConn10
            myCmd10.CommandType = CommandType.Text
            myCmd10.CommandText = "SELECT product.sku AS sku, product_main.product_name AS product, " & _
                "product.product_name as short_descr, product.prescription AS prescription, product.vaccine AS vaccine " & _
                "FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE " & _
                "(product.sku NOT LIKE '%-main') AND (product.sku = '" & strSKU & "')"
            myConn10.Open()
            Dim myReader As SqlDataReader = myCmd10.ExecuteReader()
            Dim Rx As String
            Do While myReader.Read . . .
0
OVC-it-guyAuthor Commented:
Trying again . . .  I add the For intCounter = 0 To objDT.Rows.Count - 1 and set the variable strSKU to the sku # for the row, then run the query to determine if they need to submit a prescription, but I can't get off the Pharmacy_order page.  After clicking the Submit button, which saves the record in the database and email the info to our pharmacy, I redirect back to the Checkout page, but it just sends me back to the Pharmacy_order page (code from Pharmacy_order in snippet).  What's missing?
 
For intCounter = 0 To objDT.Rows.Count - 1
            objDR = objDT.Rows(intCounter)
            strSKU = objDR("Sku")
           
            Dim myCmd10 As New SqlCommand
            Dim myConn10 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            myCmd10.Connection = myConn10
            myCmd10.CommandType = CommandType.Text
            myCmd10.CommandText = "SELECT product.sku AS sku, product_main.product_name AS product, " & _
                "product.product_name as short_descr, product.prescription AS prescription, product.vaccine AS vaccine " & _
                "FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE " & _
                "(product.sku NOT LIKE '%-main') AND (product.sku = '" & strSKU & "')"
            myConn10.Open()
            Dim myReader As SqlDataReader = myCmd10.ExecuteReader()
            Dim Rx As String
            Do While myReader.Read . . .
Dim myDate As String = System.DateTime.Now.ToString("MMM dd, yyyy")
        Dim myTime As String = System.DateTime.Now.ToString("yyyyMMddHHmmss")
        Dim strOrderNr As String = Session("OrderNr")
        Dim strSKU As String = Session("PharmSKU")
        Dim strClientName As String = txtClientName.Text
        'Dim clientname As String = String.Concat(txtClientFname.Text, txtClientLname.Text)
        Dim strPet As String = txtPet.Text
        Dim strSpecies As String = ddlSpecies.SelectedValue
        Dim strMedication As String = Session("PharmMedication")
        Dim strQuantity As String = Session("PharmQuantity")
        Dim strVname As String = txtVname.Text
        Dim strHospital As String = txtHospital.Text
        Dim strVphone As String = txtVphone.Text
        Dim strVfax As String = txtVfax.Text
        
        ' Insert them all into the database table
        Dim myCmd2 As New SqlCommand
        Dim myConn2 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
        myCmd2.Connection = myConn2
        myCmd2.CommandType = CommandType.Text
        myCmd2.CommandText = "INSERT INTO pharmacy_order (id, orderID, client_name, pet_name, species, medication, quantity, vet_name, hospital, vet_phone, vet_fax, last_updated) VALUES" & _
        " ('" & myTime & "','" & strOrderNr & "','" & strClientName & "','" & strPet & "','" & strSpecies & "','" & strMedication & "','" & strQuantity & "','" & strVname & "','" & strHospital & "','" & strVphone & "','" & strVfax & "','" & myTime & "')"
        myConn2.Open()
        myCmd2.ExecuteNonQuery()
        myConn2.Close()
        
        ' Now send the email to Pharmacy
        Dim mMailMessage As New MailMessage()
        Dim emailAddress As String = "pharmacist@omahavaccine.com"
        mMailMessage.From = New MailAddress("WebOrder@omahavaccine.com")
        mMailMessage.To.Add(New MailAddress(emailAddress))
        'mMailMessage.Subject = "Prescription Request"
        mMailMessage.Body = "<html><head></head><style type=text/css><!-- body {margin:0px; font-family:Arial; font-size:12px;} --></style><body><table width=600 border=0 cellpadding=0 cellspacing=0>" & _
        "<tr><td align=center colspan=3><font face=Arial pointsize=12px>CSR Company, Inc.<br />11143 Mockingbird Drive<br />Omaha, NE 68137<br />Phone: 800-367-4444<br />Fax: 888-809-6040</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td align=center colspan=3><font face=Arial pointsize=12px><b>PRESCRIPTION REQUEST</b></font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td align=center colspan=3><font face=Arial pointsize=12px><b>Please sign and fax back to us toll free: 888-809-6040</b></font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td align=left colspan=3><font face=Arial pointsize=12px>The client listed below placed an order for their pet&acute;s medication. Please make any necessary changes to the medications requested. If the prescription is declined, please indicate so and fax back so we can expedite the client making an appointment with your hospital.</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150>&nbsp;</td><td width=300>&nbsp;</td><td align=left width=150><font face=Arial pointsize=12px>Thank you,<br />Lisa Haug, R Ph<br />Amanda Hug, R Ph<br />" & myDate & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Order Number: </font></td><td colspan=2>" & strOrderNr & "</td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Client:&nbsp;</font></td><td colspan=2>" & strClientName & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Pet&acute;s Name:&nbsp;</font></td><td colspan=2>" & strPet & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Species:&nbsp;</font></td><td colspan=2>" & strSpecies & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Medication:&nbsp;</font></td><td colspan=2>" & strMedication & "</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Size/Quantity:&nbsp;</font></td><td colspan=2>" & strQuantity & "</td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Directions for use:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>__________________________________________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td colspan=3 width=450><font face=Arial pointsize=12px>_____________________________________________________________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Refills:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>&nbsp;0&nbsp;-&nbsp;1&nbsp;-&nbsp;2&nbsp;-&nbsp;3&nbsp;-&nbsp;4&nbsp;-&nbsp;5&nbsp;-&nbsp;PRN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date: _______________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Signature:&nbsp;</font></td>" & _
        "<td colspan=2 align=left><font face=Arial pointsize=12px>______________________________&nbsp;DVM License #:&nbsp;________</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td colspan=3 align=left><font face=Arial pointsize=12px>Veterinarian&acute;s Name:&nbsp;" & strVname & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Hospital/Clinic:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>" & strHospital & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Vet&acute;s Phone:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>" & strVphone & "</font></td></tr>" & _
        "<tr><td colspan=3>&nbsp;</td></tr>" & _
        "<tr><td width=150 align=right><font face=Arial pointsize=12px>Vet&acute;s Fax:&nbsp;</font></td>" & _
        "<td colspan=2><font face=Arial pointsize=12px>" & strVfax & "</font></td></tr></table></body></html>"
                
        mMailMessage.IsBodyHtml = True
        mMailMessage.Priority = MailPriority.Normal
 
        Dim mSmtpClient As New SmtpClient()
        mSmtpClient.Send(mMailMessage)
        
        lblSuccess.Text = "<strong>Thank you. We submitted your information to our pharmacy.<br>Proceed to <a href=Checkout.aspx>Check Out page</a>.</strong>"

Open in new window

0
OVC-it-guyAuthor Commented:
Ok, let's re-group on this.  Nothing I'm doing is working.  I have the function on the Check_out page querying to see if a product requires a prescription (see snippet). The problem is, as soon as I submit a prescription for the first product requiring it from the shopping cart, it's not going to the next.  That's probaly because I'm moving from the Pharmacy_order page back to the Check-out page.  I need to somehow mark the row in the DataTable that a form has already been submitted.  ANd I'm out of time on this project.  Can someone please tell me how to create the new DataTable column and annotate it on the Check_out page upon submission of a Pharmacy_order form so when I go back to Checkout, it knows to go to the next product in the cart?,
Function PharmacyOrder()
        For Each objDR In objDT.Rows
            strSKU = objDR("Sku")
            
            Dim myCmd10 As New SqlCommand
            Dim myConn10 As New SqlConnection(ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            myCmd10.Connection = myConn10
            myCmd10.CommandType = CommandType.Text
            myCmd10.CommandText = "SELECT product.sku AS sku, product_main.product_name AS product, " & _
                "product.product_name as short_descr, product.prescription AS prescription " & _
                "FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id WHERE " & _
                "(product.sku NOT LIKE '%-main') AND (product.sku = '" & strSKU & "')"
            myConn10.Open()
            Dim myReader As SqlDataReader = myCmd10.ExecuteReader()
            Dim Rx As String
            Do While myReader.Read
                If Not IsDBNull(myReader.Item("prescription")) Then
                    Rx = myReader.GetString(myReader.GetOrdinal("prescription"))
                    If Rx = "Y" Then
                        If Not IsDBNull(myReader.Item("sku")) Then
                            Session("PharmSKU") = myReader.GetString(myReader.GetOrdinal("sku"))
                        End If
                        If Not IsDBNull(myReader.Item("product")) Then
                            Session("PharmMedication") = myReader.GetString(myReader.GetOrdinal("product"))
                        End If
                        If Not IsDBNull(myReader.Item("short_descr")) Then
                            Session("PharmQuantity") = myReader.GetString(myReader.GetOrdinal("short_descr"))
                        End If
                        Response.Redirect("pharmacy_order.aspx")
                    End If
                End If
            Loop
            myConn10.Close()
        Next
    End Function

Open in new window

0
CodeCruiserCommented:
That's why i initially gave the suggestion to add another column to the datatable to keep track of the products for which the form has been submitted. You can add column to the datatable like this

dTable.columns.add("FormDone", Gettype(Boolean))

then you can set it to true

dTable.Rows.item(1).item("FormDone")=True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OVC-it-guyAuthor Commented:
You're right, CodeCruiser.  Sorry to have gone full circle.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.