Not getting SqlException using DataReader

R

Robert Phillips

I'm using a SqlDataReader to call a proc that reads data. My call to
raiseerror has no effect. Below is the code. if i comment out the select
statement, my code behaves as expected.

CREATE PROCEDURE [dbo].[usp_Table1Read]
@id int
AS

--select * from table1 where IId = @Id

if (@@rowcount = 0)
begin
raiserror('an error occured', 10, 1)
return 50000
end

return 0
GO

using System;

using System.Data;

using System.Data.SqlClient;

namespace ReaderTest

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

Class1 c = new Class1();

c.DoWork();

}

public void DoWork()

{

try

{

//connection

SqlConnection connection = new SqlConnection();

connection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=MyDataBase;server=(local)";

connection.Open();

connection.InfoMessage += new
SqlInfoMessageEventHandler(this.MySqlInfoHandler);

//command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = "usp_Table1Read";

command.Connection = connection;

//create return parameter

SqlParameter p = new SqlParameter();

p.ParameterName = "@RetVal";

p.Direction = ParameterDirection.ReturnValue;

p.SqlDbType = SqlDbType.Int;

command.Parameters.Add(p);

p = new SqlParameter();

p.ParameterName = "@Id";

p.Direction = ParameterDirection.Input;

p.SqlDbType = SqlDbType.Int;

p.Value = "3";

command.Parameters.Add(p);

//get reader

SqlDataReader reader = command.ExecuteReader();

//look at return parameter (1)

Console.Write("Check 1: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

while(reader.Read()){}

reader.Close();

connection.Close();

//look at return parameter (2)

Console.Write("Check 2: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

}

catch(Exception ex)

{

Console.WriteLine(ex.ToString());

}

}

public void MySqlInfoHandler(object sender, SqlInfoMessageEventArgs e)

{

Console.WriteLine("************************");

Console.WriteLine(e.Message);

Console.WriteLine("************************");

}

}

}
 
W

William \(Bill\) Vaughn

Check BOL. The Sev has to be 11 IIRC to trigger an exception.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
R

Robert Phillips

thanks....with 10, i expected the InfoMessage handler to
be called, and with 11 an exception to be thrown. neither
works with select statement uncommented (in my sample).
-----Original Message-----
Check BOL. The Sev has to be 11 IIRC to trigger an exception.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I'm using a SqlDataReader to call a proc that reads data. My call to
raiseerror has no effect. Below is the code. if i comment out the select
statement, my code behaves as expected.

CREATE PROCEDURE [dbo].[usp_Table1Read]
@id int
AS

--select * from table1 where IId = @Id

if (@@rowcount = 0)
begin
raiserror('an error occured', 10, 1)
return 50000
end

return 0
GO

using System;

using System.Data;

using System.Data.SqlClient;

namespace ReaderTest

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

Class1 c = new Class1();

c.DoWork();

}

public void DoWork()

{

try

{

//connection

SqlConnection connection = new SqlConnection();

connection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=MyDataBase;server=(local)";

connection.Open();

connection.InfoMessage += new
SqlInfoMessageEventHandler(this.MySqlInfoHandler);

//command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = "usp_Table1Read";

command.Connection = connection;

//create return parameter

SqlParameter p = new SqlParameter();

p.ParameterName = "@RetVal";

p.Direction = ParameterDirection.ReturnValue;

p.SqlDbType = SqlDbType.Int;

command.Parameters.Add(p);

p = new SqlParameter();

p.ParameterName = "@Id";

p.Direction = ParameterDirection.Input;

p.SqlDbType = SqlDbType.Int;

p.Value = "3";

command.Parameters.Add(p);

//get reader

SqlDataReader reader = command.ExecuteReader();

//look at return parameter (1)

Console.Write("Check 1: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

while(reader.Read()){}

reader.Close();

connection.Close();

//look at return parameter (2)

Console.Write("Check 2: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

}

catch(Exception ex)

{

Console.WriteLine(ex.ToString());

}

}

public void MySqlInfoHandler(object sender, SqlInfoMessageEventArgs e)

{

Console.WriteLine("************************");

Console.WriteLine(e.Message);

Console.WriteLine("************************");

}

}

}
Click to expand...


.
Click to expand...
 
W

William \(Bill\) Vaughn

I assume there are no qualifying rows? Did you try this in QA?

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Robert Phillips said:
thanks....with 10, i expected the InfoMessage handler to
be called, and with 11 an exception to be thrown. neither
works with select statement uncommented (in my sample).
-----Original Message-----
Check BOL. The Sev has to be 11 IIRC to trigger an exception.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I'm using a SqlDataReader to call a proc that reads data. My call to
raiseerror has no effect. Below is the code. if i comment out the select
statement, my code behaves as expected.

