CODEWITHSUNDEEP
c#asp.netsql-serverstored-procedures
Ishaan shringi
Ishaan shringi

Reputation: 47

ASP.NET(C#) catching exception while using stored procedure (sql database)

The code is as follows... I tried using debug points and it shows:

Procedure or function 'usp_select_legal1_data' expects parameter '@tower', which was not supplied.

C# code:

try {
    LegalView.ActiveViewIndex = 2;

    String tw2 = TextBox3.Text;
    SqlDataSource SqlDataSource2 = new SqlDataSource();
    SqlDataSource2.ID = "SqlDataSource2";
    this.Page.Controls.Add(SqlDataSource2);
    SqlDataSource2.ConnectionString = System.Configuration.ConfigurationManager
        .ConnectionStrings["constr"].ConnectionString;
    SqlDataSource2.SelectParameters.Add("@tower", tw2);
    SqlDataSource2.SelectCommand = "usp_select_legal1_data";

    GVCaseTowerWise.DataSource = SqlDataSource2;
    GVCaseTowerWise.DataBind();

    if (GVCaseTowerWise.Rows.Count == 0) {
        ScriptManager.RegisterClientScriptBlock(
            this,
            this.GetType(),
            "alertMessage",
            "alert('No cases for this tower exist in the database')",
            true);
    }
} catch (Exception ex) {
    ScriptManager.RegisterClientScriptBlock(this, this.GetType(),
            "alertMessage", "alert('error while getting data')", true);
}

And this is my stored procedure:

ALTER PROCEDURE [dbo].[usp_select_legal1_data]
    @tower nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT distinct
      [tower_to]
      ,[tower_from]
      ,[sy_no]
    FROM [dbo].[legal1]
    WHERE ((tower_to = @tower) or (tower_from = @tower))
END

Upvotes: 1

Views: 139

Answers (3)

Ishaan shringi
Ishaan shringi

Reputation: 47

I found the answer to my question. The problem was with defining the stored procedure. it was still treating the procedure as text command. here is what i used

SqlDataSource2.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;

and now it is working.

Upvotes: 0

n00b
n00b

Reputation: 1852

The problem is putting @, because the parameter name is tower. please see the MSDN documentation below https://msdn.microsoft.com/en-us/library/f58z9c1a(v=vs.110).aspx

Upvotes: 1

Alex Sikilinda
Alex Sikilinda

Reputation: 3013

Change 

SqlDataSource2.SelectParameters.Add("@tower", tw2);

to 

SqlDataSource2.SelectParameters.Add("tower", tw2);

Upvotes: 3

Related Questions