如何在vb.net中调用存储过程 [英] how to call the stored procedure in vb.net

查看：53
本文介绍了如何在vb.net中调用存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Quote：



CREATE TABLE TBL_CYL_GAS_MASTER



（



SERIAL_NO INT PRIMARY KEY IDENTITY（1,1），



GAS_NAME VARCHAR（100 ），



TRAN_DATE DATETIME



）



----------------------------------------------- -------------------------------------------------- ---------------------



文字



---------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------



创建程序gas2



 



（



 



   ;              @actio N'NBSP;                                               varchar（20），



 



       ;          @ serial_no                         int，



 



                 @ gas_name                       varchar（50）
b $ b



）

>
 



AS



 



BEGIN



 



     ;            --declare @serial_no int



 



                选择@ serial_no = serial_no FROM tbl_Cyl_gas_master WHERE serial_no = @serial_no



 



                



 



                IF（@action ='add'）



 



     ;           开始



 



         ;                        &NBSP ;              如果不是EXISTS（选择*来自tbl_Cyl_gas_master WHERE gas_name = @gas_name）



 



                                                                   INSERT INTO tbl_Cyl_gas_master（gas_name，tran_date）VALUES（@ gas_name，GEtDATE（））



 



                        &NBSP ;                        &NBSP ;              --set @Newserial_no = SCOPE_IDENTITY（）



            &NBSP ;                        &NBSP ;          ELSE



 



         ;                        &NBSP ;                        &NBSP ;      RAISERROR（'TRANSACTION ALREADY EXISTS'，16,1）



 



 



                                结束       



 



                 ELSE IF @ action ='update'



 



                 BEGIN



                &NBSP ;              



  &NBSP ;                        &NBSP ;    IF EXISTS（SELECT * FROM tbl_Cyl_gas_master WHERE serial_no = @serial_no）



 



   ;                        &NBSP ;    开始



                &NBSP ;                        &NBSP ;      IF NOT EXISTS（SELECT * FROM tbl_Cyl_gas_master WHERE gas_name = @gas_name）



                                                  - 从tbl_Cyl_gas_master中选择serial_no，gas_name，其中serial_no = @ serial_no



 



   ;                        &NBSP ;                     UPDATE tbl_Cyl_gas_master set gas_name = @ gas_name WHERE serial_no = @ serial_no



 



                                                  --select serial_no，从tbl_Cyl_gas_master其中serial_no = @ gas_name serial_no



          &NBSP ;                        &NBSP ;            ELSE



                &NBSP ;                        &NBSP ;                      RAISERROR（'TRANSACTION ALREADY EXISTS'，16,1）



 



                                 结束



 



 



               结束



 



ELSE if @ action ='delete'



 



             ;   开始



 



         ;                       如果 存在（从tbl_cyl_gas_master中选择*，其中serial_no = @ serial_no）



 



                                 开始



 



         ;                       更新tbl_Cyl_gas_master set gas_name ='已删除'



 



                                                 其中serial_no = @ serial_no



 



       ;          - 来自tbl_Cyl_gas_master的来自gas_name，其中serial_no = @ serial_no



 



    ;                        &NBSP ;   结束



 



         ;       结束



 



         ;       否则



 



         ;                       从tbl_cyl_gas_master中选择serial_no，gas_name，其中serial_no = @ serial_no



 



    ;            结束



 



 



----------------------------------------------- -------------------------------------------------- ------------------------------------



               



                                   



        



   

Gas Master





  ;      



                     



                                                                                                                                                         



                                                                                         



            &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp：Label ID =lbl1runat =serverText =lbl1>序列号



      &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp：Label ID =lbl2runat =serverText =lbl2>气体名称



      &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
