none
problems using the DataReader RRS feed

  • Question

  • I am using the following code :

    sqlConn = new SqlConnection(ConnStr);
                    sqlConn.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = "spGetUser";
                    sqlComm.CommandTimeout = 30;
    
                    SqlParameter sqlPar = new SqlParameter("UserName", SqlDbType.NVarChar, 20);
                    sqlPar.Value = username;
                    sqlComm.Parameters.Add(sqlPar);
    
                    sqlPar = new SqlParameter("Email", SqlDbType.NVarChar, 50);
                    sqlPar.Value = email;
                    sqlComm.Parameters.Add(sqlPar);
                   
                    sqlComm.Connection = sqlConn;
                    SqlDataReader sqlDR = sqlComm.ExecuteReader();
                    
                    if (!sqlDR.HasRows)
                        throw (new Exception("no such user"));
                    //if (sqlDR.RecordsAffected > 1)
                    //    throw (new Exception("More than one user with such UserName"));
    
                    while (sqlDR.Read())
                    //if (sqlDR.Read())
                    {
                        if (count > 1)
                            throw (new Exception("More than one user with such UserName"));
    
                       
                        //UserName = (string)sqlDR["UserName"]; 
                        UserName = sqlDR["UserName"].ToString();
                        EncryptedPassword = sqlDR["Password"].ToString();
                        Email = sqlDR["Email"].ToString();
                        SecretQuestion = sqlDR["Question"].ToString();
                        SecretAnswer = sqlDR["Answer"].ToString();
    ........

    As soon as the execution gets to :

    UserName = sqlDR["UserName"].ToString();

    I get the following exception :

    UserName at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name) ......

    The stored procedure I use to get the datareader, "spGetUser", is working perfectly and returns the right values, none of which is NULL.

    What is the cause of this strange exception ?

    Monday, September 14, 2015 11:19 AM

Answers

  • I think I found out the answer.

    There was another select in the sp, made long before the one I was seeing, and in the results taken by the Datareader there were the records coming from it , that's why probably I couldn't find the fields.

    I still don't get why the sp works correctly if launched on itself-

    • Marked as answer by leo68 Tuesday, September 15, 2015 3:09 PM
    Tuesday, September 15, 2015 3:09 PM

