command.ExecuteReader causing SqlException

Question

command.ExecuteReader causing SqlException

versmn23

SSC-Addicted

Points: 460
More actions
February 13, 2010 at 4:15 am

#215716

I keep getting a SQLException when the command.ExecuteReader below tries to execute, but I'm not sure why and apparently the way I have the exception setup its not providing much helpful information.
public DataTable GetPublisherByName(string name)
{
SqlConnection connection = new SqlConnection(connectionString);
DataTable rowTable = new DataTable();
try
{
//ArrayList list = new ArrayList();
string commandText =
@"SELECT pub_id, pub_name FROM dbo.publishers WHERE pub_name = @name;";
SqlParameter param = new SqlParameter();
param.ParameterName = "@name";
param.Value = name;
using (SqlCommand command = new SqlCommand(commandText, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DataRow row = rowTable.NewRow();
row["pub_id"] = reader["pub_id"];
row["pub_name"] = reader["pub_name"];
row["city"] = reader["city"];
row["state"] = reader["state"];
row["country"] = reader["country"];
rowTable.Rows.Add(row);
}
return rowTable;
}
}
}
catch (Exception ex)
{
throw new Exception("UnexpectedSql Exception Encountered" + "" +ex.Message
+ "" + ex.StackTrace);
}
finally
{
connection.Close();
}
}
I would appreciate any help or suggestions for resolving this isssue. Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

What error message are you getting in your error handler?

You need to step thru you code and try and capture some additonal information, for correct me if this is wrong but this is s forum on SQL Server.

But regardless there are some very basic steps that you can take to debug your code.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Martin Wills Ten Centuries Points: 1288 More actions · Answer 2

At the moment, your SqlCommand object doesn't know about your SqlParameter object.

You need a line

command.Parameters.add(param);

between your 2 using statements.

But, 'Welsh Corgi' is right to say that you should have at least told us the contents of ex.Message.

Martin

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

My apologies, I should have looked it up but I was a little short on time.

In Mucach's Books on VB.NET & C# have some great examples.

But of could you can get the information for free on the net.

Thanks for your input. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

versmn23 SSC-Addicted Points: 460 More actions · Answer 4

Thank you for the note about the command.Parameters.Add statement. That took care of this problem, so now I have to figure out the answer to the next problem. Thanks again for the help.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

What is the next problem?

Could you send the Solution and the scripts to create and populate the table(s)?

Are you stepping thru the code, using the immediate window and watch expresions, etc?

What error message are you getting?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/