用vb.net设置ms access存储过程（1）

1.存储过程在access中如何运行？
 不像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 CreateSPSub Main()ProductsProcs()End Sub' Products Stored Procs to be added to the db.Sub ProductsProcs()Dim sSQL As String' procProductsList - Retrieves entire tablesSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"CreateStoredProc(sSQL)' procProductsDeleteItem - Returns the details (one record) from the ' JobTitle tablesSQL = "CREATE PROC procProductsDeleteItem(@ProductID LONG) AS " _& "DELETE FROM Products WHERE ProductID = @ProductID;"CreateStoredProc(sSQL)' procProductsAddItem - Add one record to the JobTitle tablesSQL = "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 tablesSQL = "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 ProceduresSub CreateStoredProc(ByVal sSQL As String)Dim con As OleDbConnectionDim 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 = concmd.CommandText = sSQLcon.Open()cmd.ExecuteNonQuery()con.Close()End SubEnd Module
（未完待续）