使用 VB.NET 执行存储过程（更新表）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21975199/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute a stored procedure using VB.NET (update table)
提问by user3344443
My SQL Server stored procedure is
我的 SQL Server 存储过程是
ALTER PROCEDURE [dbo].[usp_updateBInfo]
@WhatTheyDo nVARCHAR(max),
@WhereTheyDo nVARCHAR(max),
@ID varchar
AS
begin
update tblBInfo
set
WhatTheyDo = @WhatTheyDo,
WhereTheyDo = @WhereTheyDo
where
ID = @ID
end
My VB code is
我的VB代码是
Dim SQLCONN As New SqlConnection
SQLCONN = New SqlConnection("Data Source=xxxxxx;Initial Catalog=xxxxx;Integrated Security=True")
Dim SQLCMD As New SqlCommand("usp_updateBInfo", SQLCONN)
SQLCMD.CommandType = CommandType.StoredProcedure
SQLCMD.Parameters.Add("@ID", SqlDbType.VarChar).Value = "1C485D2C-F34D-45CE-8694-C74445DD108D"
SQLCMD.Parameters.AddWithValue("@WhatTheyDo", "abcdefg")
SQLCMD.Parameters.Add("@WhereTheyDo", SqlDbType.NVarChar).Value = "abcdefg"
SQLCMD.Connection.Open()
SQLCMD.ExecuteNonQuery()
SQLCMD.Connection.Close()
When I hit the "update" button, no change occurs, abcdefgis not passed to whattheydo or wheretheydo with primary key 1C485D2C-F34D-45CE-8694-C74445DD108D
当我点击“更新”按钮时，没有发生任何变化，abcdefg不会通过主键传递给 whattheydo 或 wheretheydo1C485D2C-F34D-45CE-8694-C74445DD108D
May I ask where is wrong with my code? I'm pretty sure the button is linked to VB code.
请问我的代码哪里有问题？我很确定该按钮已链接到 VB 代码。
Thanks for any suggestions!
感谢您的任何建议！
回答by Steve
You don't set the size of the parameter @ID, this means that only a single char is passed to the stored procedure, and, of course, no record will be found to update
你不设置参数的大小@ID，这意味着只有一个字符被传递到存储过程，当然，不会找到更新的记录
In T-SQL, if you define a parameter NVARCHAR without a size and assign a string to it, is not like declaring and then assigning a string in VB.NET. The string will not be automatically expanded to whatever size the string is.
在 T-SQL 中，如果定义一个没有大小的参数 NVARCHAR 并为其分配一个字符串，则不像在 VB.NET 中声明然后分配一个字符串。该字符串不会自动扩展到该字符串的任何大小。
To correct the problem change your stored procedure to something like this
要纠正问题，请将您的存储过程更改为这样的
ALTER PROCEDURE [dbo].[usp_updateBInfo]
@WhatTheyDo nVARCHAR(max), @WhereTheyDo nVARCHAR(max), @ID nvarchar(30)
AS
....
or whatever size you have defined the field ID ( and use the correct type nvarchar/varchar)
或您定义的字段 ID 的任何大小（并使用正确的类型 nvarchar/varchar）
