Display stored procedure result in ASP.Net gridview

Question

I've been struggling for a while now with displaying some SQL Server stored procedure result in a gridview. I get the following error :

A field or property with the name 'Hora' was not found on the selected data source

I've checked thoroughly for any typos or misspelling. There is one thing though: the stored procedure displays a table which source is another temp table. And I think the problem is somehow related to that.

Here is my gridview aspx code:

<asp:GridView ID="gvPosicion" runat="server" AutoGenerateColumns="False" 
        Width="915px" CellPadding="4" ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <Columns>
            <asp:BoundField DataField="Hora" HeaderText="Hora" SortExpression="Hora" 
                ReadOnly="True" />
            <asp:BoundField DataField="Direccion" HeaderText="Direccion" 
                SortExpression="Direccion" />
            <asp:BoundField DataField="Posicion" HeaderText="Posicion" 
                SortExpression="Posicion" />
        </Columns>
        <EditRowStyle BackColor="#999999" />
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#E9E7E2" />
        <SortedAscendingHeaderStyle BackColor="#506C8C" />
        <SortedDescendingCellStyle BackColor="#FFFDF8" />
        <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
    </asp:GridView>

This is my code-behind on a click event:

System.Data.DataTable dt = reporte.GetData(int.Parse(ddlChofer.SelectedValue), int.Parse(ddlVehiculo.SelectedValue), Convert.ToDateTime(txtFecha.Text));

