asp.net c# ExecuteReader() error

Question

The following code produces an error. dbo.getit works fine when I call it directly on the server. The error occurs at cmd.ExecuteReader() . What am I doing wrong?

    string user;
    string pw;
    SqlDataReader dr = null;
    SqlConnection conn = new SqlConnection("Data Source=xxx;Initial Catalog=myDB;    Integrated Security=True");

    user = Username.Text.Trim();
    pw = Password.Text.Trim();

    conn.Open();

   try {
        SqlCommand cmd = new SqlCommand("dbo.getit", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@param1", user);
        cmd.Parameters.AddWithValue("@param2", pw);

        dr = cmd.ExecuteReader();

        while ( dr.Read() )
        {
            Session["username"] = user;
            // Session["admin"] = 
            // Session["completed"] =
            Server.Transfer("all_is_well.aspx");

        }
        conn.Close();
        conn.Dispose();

    } catch (Exception ex) {
        if (dr != null)
        {
            dr.Close();
            conn.Close();
        }
        Server.Transfer("ERROR.aspx");
    }

SOLUTION: Replace the two corresponding lines above with these:

SqlCommand cmd = new SqlCommand("select * from dbo.getit(@param1, @param2);", conn);
cmd.CommandType = CommandType.text;

The error is due to a bug. Be more specific about the error, and we'll be in a better position to be more specific about the bug... aka, please read http://tinyurl.com/so-hints and edit your question. — Jon Skeet, Sep 29 '11 at 16:59
usually the Exception ex has a Message and a StackTrace.. please put a breakpoint, read and post this informations to have the possibility to find the reason of the error. — Emanuele Greco, Sep 29 '11 at 17:01
I'm unable to use a break point in the environment I'm using. I'll try to print the error to the web page somehow. — elbillaf, Sep 29 '11 at 17:06
before Server.Transfer save the exception in Session, then in Error.aspx read the session and propmt the error.. — Emanuele Greco, Sep 29 '11 at 17:12
ahhh, Emanuele, tx. seems obvious once you tell me. I see it can't find the stored procedure. That's odd, since I used the same sql in a VB version of this program I wrote a few weeks back. tx, i think I can figure it out from here. — elbillaf, Sep 29 '11 at 17:24
I switched the sql from "select * from dbo.getit" to just "getit" and I get "The request for procedure 'getit' failed because 'getit' is a table valued function object." I don't understand, because I thought the whole purpose of the reader was to get the result of a table valued function. — elbillaf, Sep 29 '11 at 17:28
Code is working now. Thanks for help everyone. All comments were useful. Especially thanks for the technique, Emanuele ... my devel env is extremely limited and I have no control. My solution was sql = "select * from dbo.findUserPW(@param1, @param2);" and cmd.CommandType = CommandType.Text; I don't know why my previous stuff failed, but this works, so I'm going on to next thing. Thanks a bunch. Don't know which solution to check below ... neither was the answer, but they were both helpful...really,tx, tff. — elbillaf, Sep 29 '11 at 18:43

score 2 · Answer 1 · answered Sep 29 '11 at 17:02

2

This just seems questionable,

Session["username"] = user; 
Server.Transfer("all_is_well.aspx");

inside the while loop!

Can you at least finish iterating on the reader, using a temporary object to store the result of the query, and then initialize you session and do the Server.Transfer. .

answered Sep 29 '11 at 17:02

Anas Karkoukli

1,342
8
13

1

Isn't Server.Transfer known to throw a `ThreadAbortException` – Conrad Frix Sep 29 '11 at 17:04
Agree inside the while loop, worse case OP could set a bool indicating userFound = true. Then after the reader finishes, if (userFound) do the server transfer, doing the server transfer inside a loop is pretty ponderous. – Ta01 Sep 29 '11 at 17:12
i don't think is this the problem: a reader is executed, but the code written there is not executed.. it was just useless sample code to test the program. – Emanuele Greco Sep 29 '11 at 17:14
Avoid Server.Transfer because of the ThreadAbortException which can cause all kinds of unexpected [behavior](http://www.west-wind.com/weblog/posts/2010/Jan/20/HttpContextItems-and-ServerTransferExecute). Also [here](http://blogs.msdn.com/b/ericlippert/archive/2009/03/06/locks-and-exceptions-do-not-mix.aspx) – Maciej Sep 29 '11 at 19:34

score 1 · Answer 2 · answered Sep 29 '11 at 17:18

Server.Transfer terminates execution of the current page and starts execution of a new page for the current request. Also, Transfer calls End, which throws a ThreadAbortException exception upon completion.

I think what you are trying to do (and I am answering based on what you are trying to do - not necessarily best pratice) is verify that the user is authorized/authenticated in some way based on a data store. You'd be better off not using ExecuteReader at all. Use ExecuteScalar. If the result of ExecuteScalar is not null, the user was found.

if (cmd.ExecuteScalar() != null)
{
   Server.Transfer("all_is_well.aspx");
}

else
{
   Server.Transfer("someErrorPage.aspx");
}

I think that's the right way to do this, given my starting point; however, that's not my error. For some reason it gives an error on the excecutescalar ... I'm experimenting with different sql right now. Part of the problem is I'm switching from using a dataadapter which worked perfectly (at least in vb) to using a datareader. sql = "dbo.getit"; sql = "dbo.getit(@param1, @param2);"; sql = "select * from dbo.getit(@param1, @param2);"; etc. — elbillaf, Sep 29 '11 at 18:22

score 0 · Accepted Answer · answered Nov 18 '11 at 15:18

0

SOLUTION: Replace the two corresponding lines above with these:

SqlCommand cmd = new SqlCommand("select * from dbo.getit(@param1, @param2);", conn);
cmd.CommandType = CommandType.text;

That worked.

answered Nov 18 '11 at 15:18

elbillaf

1,952
10
37
73

asp.net c# ExecuteReader() error

3 Answers3