CREATE PROCEDURE [dbo].[usp_Table1Read]
@id int
AS

--select * from table1 where IId = @Id

if (@@rowcount = 0)
begin
raiserror('an error occured', 10, 1)
return 50000
end

return 0
GO

using System;

using System.Data;

using System.Data.SqlClient;

namespace ReaderTest

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

Class1 c = new Class1();

c.DoWork();

}

public void DoWork()

{

try

{

//connection

SqlConnection connection = new SqlConnection();

connection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=MyDataBase;server=(local)";

connection.Open();

connection.InfoMessage += new
SqlInfoMessageEventHandler(this.MySqlInfoHandler);

//command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = "usp_Table1Read";

command.Connection = connection;

//create return parameter

SqlParameter p = new SqlParameter();

p.ParameterName = "@RetVal";

p.Direction = ParameterDirection.ReturnValue;

p.SqlDbType = SqlDbType.Int;

command.Parameters.Add(p);

p = new SqlParameter();

p.ParameterName = "@Id";

p.Direction = ParameterDirection.Input;

p.SqlDbType = SqlDbType.Int;

p.Value = "3";

command.Parameters.Add(p);

//get reader

SqlDataReader reader = command.ExecuteReader();

//look at return parameter (1)

Console.Write("Check 1: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

while(reader.Read()){}

reader.Close();

connection.Close();

//look at return parameter (2)

Console.Write("Check 2: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

}

catch(Exception ex)

{

Console.WriteLine(ex.ToString());

}

}

public void MySqlInfoHandler(object sender, SqlInfoMessageEventArgs e)

{

Console.WriteLine("************************");

Console.WriteLine(e.Message);

Console.WriteLine("************************");

}

}

}
Click to expand...


.
Click to expand...
Click to expand...
 
R

Robert Phillips

works as expected in query analyzer...i get the raiserror message in the
output.

doesn't seem to matter whether or not rows are returned. i removed the "if"
statement so that raiserror is always called, it never had an effect in my
code.

William (Bill) Vaughn said:
I assume there are no qualifying rows? Did you try this in QA?

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Robert Phillips said:
thanks....with 10, i expected the InfoMessage handler to
be called, and with 11 an exception to be thrown. neither
works with select statement uncommented (in my sample).
-----Original Message-----
Check BOL. The Sev has to be 11 IIRC to trigger an exception.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I'm using a SqlDataReader to call a proc that reads data. My call to
raiseerror has no effect. Below is the code. if i comment out the select
statement, my code behaves as expected.

CREATE PROCEDURE [dbo].[usp_Table1Read]
@id int
AS

--select * from table1 where IId = @Id

if (@@rowcount = 0)
begin
raiserror('an error occured', 10, 1)
return 50000
end

return 0
GO

using System;

using System.Data;

using System.Data.SqlClient;

namespace ReaderTest

{

/// <summary>

/// Summary description for Class1.

/// </summary>

class Class1

{

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

Class1 c = new Class1();

c.DoWork();

}

public void DoWork()

{

try

{

//connection

SqlConnection connection = new SqlConnection();

connection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=MyDataBase;server=(local)";

connection.Open();

connection.InfoMessage += new
SqlInfoMessageEventHandler(this.MySqlInfoHandler);

//command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = "usp_Table1Read";

command.Connection = connection;

//create return parameter

SqlParameter p = new SqlParameter();

p.ParameterName = "@RetVal";

p.Direction = ParameterDirection.ReturnValue;

p.SqlDbType = SqlDbType.Int;

command.Parameters.Add(p);

p = new SqlParameter();

p.ParameterName = "@Id";

p.Direction = ParameterDirection.Input;

p.SqlDbType = SqlDbType.Int;

p.Value = "3";

command.Parameters.Add(p);

//get reader

SqlDataReader reader = command.ExecuteReader();

//look at return parameter (1)

Console.Write("Check 1: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

while(reader.Read()){}

reader.Close();

connection.Close();

//look at return parameter (2)

Console.Write("Check 2: ");

Console.WriteLine(command.Parameters["@RetVal"].Value);

}

catch(Exception ex)

{

Console.WriteLine(ex.ToString());

}

}

public void MySqlInfoHandler(object sender, SqlInfoMessageEventArgs e)

{

Console.WriteLine("************************");

Console.WriteLine(e.Message);

Console.WriteLine("************************");

}

}

}




.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Bug in SqlCommand? 2
Looking for Comments on Code 5
Passing a DataReader between methods and getting RETURN_VALUE 6
Could you... 2
How do you get output parameters? 4
Output parameter when using storedProcedure 14
Data Access Application Block - Using UPDATEDATASET and Returning an Identity? 0
Datareader to Datatable gets stuck... 3

Top