Stone Coast Web Design Blog

Private Sub OnSelectedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)
        Dim cmd As IDbCommand
        cmd = e.Command
        Dim recordCount As Integer = e.AffectedRows()
        lblRecordCount.Text = "Showing " & recordCount & " IMFs"
End Sub


<asp:SqlDataSource ID="dsMyIMFs" runat="server" ConnectionString="<%$ ConnectionStrings:Tearsheet_ConString %>" 
SelectCommand="p_MyIMFs" SelectCommandType="StoredProcedure" OnSelected="OnSelectedHandler">
</asp:SqlDataSource>

<asp:Label ID="lblRecordCount" runat="server" />

Dim MyConnection As SqlConnection
        
 MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
Dim MyAdapter As New SqlDataAdapter(SqlText, MyConnection)
 
 Dim DS As DataSet
Dim cmd As SqlCommand
        
 SqlText += " SELECT * FROM Table A"
       
              
 cmd = New SqlCommand(SqlText)
MyConnection.Open()
cmd.Connection = MyConnection

DS = New DataSet()
MyAdapter.Fill(DS, "SearchResults")
                           
GridView1.DataSource = DS.Tables("SearchResults").DefaultView
lblRecordCount.Text = "Your search returned " & DS.Tables("SearchResults").Rows.Count & " records"

Or

Dim intRecordCount As Integer = ds.Tables(0).Rows.Count()
lblRecordCount.Text = "Your search returned " & intRecordCount & " records"

GridView1.DataBind()

DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)

-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(  
    SELECT id, col FROM @t  
    UNPIVOT  
        (col FOR ListofColumns IN (col1,col2,col3))   
    AS unpivott) AS p
    GROUP BY id 

The syntax shown below will drop a stored procedure if it exists and recreate it.

IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE dbo.YourStoredProcName
AS
-- Logic Comes Here
GO

DECLARE @CurrentDate DateTime
SET @CurrentDate = '2010-03-27 14:15:15.390'

--Print Date in MM/YYYY format

SELECT DATENAME(m, @CurrentDate) 
+ ' ' + CONVERT(varchar(4), DATEPART(year, @CurrentDate))
as 'MM/YYYY'

--Print Date in MM-DD-YY format

SELECT CONVERT(varchar(10), @CurrentDate, 110)
as 'MM-DD-YY'