MS Access VBA interpreting string from SQL as int?

Question

I have a SQL Server stored procedure that returns a varchar value to Access VBA, but I'm getting an error from Access saying it can't convert a varchar value to int. Nowhere is the return value specified as an integer, whether the procedure or in VBA. I've simplified the actual code for the sake of this post. The below is not the full code.

Stored procedure:

create procedure proc 
@CreateOrUpdate varchar(6) output
as
begin
select @CreateOrUpdate = 'Create'
return @CreateOrUpdate
end

In Access VBA:

Dim Com As ADODB.Command
Set Com = New ADODB.Command
With Com
    .ActiveConnection = Cnn  (Set elsewhere - No problems with this)
    .CommandType = adCmdStoredProc
    .CommandText = "proc"
    .Parameters.Append .CreateParameter("@CreateOrUpdate", adVarChar, adParamOutput, 6)
End With

Result: "Conversion failed when converting the varchar value 'Create' to data type int."

I have other return values that are also varchar with identical syntax and I'm not having any problems with them. Any help is appreciated.

"I have other return values that are also varchar with identical syntax and I'm not having any problems with them" I doubt this, unless they can be implicitly cast to an int; and then you have other problems. — Thom A
– Thom A ♦, Commented Nov 9, 2020 at 15:21
If you have "other return values that are also varchar", those will only work coincidentally if they're always convertible to an integer (including NULL values and empty strings/whitespace -- these will end up as 0). You should definitely remove these if they're used in RETURN statements. You can have any number of VARCHAR output parameters, though. — Jeroen Mostert
– Jeroen Mostert, Commented Nov 9, 2020 at 15:23

Thom A · Accepted Answer · 2020-11-09 15:19:05Z

2

The problem is your attempt to use RETURN to return a value for an OUTPUT parameter. RETURN is used to denote the success of a Procedure; 0 for success and anything else for failure. RETURN returns an int, so RETURN @CreateOrUpdate will implicitly try convert @CreateOrUpdate to an int (hence the failure).

Don't RETURN the OUTPUT value, just assign it:

CREATE PROC MyProc @CreateOrUpdate varchar(6) OUTPUT AS
BEGIN
    SELECT @CreateOrUpdate = 'Create';
END;

Though the above it completely pointless. What is it trying to achieve..?

answered Nov 9, 2020 at 15:19

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

1 Comment

Thom A Over a year ago

See my comment under your question, @BellaFiorenza .

Collectives™ on Stack Overflow

MS Access VBA interpreting string from SQL as int?

Stored procedure:

In Access VBA:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Stored procedure:

In Access VBA:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related