我创建了一个测试项目，该项目只是通过使用存储过程使DataGridView与Sql Server一起使用。我负责Employee类中的所有SQL Server方法，以及frmMain形式的datagridview活动。
本质上，我将在所有四个命令中使用一些相同的列。
我想一次定义每个参数，然后将其添加到需要它的所有命令中。问题是，我收到错误消息“ SqlParameter已被另一个SqlParameterCollection包含”。有人告诉我，对于使用该参数的每个命令，我都必须使用不同的名称。我可以看到情况如何，但是我希望那里的人知道如何使单列一参数方法起作用。
Employee Class
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Public Class Employees
'Declarations
Private dtEmployees As DataTable
Private daEmployeer As SqlDataAdapter
Const HotmixCn As String = "Data Source=MyServer;Initial Catalog=MyDatabase;User ID=USER;Password=password"
Private Cn As SqlConnection
'Properties
Public Property EmployeeList As DataTable
Get
Return dtEmployees
End Get
Set(value As DataTable)
dtEmployees = value
End Set
End Property
'Methods
Public Sub New()
' Try
Cn = New SqlConnection(HotmixCn)
daEmployeer = New SqlDataAdapter
Dim cmdSelectEmployees As New SqlCommand
Dim cmdInsertEmployee As New SqlCommand
Dim cmdUpdateEmployee As New SqlCommand
Dim cmdDeleteEmployee As New SqlCommand
'Configure the Select command (!! have to for SP)
With cmdSelectEmployees
.CommandType = CommandType.StoredProcedure
.CommandText = "uspTestGetEmployees"
.Connection = Cn
End With
--- Same thing for Insert, Update and Delete
'
'/// Add The Parameters To All Three Commands ///
Dim parm As SqlParameter
parm = New SqlParameter
With parm
.ParameterName = "@EmpNo"
.SqlDbType = SqlDbType.Int
.Direction = ParameterDirection.Input
.SourceColumn = "EmpNo"
End With
cmdInsertEmployee.Parameters.Add(parm)
cmdUpdateEmployee.Parameters.Add(parm)
'cmdDeleteEmployee.Parameters.Add(parm)
'
----- Similar for remaining parameters
'
'Include the individual commands in the dataadapter
daEmployeer.SelectCommand = cmdSelectEmployees
daEmployeer.UpdateCommand = cmdUpdateEmployee
daEmployeer.InsertCommand = cmdInsertEmployee
daEmployeer.DeleteCommand = cmdDeleteEmployee
'/// Fill the Datatable
dtEmployees = New DataTable
Cn.Open()
daEmployeer.Fill(dtEmployees)
Cn.Close()
'Catch ex As Exception
'MsgBox(ex.Message)
'End Try
End Sub
'Events
End Class
这是DataGridView附带的小代码：
Imports DataLayer
Public Class frmMain
Dim emp As New Employees
Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dgvEmployees.DataSource = emp.EmployeeList
End Sub
End Class
似乎您正在尝试将相同的参数添加到两个不同的SqlCommand中的两个不同的参数集合中。从错误消息中可以看到，这是不可能的。
可能的解决方法是创建原始命令的副本，然后将此副本添加到其他命令中。这是一项繁琐的工作，因此最好创建一个扩展方法来为您完成工作
Imports System.Runtime.CompilerServices
Module SqlClientExtensions
<Extension()>
Public Function Duplicate(ByVal src As SqlParameter) As SqlParameter
Dim copy = New SqlParameter(src.ParameterName,src.SqlDbType, src.Size, src.Direction,
src.IsNullable, src.Precision, src.Scale, src.SourceColumn,
src.SourceVersion, src.Value)
Return copy;
End Function
End Module
现在你可以写这样的东西
Dim parm As SqlParameter
parm = New SqlParameter
With parm
.ParameterName = "@EmpNo"
.SqlDbType = SqlDbType.Int
.Direction = ParameterDirection.Input
.SourceColumn = "EmpNo"
End With
cmdInsertEmployee.Parameters.Add(parm)
cmdUpdateEmployee.Parameters.Add(parm.Duplicate())
cmdDeleteEmployee.Parameters.Add(parm.Duplicate())
我还注意到SqlParameter实现了ICloneable接口。因此，乍一看，您似乎可以写这样的东西
Dim b = new SqlCommand()
Dim p = new SqlParameter()
p.ParameterName = "@Test"
b.Parameters.Add(p)
Dim b1 = new SqlCommand()
Dim p1 = CType(CType(p, ICloneable),SqlParameter)
b1.Parameters.Add(p1)
但这可以归结为原始错误“另一个SqlParameterCollection已经包含SqlParameter”（并且即使工作正常，我还是希望使用扩展方法来保持其清晰性）
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句