行动  



     ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;< ASP：DropDownList的ID = DDL1 RUNAT = 服务器 的AutoPostBack = 真 风格= 高度：的22px >&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;      



             &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;< ASP：的ListItem>查看



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&N BSP;&NBSP; < ASP：的ListItem>添加



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < ASP：的ListItem>修改



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < ASP：的ListItem>删除&NBSP;&NBSP;



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;         



 



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
  



  



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;< ; asp：DropDownList ID =ddl2runat =serverAutoPostBack =true 高度=23px宽度=250px>



            &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;      < asp：TextBox ID =txtsnorunat =serverHeight =18pxWidth =250px>



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
 



        ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp：TextBox ID =txtgasnamerunat =serverHeight =18pxWidth =250px> 



 



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;




        





       

       





           



             &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; <无线电通信/>


&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP; < asp：Button ID =btnmodifyFont-Bold 前景色= 暗红色 &NBSP; runat =serverText =ApplyWidth =62px  />



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;
< asp：Button ID =btnresetFont-Bold  ForeColor =DarkRedrunat =serverText =ResetWidth =62px/>



       ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;




       





       





---------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------



       如果ddl1.Text =添加那么



            Dim sQry As String



            Dim scmd As New SqlCommand



           昏暗的scn作为新的SqlConnection



&NBSP（ConfigurationManager.ConnectionStrings（ sastrystring）的ConnectionString）;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; scn.Open（）



            Dim cmd As New SqlCommand



            cmd.Connection = scn



            sQry =插入  jpm_sastry 选择'& txtsno.Text& '，'& txtgasname.Text& '



            cmd.CommandText = sQry



           暗淡的RS作为SqlDataReader的= cmd.ExecuteReader（）



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; scn.Close（）



            MSGBOX（ 条记录成功插入 &安培; txtsno.Text&安培; txtgasname.Text）



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 'txtgasname.Text =



            'txtsno.Text =



            Response.Redirect（HttpContext.Current.Request.Url.ToString（），True）



 



        ElseIf ddl1.Text =修改然后



 



     ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; Dim sQry As String



            Dim scmd As New SqlCommand



           昏暗的scn作为新的SqlConnection



&NBSP（ConfigurationManager.ConnectionStrings（ sastrystring）的ConnectionString）;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; scn.Open（）



            Dim cmd As New SqlCommand



            cmd.Connection = scn



            MsgBox（ddl2.Text）



            sQry =update  jpm_sastry  set dname  ='& txtgasname.Text& 'sno ='& ddl2.Text& '



            cmd.CommandText = sQry



           暗淡的RS作为SqlDataReader的= cmd.ExecuteReader（）



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; scn.Close（）



            MSGBOX（ 条记录更新成功 &放大器; txtsno.Text&安培; txtgasname.Text）



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; 'ddl1.Visible = True



            Response.Redirect（HttpContext.Current.Request.Url.ToString（），True）



 



        ElseIf ddl1.Text =删除然后



 



     ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; Dim sQry As String



            Dim scmd As New SqlCommand



           昏暗的scn作为新的SqlConnection



&NBSP（ConfigurationManager.ConnectionStrings（ sastrystring）的ConnectionString）;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; scn.Open（）



            Dim cmd As New SqlCommand



            cmd.Connection = scn



            sQry =从    jpm_sastry 中删除sno = '& ddl2.Text& '



            cmd.CommandText = sQry



           暗淡的RS作为SqlDataReader的= cmd.ExecuteReader（）



&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; scn.Close（）



            MsgBox（Recored deleted successfully）



            'ddl1.Visible = True



            Response.Redirect（HttpContext.Current.Request.Url.ToString（），True）



 



       结束如果



 



   结束子



-------------------------------- -------------------------------------------------- -------------------------------------------------- ---



 

解决方案

