Parameter Object inproperly defined

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.
 
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
 
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)
 
Back
Top