SQL query to bind (GridView) using asp.net/vb.net.

Question

User-49671077 posted

I need the using following SQL query to bind (GridView) using asp.net/vb.net.

SELECT DISTINCT datein INTO #Dates
FROM AttnEmployee
ORDER BY datein

-- a comma seperated value string from the dates in #Dates
DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, datein, 106)
				+ ']','[' + CONVERT(varchar, datein, 106) + ']')
FROM    #Dates
ORDER BY datein

-- Building the query with dynamic dates
DECLARE @qry NVARCHAR(4000)
SET @qry = 'SELECT * FROM (SELECT sid,datein,whours FROM AttnEmployee)emp PIVOT (MAX(whours) FOR datein IN (' + @cols + ')) AS stat'


-- Executing the query
EXEC(@qry)


-- Dropping temporary tables
-- DROP TABLE #Dates

Thanks
 

Answer 1

User288213138 posted

Hi kafsar,

I need the using following SQL query to bind (GridView) using asp.net/vb.net.

You can put your SQL query into the stored procedure and then use Ado.net to call the stored procedure.

stored procedure:
CREATE PROCEDURE test
AS
BEGIN
....
END
GO

Ado.net:
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

    Using con As SqlConnection = New SqlConnection(constring)

        Using cmd As SqlCommand = New SqlCommand("test", con)
            cmd.CommandType = CommandType.StoredProcedure
            con.Open()
            Dim idr As IDataReader = cmd.ExecuteReader()
            GridView1.DataSource = idr
            GridView1.DataBind()
        End Using
    End Using

About how to create a stored procedure you can refer to this link:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

Best regards,

Sam