用vb.net操作ms access存储过程（1）

1.存储过程在aclearcase/" target="_blank" >ccess中如何运行？
     不像access中的其他对象或者ms sql中可以有直观的设计界面，在access中的存储过程，没有这些，所以我们不能在access中建立他们，我将向大家展示在ado.net中如何操作他们。
2。创建存储过程
    我们需要使用一段sql语句来创建存储过程，我们使用事例数据库Northwind 来说明我们的例子。
    一个简单的存储过程
           "CREATE PROC procProductsList AS SELECT * FROM Products;"
  CREATE PROC procProductsList 意思是创建存储过程as 后面可以是任何有效的sql语句。
   但是有的时候我们需要制定某一参数，比如我们要删除指定ProductsID 的记录，这时就需要这样的存储过程。"CREATE PROC procProductsDeleteItem(inProductsID LONG)" & _
"AS DELETE FROM Products WHERE ProductsID = inProductsID;" 在给出一个更复杂的：
"CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " & _
"inSupplierID LONG, inCategoryID LONG) " & _
"AS INSERT INTO Products (ProductName, SupplierID, CategoryID) " & _
"Values (inProductName, inSupplierID, inCategoryID);""CREATE PROC procProductsUpdateItem(inProductID LONG, " & _
"                                   inProductName VARCHAR(40)) " & _
"AS UPDATE Products SET ProductName = inProductName " & _
"    WHERE ProductID = inProductID;"好了，原理已经知道了。我们把这些综合一下做一个模块，岂不更好，说干就干。
Imports SystemImports System.DataImports System.Data.OleDbModule CreateSP    Sub Main()        ProductsProcs()    End Sub    @# Products Stored Procs to be added to the db.    Sub ProductsProcs()        Dim sSQL As String        @# procProductsList - Retrieves entire table        sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"        CreateStoredProc(sSQL)        @# procProductsDeleteItem - Returns the details (one record) from the         @# JobTitle table        sSQL = "CREATE PROC procProductsDeleteItem(@ProductID LONG) AS " _            & "DELETE FROM Products WHERE ProductID = @ProductID;"        CreateStoredProc(sSQL)        @# procProductsAddItem - Add one record to the JobTitle table        sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " _            & "inSupplierID LONG, inCategoryID LONG) AS INSERT INTO " _            & "Products (ProductName, SupplierID, CategoryID) Values " _            & "(inProductName, inSupplierID,   CategoryID);"        CreateStoredProc(sSQL)        @# procProductsUpdateItem - Update one record on the JobTitle table        sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, " _            & "inProductName VARCHAR(40)) AS UPDATE Products SET " _            & "ProductName = inProductName WHERE ProductID = inProductID;"        CreateStoredProc(sSQL)    End Sub    @# Execute the creation of Stored Procedures    Sub CreateStoredProc(ByVal sSQL As String)        Dim con As OleDbConnection        Dim cmd As OleDbCommand = New OleDbCommand()        Dim da As OleDbDataAdapter        @# Change Data Source to the location of Northwind.mdb on your local         @# system.        Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data " _            & "Source=C:\Program Files\Microsoft " _            & "Office\Office10\Samples\Northwind.mdb"        con = New OleDbConnection(sConStr)        cmd.Connection = con        cmd.CommandText = sSQL        con.Open()        cmd.ExecuteNonQuery()        con.Close()    End SubEnd Module
（未完待续）

原文转自：http://www.ltesting.net