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

2024-07-10 13:02:50

字体：大中小

来源：转载

供稿：网友

本文来源于网页设计爱好者web开发社区http://www.html.org.cn收集整理，欢迎访问。

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
（未完待续）

上一篇：使用 ADO.NET 解锁 Microsoft Access 数据（2）

下一篇：用vb.net操作access存储过程（2）