Question While reader.Read() not working

[sansalk]

I am using this code for read some parameters from SQL DB but this Readers while loop is not working its allways cose false but out put parameters are retun the valuses. please help me

    Dim SqlCommand As SqlCommand = New SqlCommand
        
 objDbcnn.GetConnection()
        
        SqlCommand.CommandType = CommandType.StoredProcedure
        SqlCommand.CommandText = "spGetRegPickersDtl"
        SqlCommand.Connection = objDbcnn.Connection
 
 SqlCommand.Parameters.Add(New SqlParameter("@iPickersID", SqlDbType.Int, 0, "iPickersID"))
 sqlCommand.Parameters.Add(New SqlParameter("@szFname", SqlDbType.VarChar, 50, ParameterDirection.Output, False, 0, 50, "szFname", DataRowVersion.[Default], Nothing))
 SqlCommand.Parameters.Add(New SqlParameter("@szLname", SqlDbType.VarChar, 50, ParameterDirection.Output, False, 0, 50, "szLname", DataRowVersion.[Default], Nothing)) 
 SqlCommand.Parameters.Add(New SqlParameter("@szNote", SqlDbType.VarChar, 50, ParameterDirection.Output, False, 0, 50, "szNote", DataRowVersion.[Default], Nothing))  
 SqlCommand.Parameters("@iPickersID").Value = intPickersID
       

        Dim reader As SqlDataReader
        reader = SqlCommand.ExecuteReader()
        While reader.Read()
             propFname = SqlCommand.Parameters("@szFname").Value
             propLname = SqlCommand.Parameters("@szLname").Value
             propNote = SqlCommand.Parameters("@szNote").Value
        End While
        reader.Close()

 

[Menthos]

You should be reading the results from your reader object, not the sqlCommand object.

Check the example on the MSDN documentation for SqlDataReader

 

[sansalk]

Thanks menthos

But the problem is when i use While loop Executing line(F11) not go in to the loop. but when i remove the While loop results its giveing
proper output but not handling DBnulls. I cant understand the reason for that. i am using VS 2010.
My Sp is
ALTEPROCEDURE [dbo].[spGetRegPickersDtl] (@iPickersID int, @szFname varchar(50) output,@szLname varchar(50) output,@szNote varchar(300)output)
AS
BEGIN
SET NOCOUNT ON;
SELECT @szFname=szFname,@szLname=szLname,@szNote= szNote
FROM tblPickers
WHERE iPickersID=@iPickersID
END

 

[sansalk]

Any one can help me..........................

 

[Menthos]

Basically your reader is only pulling back one recordset, so your loop condition is true at the start and the code with your loop won't run. Removing the loop isn't the cause of your code breaking with unhandled dbNull's - if your loop were to actually run, it would break all the same.

If you know your query will only ever return a single recordset, then a loop isn't the route to take. Personally I use datatables and sqlDataAdapters.

With regards to not handling dbNulls - either use ISNULL within your stored procedure, or specifically handle them with your code.

 

[jmcilhinney]

You should not be using a data reader at all because there's no result set to read. Your SELECT statement is not creating a result set; it's populating output parameters. You should be calling ExecuteNonQuery, not ExecuteReader, and getting rid of that loop altogether.

 

[sansalk]

Thanks menthos and jmcihinney