if (dt.Rows.Count > 0)
{
   foreach (DataRow dr in dt.Rows)
   {
       string salida = dr["Salida"].ToString();
       string llegada = dr["Llegada"].ToString();

       if (salida != "" && llegada != "")
       {
          DataSetTableAdapters.sp_ChecarVehiculosTableAdapter cv = new DataSetTableAdapters.sp_ChecarVehiculosTableAdapter();
          gvPosicion.DataSource = cv.GetData(int.Parse(ddlVehiculo.SelectedValue), DateTime.Parse(salida), DateTime.Parse(llegada)).ToString();
          gvPosicion.DataBind();

And this is my stored procedure, which I think is a mess LOL, but really I'm just a SQL beginner.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_ChecarVehiculos]
@carro int,
@f1 datetime,
@f2 datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT cve_punto_gps,fecha_servidor, latitud, longitud, velocidad,direccion
INTO #temp
FROM [desarrollo].[dbo].[vehiculo_punto_gps]
WHERE cve_vehiculo=@carro
and fecha_servidor>=@f1
and fecha_servidor<=@f2
DECLARE @cve_inicio as int,
    @cve_final as int,
    @fecha_inicio as datetime,
    @fecha_final as datetime,
    @latitud_inicio as decimal(18,15),
    @latitud_final as decimal (18,15),
    @longitud_inicio as decimal (18,15),
    @longitud_final as decimal (18,15),
    @velocidad_inicio as int,
    @velocidad_final as int,
    @direccion_inicio as nvarchar(150),
    @direccion_final as nvarchar(150)
DECLARE VehicleCursor CURSOR FAST_FORWARD FOR 
SELECT  cve_punto_gps, 
    fecha_servidor,
    latitud,
    longitud,
    velocidad,
    direccion
FROM #temp
ORDER BY cve_punto_gps
 OPEN VehicleCursor FETCH NEXT FROM VehicleCursor INTO @cve_inicio, @fecha_inicio,        @latitud_inicio, @longitud_inicio, @velocidad_inicio, @direccion_inicio
FETCH NEXT FROM VehicleCursor INTO @cve_final, @fecha_final, @latitud_final, @longitud_final,@velocidad_final, @direccion_final
create table #tempb(cve int, fecha datetime, posicion nvarchar(60), velocidad int, direccion nvarchar(150) )
WHILE @@FETCH_STATUS = 0 BEGIN
    IF (round(@latitud_inicio,4,1) != round(@latitud_final,4,1))
    BEGIN   
        IF DATEDIFF(MI,@fecha_inicio,@fecha_final) >=3
        BEGIN           
            IF @velocidad_inicio !=0
            BEGIN
                declare @posicion nvarchar(60)=(cast(@latitud_inicio as nvarchar(30)) +' '+ cast(@longitud_inicio as nvarchar(30)))

                insert #tempb values(@cve_inicio,@fecha_inicio, @posicion,@velocidad_inicio, @direccion_inicio)
                --PRINT 'Posición: '+cast(@fecha_inicio as nvarchar(30))+' en '+@direccion_inicio+'('+@posicion+')';
            END     
        END
    END
    SET @cve_inicio = @cve_final
    SET @fecha_inicio = @fecha_final
    SET @latitud_inicio=@latitud_final
    SET @longitud_inicio=@longitud_final
    SET @velocidad_inicio=@velocidad_final
    SET @direccion_inicio=@direccion_final
    FETCH NEXT FROM VehicleCursor INTO @cve_final, @fecha_final, @latitud_final, @longitud_final, @velocidad_final, @direccion_final
END   
CLOSE VehicleCursor 
DEALLOCATE VehicleCursor
select convert(varchar(8),fecha,108) as 'Hora', direccion as 'Direccion', posicion as   'Posicion'  /*into #tempc */from #tempb
/*select * from #tempc*/
END

If you're wondering why I made that stored procedure, check the following post. Also, if I execute the stored procedure with some parameters in SQL, I get no error.

Thanks in advance.

Answer 1

In addition to the suggestions I provided in the comments, I think this will give the results you're after without all of the variables, cursors and temp tables in your procedure. Please try it out and let me know if I've interpreted any of your logic incorrectly.

ALTER PROCEDURE [dbo].[sp_ChecarVehiculos]
  @carro int,
  @f1 datetime,
  @f2 datetime
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH d AS
  (
    SELECT 
        cve_punto_gps, fecha_servidor, velocidad, direccion, latitud, longitud
        posicion = RTRIM(latitud) + ' ' + RTRIM(longitud),
        rn = ROW_NUMBER() OVER (ORDER BY fecha_servidor)
        FROM [desarrollo].[dbo].[vehiculo_punto_gps]
        WHERE carro = @carro
        AND fecha_servidor >= @f1
        AND fecha_servidor <= @f2
  )
  SELECT 
    Hora = CONVERT(CHAR(8), d.fecha_servidor, 108),
    d.direccion, d.posicion
  FROM d LEFT OUTER JOIN d AS d2
  ON d.rn = d2.rn - 1
  WHERE d.rn = 1
  OR 
  (
    DATEDIFF(MINUTE, d.fecha_servidor, COALESCE(d2.fecha_servidor, GETDATE())) >= 3 
    AND ROUND(d.latitud, 4, 1) <> ROUND(d2.latitud, 4, 1)
    AND d.velocidad <> 0
  )
  ORDER BY d.fecha_servidor;
END
GO

If it is incorrect it shouldn't take much editing to correct whatever mistakes I've made based on your existing code. Not only will this be much more efficient within SQL Server, but it should also make it much easier for your C# code to deal with the output.

EDIT example that shows time delay between selected rows:

ALTER PROCEDURE [dbo].[sp_ChecarVehiculos]
  @carro int,
  @f1 datetime,
  @f2 datetime
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH d AS
  (
    SELECT 
        cve_punto_gps, fecha_servidor, velocidad, direccion, latitud, longitud
        posicion = RTRIM(latitud) + ' ' + RTRIM(longitud),
        rn = ROW_NUMBER() OVER (ORDER BY fecha_servidor)
        FROM [desarrollo].[dbo].[vehiculo_punto_gps]
        WHERE cve_vehiculo = @carro
        AND fecha_servidor >= @f1
        AND fecha_servidor <= @f2
  ), s AS
  (
    SELECT 
      Hora = CONVERT(CHAR(8), d.fecha_servidor, 108),
      d.direccion, d.posicion, d.fecha_servidor,
      rn = ROW_NUMBER() OVER (ORDER BY d.fecha_servidor)
    FROM d LEFT OUTER JOIN d AS d2
    ON d.rn = d2.rn - 1
    WHERE d.rn = 1
    OR 
    (
      DATEDIFF(MINUTE, d.fecha_servidor, 
        COALESCE(d2.fecha_servidor, GETDATE())) >= 3 
      AND ROUND(d.latitud, 4, 1) <> ROUND(d2.latitud, 4, 1)
      AND d.velocidad <> 0
    )
  )
  SELECT s.Hora, delta = DATEDIFF(MINUTE, s.fecha_servidor, s2.fecha_servidor),
    s.direccion, s.posicion
  FROM s LEFT OUTER JOIN s as s2 ON s.rn = s2.rn - 1
  ORDER BY s.rn;
END
GO