Filling SQLDataReader with stored procedure recordset

Hi All,

I'm hoping somebody can help me with this as it is driving me mad. I've created a stored procedure which returns Employee information recordset when the windows username is passed to it as a parameter. I want to then store this information in Session variables so content can be filtered depending on employee status, but when I execute my code no records are returned. I know for a fact that the stored procedure works because I bound it sqldatasource and displayed results from it in a Datalist and tested it in sql server. My code is as follows can anybody see any problems with it, in runs through fine with but when I try a read a field from the datareader in says there is no data to read.

Dim CurrentUserAs String, Pos1As Int16, EmployeeIdAs Int32Dim cnAs New System.Data.SqlClient.SqlConnectionDim paramAs New System.Data.SqlClient.SqlParameterDim readerAs System.Data.SqlClient.SqlDataReaderDim cmdAs New System.Data.SqlClient.SqlCommand CurrentUser =CStr(User.Identity.Name) Pos1 = InStr(CurrentUser,"\") + 1 CurrentUser = Mid(CurrentUser, Pos1) Session("User") = CurrentUser Session("CID") =Nothing cn.ConnectionString ="Data Source=LAPTOP-4\SQLEXPRESS;Initial Catalog=SCMdb;Integrated Security=True" cn.Open() cmd.Connection = cn cmd.CommandText ="CurrentUser" cmd.CommandType = CommandType.StoredProcedure param = cmd.CreateParameter param.ParameterName ="@.UserName" param.SqlDbType = SqlDbType.VarChar param.Value = CurrentUser cmd.Parameters.Add(param) reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) EmployeeId = reader.Item("EmployeeID") reader.Close()

Any help would be much appricated this is driving me mad.

Thank You

Shaft

To read items from reader,

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection):

while (reader.Read())
{
int _employeeId = (int) reader["EmployeeID"];
}

Thanks

|||

Hi there,

If you are just reading a single value of the stored procedure I will advice you to use OUTPUT parameters to achieve this.Datareader will also do the job, but you leave yourself more vulnerable with connection not closing properly and also with overhead of creating a reader object.Just a thought.. what you are doing is also correct and will work

|||

e_screw:

To read items from reader,

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection):

while (reader.Read())
{
int _employeeId = (int) reader["EmployeeID"];
}

Thanks

I've tried that all ready but it still says there is no data.

Any other ideas anyone?

|||

The actual error is "Invalid attempt to read when no data is present." so I'm thinking the Command Object hasn't pulled any data which leads me think there is a problem with the way I've declared the parameter because it is running the stored procedure just not retrieving any data.

|||

Sorry may mistake it did work I'd just forgot to comment out the statment that was wrong.

Here's the code that e-screw posted but converted to vb .net as I needed it

Do While reader.Read EmployeeId = reader("EmployeeID")Loop

Cheers E-Screw