如何在VB.NET的datagrid视图中显示存储过程的结果 [英] How to display the result of stored procedure to the datagrid view in VB.NET
问题描述
我有一个存储过程正在生成出勤报告(在sql server 2012中).
现在，我必须从VB.NET应用程序执行此存储过程，并在DataGrid视图中显示结果.帮助我在vb的datagrid视图中显示结果.
这是存储过程的代码:
I have a stored procedure which is generating a attendance report (in sql server 2012).
now I have to execute this stored procedure from my VB.NET application and display the results in a DataGrid View. help me out to display the result in the datagrid view in vb.
This is the code for stored procedure:
<pre>USE [first_db]
GO
/****** Object: StoredProcedure [dbo].[GET_ATTENDANCE] Script Date: 22-02-2018 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GET_ATTENDANCE]
@STARTDATE DATETIME,
@ENDDATE DATETIME
AS BEGIN
--Now generate dates between two dates by Common table expression . and store that values in one temporary table (#TMP_DATES) .
;WITH DATERANGE AS
(
SELECT DT =DATEADD(DD,0, @STARTDATE)
WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE
UNION ALL
SELECT DATEADD(DD, 1, DT)
FROM DATERANGE
WHERE DATEADD(DD, 1, DT) <= @ENDDATE
)
SELECT * INTO #TMP_DATES
FROM DATERANGE
--As the Report columns (Dates) are dynamic , hence Columns (Dates) are concatenated one by one from Temporary table (#TMP_DATES) and store the value in a local variable .
DECLARE @COLUMN VARCHAR(MAX)
SELECT @COLUMN=ISNULL(@COLUMN+',','')+ '['+ CAST(CONVERT(DATE , T.DT) AS VARCHAR) + ']' FROM #TMP_DATES T
--After Pivot , some columns may be Null as data (here PRESENT_STATUS) not exists in pivot section .Now replace the Null values by 'N/A' .
DECLARE @Columns2 VARCHAR(MAX)
SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+ CAST(CONVERT(DATE , DT) as varchar )+'],''A'') AS ['+CAST(CONVERT(DATE , DT) as varchar )+']' FROM #TMP_DATES GROUP BY dt FOR XML PATH('')),2,8000)
--Now declare one local variable to write the dynamic sql query .
DECLARE @QUERY VARCHAR(MAX)
--Here Right outer join is done to show the all dates from the temporary table
SET @QUERY = 'SELECT User_Id, ' + @Columns2 +' FROM
(
SELECT A.User_Id , B.DT AS DATE, A.STATUS FROM MarkA A RIGHT OUTER JOIN #TMP_DATES B ON A.DATE=B.DT
) X
PIVOT
(
MIN([STATUS])
FOR [DATE] IN (' + @COLUMN + ')
) P
WHERE ISNULL(User_Id,'''')<>''''
'
EXEC (@QUERY)
--Drop the temporary table
DROP TABLE #TMP_DATES
END
GO
我尝试过的事情:
这是vb中的代码:
What I have tried:
this is the code in vb:
Private Sub StartDatePicker_ValueChanged(sender As Object, e As EventArgs) Handles StartDatePicker.ValueChanged
'' StartDate = Format(StartDatePicker.Value, "yyyy/mm/dd")
sd = Date.Parse(StartDatePicker.Text)
End Sub
Private Sub EndDatePicker_ValueChanged(sender As Object, e As EventArgs) Handles EndDatePicker.ValueChanged
''EndDate = Format(EndDatePicker.Value, "yyyy/mm/dd")
ed = Date.Parse(EndDatePicker.Text)
End Sub
Private Sub Show_Button_Click(sender As Object, e As EventArgs) Handles Show_Button.Click
Try
Dim dt As DataTable = New DataTable()
Dim cmd As SqlCommand = New SqlCommand("GET_ATTENDANCE", connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connection
cmd.Parameters.AddWithValue("@STARTDATE", sd)
cmd.Parameters.AddWithValue("@ENDDATE", ed)
connection.Open()
Dim adp As New SqlDataAdapter(cmd)
Dim ds As DataSet = New DataSet()
cmd.ExecuteNonQuery()
adp.Fill(ds, "GET_ATTENDANCE")
DataGridView1.DataSource = ds.Tables(0)
connection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
我有一个表单可以从用户中选择开始日期和结束日期并在数据网格视图中显示结果.但是此代码既不会产生错误，也不会引发异常.所以我该怎么办(结果不会显示在datagridview中)
I have a form which selects the start date and end date from the user and displays the result in the data grid view.But this code neither generates an error nor an exception is thrown ?? so what should i do (results are not getting displayed in the datagridview)
推荐答案
尝试一下-
下面的代码Spinet来自我的程序，对我来说很好用
try this -
below code spinet is from my program, this works fine for me
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlCommand command = new SqlCommand();
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
int i = 0;
string sql = null;
string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****";
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "EmployeeData";
adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
connection.Close();
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
}
也请阅读此博客-
使用SP将数据绑定到网格视图
还有这个
使用存储过程进行网格视图绑定 [
read this blog as well-
Bind data to grid view using SP
and this one as well
Grid View Binding using Stored Procedure[^]
这篇关于如何在VB.NET的datagrid视图中显示存储过程的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
