Click here to Skip to main content
15,898,861 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to pass parameters from asp.net to my stored procedure. The parameters would be the value selected by the user from the dropdownlists and then get data back from the stored procedure and display on a Gridview in asp.net when the user clicks the submit button on the webpage. An exception pops up by the Fill method when I try to debug it saying I need to pass parameters. The exception is copied below.

System.Data.SqlClient.SqlException: 'Procedure or function 'Submit' expects parameter '@Address', which was not supplied.'


What I have tried:

Below is my stored procedure


SQL
USE [Database]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [Submit]
    @Address varchar(12),
    @Number varchar(5),
    @Name varchar(24),
    @Type varchar(3)
    as 
    begin
        select a.*, b.*
        from Employee as a
        inner join Department as b
        on a.Number = b.Number
        where a.Address = @Address and a.Number = @Number and a.name = @Name and b.Type 
        = @Type 
    end


Below is my .cs code


C#
protected void Button1_Click1(object sender, EventArgs e)
            {
                con.Open();
                string s = "Submit";
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Submit";
                cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = DropDownList5.SelectedItem.Value;
                cmd.Parameters.Add("@Number", SqlDbType.VarChar).Value = DropDownList2.SelectedItem.Value;
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = DropDownList3.SelectedItem.Value;
                cmd.Parameters.Add("@Type", SqlDbType.VarChar).Value = DropDownList4.SelectedItem.Value;
                SqlDataAdapter da = new SqlDataAdapter(s, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
    
                GridView1.DataSource = ds;
                GridView1.DataBind();
                con.Close();
            }
Posted
Updated 19-Jun-18 7:40am
v4
Comments
Bryian Tan 19-Jun-18 12:21pm    
and your issue is?
Member 13863605 19-Jun-18 12:53pm    
An exception pops up by the Fill method when I try to debug it saying I need to pass parameters. The exception is copied below.

System.Data.SqlClient.SqlException: 'Procedure or function 'Submit' expects parameter '@Address', which was not supplied.'
OriginalGriff 19-Jun-18 12:22pm    
And?
What does it do that you didn't expect, or not do that your did?
What have you tried to find out why?
Where are you stuck?
What help do you need?

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. And that means we have no access to your data or your display - so we have no idea what should be happening, and much less what actually is!

Use the "Improve question" widget to edit your question and provide better information.
Member 13863605 19-Jun-18 12:54pm    
An exception pops up by the Fill method when I try to debug it saying I need to pass parameters. The exception is copied below.

System.Data.SqlClient.SqlException: 'Procedure or function 'Submit' expects parameter '@Address', which was not supplied.'
MadMyche 19-Jun-18 12:39pm    
Some sample data being passed would be great, along with whatever rows from tables a & b should match, as well as what are the results when you run this in SSMS or similar program....

1. Make sure your values are not null
2. Try the AddWithValue() method
C#
cmd.Parameters.AddWithValue("@ParameterName", ParameterValue);
 
Share this answer
 
Comments
Member 13863605 19-Jun-18 13:39pm    
That does not work. Same error. The exception is copied below.

System.Data.SqlClient.SqlException: 'Procedure or function 'Submit' expects parameter '@Address', which was not supplied.'
MadMyche 19-Jun-18 13:56pm    
And did you follow Rule #1?
Member 13863605 19-Jun-18 16:45pm    
yes, I did. The DataAdapter's Fill method is showing an exception.
MadMyche 19-Jun-18 17:11pm    
Yes, we understand that the Fill method is where the exception is thrown, and that the exception is that @Address was not supplied. This would infer that the value for @Address was NULL; contrary to Rule #1 which is the same as OG posted as solution 2
Quote:
An exception pops up by the Fill method when I try to debug it saying I need to pass parameters. The exception is copied below.

System.Data.SqlClient.SqlException: 'Procedure or function 'Submit' expects parameter '@Address', which was not supplied.'

Use the debugger to check the values that you are passing in: remember that a .NET null value will count as "parameter not supplied" as your SP does not allow NULL value parameters.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900