how to call the stored procedure in vb.net

Question

0.00/5 (No votes)

See more:

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

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

Posted 4-May-14 20:10pm

vanarajranjit

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Senthil Sambandam · Answer 1 · 2014-11-15T15:38:00

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

VB

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()

ravikhoda · Answer 2 · 2014-05-04T21:00:00

Solution 1

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

Posted 4-May-14 21:00pm

ravikhoda

Comments

vanarajranjit 5-May-14 3:03am

Can you make I tried it's not working

vanarajranjit 5-May-14 3:24am

If you given means it helpful for me I will apply in all pages