Parameter Object inproperly defined

[Guest]

I have the following command button code to run an Insert SP:
im cmd As ADODB.Command
Dim pmt As ADODB.Parameter

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

''Create Paramters required by the SP
Set pmt = cmd.CreateParameter("@ContactID", adInteger, adParamInput, ,
Me.ContactID)
cmd.Parameters.Append pmt

Set pmt = cmd.CreateParameter("@ScheduleID", adInteger, adParamInput, ,
Me.ScheduleID)
cmd.Parameters.Append pmt

Set pmt = cmd.CreateParameter("@PO", adChar, adParamInput, , Me.PONum)
cmd.Parameters.Append pmt

''Execute the SP
cmd.Execute

But i keep getting a "parameter Object inproperly defined" for the set pmt
line for @PO, the insert works fine manually running the SP, but throws an
error using the above VB with a button. PONum is a standard form textbox and
the underlying datatype is char. Please help. thanks

 

[Sylvain Lafontaine]

Personnally, I always use adVarChar for char() and varchar() and adVarWChar
for nchar() and nvarchar(). Also, in your case, maybe it's the size that
it's missing.

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

Sub liste_des_parametres(nom_procedure As String)

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

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

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.

 

[Guest]

how about for ntext, should the length be the same as the datatype, in this
case 16?

 

[Sylvain Lafontaine]

I don't know, I never to pass back a text or ntext field as a parameter
instead of using a recordset and I don't think that would be a god idea to
do so.

A quick test show that ntext is passed as a string with a maximum size of
about 1Gig. However, it is also my understanding that the SET TEXTSIZE
option should have an effect on the effective size that will be passed back.

 

[aaron.kempf]

do you really do your statements like this?

I don't use a parameters BS; i just think that it's 100 times easier to
run this


strSql = "EXEC mySproc 10,12,102,123123,'hello world'"
Docmd.Runsql StrSql

it just seems a whole lot easier.

I do believe that if you're having problems reading and writng to TEXT
fields I think that you need to lookup

TEXTPTR
READTEXT
WRITETEXT methods of TSQL

I wish i remembered the exact syntax; hope that helps

-Aaron

 

[Vadim Rapp]

All it takes is

currentproject.connection.execute "exec sproc_NewEnrollment contactid,
ScheduleID, PONum

(if any of the parameters are literals, wrap in quotes: ...., "'" & PONum&
"'"

Vadim Rapp

S> I have the following command button code to run an Insert SP:
S> im cmd As ADODB.Command
S> Dim pmt As ADODB.Parameter

S> Set cmd = New ADODB.Command
S> Set cmd.ActiveConnection = CurrentProject.Connection
S> cmd.CommandType = adCmdStoredProc
S> cmd.CommandText = "sproc_NewEnrollment"

S> ''Create Paramters required by the SP
S> Set pmt = cmd.CreateParameter("@ContactID", adInteger, adParamInput,
S> , Me.ContactID)
S> cmd.Parameters.Append pmt

S> Set pmt = cmd.CreateParameter("@ScheduleID", adInteger,
S> adParamInput, , Me.ScheduleID)
S> cmd.Parameters.Append pmt

S> Set pmt = cmd.CreateParameter("@PO", adChar, adParamInput, ,
S> Me.PONum)
S> cmd.Parameters.Append pmt

S> ''Execute the SP
S> cmd.Execute

S> But i keep getting a "parameter Object inproperly defined" for the set
S> pmt line for @PO, the insert works fine manually running the SP, but
S> throws an error using the above VB with a button. PONum is a standard
S> form textbox and the underlying datatype is char. Please help. thanks

With best regards, Vadim Rapp. E-mail: (e-mail address removed)