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

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

Post by Nige » Sun, 12 Aug 2001 09:48:59



/*
This is the Access PROCEDURE
Works w/Jet engine
DataType in Access table is Memo
*/
PARAMETERS m_rsp Binary;
INSERT INTO Responses ( xmlResponse )
VALUES (m_rsp);

'Use this in a VB form.
Private Sub cmdInsRsp_Click()
    On Error GoTo ErrorHandler

    Dim oConn As ADODB.Connection
    Dim oCmd As ADODB.Command

    Dim m_xmlResp As String
    m_xmlResp = xmlDOM.xml
    Set xmlDOM = Nothing

    Set oConn = New ADODB.Connection
    With oConn
        .ConnectionString = "Memo"
        .ConnectionTimeout = 10
        .Open
    End With

    Set oCmd = New ADODB.Command
    With oCmd
        .ActiveConnection = oConn
        .CommandText = "InsertResp"
        .Parameters.Append .CreateParameter("m_rsp", adLongVarWChar,
adParamInput, 63000, m_xmlResp)
        .Execute
    End With

    Exit Sub

ErrorHandler:

    Debug.Print Err.Description
End Sub

 
 
 

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

Post by Sukesh Hooga » Sun, 12 Aug 2001 12:53:53


Nigel

Try

With oCmd
        .ActiveConnection = oConn
        .CommandType =adCmdStoreProc
        .CommandText = "InsertResp"
        .Parameters.Append .CreateParameter("m_rsp", adLongVarWChar,
adParamInput, 63000, m_xmlResp)
        .Execute
    End With

Sukesh


Quote:> /*
> This is the Access PROCEDURE
> Works w/Jet engine
> DataType in Access table is Memo
> */
> PARAMETERS m_rsp Binary;
> INSERT INTO Responses ( xmlResponse )
> VALUES (m_rsp);

> 'Use this in a VB form.
> Private Sub cmdInsRsp_Click()
>     On Error GoTo ErrorHandler

>     Dim oConn As ADODB.Connection
>     Dim oCmd As ADODB.Command

>     Dim m_xmlResp As String
>     m_xmlResp = xmlDOM.xml
>     Set xmlDOM = Nothing

>     Set oConn = New ADODB.Connection
>     With oConn
>         .ConnectionString = "Memo"
>         .ConnectionTimeout = 10
>         .Open
>     End With

>     Set oCmd = New ADODB.Command
>     With oCmd
>         .ActiveConnection = oConn
>         .CommandText = "InsertResp"
>         .Parameters.Append .CreateParameter("m_rsp", adLongVarWChar,
> adParamInput, 63000, m_xmlResp)
>         .Execute
>     End With

>     Exit Sub

> ErrorHandler:

>     Debug.Print Err.Description
> End Sub


 
 
 

1. 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?

2. Error 823

3. No Resultset by using ADO 2.5 Command-Object with SQL2000 Stored Procedures

4. FoxPro vs. Access

5. SQL Server stored procedure return value - how to access from ADO Command object

6. What is the EditMask for 4 digit year date field?

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

8. ADO Command Object - accessing MS SQL Server Stored Procedure return value

9. VB - ADO --> Access using memo Fields

10. Using the ADO Command Object to send an Execute command

11. How to write Memo BLOB to DB using ADO -- how to convert binary to VARIANT

12. accessing stored procedure return value using ADO (2.0) command execute method