 |
-
Powered by
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Insert Records using "Code-Behind
Web Forms" Technique
and Return Identity Value
Using the Post Form method
We started re-coding the Components Section of ASPFree.com using ASP.NET.
Being new to the various techniques of inserting records, returning data to a
form with ASP.NET. We decided to use a technique called "Code-Behind
Web Forms" and like so many other things about ASP.NET....we loved
it!. This allows up to put our Business logic into a separate
file.
This technique allows for true separation of the HTML
code and Business Logic. The HTML is on one aspx page and the business logic is
stored in its own file away from trouble! All that is required at
the top of your aspx page is one line! One attribute uses the Inherits
directive (This tells which function to use) and src(where the .vb
or .cs file reside). For this example
| <%@ Page
Inherits="MyCodeBehind" Src="c2.vb" %> |
There is a nice section in the quickstart docs on this topic also. Click
here to read up on it!
Here is the code
This example uses the following
- MS-SQL Server 7.0 database
- Stored Procedure
- Component1a.aspx (HTML File)
- c2.vb
Component1a.aspx (The Page that
is the UI)
<%@ Page Inherits="MyCodeBehind" Src="c2.vb"
Debug="True" trace="True" %>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
response.write("SMILE!!! I love learning new things everyday")
End Sub
</script>
<html>
<head>
<title>Component Page 1</title>
</head>
<body>
<table border=0 cellpadding=3 cellspacing=3>
<tr bgcolor="#CCCCCC">
<td>
<font face="Arial, Helv" size="-1">
Please fill out this form to create a new user profile for your
Company's Component.
<br>
Once this information is gathered you will not need to enter it again
and you will be able to update anytime.
<p>
Use the button at the bottom of this page to continue when you are
finished.
<br>
</font>
</td>
</tr>
</table>
<font
size="+1"><b><font
color="#ff0000">*=Required Fields</b><br>
<form method="Post" name="form1" runat="server">
<table> <tr>
<td align=right>
<asp:Label id="Label1" Text="Company Name"
Font-Name="Verdana" Font-Size="10pt"
Width="200px" BorderStyle="solid" BorderColor="#cccccc"
runat="server"/>
</td>
<td>
<asp:TextBox id="CompanyName"
size="30" runat="server" />
<asp:RequiredFieldValidator
ControlToValidate="CompanyName" Display="Dynamic"
errormessage="You must enter your name!" runat=server/>
</td>
</tr>
<tr>
<td align=right>
<asp:Label id="Label2" Text="Company URL"
Font-Name="Verdana" Font-Size="10pt"
Width="200px" BorderStyle="solid" BorderColor="#cccccc"
runat="server"/>
</td>
<td>
<asp:TextBox id="CompanyURL"
size="30" runat="server" />
</td>
</tr>
</font>
<tr>
<td align=right>
<asp:Label id="Label3" Text="Contact Email"
Font-Name="Verdana" Font-Size="10pt"
Width="200px" BorderStyle="solid" BorderColor="#cccccc"
runat="server"/>
</td>
<td>
<asp:TextBox id="EmailAddress"
size="30" runat="server" maintainstate="false"
/>
<asp:RegularExpressionValidator ControlToValidate="EmailAddress"
ValidationExpression="[\w-]+@[\w-]+\.(com|net|org|edu|mil)"
Display="Dynamic" Font-Name="verdana"
Font-Size="9pt" ErrorMessage="Must use a valid email
address." runat="server"> </asp:RegularExpressionValidator>
<asp:RequiredFieldValidator ControlToValidate="EmailAddress"
Display="dynamic" Font-Name="verdana"
Font-Size="9pt" ErrorMessage="'Email' must not be left
blank." runat=server> </asp:RequiredFieldValidator>
</td>
</tr>
</table>
<table border=0 bgcolor="#CCCCCC" cellpadding=3 cellspacing=3
width="490">
<tr>
<td width="100%" colspan="2">
<asp:Button id="Button1" Text="Create Profile"
OnClick="Button1_Click" Runat="server"/>
</td>
</tr>
</table>
</form>
</body>
</html>
|
| c2.vb File(This File Contains
the business logic that is inherited just like a compiled DLL
Option Strict Off
Imports System
Imports System.DateTime
Imports System.Globalization
Imports System.Data
Imports System.Data.SQL
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Public Class MyCodeBehind : Inherits Page
'Declare a public variable to hold the Identity
Value
Public strID as String
Public Sub Button1_Click(Sender As Object, E As
EventArgs)
If Page.IsValid Then
Dim MyConnection As
SQLConnection
MyConnection = New SQLConnection("server=localhost;uid=sa;pwd=;database=aspfree")
'Puts the local date in variable to be written to the database
dim d as dateTime
d = now()
d = d.Date()
'Using the request.form, this retrieves the variables out of the
forms collection
Dim MyCommand = New
SQLCommand("sp_ComponentCompanyInfo", MyConnection)
MyCommand.CommandType =
CommandType.StoredProcedure
MyCommand.Parameters.Add(New
SQLParameter("@CompanyName", SQLDataType.VarChar, 50))
MyCommand.Parameters("@CompanyName").Value
= request.Form("CompanyName")
MyCommand.Parameters.Add(New
SQLParameter("@CompanyURL", SQLDataType.VarChar, 50))
MyCommand.Parameters("@CompanyURL").Value
= request.Form("CompanyURL")
MyCommand.Parameters.Add(New
SQLParameter("@EmailAddress", SQLDataType.VarChar, 50))
MyCommand.Parameters("@Emailaddress").Value
= request.Form("EmailAddress")
MyCommand.Parameters.Add(New
SQLParameter("@DateSubmitted", SQLDataType.DateTime, 8))
MyCommand.Parameters("@DateSubmitted").Value
= d //stores the date variable!
MyCommand.ActiveConnection.Open()
Dim workParam as SQLParameter
'This line brings back the value using the output method
workParam = myCommand.Parameters.Add(new SQLParameter("@CompanyID",
SQLDataType.Int, 4))
workParam.Direction = ParameterDirection.Output
Try
myCommand.Execute()
'This line populates the strID variable with the Identity value
strID = (myCommand.Parameters("@CompanyID").Value.ToString())
Catch myException2 as Exception
Response.Write(myException2.ToString())
finally
End try
MyCommand.ActiveConnection.Close()
dim strURL as string =
"component1a.aspx?CategoryID=" & strID
page.navigate(strURL)
End If
End Sub
End Class
|
|
Stored Procedure that does the business logic
CREATE PROCEDURE sp_ComponentCompanyInfo
@CompanyName varchar(100),
@CompanyURL varchar(100),
@EmailAddress varchar(100),
@DateSubmitted datetime,
@CompanyID int OUTPUT
AS
INSERT tblComponentCompanyInfo(CompanyName, CompanyURL, EmailAddress,
DateSubmitted)
VALUES (@CompanyName, @CompanyURL, @EmailAddress, @DateSubmitted)
Select @CompanyID = @@Identity
RETURN
|
|
DDL that Creates the table
CREATE TABLE [dbo].[tblComponentCompanyInfo] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [varchar] (100) NULL ,
[CompanyURL] [varchar] (100) NULL ,
[EmailAddress] [varchar] (100) NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblComponentCompanyInfo] WITH NOCHECK ADD
CONSTRAINT [PK_tblComponentCompanyInfo] PRIMARY KEY NONCLUSTERED
(
[CompanyID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
|
|
|
tio |