locked
call mssql storred procedure from asp VS2017 RRS feed

  • Question

  • User-1709999208 posted

    Hello. I'm trying to call a stored procedure in asp from MSSQL2016 but with no luck so far. It's complaining it's not finding stored procedure.

    Here is my code

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Web;
    
    public partial class sendcode3 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            HttpRequest cuRequest = HttpContext.Current.Request;
            String id = Server.UrlEncode(Request.QueryString["@login_id"]);
            String pw = Server.UrlEncode(Request.QueryString["@login_pw"]);
            String ip = "127.0.0.1";
            int ret = 0;
    
    
            try
            {
    
                //string connString = ConfigurationManager.ConnectionStrings["Data Source=DESKTOP-01V0HN7;Initial Catalog=RohanUser;Integrated Security=True"].ConnectionString;
                SqlConnection sqlConn = new SqlConnection("Data Source=DESKTOP-01V0HN7;Initial Catalog=RohanUser;User ID=sa;Password=******"); 
                
                    
                    var cmd = new SqlCommand();
                    
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "[dbo].[ROHAN3_SendCode](@login_id,@login_pw,@ip,@ret)";
                    cmd.Parameters.AddWithValue("@login_id", id);
                    cmd.Parameters.AddWithValue("@login_pw", pw);
                    cmd.Parameters.AddWithValue("@ip", ip);
                    cmd.Parameters.Add("@ret", SqlDbType.Int).Direction = ParameterDirection.Output;
    
                    cmd.Connection = sqlConn;
                    sqlConn.Open();
    
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        ret = -202;
                    }
                    
                    cmd.Connection.Close();
                    sqlConn.Close();
    
                    Response.Write(ret);
                
            }
    
    
            catch (Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }
    }

    My storred procedure looks like this:

    /*    ==Scripting Parameters==
    
        Source Server Version : SQL Server 2016 (13.0.4001)
        Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
        Source Database Engine Type : Standalone SQL Server
    
        Target Server Version : SQL Server 2017
        Target Database Engine Edition : Microsoft SQL Server Standard Edition
        Target Database Engine Type : Standalone SQL Server
    */
    
    USE [RohanUser]
    GO
    /****** Object:  StoredProcedure [dbo].[ROHAN3_SendCode]    Script Date: 8/14/2017 6:42:45 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    
    
    
    
    
    ALTER    PROCEDURE [dbo].[ROHAN3_SendCode]
    (
    	@login_id	VARCHAR(20)
    	,@login_pw	VARCHAR(50)
    	,@ip		VARCHAR(20)
    	,@ret		int	output
    )
    AS
    SET NOCOUNT ON
    
    --declare	@ret		int
    --	,@species	int
    --	,@grade		tinyint
    --	,@bill_no	int
    declare @species	int
    	,@grade		tinyint
    	,@bill_no	int
    
    -- -1 ??? ? ?? ?????.
    -- -2 ????? ????.
    -- -3 Sanctioned or not allowed to have that grade
    -- -4 Banned IP	
    -- -5 Deleted User
    exec @ret = LOCAL3_CheckAuth @login_id, @login_pw, @ip, @grade output, @species output, @bill_no output
    if @ret <> 0 return @ret		
    
    -- -201 ??? ?? ??? ???.
    -- -202 ?? ??? ??? ???.
    if dbo.IsSafeLogin(@species) = 0	return -202
    
    declare	@user_id	int
    exec @ret = LOCAL3_SetLogin @login_id, @login_pw, @grade, @species, @bill_no, @user_id output, NULL
    if @ret <> 0 return @ret
    
    -- ?? ??(6??)
    declare	@code	int
    set @code = cast(rand() * 1000000 as int)
    
    -- ?? ??? ??
    -- -203 ??? ???(30? ?? ??? ??)
    exec @ret = LOCAL3_UpdateCode @user_id, @login_id, @code
    if @ret <> 0 return @ret
    
    set @ret = 0
    return 0
    
    
    
    
    
    
    
    
    

    and my connection strings:

    <connectionStrings>
        <add name="RohanUser" providerName="System.Data.SqlClient" connectionString="Data Source=DESKTOP-01V0HN7;Initial Catalog=RohanUser;Integrated Security=True; User ID=sa;Password=*******"/>
        </connectionStrings>

    The message I get is the following (short portion of it):

    Could not find stored procedure ''. at System.Data.SqlClient.SqlConnection.OnError

    What am I doing wrong?

    Monday, August 14, 2017 3:52 PM

All replies

  • User347430248 posted

    Hi Nick88,

    on line below , just try to add the procedure name without parameters name like below.

    cmd.CommandText = "ROHAN3_SendCode";
    

    also try to check that you are able to execute the procedure from SQL.

    let us know if that work for you or not.

    if you want example of calling store procedure then you can refer link below.

    Select SQL Server Stored Procedures using ASP.Net Example

    Regards

    Deepak

    Tuesday, August 15, 2017 12:59 AM
  • User-1709999208 posted

    If I do that it complains about not passing any parameter values, more specific, @login_id. But it should get the login_id from the table.

    Even if I try from SQL highlighting the stored procedure and run it it complains about the same thing. So I guess the problem it's with the stored procedure itself not with the code. If I select everything from the stored procedure runs fine.

    Tuesday, August 15, 2017 6:29 AM
  • User347430248 posted

    Hi Nick88,

    I can see that you are not passing the value when you execute it in sql

    exec @ret = LOCAL3_CheckAuth @login_id, @login_pw, @ip, @grade output, @species output, @bill_no

    can you try to pass it like below.

    exec @ret = LOCAL3_CheckAuth @login_id='abc', @login_pw='xyz', @ip='198.168.10.0', @grade output='demovalue', @species output='demovalue', @bill_no=1234

    try to check it on your side and let us know whether it work for you or not.

    if you can successfully execute it in SQL then you can also call it from code.

    Regards

    Deepak

    Thursday, August 17, 2017 3:10 AM