Parameter not properly defined error - 3708???

[ChanHY]

Hi,

I have retrieved some recordset via a stored procedure and they are correctly populated on a form in an Access ADP. Then I attempted to save these recordsets with some other newly defined fields into a temp table with the following code but I have the error 3708 stating that parameter not properly defined or inconsistent. Please Help....

rivate Sub SaveToTempTrial_Click()

Dim strMsg As String
Dim strOK As String
Dim cmd As ADODB.Command


On Error GoTo HandleErr



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

.Parameters.Append .CreateParameter( _
"@UpfrontFeeID", adInteger, adParamInput, , Me.UpfrontFeeID)
.Parameters.Append .CreateParameter( _
"@PrimaryAccountNo", adChar, adParamInput, , Me.PrimaryAccountNo)
.Parameters.Append .CreateParameter( _
"@Amount", adCurrency, adParamInput, , Me.Amount)
.Parameters.Append .CreateParameter( _
"@FeeCode", adInteger, adParamInput, , Me.FeeCode)
.Parameters.Append .CreateParameter( _
"@ReconciledNominalDate", adDBTimeStamp, adParamInput, , Me.ReconciledNominalDate)
.Parameters.Append .CreateParameter( _
"@UpfrontNominalFeeID", adInteger, adParamInput, , Me.UpfrontNominalFeeID)
.Parameters.Append .CreateParameter( _
"@ReconcileNominal", adBoolean, adParamInput, , Nz(Me.ReconcileNominal, 0))
.Parameters.Append .CreateParameter( _
"@NominalAmount", adCurrency, adParamInput, , Me.NominalAmount)
.Parameters.Append .CreateParameter( _
"@ReconcileMAP33", adBoolean, adParamInput, , Nz(Me.ReconcileMAP33, 0))
.Parameters.Append .CreateParameter( _
"@ReconciledMAP33Date", adDBTimeStamp, adParamInput, , Me.ReconciledMAP33Date)
.Execute

End With
Set cmd = Nothing

ExitHere:

Exit Sub

HandleErr:
MsgBox Err & ": " & Err.Description, , "Insert to Temp error"
Resume ExitHere
Resume


End Sub

Alter Procedure "spInsertTrialSample2"(
@UpfrontFeeID int,
@PrimaryAccountNo char,
@Amount money,
@FeeCode int,
@ReconciledNominalDate datetime = Null,
@UpfrontNominalFeeID int,
@ReconcileNominal bit,
@NominalAmount money,
@ReconcileMAP33 bit,
@ReconciledMAP33Date datetime = Null)
As
Set Nocount on

Create table #tempTrial
(UpfrontFeeID int,
PrimaryAccountNo char,
Amount money,
FeeCode int,
ReconciledNominalDate datetime,
UpfrontNominalFeeID int,
ReconcileNominal bit default 0,
NominalAmount money,
ReconcileMAP33 bit default 0,
ReconciledMAP33Date datetime)

Insert into #tempTrial
(UpfrontFeeID,
PrimaryAccountNo,
Amount,
FeeCode,
ReconciledNominalDate,
UpfrontNominalFeeID,
ReconcileNominal,
NominalAmount,
ReconcileMAP33,
ReconciledMAP33Date)
Values(
@UpfrontFeeID,
@PrimaryAccountNo,
@Amount,
@FeeCode,
@ReconciledNominalDate,
@UpfrontNominalFeeID,
@ReconcileNominal,
@NominalAmount,
@ReconcileMAP33,
@ReconciledMAP33Date)


return

 

[cmmrfrds]

One item I noticed is a character data type without a length. Try supplying the length i.e. 20 or whatever it is.

.Parameters.Append .CreateParameter( _
"@PrimaryAccountNo", adChar, adParamInput, , Me.PrimaryAccountNo,20)

 

[Nowell]

Hi chaps,

Yeah i had this problem and it was as cmmrfrds says, the size of the data type also needs to be specified. You can check whether the data type sizes are needed by checking how the parameters are implemented in the stored procedure.

Cheers

 

[ChanHY]

Thanks both of u. Now the problem is fixed. However, here's another one...
Once the form in Access is populated with the recordsets retreived from the temp table via stored procedure, how can I update the records from the form??

 

[cmmrfrds]

You could structure your own update sql in the Form's afterupdate event.

Example.
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset
Dim ret As Long
Set cn = CurrentProject.Connection
sql1 = " Insert into IDTable (ID, startdate, enddate) "
sql1 = sql1 + "Values ( "
sql1 = sql1 + "6, " + "'1/1/2001', " + "'2/1/2001' )"
ret = 0
cn.Execute Sql1, ret
Debug.Print "Records updated = "; ret

Of course, you will need the unique index value for the record you want to update.

 

[ChanHY]

Hi cmmrfrds,

However, I can't even click on the textboxes or checkboxes on the form to update the records. It just says Record is not updatable......please help......I have been stucked here for the last 2 days.....

Thanks a lot
Chanhy (Kirsten)

 

[cmmrfrds]

Access and ADO need to know in sql server which table and record to update. If the stored procedure is returning a temp table there is not sufficient information to do an update. The temp table would be better used for a report or informational only form. Base your update form on a recordset that Access/ADO can determine which table and record to update. Look at the data tab on the form and supply the unique table property. If you must use a temp table then you will need to make unbound fields on the form and handle the updates yourself through vba code.