过程或函数需要参数，该参数未提供。 [英] Procedure or function expects parameter, which was not supplied.

问题描述

我遇到传递参数以将数据从存储过程加载到RDLC图表的问题。

这是我的存储过程

i have a problem of passing parameter to load data from stored procedure to RDLC Chart.
this is my stored procedure

USE [COADB]
GO
/****** Object:  StoredProcedure [dbo].[DashPartial]    Script Date: 06/13/2017 02:57:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[DashPartial]
@from datetime,
@to datetime
as
begin
select description, SUM(amountpaid) as TotalIncome
from tblPayment where Cast(date as datetime) between @from and @to  group by description
end

我尝试过：



我用这段代码调用存储过程

What I have tried:

And i call the stored procedure with this code

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.ReportViewer1.RefreshReport()
        Dim constr As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand()
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = "DashPartial"
                cmd.Parameters.Add("@from", SqlDbType.DateTime)
                cmd.Parameters("@from").Value = dtfrom.Value

                cmd.Parameters.Add("@to", SqlDbType.DateTime)
                cmd.Parameters("@to").Value = dtTo.Value

                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                Me.DashPartialTableAdapter.Fill(Me.DsDash.DashPartial)
                Me.ReportViewer1.RefreshReport()
                con.Close()
            End Using
        End Using
    End Sub

我的代码缺少什么

请帮忙。谢谢

推荐答案

您正在设置连接（ con ）和命令（ cmd ），并将参数添加到命令中。



然后在命令上调用 ExecuteNonQuery ，这是一种错误的使用方法，如解决方案1中所述。



但真正的问题出现在下一行：你正在调用 Me.DashPartialTableAdapter.Fill ，这与您刚刚设置的命令无关，显然没有设置参数。



您需要在报告中设置参数表适配器代替：

You're setting up a connection (con) and a command (cmd), and adding the parameters to the command.

You then call ExecuteNonQuery on the command, which is the wrong method to use, as mentioned in solution 1.

But the real problem comes on the next line: you're calling Me.DashPartialTableAdapter.Fill, which has nothing to do with the command you've just set up, and obviously doesn't have the parameters set.

You need to set the parameters on the report table adapter instead:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Me.ReportViewer1.RefreshReport()
    Me.DashPartialTableAdapter.SelectCommand.Parameters.AddWithValue("@from", dtfrom.Value)
    Me.DashPartialTableAdapter.SelectCommand.Parameters.AddWithValue("@to", dtTo.Value)
    Me.DashPartialTableAdapter.Fill(Me.DsDash.DashPartial)
    Me.ReportViewer1.RefreshReport()
End Sub

原因是这一行：

The reason is this line:

cmd.ExecuteNonQuery()

注意，SqlCommand.ExecuteNonQuery Method（System.Data.SqlClient） [ ^ ]与 INSERT 或 UPDATE 语句一起使用。



而不是使用 SqlCommand.ExecuteReader方法（System.Data.SqlClient） [ ^ ]能够将数据加载到 DataSet 或 DataTable 使用加载 [ ^ ]方法。

Note, that SqlCommand.ExecuteNonQuery Method (System.Data.SqlClient)[^] is used with INSERT or UPDATE statement.

Instead of that, use SqlCommand.ExecuteReader Method (System.Data.SqlClient)[^] to be able to load data into DataSet or DataTable by using Load[^] method.

这篇关于过程或函数需要参数，该参数未提供。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！