如何使用存储过程从VBA更新BIT列

This looks like it should be so simple, but ... When I set a value in my 2nd parameter I get an error, 3421, telling me that my "value is the wrong type".

这看起来应该很简单,但是...当我在第二个参数中设置一个值时,我得到一个错误,3421,告诉我我的“值是错误的类型”。

This is my table:

这是我的表:

CREATE TABLE [dbo].[Emails]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [ManagerList] BIT NOT NULL, 
    [FixtureList] BIT NOT NULL, 
    [Shomatch] BIT NOT NULL, 
    [Newsletter] BIT NOT NULL, 
    [Turn] BIT NOT NULL, 
    [Started] BIT NOT NULL, 
    [Sent] BIT NOT NULL, 
    [Acknowledged] BIT NOT NULL, 
    [League] INT NOT NULL, 
    CONSTRAINT [FK_Emails_League] FOREIGN KEY ([League]) REFERENCES [Leagues]([Id]) 
)

This is my stored procedure :

这是我的存储过程:

CREATE PROCEDURE [dbo].[UpdateEmails]
    @pLeagueId INT,
    @pFixtureList BIT
AS
    UPDATE [dbo].[Emails]
    SET [FixtureList] = @pFixtureList
    WHERE [League] = @pLeagueId;

This is my code :

这是我的代码:

Option Explicit
Option Base 1
Option Compare Text

Sub UpdateEmailsFixtureList()

Set KA_Com = New ADODB.Command

KA_Com.CommandText = "UpdateEmails"
KA_Com.CommandType = adCmdStoredProc

Set KA_Parameter = KA_Com.CreateParameter(Name:="pLeagueId", Type:=adInteger)

KA_Com.Parameters.Append KA_Parameter
KA_Com.Parameters("pLeagueId").Value = KA_RS_Leagues![ID]

Set KA_Parameter = KA_Com.CreateParameter(Name:="pFixtureList", Type:=adBinary, Size:=1)

KA_Com.Parameters.Append KA_Parameter
KA_Com.Parameters("pFixtureList").Value = 1 <<< ERROR 3421 on this line

Set KA_RS_Leagues = KA_Com.Execute

End Sub

Thanks for your help, I can't understand how something that looks so simple can be so stubborn !

谢谢你的帮助,我无法理解看起来如此简单的事情会如此顽固!

2 个解决方案

#1

Instead of passing the Parameter as Bit, change it to Char and Pass change your Procedure as Below

不要将参数作为位传递,而是将其更改为Char和Pass,将过程更改为下方

CREATE PROCEDURE [dbo].[UpdateEmails]
    @pLeagueId int,
    @pFixtureList char(1)

AS

    UPDATE [dbo].[Emails]

    SET [FixtureList] = CASE @pFixtureList WHEN '1' THEN 1 ELSE 0 END

    WHERE [League] = @pLeagueId;

    GO

#2

Pass value to true OR false, as your type is BIT in database/sp (@pFixtureList bit) it will accept true/false as value and display 1/0 in table respectively.

将值传递给true或false,因为您的类型是数据库/ sp中的BIT(@pFixtureList位),它将接受true / false作为值并分别在表中显示1/0。

KA_Com.Parameters("pFixtureList").Value = true;

#1