用SqlCommand传递参数不起作用 [英] Passing a parameter with SqlCommand is not working

问题描述

这是我的代码:

Here is the code I have:

Dim mySqlCommand As New SqlCommand()
Dim mySqlDataAdapter As New SqlDataAdapter()
mySqlCommand.CommandText = myCommandText
mySqlCommand.Connection = SecurityRolesConnection
mySqlCommand.Parameters.Add("@EmployeeID", SqlDbType.BigInt)
Long.TryParse(idTextBox.Text, myBigInt)
mySqlCommand.Parameters("@EmployeeID").Value = myBigInt
mySqlCommand.Prepare()
mySqlDataAdapter.SelectCommand = mySqlCommand
mySqlDataAdapter.Fill(myDataSet)
myTableView = myDataSet.Tables(0).DefaultView

程序在"mySqlDataAdapter.Fill(myDataSet)"语句中失败，并显示以下错误消息:
过程或函数"AllSecurityRolesAndSelected"期望参数"@EmployeeID"，但未提供."
当我将StoredProcedure更改为不查找此参数，并将有效的EmployeeID硬编码到StoredProcedure中，并且将VB代码更改为不尝试传递参数时，StoredProcedure和VB能够通信，并且在我的表单上使用DataGrid填充正确的数据.
我只是不知道为什么这段代码无法将@EmployeeID参数实际添加到SqlCommand中.
我需要做的任何帮助将不胜感激.

The program fails at the "mySqlDataAdapter.Fill(myDataSet)" statement with this error message:
"Procedure or function ''AllSecurityRolesAndSelected'' expects parameter ''@EmployeeID'', which was not supplied."
When I change the StoredProcedure to not look for this parameter, and hard code a valid EmployeeID into the StoredProcedure, and change the VB code to not try to pass a parameter, the StoredProcedure and VB are able to communicate, and the DataGrid on my form fills with the correct data.
I just have no idea why this code is not working to actually add the @EmployeeID parameter to the SqlCommand.
Any help with what I need to do would be greatly appreciated.

推荐答案

您的命令文本是什么?它是查询还是存储过程名称?如果要使用存储过程，则必须将CommandType属性显式设置为CommandType.StoredProcedure.

What is your commandtext? Is it a query, or is it a stored procedure name? If you''re trying to used a stored procedure, you have to explicity set the CommandType property to CommandType.StoredProcedure.

请共享您的存储过程.参数与您要传递的参数匹配

Please share your stored..procedure, and check your sp parameter is matching with the one you are passing

John，谢谢！有趣的是，此方法没有参数，但是我的命令文本(很抱歉，我在原始帖子中没有得到它)​​是"Execute AllSecurityRolesAndSelected".
将CommandType属性设置为CommandType.StoredProcedure之后，我取出了Execute，现在可以使用了！
再次感谢您！
维克(Vic)

John, Thank you! Interesting this worked with no parameters, but my command text (sorry I missed having it in my original post) was "Execute AllSecurityRolesAndSelected".
After setting the CommandType property to CommandType.StoredProcedure, I took the Execute out, and now it works!
Again, thank you!
Vic

这篇关于用SqlCommand传递参数不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！