如何在vb.net中调用存储过程。附加一种形式的aspcode，vb代码，存储过程，table.please将代码转换为存储过程而不使用表调用。 [英] How to call the stored procedure in vb.net.here o attached one form of aspcode,vb code, stored procedure, table.please convert the code to stored procedure without using tables calling.
本文介绍了如何在vb.net中调用存储过程。附加一种形式的aspcode，vb代码，存储过程，table.please将代码转换为存储过程而不使用表调用。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
CREATE TABLE TBL_CYL_GAS_MASTER
(
SERIAL_NO INT PRIMARY KEY IDENTITY(1,1),
GAS_NAME VARCHAR(100),
TRAN_DATE DATETIME
)
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
<table bordercolor="gray" cellpadding="0" cellspacing="0" width="100%" border="1">
<tr><td width="100%" bgcolor="gray"><div align="center"><h1>Gas Master</h1></div> </td></tr>
<tr>
<td><div>
<table border="0" cellpadding="0" cellspacing="5">
<tr>
<td style="height: 37px; width: 4%;">Action</td>
<td style="height: 37px; width:20%;">
<asp:DropDownList ID="ddl1" runat="server" AutoPostBack ="true" style="height: 22px">
<asp:ListItem>View
<asp:ListItem>Add
<asp:ListItem>Modify
<asp:ListItem>Delete
</td>
</tr>
<tr>
<td style="height: 37px; width: 4%;">
<asp:Label ID="lbl1" runat="server" Text="lbl1">Serial Number
</td>
<td style="width: 20%; height: 37px;">
<asp:DropDownList ID="ddl2" runat="server" AutoPostBack="true" Height="23px" Width="250px">
<asp:TextBox ID="txtsno" runat="server" Height="18px" Width="250px" >
</td>
</tr>
<tr>
<td style="height: 34px; width: 4%;">
<asp:Label ID="lbl2" runat="server" Text="lbl2">Gas name
</td>
<td style="height: 34px; width: 20%;">
<asp:TextBox ID="txtgasname" runat="server" Height="18px" Width="250px" >
</td>
</tr>
</table>
</div>
</td>
</tr>
<tr>
<td align="center">
<div id="apply">
<table>
<tr>
<td style="width:40%">
<asp:Button ID="btnmodify" Font-Bold ForeColor="DarkRed" runat="server" Text="Apply" Width="62px" />
</td>
<td style="width:40%"><asp:Button ID="btnreset" Font-Bold ForeColor="DarkRed" runat="server" Text="Reset" Width="62px" />
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
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推荐答案
Please, read my comment to the question.
Here is main idea about: How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET[^]. Read related article and change the code to your needs.
Please, read my comment to the question.
Here is main idea about: How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET[^]. Read related article and change the code to your needs.
这篇关于如何在vb.net中调用存储过程。附加一种形式的aspcode，vb代码，存储过程，table.please将代码转换为存储过程而不使用表调用。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
