CreateParameter gives '....incomplete or inconsistent info provided.' -AAARRGGGHHH!!

[Joss]

PLease help. I am trying to return a variable from a stored procedure but
banging my head against the wall tryingto get passed this error. I can see
from looking at many groups that it is a common problem but cannot see where
my code is wrong. The procedure should return one value which I can then use
in the code to update a form control, but, I keep getting 'Parameter object
is improperly defined.Incomplete or inconsistent information was provided.
Have tried using varchar instead of nvarchar and various cominations in the
brackets, but no good. If somebody can turn the light on it would be
appreciated.

thanks,
Jo



Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim faxout As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spcontactNo"

Set prm = cmd.CreateParameter("@faxno", adNvarChar, adParamOutput, 20, "")
'something wrong in this line. Tried many variations of parameters.

cmd.Parameters.Append prm

cmd.Execute
faxout = cmd.Parameters(1).value

MsgBox faxout




Alter procedure spContactNo

@faxno nvarchar(20) OUTPUT

as

Select @faxno = contFax

FROM dbo.tblContacts

where contCustomerCode= 'CUSTOMER'



return @faxno;

 

[Joss]

created a simple test table, stored proc and code using varchar and it
works. But changing to nvarchar brings the error back. Anybodyhave any
ideas? Interestingly, 'adnvarchar' does not get 'capitalised' as 'advarchar'
when it is entered. Perhaps it is not recognised in createparameter?

Jo

 

[Dirk Goldgar]

PLease help. I am trying to return a variable from a stored procedure
but banging my head against the wall tryingto get passed this error.
I can see from looking at many groups that it is a common problem but
cannot see where my code is wrong. The procedure should return one
value which I can then use in the code to update a form control, but,
I keep getting 'Parameter object is improperly defined.Incomplete or
inconsistent information was provided. Have tried using varchar
instead of nvarchar and various cominations in the brackets, but no
good. If somebody can turn the light on it would be appreciated.

thanks,
Jo



Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim faxout As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spcontactNo"

Set prm = cmd.CreateParameter("@faxno", adNvarChar, adParamOutput,
20, "") 'something wrong in this line. Tried many variations of
parameters.

cmd.Parameters.Append prm

cmd.Execute
faxout = cmd.Parameters(1).value

MsgBox faxout




Alter procedure spContactNo

@faxno nvarchar(20) OUTPUT

as

Select @faxno = contFax

FROM dbo.tblContacts

where contCustomerCode= 'CUSTOMER'



return @faxno;

I don't think there's any ADO constant "adNvarChar". I believe, though
I haven't checked it, that you should use the constant adVarWChar.
That's based on this table, provided by Carl Prothman:

http://www.carlprothman.net/Default.aspx?tabid=97

I don't think you need the '@' in front of the parameter name in your
call to CreateParameter, but I don't remember if that does any harm.

 

[Stephen Howe]

PLease help. I am trying to return a variable from a stored procedure but
banging my head against the wall tryingto get passed this error. I can see
from looking at many groups that it is a common problem but cannot see where
my code is wrong. The procedure should return one value which I can then use
in the code to update a form control, but, I keep getting 'Parameter object
is improperly defined.Incomplete or inconsistent information was provided.

So why bother?

Do this

1. Abandon Creating and Appending the Parameter.
Instead call Parameter.Refresh() on setting up.
It should setup the 1st parameter correctly
Verify that the SP works correctly

2. Then dump the 1 Parameter object and make sure you dump all fields:
direction, scale, width etc.

3. Comment out Parameter.Refresh() and setup the parameter with the same
info you learned in step 2. It is then faster as you are not making a round
trip to the Server. The only thing to change in step 2 is that ADO sets up
all Output parameters as InputOuput. But you can change that.

Stephen Howe

 

[William \(Bill\) Vaughn]

I have a free VB6 addin that generates the correct parameters collection
code for you on my website.
http://www.betav.com/Files/Content/tools/tools.htm

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Joss]

Thanks for your time and advice. It is a great help.

Bill, I have just downloaded the widget and will give it a whirl.

Jo