Solved

Handling an SqlException with SqlDataReader

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
rebind a grid after user clicks on node in treeview 1 34
imap read mail 1 27
Where did System.Data.Objects go? 2 23
List<PaisEntity> - show some 1 23
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…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

10 Experts available now in Live!

Get 1:1 Help Now