将参数从ASP.NET传递到SQL Server中的存储过程，并从存储过程中获取数据并在gridview上查看它 [英] Pass parameters from ASP.NET to stored procedure in SQL server and get data back from the stored procedure and view it on a gridview

问题描述

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

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

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

推荐答案

1。确保您的值不为空

2.尝试 AddWithValue（）方法

1. Make sure your values are not null
2. Try the AddWithValue() method

cmd.Parameters.AddWithValue("@ParameterName", ParameterValue);

Quote：

当我尝试调试它时，Fill方法弹出一个异常，说我需要传递参数。下面复制了例外。



System.Data.SqlClient.SqlException：'过程或函数'提交'期望参数'@Address'，这是未提供的。

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

使用调试器检查传入的值：记住.NET null 值将计为参数未提供因为您的SP不允许 NULL 值参数。

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.

这篇关于将参数从ASP.NET传递到SQL Server中的存储过程，并从存储过程中获取数据并在gridview上查看它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！