adLongVarChar parameter for TEXT data type size problem

[Keith G Hicks]

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

 

[Sylvain Lafontaine]

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.

 

[Keith G Hicks]

Thank you.

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.