使用 odbc 通过 addwithvalue 设置参数到存储过程
我使用下面的代码通过执行存储过程将值插入数据库。
Dim connstring As String = "dsn=test"
Dim oConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(connstring)
Dim com As New Odbc.OdbcCommand("{? = call sp_test1(?,?,?,?)}", oConn)
com.CommandType = CommandType.StoredProcedure
com.Parameters.AddWithValue("@Code", 2)
com.Parameters.AddWithValue("@Name", "2")
com.Parameters.AddWithValue("@Familly", "2")
com.Parameters.AddWithValue("@Pname", "2")
oConn.Open()
com.ExecuteNonQuery()
oConn.Close()
但我收到此错误
ERROR [HY105] [Microsoft][ODBC SQL Server Driver]Invalid parameter type
，这是我的存储过程
USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[sp_test1]
@Code bigint,
@Name nvarchar(50),
@Familly nvarchar(50),
@Pname nvarchar(50),
As
Begin
Insert Into test1
([Code],[Name],[Familly],[Pname])
Values
(@Code,@Name,@Familly,@Pname)
Declare @ReferenceID int
Select @ReferenceID = @@IDENTITY
Return @ReferenceID
End
有解决方案吗？
I use codes below to inserts values to database by executing stored procedure.
Dim connstring As String = "dsn=test"
Dim oConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(connstring)
Dim com As New Odbc.OdbcCommand("{? = call sp_test1(?,?,?,?)}", oConn)
com.CommandType = CommandType.StoredProcedure
com.Parameters.AddWithValue("@Code", 2)
com.Parameters.AddWithValue("@Name", "2")
com.Parameters.AddWithValue("@Familly", "2")
com.Parameters.AddWithValue("@Pname", "2")
oConn.Open()
com.ExecuteNonQuery()
oConn.Close()
but I got this error
ERROR [HY105] [Microsoft][ODBC SQL Server Driver]Invalid parameter type
and here is my stored procedure
USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[sp_test1]
@Code bigint,
@Name nvarchar(50),
@Familly nvarchar(50),
@Pname nvarchar(50),
As
Begin
Insert Into test1
([Code],[Name],[Familly],[Pname])
Values
(@Code,@Name,@Familly,@Pname)
Declare @ReferenceID int
Select @ReferenceID = @@IDENTITY
Return @ReferenceID
End
is there a solution ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
乍一看，存储过程的第一个参数为 bigint 类型，而它以输入形式给出了一个字符串值。
com.Parameters.AddWithValue("@Code", "2")将上面的内容更改为
com.Parameters.AddWithValue("@Code", 2)这样可以吗？
编辑：您可以将此行
Dim com As New Odbc.OdbcCommand("{? = call sp_test1(?,?,?,?)}", oConn)更改为
Dim com As New Odbc.OdbcCommand("sp_test1", oConn)At the first glance, the stored procedure has the 1st parameter of type
bigintwhereas it is given a string value in form of an input.com.Parameters.AddWithValue("@Code", "2")Change the above to
com.Parameters.AddWithValue("@Code", 2)Does that work?
EDIT: Can you change this line
Dim com As New Odbc.OdbcCommand("{? = call sp_test1(?,?,?,?)}", oConn)to
Dim com As New Odbc.OdbcCommand("sp_test1", oConn)发生错误的原因是您尚未传递在 CommandText 中指定的返回值参数，但参数集合中缺少该参数（命令文本中的第一个问号是返回值参数）。
由于参数必须按顺序传递，ODBC认为@Code是RETURN_VALUE，而@Name实际上是@Code，所以会出现类型不匹配错误。
您必须添加以下代码
Error occurs because you have not passed the Return Value parameter, which is specified in CommandText, but is missing from parameters collection (the first question mark in the command text IS the return value parameter).
Because parameters must be passed sequentially, ODBC considers, that @Code is the RETURN_VALUE, and @Name is actually @Code, so the type mismatch error occurs.
You must add the following code