Stand-alone EXE or a Windows Service version).">
  •      Powered by

Returning Identity Column using ExecuteScalar Method

Click here for demo

According to the .NET documentation.  The ExecuteScalar method returns only the first column and first result of a query, the rest is ignored.  The meaning of this article is to describe the most efficient way of JUST returning one value to the aspx page. I ran into no problems using the ExecuteNonQuery method but again just looking for the fastest and most efficient method. 

One of the main things done in most asp based applications is to return the record number after the data has be submitted to the database.  The following code sample below shows returning the identity column using a stored procedure and using the ExecuteScalar method.  In the table below, is the variations of the three different execute methods in the Oledb or SQLClient Command namespaces.

ExecuteNonQuery Executes a Transact-SQL statement against the Connection and returns the number of rows affected.
ExecuteReader Overloaded. Sends the CommandText to the Connection and builds a SqlDataReader.
ExecuteScalar Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
<%@ Page Language="VB" EnableSessionState="False" EnableViewState="False" 
Trace="False" Debug="False" Strict="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs) 
End Sub
Sub b1_OnClick(Sender As Object, E As EventArgs) 
    Dim myConnection As SqlConnection = New SqlConnection_
Dim MyCommand as SQLCommand = New SQLCommand("sp_ReturnIdentity", MyConnection)
    MyCommand.CommandType = CommandType.StoredProcedure
Dim workParam As SQLParameter = Nothing
MyCommand.Parameters.Add(New SQLParameter("@FName", SQLDbType.VarChar, 50))
MyCommand.Parameters("@FName").Value = request.form("Fname")
MyCommand.Parameters.Add(New SQLParameter("@LName", SQLDbType.VarChar, 50))
MyCommand.Parameters("@LName").Value = request.form("Lname")
MyCommand.Parameters.Add(New SQLParameter("@FavColor", SQLDbType.VarChar, 50))
MyCommand.Parameters("@FavColor").Value = request.form("Favcolor")
workParam = MyCommand.Parameters.Add(New SQLParameter("@ID",SQLDbType.Int, 4))
workParam.Direction = ParameterDirection.Output 
' Open the connection and execute the Command 
'Bind Value to a local variable.
            dim strID as string = MyCommand.Parameters("@ID").Value.tostring() 
            Message.InnerHtml = strID     
Catch f As Exception
       throw f 
' Close the Connection 
        If myConnection.State = ConnectionState.Open then 
        End If 
End Try End Sub </script> <html><head></head><body> <h2>Your ID: is <br> <span id="Message" runat="server"/></h2> <form method="post" name="form1" runat="server"> <strong>First Name</strong><br> <input type="text" size="40" name="FName"><br> <strong>Last Name</strong><br> <input type="text" size="40" name="LName"><br> <strong>FavoriteColor</strong><br> <input type="text" size="40" name="FavColor"><br> <p> <input type="submit" OnServerClick="b1_OnClick" value="Add Record" runat="server"> </font> </form> </body> </html>

The DDL to create the Stored Procedure

CREATE PROCEDURE sp_ReturnIdentity
'This is how you declare variables in a stored procedure
    @Fname varchar(50),
    @Lname varchar(50),
    @FavColor varchar(50),
    @ID int OUTPUT
) AS
'Insert data into the table
INSERT tbladoadd(FirstName, LastName, FavoriteColor) 
VALUES (@Fname, @LName, @favColor)
'Variable to hold the Identity column
Select @ID = @@Identity

The DDL to create the Table

CREATE TABLE [dbo].[tblAdoADD] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (50) NULL ,
	[LastName] [varchar] (50) NULL ,
	[FavoriteColor] [varchar] (50) NULL 
                        <add key="DSN" value="server=yourserver;uid=sa;pwd=;Database=somedb"/>
                        <customErrors mode="On" defaultRedirect="/errorpage.aspx"/>




Terms of Use | Privacy Statement ©2005-2006 All rights reserved - Powered by IIS7 - info @