?
Solved

Handling an SqlException with SqlDataReader

Posted on 2004-08-02
7
Medium Priority
?
925 Views
Last Modified: 2008-02-26
I'm using an SqlDataReader method to run a stored procedure. I get no compile errors if I don't use a catch statement, but I also don't get any data. I've tried doing a console.writeline which doesnt work (guess you can't write to the console when using ASP with classes?). Using this:

try {

 myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
            }

            catch (SqlException e) {
                    Console.WriteLine(e.Message + "\n");
                    Console.WriteLine(e.StackTrace);            
            }
Gives me the error: Not all code paths return a value. How can I return the exception object using a datareader? Thanks! Here's the entire metod:


public SqlDataReader gettheitems(string variety) {


        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("gettheitems", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter parametervariety = new SqlParameter("@variety", SqlDbType.NVarChar, 50);
            parametervariety.Value = variety;
            myCommand.Parameters.Add(parametervariety);
try {

 myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
            }

            catch (SqlException e) {
                    Console.WriteLine(e.Message + "\n");
                   Console.WriteLine(e.StackTrace);
            }
 }






0
Comment
Question by:dprasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
7 Comments
 
LVL 28

Accepted Solution

by:
mmarinov earned 672 total points
ID: 11697794
Hi dprasad,

when you use asp.net applications you can not write to the Console, so the ways are:
1. Write the error message in the EventLog, by EventLog.WriteLine
2. throw the exceptoin from the gettheitems method like this

catch ( SqlException e )
{
throw;
}

then in the calling method yuo must have try..catch blocks and in the catch block to Response.Write the message or to add a message to some label that will display the error


B..M
0
 
LVL 17

Assisted Solution

by:AerosSaga
AerosSaga earned 664 total points
ID: 11697798
You could iterate through the exception like so:
Imports System
Imports System.Resources
Imports System.Collections
Imports Microsoft.VisualBasic

Class EnumerateResources
   
   Public Shared Sub Main()
      ' Create a ResourceReader for the file items.resources.
      Dim rr As New ResourceReader("items.resources")      
     
      ' Create an IDictionaryEnumerator to iterate through the resources.
      Dim id As IDictionaryEnumerator = rr.GetEnumerator()
     
      ' Iterate through the resources and display the contents to the console.
      While id.MoveNext()
         Console.WriteLine(ControlChars.NewLine + "[{0}] " + ControlChars.Tab + "{1}", id.Key, id.Value)
      End While

      rr.Close()

   End Sub

End Class

Regards,

Aeros
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11697803
try {

 myConnection.Open();
            SqlDataReader result myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
            }

            catch (SqlException e) {
                    Console.WriteLine(e.Message + "\n");
                    Console.WriteLine(e.StackTrace);            
            }
            return null;
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 11697839
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemExceptionClassTopic.asp

This will show you how to expose whatever property your looking for in the exception class

Regards,

Aeros
0
 
LVL 37

Assisted Solution

by:gregoryyoung
gregoryyoung earned 664 total points
ID: 11697905
I should be more specific ...

the error you are getting is because not all of your paths returned a value ... everything else is fine ...

try {

 myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
            }

            catch (SqlException e) {
                    Console.WriteLine(e.Message + "\n");
                    Console.WriteLine(e.StackTrace);            
            }
            return null; //this returns null no matter what ...


another method would be to putit like this ...

public SqlDataReader gettheitems(string variety) {
            SqlDataReader result = null;
           SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand myCommand = new SqlCommand("gettheitems", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter parametervariety = new SqlParameter("@variety", SqlDbType.NVarChar, 50);
            parametervariety.Value = variety;
            myCommand.Parameters.Add(parametervariety);
            try {
            myConnection.Open();
            result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }

            catch (SqlException e) {
                    Console.WriteLine(e.Message + "\n");
                   Console.WriteLine(e.StackTrace);
            }
            // Return the datareader result
            return result;
 }
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month13 days, 2 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question