过程或函数需要未提供的参数 [英] Procedure or function expects parameter which was not supplied

问题描述

我在VB.Net中有一个链接到SQL DB的应用程序。



现在我正在尝试将信息从excel导入DataGridView并从那里到数据库。



我总是收到这个错误：



System.Data.SqlClient.SqlException（0x80131904）：过程或函数'InsertDataIntoDevicesLDL'需要参数'@SN'。没有提供。





我的程序是：

I have an application in VB.Net linked to a SQL DB.

Now I'm trying to import info from an excel to a DataGridView and from there to the database.

I'm always getting this error:

System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'InsertDataIntoDevicesLDL' expects parameter '@SN'. which was not supplied.


The procedure I have is:

CREATE PROCEDURE [dbo].[InsertDataIntoDevicesLDL]

@SN         NCHAR (10), 
@IdClass    INT,        
@IdManu     INT,       
@IdModel    INT,    
@PartNum    NCHAR (10),  
@OrdNum     NCHAR (10), 
@ATCode     NCHAR (10),  
@Ticket     NCHAR (10),
@CreDate    DATETIME , 
@ExpeDate   DATETIME,   
@OBS        NCHAR (200), 
@IdClt      INT,        
@IdWh       INT,       
@Slot       NCHAR (10),
@IdLoc      INT,     
@IdSts      INT,
@IdLdl      INT      

AS

BEGIN

Insert into Equipamento (SerialNumber, ID_CLASS, ID_Manufacturer, ID_Model,ProductNumber, OrderNumber, ATCode, Ticket, CreationDate, ExpeditionDate, Observations, ID_CLT,ID_WH, Slot, ID_loc, ID_STS, ID_LDL)
    Values (@SN, @IdClass, @IdManu, @IdModel, @PartNum, @OrdNum, @ATCode, @Ticket, @CreDate, @ExpeDate, @OBS, @IdClt, @IdWh, @Slot, @IdLoc, @IdSts, @IdLdl )

END

在我的表格上：

And on the form I have:

MyConnection.Open()


       Try
           Using MyConnection

               Dim command As New SqlCommand
               command.Connection = MyConnection
               command.CommandType = CommandType.StoredProcedure
               command.CommandText = "InsertDataIntoDevicesLDL"


               For i As Integer = 0 To dgv_import.Rows.Count - 1
                   command.Parameters.AddWithValue("sn", dgv_import.Rows(i).Cells(0).Value)
                   command.Parameters.AddWithValue("PartNum", dgv_import.Rows(i).Cells(1).Value)
                   command.Parameters.AddWithValue("OrdNum", dgv_import.Rows(i).Cells(6).Value)
                   command.Parameters.AddWithValue("ATCode", dgv_import.Rows(i).Cells(7).Value)
                   command.Parameters.AddWithValue("Ticket", dgv_import.Rows(i).Cells(8).Value)
                   command.Parameters.AddWithValue("CreDate", dgv_import.Rows(i).Cells(9).Value)
                   command.Parameters.AddWithValue("ExpeDate", dgv_import.Rows(i).Cells(10).Value)
                   command.Parameters.AddWithValue("IdModel", dgv_import.Rows(i).Cells(11).Value)
                   command.Parameters.AddWithValue("IdClass", dgv_import.Rows(i).Cells(12).Value)
                   command.Parameters.AddWithValue("IdManu", dgv_import.Rows(i).Cells(13).Value)
                   command.Parameters.AddWithValue("IdClt", dgv_import.Rows(i).Cells(14).Value)
                   command.Parameters.AddWithValue("IdWh", dgv_import.Rows(i).Cells(15).Value)
                   command.Parameters.AddWithValue("IdSts", dgv_import.Rows(i).Cells(17).Value)
                   command.Parameters.AddWithValue("Slot", dgv_import.Rows(i).Cells(18).Value)
                   command.Parameters.AddWithValue("IdLoc", dgv_import.Rows(i).Cells(19).Value)
                   command.Parameters.AddWithValue("IdLdl", dgv_import.Rows(i).Cells(20).Value)
                   command.Parameters.AddWithValue("OBS", dgv_import.Rows(i).Cells(21).Value)
                   command.ExecuteNonQuery()

                   command.Parameters.Clear()

               Next
           End Using

       Catch ex As Exception
           MessageBox.Show(ex.ToString)
       End Try
       MyConnection.Close()

它可以很好地导入到DataGridView，但是当从DGV传递到数据库时它会显示错误。



但是，即使出现错误，所有信息都会传递给数据库。



我是什么尝试过：



我已经花了几个小时试图找出它发生的原因，没有运气，



你能帮忙找到造成这种情况的原因吗？

It works fine importing to the DataGridView, but when passing from the DGV to the DB it shows the error.

But, even with the error all of the information is being passed to the DB.

What I have tried:

I've been for a few hours trying to find out why its happening, with no luck,

Can you help finding what is causing this?

推荐答案

Quote：

你能帮忙找到造成这种情况的原因吗？

Can you help finding what is causing this?

不是真的，没有。

你正在调用一个名为 InsertDataIntoDeviceLDL （虽然你的代码称之为 InsertDataIntoDevicesLDL 所以其中一个是错的）并且它需要某些参数，其中一个被称为 @SN ，可能不包含空值。猜测，其中一行在第一列中有一个空格的单元格。

Not really, no.
You are calling a stored procedure called InsertDataIntoDeviceLDL (though your code calls it InsertDataIntoDevicesLDL so one of them is wrong) and it expects certain parameters, one of which is called @SN which may not contain a null value. At a guess, one of the rows has a cell with a null in the first column.

Quote：

command.Parameters.AddWithValue("sn", dgv_import.Rows(i).Cells(0).Value)

您的存储过程使用参数 @SN ，但您的vb代码定义参数 sn 。

首先要做的事情是匹配参数名称。

Your stored procedure uses parameter @SN, but your vb code defines parameter sn.
The first thing to do is to match parameters names.

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