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.

Question

1.00/5 (1 vote)

See more:

SQL

CREATE TABLE TBL_CYL_GAS_MASTER

(

SERIAL_NO INT PRIMARY KEY IDENTITY(1,1),

GAS_NAME VARCHAR(100),

TRAN_DATE DATETIME

)

SQL

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

ASP.NET

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

C#

        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 19:29pm

vanarajranjit

Updated 4-May-14 19:49pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 5-May-14 2:14am

No one is interested in code 'convertion'. It doesn't work like this!

vanarajranjit 5-May-14 2:23am

Please try

1 solution

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)

Maciej Los · Answer 1 · 2014-05-04T20:15:00

Solution 1

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.

Posted 4-May-14 20:15pm

Maciej Los

Comments

vanarajranjit 5-May-14 2:21am

That is okay I already tried but it is not working once I got a correct code means I will apply through all my pages