用vb.net操作ms access預存程序(1)

來源:互聯網
上載者:User
access|預存程序 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
(未完待續)


相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。