Invalid attempt to read when no data is present
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: Invalid attempt to read when no data is present

  1. #1
    Join Date
    Jul 2008
    Posts
    61

    Invalid attempt to read when no data is present

    hi there, I'm writing an windows application that receive money from customer, it has three value in database,
    firstPayment : this is the first field that customer give to program as first payment
    secondPayment : this is the second field that cumstomer give to program as second payment .
    thirdPayment : this is the last payment that customer give to application ...
    Ok, now I want to show values that stored in these fields in seperate text boxes ..., textbox1 : for presenting the value of firstPayment, textbox2 : presenting the value of secondPayment and so on
    I've been used SqlDataReader class for this kind of thing, when I started filling the firstPayment and secondPayment by clicking on the button I've been recieved the answer, textbox1 has been shown firstPayment value, and secondPayment has been shown in the textbox2 , but when the thirdPayment fills things changed exotic , now when thirdPayment has value, whenever I press Button it must show three value into three textboxes, but an error has occured,
    " and attempt to read when no data is present "
    I don't know what's the matter, I checked different kind of codes but I got no answer,
    here's my code,

    Code:
    private void button6_Click(object sender, EventArgs e)
            {
                using (SqlConnection con = new SqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ClinicDB.mdf;Integrated Security=True;User Instance=True";
                        cmd.CommandText = "SELECT firstPayment,secondPayment,thirdPayment FROM financialT where name = @name and family = @family";
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@name", txtName.Text);
                        cmd.Parameters.AddWithValue("@family", txtFamily.Text);
                        con.Open();
                        using (SqlDataReader sdr = cmd.ExecuteReader())
                        {
                            if (sdr != null)
                            {
                                sdr.Read();
                                textBox1.Text = sdr.GetValue(0).ToString(); /* == DBNull.Value ? "" : sdr.GetValue(0).ToString();*/
                                textBox2.Text = sdr.GetValue(1).ToString();/* == DBNull.Value ? "" : sdr.GetValue(1).ToString() ;*/
                                textBox4.Text = sdr.GetValue(2).ToString();/* == DBNull.Value ? "" : sdr.GetValue(2).ToString(); */
                            }
                        }
                    }
                }
            }
    can anyone help me for solving this problem !?

    thanks alot ...

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,202

    Re: Invalid attempt to read when no data is present

    I don't know your exact problem because you need to post more relavent code. However, I have several suggestions:

    1) Pull the database access code out of the UI (and out of the button handler code). This will allow you to test the data access code separate from the UI. It will also allow you to reuse the db code if you create a different UI.
    2) Write yourself a data access layer (DAL) that allows you to access the column data without always checking against DbNull.Value. Actually, your code still checks for null within your data access layer, but frees the calling code from having to do so.
    3) I recommend using stored procedures rather than having inline T-SQL in the .net code level. This extra layer of abstraction allows you to modify the database operation (query, insert, etc.) without having to recompile your .Net code. Btw, if you aren't familar with stored procedures, don't sweat it. They are easy to write and easy to call from .Net.

    Here's an example of what I mentioned above.

    Code:
    public
    Code:
    Employee[ ] GetEmployees( Guid companyID, bool includeInactive )
    {
      List<Employee> list = newList<Employee>( );
     
      using( DAL dal = DAL.Create( Constants.DB.Connections.MyDatabaseName ) )
      {
        dal.Open( );
     
        SqlParamCollection sqlParams = SqlParamCollection.Create( );
        sqlParams.Add( Constants.DB.Params.CompanyID, companyID );
        sqlParams.Add( Constants.DB.Params.IncludeInactive, includeInactive );
     
        using( SafeDataReader dr = SafeDataReader.Create( dal.ExecuteReader( Constants.DB.Sprocs.Employee.GetList, sqlParams ) ) )
        {
          while( dr.Read( ) )
          {
            list.Add( EmployeeCreator.Create( dr ) );
          }
        }
      }
     
      return list.ToArray( );
    }


    Here some explaination of the code above:
    1) The Constants.DB is a partial class that contains embedded classes which contains constant string params. For example, let's look at the "Constants.DB.Sprocs.Employee.GetList" constant.

    Code:
    publicclassConstants
    {
      internalstaticclassDB
      {
        publicstaticclassSprocs
        {
          publicstaticclassEmployee
          {
            publicconststring Delete = "[dbo].[Employee.Delete]";
            publicconststring Get = "[dbo].[Employee.Get]";
            publicconststring GetList = "[dbo].[Employee.GetList]";
            publicconststring Update = "[dbo].[Employee.CU]";
          }
        }
     
        publicstaticclassConnections
        {
          publicstaticstring MyDatabaseName
          {
            get { returnConfigurationManager.ConnectionStrings[ "MyDatabaseName" ].ConnectionString; }
          }
        }
      }
    }


    You may notice the EmployeeCreator class in the "list.Add( EmployeeCreator.Create( dr ) );" statement.

    This class just creates an instance of the Employee class and uses the data reader to populate it. Sure, you can do this all inline, but it just makes it cleaner to do it this way (IMO).

    The EmployeeCreator class looks like:

    Code:
    internalclassEmployeeCreator
    {
      publicstaticEmployee Create( SafeDataReader dr )
      {
        Employee employee = newEmployee( );
     
        employee.CompanyID = dr.GetGuid( Constants.DB.Params.CompanyID );
        employee.DateBirth = dr.GetSmartDate( Constants.DB.Params.DateBirth ).Date;
        employee.EmployeeID = dr.GetGuid( Constants.DB.Params.EmployeeID );
        employee.ExtEmployeeID = dr.GetInt32( Constants.DB.Params.ExtEmployeeID );
        employee.GenderTypeID = dr.GetInt32( Constants.DB.Params.GenderTypeID );
        employee.StatusTypeID = dr.GetInt32( Constants.DB.Params.StatusTypeID );
        employee.NameFirst = dr.GetString( Constants.DB.Params.NameFirst );
        employee.NameMiddle = dr.GetString( Constants.DB.Params.NameMiddle );
        employee.NameLast = dr.GetString( Constants.DB.Params.NameLast );
     
        return employee;
      }
    }


    You'll notice that I call the various GetString, GetInt32, and other accessor methods without checking for null. This is because the SafeDataReader class wraps this functionality so the calling code (i.e. this code) doesn't need to.

    Lastly, I believe there might be an issue with the following code.

    Code:
    sdr.GetValue(0).ToString();


    I'm not sure if you can convert to a string when GetValue is null. This may be a problem in your code. I'm not sure because I wrote my SafeDataReader class several years ago and now don't have to remember stuff like this.

  3. #3
    Join Date
    Feb 2009
    Location
    Atlanta, GA
    Posts
    17

    Re: Invalid attempt to read when no data is present

    Try using this approach:
    Code:
        private void button2_Click(object sender, EventArgs e)
        {
          string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");
          const string query = @"Select Top 500 * From Customer (NOLOCK) Where Name Like '%[a-Z]%,%[a-Z]%' and Name Not Like ',%' and Len(Name) >= 15 Order By Name";
          using (SqlConnection conn = new SqlConnection(connStr))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
    
              using (SqlDataReader dr = cmd.ExecuteReader())
              {
                while (dr.Read())
                {
                  //Convert.ToString() will convert DBNull.Value to string.Empty so you dont have to check for it
                  string text = Convert.ToString(dr["Name"]);
                } //while read
              } //execute reader
            } //cmd
          } //conn
        }
    Scott Knake
    Custom Software Development
    Apex Software, Inc.

  4. #4
    Join Date
    Jul 2006
    Posts
    297

    Re: Invalid attempt to read when no data is present

    Quote Originally Posted by escap3 View Post
    Code:
     
    textBox1.Text = sdr.GetValue(0).ToString();
    textBox2.Text = sdr.GetValue(1).ToString();
    textBox4.Text = sdr.GetValue(2).ToString();
    Maybe this isn't the problem but you mentioned textBox3 not textBox4 is this a typo or is it actually named textBox4?

  5. #5
    Join Date
    Jul 2008
    Posts
    61

    Re: Invalid attempt to read when no data is present

    the problem solved ..., I gave a description about it in MSDN forums, you can find it ...
    thanks from your helpful answers. ...

  6. #6
    Join Date
    May 2009
    Location
    Bengaluru, India
    Posts
    422

    Re: Invalid attempt to read when no data is present

    can you put the exact solution here which really solved your problem ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center