在Access2000使用存储过程进行参数查询(ASP.NET)

不像SQLSERVER存储过程,Access2000参数查询不支持多操作或者输出参数。

现在将作者的设计思路列出来：

数据库存储过程设计(Access2000)

插入查询：

PARAMETERS text1 Text ( 50 ), long1 Long, date1 DateTime;
INSERT INTO datatable ( textfield, numberfield, datefield )
VALUES (text1, long1, date1);

选择查询：

SELECT *
FROM datatable;

更新查询：

PARAMETERS text1 Text ( 50 ), long1 Long, date1 DateTime, longId Long;
UPDATE datatable SET datatable.textfield = text1, datatable.numberfield = long1, datatable.datefield = date1
WHERE (((datatable.id)=[longId]));

删除查询：

PARAMETERS longId Long;
DELETE *
FROM datatable
WHERE (((datatable.id)=[longId]));

例子代码实现过程(C#)：

<%@ Page Language="C#" debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

    void Button1_Click(object sender, EventArgs e) {
        Label1.Text = "";
        OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        OleDbCommand myCommand = new OleDbCommand("InsertQuery", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        OleDbParameter parametertext = new OleDbParameter("text1", OleDbType.VarChar, 50);
        parametertext.Value = TextBox2.Text;
        myCommand.Parameters.Add(parametertext);
        OleDbParameter parameternumber = new OleDbParameter("long1", OleDbType.Integer);
        parameternumber.Value = Convert.ToInt32(TextBox3.Text);
        myCommand.Parameters.Add(parameternumber);
        OleDbParameter parameterdate = new OleDbParameter("date1", OleDbType.Date);
        parameterdate.Value = TextBox4.Text;
        myCommand.Parameters.Add(parameterdate);
        try{
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            Label1.Text = "Success";
        }
        catch{
            Label1.Text = "Insert Failed";
        }
        finally{
            myConnection.Close();
        }
    }
   
    void Button2_Click(object sender, EventArgs e) {
        Label1.Text = "";
        OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        OleDbCommand myCommand = new OleDbCommand("UpdateQuery", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        OleDbParameter parametertext = new OleDbParameter("text1", OleDbType.VarChar, 50);
        parametertext.Value = TextBox2.Text;
        myCommand.Parameters.Add(parametertext);
        OleDbParameter parameternumber = new OleDbParameter("long1", OleDbType.Integer);
        parameternumber.Value = Convert.ToInt32(TextBox3.Text);
        myCommand.Parameters.Add(parameternumber);
        OleDbParameter parameterdate = new OleDbParameter("date1", OleDbType.Date);
        parameterdate.Value = TextBox4.Text;
        myCommand.Parameters.Add(parameterdate);
        OleDbParameter parameterid = new OleDbParameter("longId", OleDbType.Integer);
        parameterid.Value = Convert.ToInt32(TextBox1.Text);
        myCommand.Parameters.Add(parameterid);
        try{
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            Label1.Text = "Success";
        }
        catch{
            Label1.Text = "Update Failed";
        }
        finally{
            myConnection.Close();
        }
    }
   
    void Button3_Click(object sender, EventArgs e) {
        Label1.Text = "";
        OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        OleDbCommand myCommand = new OleDbCommand("DeleteQuery", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        OleDbParameter parameterid = new OleDbParameter("@longId", OleDbType.Integer);
        parameterid.Value = Convert.ToInt32(TextBox1.Text);
        myCommand.Parameters.Add(parameterid);
        try{
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            Label1.Text = "Success";
        }
        catch{
            Label1.Text = "Delete Failed";
        }
        finally{
            myConnection.Close();
        }
    }
   
    void Button4_Click(object sender, EventArgs e) {
        Label1.Text = "";
        OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
        OleDbCommand myCommand = new OleDbCommand("SelectQuery", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        try{
            myConnection.Open();
            DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            DataGrid1.DataBind();
        }
        catch{
            Label1.Text = "Select Failed";
        }
        finally{
            myConnection.Close();
        }
    }

</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: Arial, Trebuchet MS">
    <form runat="server">
        <table cellspacing="0" cellpadding="4">
            <tbody>
                <tr>
                    <td align="middle" colspan="2">
                        <h2>Access Stored Procedure
                            <br />
                            (Parameter Query)
                        </h2>
                    </td>
                </tr>
                <tr>
                    <td align="right">
                        Id</td>
                    <td>
                        <asp:TextBox id="TextBox1" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td align="right">
                        Text
                    </td>
                    <td>
                        <asp:TextBox id="TextBox2" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td align="right">
                        Number</td>
                    <td>
                        <asp:TextBox id="TextBox3" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td align="right">
                        Date</td>
                    <td>
                        <asp:TextBox id="TextBox4" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td align="middle" colspan="2">
                        <asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Insert"></asp:Button>
                        &nbsp;
                        <asp:Button id="Button2" onclick="Button2_Click" runat="server" Text="Update"></asp:Button>
                        &nbsp;
                        <asp:Button id="Button3" onclick="Button3_Click" runat="server" Text="Delete"></asp:Button>
                        &nbsp;
                        <asp:Button id="Button4" onclick="Button4_Click" runat="server" Text="Select"></asp:Button>
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:Label id="Label1" runat="server" forecolor="Red"></asp:Label></td>
                </tr>
                <tr>
                    <td align="middle" colspan="2">
                        <asp:datagrid id="DataGrid1" runat="server" ForeColor="Black" EnableViewState="False"
                        BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1">
                            <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
                            <ItemStyle backcolor="#DEDFDE"></ItemStyle>
                        </asp:datagrid>
                    </td>
                </tr>
            </tbody>
        </table>
    </form>
</body>
</html>