📄 update-with-storedproc.aspx
字号:
<%@Page Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>
<%@Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Updating Data with a Stored Procedure</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Updating Data with a Stored Procedure</span><hr />
<!--------------------------------------------------------------------------->
<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>
<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>Stored Procedure Name: <b><span id="outSQL" runat="server"></span></b></div>
<div id="outError" runat="server"> </div>
<div>Input Parameters: <b><span id="outInParams" runat="server"></span></b></div>
<div>Output Parameter: <b><span id="outOutParams" runat="server"></span></b></div>
<script language="vb" runat="server">
Sub Page_Load()
'get connection string from ..\global\connect-strings.ascx user control
Dim strConnect = ctlConnectStrings.OLEDBConnectionString
outConnect.InnerText = strConnect 'and display it
'specify the stored procedure name
Dim strSQL As String = "AddNewBook"
outSQL.InnerText = strSQL 'and display it
'create a new Connection object using the connection string
Dim objConnect As New OleDbConnection(strConnect)
'create a new Command using the connection object and stored proc name
Dim objCommand As New OleDbCommand(strSQL, objConnect)
objCommand.CommandType = CommandType.StoredProcedure
'create a variable to hold a Parameter object
Dim objParam As OleDbParameter
'create a new Parameter object named 'ISBN' with the correct data
'type to match a SQL database 'varchar' field of 12 characters
objParam = objCommand.Parameters.Add("ISBN", OleDbType.VarChar, 12)
'specify that it's an Input parameter and set the value
objParam.Direction = ParameterDirection.Input
objParam.Value = "1999999999"
'create a new Parameter object named 'Title' with the correct data
'type to match a SQL database 'varchar' field of 50 characters
'specify that it's an Input parameter and set the value
objParam = objCommand.Parameters.Add("Title", OleDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = "Programming in the Virtual World"
'create another input Parameter object named 'Date' with the correct
'data type to match a SQL database 'datetime' field
'specify that it's an Input parameter and set the value
objParam = objCommand.Parameters.Add("Date", OleDbType.DBDate)
objParam.Direction = ParameterDirection.Input
objParam.Value = "2001-05-01"
'create an output Parameter object named 'Result' with the correct
'data type to match a SQL database 'integer' field
'specify that it's an Output parameter so no value required
objParam = objCommand.Parameters.Add("Result", OleDbType.Integer)
objParam.Direction = ParameterDirection.Output
'display the value of the input parameters
outInParams.InnerText = "ISBN='" & objCommand.Parameters("ISBN").Value _
& "' Title='" & objCommand.Parameters("Title").Value _
& "' Date='" & objCommand.Parameters("Date").Value & "'"
Try
'open the connection to the database
objConnect.Open()
'execute the SQL statement against the command
objCommand.ExecuteNonQuery()
'close the connection to the database
objConnect.Close()
Catch objError As Exception
'display error details
outError.InnerHtml = "<b>* Error while updating original data</b>.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
'collect and display the value of the output parameter
Dim intResult As Integer = objCommand.Parameters("Result").Value
Dim strResult As String = "Result='" & CStr(intResult) & "'<br />"
If intResult = 0 Then
strResult += "Successfully inserted new book details"
Else
strResult += "Failed to insert new book details and instead " _
& "deleted existing record with this ISBN"
End If
outOutParams.InnerHtml = strResult
End Sub
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -