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

• • Reply
  kafsar

  Member

  22 Points

  82 Posts

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

  Feb 21, 2020 09:08 AM|kafsar|LINK

  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
   

• • Reply
  samwu

  Contributor

  3370 Points

  1409 Posts

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

  Feb 24, 2020 06:12 AM|samwu|LINK

  Hi kafsar,

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.