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

最新推荐文章于 2019-11-05 09:32:57 发布

zgqtxwd

最新推荐文章于 2019-11-05 09:32:57 发布

阅读量450

点赞数

分类专栏： .net 文章标签： access vb.net 存储 string insert delete

.net 专栏收录该内容

1539 篇文章 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 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
 （未完待续）

seover="window.status='正文--用vb.net操作ms access存储过程（1）';return true">
 <script type="text/javascript"> 
                                                                  </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> 
                                                                 
                                                                   <script type="text/javascript"> 
                                                                   </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>