Invalid attempt to read when no data is present

[escap3]

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,



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 ...

[Arjay]

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.

public Employee[ ] GetEmployees( Guid companyID, bool includeInactive )
{
List<Employee> list = new List<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.


public class Constants
{
internal static class DB
{
public static class Sprocs
{
public static class Employee
{
public const string Delete = "[dbo].[Employee.Delete]";
public const string Get = "[dbo].[Employee.Get]";
public const string GetList = "[dbo].[Employee.GetList]";
public const string Update = "[dbo].[Employee.CU]";
}
}

public static class Connections
{
public static string MyDatabaseName
{
get { return ConfigurationManager.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:


internal class EmployeeCreator
{
public static Employee Create( SafeDataReader dr )
{
Employee employee = new Employee( );

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.

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.

[sknake]

Try using this approach:

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
}

[monalin]

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?

[escap3]

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

[vcdebugger]

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