I'm trying to run a sql stored procedure where one of the parameters is TEXT
datatype. Through some online research I found that the 2nd parameter in the
line below needs to be adLongVarChar to accomodate the backend's TEXT
datatype. But I have no idea what to put in the "size" parameter position.
I've tried a few kind of at random but get errors on the execute statement
that it's wrong. If I leave it blank it tells me it's not properly defined.

..Parameters.Append .CreateParameter("@FullNoticeText", adLongVarChar,
adParamInput, <WHAT GOES HERE?>, Trim(Me.FullNoticeText))

Can anyone tell me what value I need to use there?

Thanks,

Keith

Here's a little procedure to display the parameters and their properties for
a SP:

Sub ListOfParameters (ProcedureName As String)

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = ProcedureName

cmd.Parameters.Refresh

Dim p As ADODB.Parameter

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

Set cmd = Nothing

End Sub

For using this procedure, you must *not* give the prefix dbo. to the name of
the SP. See http://www.asp101.com/articles/john/adovbs/adojavas.inc for the
numerical values.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Keith G Hicks" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I'm trying to run a sql stored procedure where one of the parameters is
> TEXT
> datatype. Through some online research I found that the 2nd parameter in
> the
> line below needs to be adLongVarChar to accomodate the backend's TEXT
> datatype. But I have no idea what to put in the "size" parameter position.
> I've tried a few kind of at random but get errors on the execute statement
> that it's wrong. If I leave it blank it tells me it's not properly
> defined.
>
> .Parameters.Append .CreateParameter("@FullNoticeText", adLongVarChar,
> adParamInput, <WHAT GOES HERE?>, Trim(Me.FullNoticeText))
>
> Can anyone tell me what value I need to use there?
>
> Thanks,
>
> Keith
>
>

Thank you.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:(E-Mail Removed)...
> Here's a little procedure to display the parameters and their properties
for
> a SP:
>
> Sub ListOfParameters (ProcedureName As String)
>
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
>
> Set cmd.ActiveConnection = CurrentProject.Connection
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = ProcedureName
>
> cmd.Parameters.Refresh
>
> Dim p As ADODB.Parameter
>
> For Each p In cmd.Parameters
> Debug.Print "name = " & p.name
> Debug.Print "Direction = " & p.Direction
> Debug.Print "Type = " & p.Type
> Debug.Print "Size = " & p.Size
> Debug.Print "Precision = " & p.Precision
> Debug.Print "NumericScale = " & p.NumericScale
> Debug.Print
> Next
>
> Set cmd = Nothing
>
> End Sub
>
> For using this procedure, you must *not* give the prefix dbo. to the name
of
> the SP. See http://www.asp101.com/articles/john/adovbs/adojavas.inc for
the
> numerical values.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Keith G Hicks" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > I'm trying to run a sql stored procedure where one of the parameters is
> > TEXT
> > datatype. Through some online research I found that the 2nd parameter in
> > the
> > line below needs to be adLongVarChar to accomodate the backend's TEXT
> > datatype. But I have no idea what to put in the "size" parameter
position.
> > I've tried a few kind of at random but get errors on the execute
statement
> > that it's wrong. If I leave it blank it tells me it's not properly
> > defined.
> >
> > .Parameters.Append .CreateParameter("@FullNoticeText", adLongVarChar,
> > adParamInput, <WHAT GOES HERE?>, Trim(Me.FullNoticeText))
> >
> > Can anyone tell me what value I need to use there?
> >
> > Thanks,
> >
> > Keith
> >
> >
>
>