[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Handling an SqlException with SqlDataReader

Posted on 2004-08-02
7
Medium Priority
?
932 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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