您可能需要以下vb.net代码用于存储过程。

  Dim  spName  As   String  =   gas2 
  Dim  sqlConnection 作为 新 SQLClient.SqlConnection（  ConnectionString）
  Dim  sqlCommand 作为 新 SQLClient.SqlCommand 
 
 sqlCommand.Connection = sqlConn挠度
 sqlCommand.CommandType = CommandType.StoredProcedure 
 sqlCommand.CommandText = spName 
 
 sqlCommand.Parameters.AddWithValue（<跨度类= 代码串>  @ action，Action）
 sqlCommand.Parameters.AddWithValue（  @ serial_no，serial_no）
 sqlCommand.Parameters.AddWithValue（<跨度类= 代码串>  <跨度类= 代码串> @gas_name，gas_name）
 sqlConnection.Open（）
 sqlCommand.ExecuteNonQuery（）


写的名称sQry上的存储过程并将sqlcommand.commandtype更改为commandtype.storedprocedure。这应该有效。


Quote:


CREATE TABLE TBL_CYL_GAS_MASTER

(

SERIAL_NO INT PRIMARY KEY IDENTITY(1,1),

GAS_NAME VARCHAR(100),

TRAN_DATE DATETIME

)

----------------------------------------------------------------------------------------------------------------------

Text

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE procedure gas2

 

(

 

                @action                                               varchar(20),

 

                @serial_no                         int,

 

                @gas_name                       varchar(50)

 

)

 

AS

 

BEGIN

 

                --declare @serial_no int

 

                select @serial_no=serial_no FROM tbl_Cyl_gas_master WHERE serial_no = @serial_no

 

               

 

                IF(@action ='add')

 

                BEGIN

 

                                                IF NOT EXISTS(SELECT * FROM tbl_Cyl_gas_master WHERE gas_name = @gas_name )

 

                                                                INSERT INTO tbl_Cyl_gas_master(gas_name,tran_date) VALUES (@gas_name,GEtDATE())

 

                                                                --SET @Newserial_no = SCOPE_IDENTITY()

                                                ELSE

 

                                                                RAISERROR('TRANSACTION ALREADY EXISTS',16,1)

 

 

                                end       

 

                ELSE IF @action='update'

 

                BEGIN

                               

                                IF EXISTS(SELECT * FROM tbl_Cyl_gas_master WHERE serial_no = @serial_no )

 

                                begin

                                                IF NOT EXISTS(SELECT * FROM tbl_Cyl_gas_master WHERE gas_name = @gas_name )

                                                --select serial_no ,gas_name from tbl_Cyl_gas_master where serial_no=@serial_no

 

                                                UPDATE tbl_Cyl_gas_master set gas_name=@gas_name WHERE serial_no=@serial_no

 

                                                --select serial_no ,gas_name from tbl_Cyl_gas_master where serial_no=@serial_no

                                                ELSE

                                                                RAISERROR('TRANSACTION ALREADY EXISTS',16,1)

 

                                end

 

 

                END

 

ELSE if @action='delete'

 

                begin

 

                                if  exists( select * from tbl_cyl_gas_master where serial_no=@serial_no )

 

                                begin

 

                                update tbl_Cyl_gas_master set gas_name='Deleted'

 

                                                where serial_no=@serial_no

 

                --DELETE gas_name from tbl_Cyl_gas_master where serial_no=@serial_no

 

                                end

 

                end

 

                else

 

                                select serial_no,gas_name from tbl_cyl_gas_master where serial_no=@serial_no

 

                END

 

 

-------------------------------------------------------------------------------------------------------------------------------------

               

                                  

        

   

