How do I update a BIT column from VBA using a stored procedure

Question

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".

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 !

Jayasurya Satheesh · Accepted Answer · 2018-02-13 07:09:18Z

1

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

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

answered Feb 13, 2018 at 7:09

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary Heath Over a year ago

That worked, superb, thank you, but why ?!? I mean, if you have to be so explicit when defining Columns and DataTypes why does the system not work when you actually use them ?!?!? MS can be very bizarre sometimes !!!

Sandip - Frontend Developer · Accepted Answer · 2018-02-13 07:09:00Z

0

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.

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

answered Feb 13, 2018 at 7:09

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

1 Comment

Sandip - Frontend Developer Over a year ago

when you pass true/false your parameter must remain same : @pFixtureList BIT

Collectives™ on Stack Overflow

How do I update a BIT column from VBA using a stored procedure

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related