System.Data.SqlClient.SqlDataReader

wk · September 23rd, 2006, 08:02 AM

Dear all,

I'm using sqldatareader to return data that i need and then bind it to the gridview. Anyway, I'm facing the error message " 'System.Data.SqlClient.SqlDataReader' has no constructors defined".

Please help, thanks.

Levine

RobC · September 23rd, 2006, 04:30 PM

My understanding of the DataReader object is that you don't create a new instance of it, like this:

Code:

SqlDataReader myReader = new SqlDataReader();

This will give you the error, "'System.Data.SqlClient.SqlDataReader' has no constructors defined".

Instead, you assign an instance of the DataReader object to a SqlCommand command:

Code:

string strConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;

SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = strConnectionString;

string strCommandText = "SELECT FirstName, LastName FROM Users;

SqlCommand myCommand = new SqlCommand(strCommandText, myConnection);

myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(); // <-- *** Here is the instantiation
while (myReader.Read())
{
    // process the rows in myReader using
    // Convert.ToString(myReader["FirstName"]) and
    // Convert.ToString(myReader["LastName"])
}
myReader.Close();

wk · September 26th, 2006, 12:50 AM

Hi RobC,

Thanks for that. But may I know how can I return the result set and bind it to gridview? Below is my code.

public method in Employee class file

public SqlDataReader A(String EmpID)
{
SqlConnection con = new SqlConnection("myconnection");
con.Open();
SqlCommand cmd = new SqlCommand("", con);

cmd.CommandText = "SELECT Emp_Name, Emp_Add from " + EmpID + " WHERE UPPER(Emp_Name) IN (SELECT UPPER(Emp_Name) as Name FROM " + EmpID + " GROUP BY Emp_Name HAVING (COUNT(UPPER(Emp_Name)) > 1)) ORDER BY Emp_Name";

SqlDataReader dtr = cmd.ExecuteReader();
return dtr;
}

The code that I have in asp.cs is as below:

Employee myEmp = new Employee();
grv.DataSource = A ;
grv.DataBind();

May I know normally is the sqldatareader able to read a sqlcommand that have input parameter and return the result set and bind it to gridview? or is there a better way to handle this kind of situation instead of using sqldatareader?

thanks in advance

Levine

RobC · September 26th, 2006, 08:53 AM

This line doesn't look right:

Code:

grv.DataSource = A ;

I think it should be something like:

Code:

grv.DataSource = myEmp.A(<someString>);

wk · September 26th, 2006, 10:04 AM

Thanks again RobC :)

I managed to convert the data get from sqldatareader to dataset and bind it to gridview , but I'm facing problem in getting the 'paging' in my gridview. Does anyone have any ideas? Thanks in advance.

Levine

RobC · September 26th, 2006, 10:10 AM

Just set the AllowPaging property of the DataGrid to true. It's in the Properties panel.

wk · September 26th, 2006, 10:41 AM

Hi there,

Below is the error message I got when I tried to click on page 2 :
The GridView 'GridView1' fired event PageIndexChanging which wasn't handled. Any ideas?

Levine

dparsons · September 26th, 2006, 01:29 PM

it sounds like you do not have a method that handles your PageIndexChanging event.

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

wk · September 27th, 2006, 05:00 AM

Problem solved :)