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;
Jo
"Joss" <jo...@pleasereplytogroup.com> wrote in message
news:d3jd0d$kg4$1...@hercules.btinternet.com...
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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
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
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.
__________________________________
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:eHwSlHEQ...@tk2msftngp13.phx.gbl...
Bill, I have just downloaded the widget and will give it a whirl.
Jo
"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message
news:OCiFkOEQ...@TK2MSFTNGP14.phx.gbl...