"Converting Data Type to Bit" after updating Database

Question

I have created 2 VBA routines that call 2 different SQL Server Stored Procedures which are virtually identical. However one routine was working to update a Column in the RecordSet, the other wasn't working. No idea at all what was wrong.

In order to possibly resolve it and in any case, to maybe improve it, I amended the RecordSet definition so that the Columns were changed from Bit to Char and I am now passing a "Y" or a "N" rather than 0 or 1.

Everything in the Database looks OK (details below), the Columns are declared as Char and it Builds & Deploys just fine. However, the VBA program is now throwing an error

"Run-time Error -2147217913(80040e07) Error converting data type char to bit."

Can anybody please tell me why it is doing this ?

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:=adChar, Size:=1)

KA_Com.Parameters.Append KA_Parameter
KA_Com.Parameters("pFixtureList").Value = "Y"

Set KA_Com.ActiveConnection = KA_DB

Set KA_RS_Emails = KA_Com.Execute ***<<<<< Error occurs here <<<<<***

End Sub

The new RecordSet Create ...

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

The Stored Procedure ...

CREATE PROCEDURE [dbo].[UpdateEmailsFixtureList]
    @pLeagueId int,
    @pFixtureList char(1)
AS
    UPDATE [dbo].[Emails]
    SET [FixtureList] = @pFixtureList
    WHERE [League] = @pLeagueId;

GO

You appear to be using "Recordset" to mean "database table", it makes the question kind of misleading. And the stored procedure you've shown is not the one you're calling. — GSerg
– GSerg, Commented Feb 16, 2018 at 11:07
As far as I understood they are the same thing ?!? I create the Database with Tables, but when I access them via VBA they are declared as RecordSets, are they not ?!? Am I making some kind of fundamental error :-o ?!?!? — Gary Heath
– Gary Heath, Commented Feb 16, 2018 at 11:10
Yes, you are. A table is an object in the database, and a recordset is a set of rows in the memory. A recordset does not care how it got its data, which can be by directly loading from a database table or view, by executing a query, by loading serialized data from a file, or by adding the rows manually from code. ADO provides recordsets because it's a much more useful concept at this level, but they are not interchangeable with database tables. The are other ways to access a database, too, where recordsets are not even a thing, but database tables always remain database tables. — GSerg
– GSerg, Commented Feb 16, 2018 at 11:23
Fair point, GSerg, thank you for the explanation and I see how I was confused :-) — Gary Heath
– Gary Heath, Commented Feb 16, 2018 at 11:27
For what it is worth, changing from BIT to CHAR(1) with Y and N is unlikely to offer any advantage whatsoever, as far as I can tell all you have done is increase the storage, and increase the allowable number of values. Also, you should always specify a length when using CHAR/NCHAR/VARCHAR/NVARCHAR. It would have been a much better idea to try and resolve whatever issue a BIT column was causing. — GarethD
– GarethD, Commented Feb 16, 2018 at 11:40

bastos.sergio · Accepted Answer · 2018-02-16 11:11:31Z

1

You're invoking a SP called UpdateEmails in your code...

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

But your SP is called

CREATE PROCEDURE [dbo].[UpdateEmailsFixtureList]

answered Feb 16, 2018 at 11:11

bastos.sergio

6,7644 gold badges29 silver badges36 bronze badges

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

1 Comment

Gary Heath Over a year ago

bastos.sergio I could kiss you !!! That line was leftover from when I tried to use just one procedure and to change the names of the RecordSets and Stored Procedures using Variables ... on another thread I found out it was an over-complicated way of doing things so went to separate Modules & Procedures, but completely overlooked that line !!!! Thank you Thank you Thank you ... a million times, thank you !!!

Collectives™ on Stack Overflow

"Converting Data Type to Bit" after updating Database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related