Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures 
Author Message
 Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

I am using VB6 and Access 97 with ADO 2.1

I can successfully read Memo fields from an Access database using an
ADODB recordset.  The memo lines are at maximum 350 characters long.

I am trying to Add  and Update a Memo field in an Access 97 database
using ADO 2.1, though a stored procedure (query) in the access
database.  
 I cannot find a match between the  data type and the length using the
Parameter statement.  All my attempts so far have met with failure
usually due to wrong type of data or length mismatches.
The closest I have got so far is the code following.  gMEMOSIZE is the
size of the field returned to me when I used the OpenSchema to examine
the table I was using.  The data type was 129 (adChar) and the length
given (Maximum _Character_Size) was this very large number.  
Plugging these into the parameter statement and executing the program
came up with ...

"Run Time Error -2147217887 (80040e21) Invalid Precision Value"

and the program fails on the cm.Execute line

The offending code is.....

Global Const gMEMOSIZE = 2147483647

'  *********  code that doesn't work......

    Set cm = New ADODB.Command
    With cm
        .ActiveConnection = dbConn
        .CommandText = gSPFormFiltersUpdate
        .CommandType = adCmdStoredProc
    End With

    '
    '  Load the Parameters
    '
    cm.Parameters.Append cm.CreateParameter("pLabelList", adChar, _
adParamInput, gMEMOSIZE, frmFormFilters.txtLabelList)
    cm.Execute lRecsAffected

How do I update and / or add data to a Memo field in an Access
Database using a Parameter Statement trhough ADO?



Sun, 14 Apr 2002 03:00:00 GMT  
 Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures
John,
For Access 97 / Microsoft.Jet.OLEDB.3.51, use "adLongVarChar" for the data
type in the ADO Parameter

For Access 2000 / Microsoft.Jet.OLEDB.4.0 , use "adLongVarWChar" for the
data type in the ADO Parameter

http://www.able-consulting.com/ADODataTypeEnum.htm

--

Thanks,
Carl Prothman, MVP, CCP
http://www.able-consulting.com


Quote:
> I am using VB6 and Access 97 with ADO 2.1

> I can successfully read Memo fields from an Access database using an
> ADODB recordset.  The memo lines are at maximum 350 characters long.

> I am trying to Add  and Update a Memo field in an Access 97 database
> using ADO 2.1, though a stored procedure (query) in the access
> database.
>  I cannot find a match between the  data type and the length using the
> Parameter statement.  All my attempts so far have met with failure
> usually due to wrong type of data or length mismatches.
> The closest I have got so far is the code following.  gMEMOSIZE is the
> size of the field returned to me when I used the OpenSchema to examine
> the table I was using.  The data type was 129 (adChar) and the length
> given (Maximum _Character_Size) was this very large number.
> Plugging these into the parameter statement and executing the program
> came up with ...

> "Run Time Error -2147217887 (80040e21) Invalid Precision Value"

> and the program fails on the cm.Execute line

> The offending code is.....

> Global Const gMEMOSIZE = 2147483647

> '  *********  code that doesn't work......

>     Set cm = New ADODB.Command
>     With cm
>         .ActiveConnection = dbConn
>         .CommandText = gSPFormFiltersUpdate
>         .CommandType = adCmdStoredProc
>     End With

>     '
>     '  Load the Parameters
>     '
>     cm.Parameters.Append cm.CreateParameter("pLabelList", adChar, _
> adParamInput, gMEMOSIZE, frmFormFilters.txtLabelList)
>     cm.Execute lRecsAffected

> How do I update and / or add data to a Memo field in an Access
> Database using a Parameter Statement trhough ADO?



Sun, 14 Apr 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

2. Using ADO Command object and Access PROCEDURE to insert Memo/Binary fields

3. ADO Update (using VB) of Memo fields in Access 97 DB

4. ado recordsets from stored procedures, stored procedures have input parameters

5. ADO, Access, Memo-Field update error at size above 64K

6. Q:Sorting A Grid Bound To A Ado DataControl Using A Parameter'd Stored Procedure

7. ADO populating DataGrid from Recordset(data retrieved from stored procedure using 3 parameters)

8. How to passing parameters from and to a SQL Server stored procedure using ADO

9. Pass Null to stored procedure parameter using ADO?

10. Problem with ADO and Stored Procedures with parameters in an Access 97 database

11. Accessing Oracle Stored procedure output parameter from VB using RDO2.0

12. Updating data to a memo field using SQL UPDATE

 

 
Powered by phpBB® Forum Software