×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Parameter not properly defined error - 3708???

Parameter not properly defined error - 3708???

Parameter not properly defined error - 3708???

(OP)
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

RE: Parameter not properly defined error - 3708???

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)

RE: Parameter not properly defined error - 3708???

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

RE: Parameter not properly defined error - 3708???

(OP)
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??

RE: Parameter not properly defined error - 3708???

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.

RE: Parameter not properly defined error - 3708???

(OP)
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)

RE: Parameter not properly defined error - 3708???

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close