5 Replies - 244 Views - Last Post: 23 August 2019 - 09:10 AM Rate Topic: -----

#1 Skylar0202   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-August 19

SQLDataReader throws a SystemInvalidOperation Exception

Posted 23 August 2019 - 08:29 AM

I have created a store procedure to pull data. I got an System Invalid Operation Exception when I run it goes to my while(dr.Read()) the goes straight to the finally statement so I had to put an Debug.WriteLine(dr) to see what the error was, I also get an Enumeration yielded no results. I am still new at coding and lost.

       public int SendConfirmationEmail(int orderid)
        {
            int failurecode = 0;
            string custemailstr = String.Empty;
            MailAddress fromAddress = new MailAddress("[email protected]", "Company");

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ACAOSBConnectionString"].ToString()))
            {
                // create a command object
                SqlCommand cmd = new SqlCommand();

                // Assign the connection to the command
                cmd.Connection = conn;
                cmd.CommandText = "usp_automated_email";
                cmd.CommandType = CommandType.StoredProcedure;

                // append the parameters
                cmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = orderid;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    try
                    {
                        while(dr.Read())
                        {
                            custemailstr += "Order Number:";
                            custemailstr += orderid.ToString();
                            custemailstr += "<br>";

                            custemailstr += "Shipment Status:";
                            custemailstr += "CONFIRMED (";
                            custemailstr += dr["creationdt"].ToString() + ")";
                            custemailstr += "<br>";

                            custemailstr += "ORDER DETAILS:";
                            custemailstr += "<br>";

                            custemailstr += "Shipment Pickup Location:";
                            custemailstr += "<br>";
                            custemailstr += dr["pickupaddress"].ToString();
                            custemailstr += "<br>";
                            custemailstr += dr["pickupcity"].ToString() + "," + dr["pickupstate"].ToString() + "&nbsp;" + dr["pickupzip"].ToString();
                            custemailstr += "<br>";

                            custemailstr += "Shipment Delivery Location:";
                            custemailstr += "<br>";
                            custemailstr += dr["delivercontact"].ToString();
                            custemailstr += "<br>";
                            custemailstr += dr["deliveraddress"].ToString();
                            custemailstr += "<br>";
                            custemailstr += dr["delivercity]"].ToString() + "," + dr["deliverstate"].ToString() + "&nbsp;" + dr["deliverzip"].ToString();
                            custemailstr += "<br>";

                            custemailstr += "Equipment Type:";
                            custemailstr += dr["vehicaltypename"].ToString();
                            custemailstr += "<br>";

                            custemailstr += "Service Type:";
                             custemailstr += dr["servicetypename"].ToString();
                            custemailstr += "<br>";

                            custemailstr += "Pieces and Weight:";
                            custemailstr += dr["numberofpieces"].ToString() + " @ " + dr["totalweight"].ToString();
                            custemailstr += "<br>";

                            custemailstr += "Link to Online Order:";
                            custemailstr += "<a href=\"https://my.website.com\">www.website.com</a>";
                            custemailstr += "<br>";
                            custemailstr += "Thank You<br>";
                            custemailstr += "Company"<br>";
                            custemailstr += "(555) 000-0000<br>";
                            custemailstr += "[email protected]<br>";
                            custemailstr += "website<br>";


                        }
                        Debug.WriteLine(dr);
                    }
                    catch(Exception ex)
                    {
                        ex.ToString();
;
                    }
                    finally
                    {
                        dr.Close();
                    }
                }
                catch(Exception ex)
                {
                    ex.ToString();
                
                }
                try
                {
                    if(custemailstr != string.Empty)
                    {
                        SmtpClient smtpclient = new SmtpClient();
                        System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
                         //Set the email smtp host 
                        smtpclient.Host = "smtp.gmail.com";
                        //Set the email client port 
                        smtpclient.Port = 587;
                        mail.From = fromAddress;
                        //Adding email id of receiver link
                        mail.To.Add("[email protected]");
                        //Set the email subject
                        mail.Subject = (" Order Confirmation");
                        mail.IsBodyHtml = true;
                        mail.Body = custemailstr;

                        smtpclient.Send(mail);
                    }

                }
                catch (Exception ex)
                {

                    ex.ToString();
              
                }
                return failurecode;
            }
        }
        
    }
}



Is This A Good Question/Topic? 0
  • +

Replies To: SQLDataReader throws a SystemInvalidOperation Exception

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15310
  • View blog
  • Posts: 61,396
  • Joined: 12-June 08

Re: SQLDataReader throws a SystemInvalidOperation Exception

Posted 23 August 2019 - 08:37 AM

Line 71 is not correct given the number of double quotes.

071                            custemailstr += "Company"<br>";



String interpolation may help you out to make it more clean.
https://docs.microso...ns/interpolated

Ex:

                            custemailstr = $@"Order Number: {orderid} <br />
                                Shipment Status: CONFIRMED ({dr["creationdt"]}) <br />
                                ORDER DETAILS:<br />";

Was This Post Helpful? 0
  • +
  • -

#3 Skylar0202   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-August 19

Re: SQLDataReader throws a SystemInvalidOperation Exception

Posted 23 August 2019 - 08:46 AM

I made the changes but still does not make it into my while loop.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15310
  • View blog
  • Posts: 61,396
  • Joined: 12-June 08

Re: SQLDataReader throws a SystemInvalidOperation Exception

Posted 23 August 2019 - 08:53 AM

You should extend your try/catches to include setting up your connection and what not.

Is your connection open? Did you open it?

You can use the 'USING' with SQLCommand objects to. May as well as add that.
Was This Post Helpful? 0
  • +
  • -

#5 Skylar0202   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 23-August 19

Re: SQLDataReader throws a SystemInvalidOperation Exception

Posted 23 August 2019 - 09:03 AM

Do you have an example I'm new at coding. Thanks
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15310
  • View blog
  • Posts: 61,396
  • Joined: 12-June 08

Re: SQLDataReader throws a SystemInvalidOperation Exception

Posted 23 August 2019 - 09:10 AM

You are using both the 'try/catch' and the 'using' in your existing code. That's your example.

Make one of the trys start sooner, and follow your existing pattern on the using.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1