Solved

Handling an SqlException with SqlDataReader

Posted on 2004-08-02
7
888 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
  • 2
  • 2
7 Comments
 
LVL 28

Accepted Solution

by:
mmarinov earned 168 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 166 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 166 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now