|
Returning Identity Column using ExecuteScalar Method
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_
(ConfigurationSettings.AppSettings("DSN_dev"))
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
Try
' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteScalar()
'Bind Value to a local variable.
dim strID as string = MyCommand.Parameters("@ID").Value.tostring()
Message.InnerHtml = strID
Catch f As Exception
throw f
Finally
' Close the Connection
If myConnection.State = ConnectionState.Open then
myConnection.Close()
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
RETURN
|
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
) ON [PRIMARY]
GO
|
<configuration>
<appSettings>
<add key="DSN"
value="server=yourserver;uid=sa;pwd=;Database=somedb"/>
</appSettings>
<system.web>
<customErrors mode="On" defaultRedirect="/errorpage.aspx"/>
</system.web>
</configuration>
|
Enjoy!! |
|