All replies

  • ask at https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    this forum is for the core libraries like System.IO and System.Globalization.



    Visual C++ MVP

    Monday, September 14, 2015 1:56 PM
  • Hi,

    Please try and replace code as below,

    UserName = sqlDR.GetString(sqlDR.GetOrdinal("UserName"));
    EncryptedPassword  = sqlDR.GetString(sqlDR.GetOrdinal("Password"));
    Email = sqlDR.GetString(sqlDR.GetOrdinal("Email"));
    SecretQuestion = sqlDR.GetString(sqlDR.GetOrdinal("Question"));
    SecretAnswer = sqlDR.GetString(sqlDR.GetOrdinal("Answer"));

    Thank

    Monday, September 14, 2015 2:57 PM
  • Tried as you suggested Stef, 

    same error as before

    Monday, September 14, 2015 3:24 PM
  • ok thanks
    Monday, September 14, 2015 3:24 PM
  • Hi,

    Please check your column name.

    Did the column name which return from the SP are same inside the coding?

    Monday, September 14, 2015 3:41 PM
  • >>What is the cause of this strange exception ?

    Probably that the SP doesn't return any column with the name "UserName". This will only work if any of the column names in the result set returned from the SP is "UserName":

    UserName = sqlDR["UserName"].ToString();

    If the stored procedure always returns five non-NULL values, you could access them by their index:

    UserName = sqlDR[0].ToString();
    EncryptedPassword = sqlDR[1].ToString();
    Email = sqlDR[2].ToString();
    SecretQuestion = sqlDR[3].ToString();
    SecretAnswer = sqlDR[4].ToString();

    Othwerwise you need to know and use the exakt names of the returned columns.

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Monday, September 14, 2015 4:24 PM
  • Yes they match perfectly.
    Tuesday, September 15, 2015 6:20 AM
  • As I told, the sp works perfectly and returns exactly the fields I try to access with the DataReader.

    After all, if I eliminate the first field ("UserName") from the Datareader query, the problem shifts to the second field, and so on ..... so I don't think it's a matter of field names or fields returned.

    Tuesday, September 15, 2015 6:22 AM
  • Hi

    May i kow your sp got like with prefix as sample below ?

    Select tbl1.UserName, tbl1.Password, tbl2.Email, ... FROM tblUser tbl1, inner join tblUserInfo tbl2

    If yes, please try to change the query as below
    Select tbl1.UserName AS UserName, tbl1.Password As Password, tbl2.Email As Email, ...
    FROM tblUser tbl1,
    inner join tblUserInfo tbl2

    Thanks

    Tuesday, September 15, 2015 7:16 AM
  • No it doesn't use any prefix .....

    It's like :

    SELECT Isnull (IdUser, '')  as UserName,

    IsNull (Email,''),
    IsNull (Password,''),

    ....

    From Users 

    .....

    Tuesday, September 15, 2015 7:38 AM
  • Hi,

    Please try to assign the column name for the those ISNULL function as below

    SELECT
    Isnull (IdUser, '')  as UserName,
    IsNull (Email,'')  as Email,
    IsNull (Password,'') as Password,
    
    ....
    

    Thanks

    Tuesday, September 15, 2015 1:33 PM
  • The following code...

          string ConnStr = "...your connection string...";
          string UserName = "";
          SqlConnection sqlConn = new SqlConnection(ConnStr);
          sqlConn.Open();
          SqlCommand sqlComm = new SqlCommand();
          sqlComm.CommandType = CommandType.StoredProcedure;
          //sqlComm.CommandText = "spGetUser";
          sqlComm.CommandText = "my_test_sp";
          sqlComm.CommandTimeout = 30;
    
    
          sqlComm.Connection = sqlConn;
          SqlDataReader sqlDR = sqlComm.ExecuteReader();
    
          if (!sqlDR.HasRows)
            throw (new Exception("no such user"));
    
          while (sqlDR.Read()) {
            UserName = sqlDR["UserName"].ToString();
          }
          sqlConn.Close();
    

    ...works as expected for the following stored procedure (note the 'AS UserName') so appearantly your stored procedure doesn't return what you expect.

    CREATE PROCEDURE my_test_sp AS
    
    DECLARE @IdUser VARCHAR(5)
    SELECT
    ISNULL(@IdUser, '12345')  as UserName
    


    Try to select a single column in the SP to begin with, put a breakpoint in your code, evaluate exactly what the sqlDR variable contains at runtime and then go from there.


    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Tuesday, September 15, 2015 1:46 PM
  • Hi,

    Please try to assign the column name for the those ISNULL function as below

    SELECT
    Isnull (IdUser, '')  as UserName,
    IsNull (Email,'')  as Email,
    IsNull (Password,'') as Password,
    
    ....

    Thanks

    I don't get you : being the problem already in the first field returned , what's the difference if I change the way  to return the following fields ?

    Tuesday, September 15, 2015 1:55 PM
  • There is no way to debug .

    The debugger doesn't work, it has plenty of flaws that make it impossible  to use it to test the application.

    I even exposed the problem on this forum, without getting any response.

    Anyway , all I can say  is that the sp does return the correct fileds and the correct values stored in the table.

    Tuesday, September 15, 2015 2:01 PM
  • Hi,

    if you use Isnull(Email,'') and not asign the column name "Email" there will return "(No column Name) for the column header for in the result.

    Please refer the link below to check the column name in the sqldatareader.

    http://stackoverflow.com/questions/373230/check-for-column-name-in-a-sqldatareader-object

    Wish the link above can help found out what the column header name.

    thanks.

    Tuesday, September 15, 2015 2:30 PM
  • I think I found out the answer.

    There was another select in the sp, made long before the one I was seeing, and in the results taken by the Datareader there were the records coming from it , that's why probably I couldn't find the fields.

    I still don't get why the sp works correctly if launched on itself-

    • Marked as answer by leo68 Tuesday, September 15, 2015 3:09 PM
    Tuesday, September 15, 2015 3:09 PM