显示存储过程结果在ASP.Net的GridView [英] Display stored procedure result in ASP.Net gridview
问题描述
我一直在挣扎了一会儿，在一个GridView显示一些SQL Server存储过程的结果。我收到以下错误：
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.
下面是我的GridView ASPX code：
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>
这是我的code-背后的click事件：
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();
这是我的存储过程，我认为这是一个烂摊子大声笑，但真的我只是一个初学者的SQL。
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
如果你想知道为什么我做的存储过程，检查以下<一个href=\"http://stackoverflow.com/questions/10625931/how-to-make-a-sql-loop-query-to-check-difference-between-values\">post.另外，如果我在SQL一些参数执行存储过程，我没有得到任何错误。
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.
先谢谢了。
推荐答案
在除了我在评论中所提供的建议，我的认为的这会给你的结果后，没有所有的变量，游标和临时表中的过程。请尝试一下，让我知道，如果我PTED你的任何逻辑错误间$ P $。
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
如果它是不正确它不应该花太多的编辑来纠正我根据您现有的code作出任何错误。这不仅是在SQL Server中更有效率，但同时也应该使它更容易为你的C＃code处理与输出。
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
这篇关于显示存储过程结果在ASP.Net的GridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
