Solved

Handling an SqlException with SqlDataReader

Posted on 2004-08-02
7
922 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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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