Gas Master



       

                    

                                                                                                                                                        

                                                                                       

          
Action 

                    <asp:DropDownList ID="ddl1" runat="server" AutoPostBack ="true" style="height: 22px">                        

                    <asp:ListItem>View

                    <asp:ListItem>Add

                    <asp:ListItem>Modify

                    <asp:ListItem>Delete  

                    

 

                   
                    <asp:Label ID="lbl1" runat="server" Text="lbl1">Serial Number

                    		  

  

                    <asp:DropDownList ID="ddl2" runat="server" AutoPostBack="true"  Height="23px" Width="250px">

                   

                   

                    <asp:TextBox ID="txtsno" runat="server" Height="18px" Width="250px" >

                    

                    
                    <asp:Label ID="lbl2" runat="server" Text="lbl2">Gas name

                    		 

                    <asp:TextBox ID="txtgasname" runat="server" Height="18px" Width="250px" > 

 

                   


        



       

       



           

                                     

                                                         

                           
                    <asp:Button ID="btnmodify" Font-Bold  ForeColor="DarkRed"  runat="server" Text="Apply" Width="62px"  />

                    		<asp:Button ID="btnreset" Font-Bold  ForeColor="DarkRed" runat="server" Text="Reset" Width="62px" />

                   


       



       



------------------------------------------------------------------------------------------------------------------------------------------------------

        If ddl1.Text = "Add" Then

            Dim sQry As String

            Dim scmd As New SqlCommand

            Dim scn As New SqlConnection(ConfigurationManager.ConnectionStrings("sastrystring").ConnectionString)

            scn.Open()

            Dim cmd As New SqlCommand

            cmd.Connection = scn

            sQry = "insert into  jpm_sastry  select '" & txtsno.Text & "','" & txtgasname.Text & "'"

            cmd.CommandText = sQry

            Dim rs As SqlDataReader = cmd.ExecuteReader()

            scn.Close()

            MsgBox("Recored inserted successfully" & txtsno.Text & txtgasname.Text)

            'txtgasname.Text = ""

            'txtsno.Text = ""

            Response.Redirect(HttpContext.Current.Request.Url.ToString(), True)

 

        ElseIf ddl1.Text = "Modify" Then

 

            Dim sQry As String

            Dim scmd As New SqlCommand

            Dim scn As New SqlConnection(ConfigurationManager.ConnectionStrings("sastrystring").ConnectionString)

            scn.Open()

            Dim cmd As New SqlCommand

            cmd.Connection = scn

            MsgBox(ddl2.Text)

            sQry = "update  jpm_sastry  set dname  ='" & txtgasname.Text & "' where sno='" & ddl2.Text & "'"

            cmd.CommandText = sQry

            Dim rs As SqlDataReader = cmd.ExecuteReader()

            scn.Close()

            MsgBox("Recored updated successfully " & txtsno.Text & txtgasname.Text)

            'ddl1.Visible = True

            Response.Redirect(HttpContext.Current.Request.Url.ToString(), True)

 

        ElseIf ddl1.Text = "Delete" Then

 

            Dim sQry As String

            Dim scmd As New SqlCommand

            Dim scn As New SqlConnection(ConfigurationManager.ConnectionStrings("sastrystring").ConnectionString)

            scn.Open()

            Dim cmd As New SqlCommand

            cmd.Connection = scn

            sQry = "delete from   jpm_sastry  where sno =  '" & ddl2.Text & "'"

            cmd.CommandText = sQry

            Dim rs As SqlDataReader = cmd.ExecuteReader()

            scn.Close()

            MsgBox("Recored deleted successfully")

            'ddl1.Visible = True

            Response.Redirect(HttpContext.Current.Request.Url.ToString(), True)

 

        End If

 

    End Sub

---------------------------------------------------------------------------------------------------------------------------------------

 

解决方案

You may required the following vb.net code for your storedprocedure.

Dim spName As String = "gas2"
Dim sqlConnection As New SQLClient.SqlConnection("ConnectionString")
Dim sqlCommand As New SQLClient.SqlCommand

sqlCommand.Connection = sqlConnection
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.CommandText = spName

sqlCommand.Parameters.AddWithValue("@action", Action)
sqlCommand.Parameters.AddWithValue("@serial_no", serial_no)
sqlCommand.Parameters.AddWithValue("@gas_name", gas_name)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()


write the name of the stored procedure on sQry and change sqlcommand.commandtype to commandtype.storedprocedure. this should work.


这篇关于如何在vb.net中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