Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Unhappy Unanswered: INSERT SQL to Server via Command with NULL

    Hi,

    i hope so much you can help me.
    The whole day i tried to resolve a Problem.

    Via a ADODB.Command i tried to send a INSERT SQL to Oracle (9i).
    The SQL-String ist prepared
    "INSERT INTO [TBL] VALUES (:[FLD1],:[FLD2],:[FLD3])"

    The values are parameters.

    The Programm works fine. I get all Questions correct, i have tested with parameters, but the INSERT will not work.
    The Problem is, that (i.E. FLD3) is a nullable foreign key.

    I have tried it with DataTypeEnum adEmpty... this was a fatal error ^^
    I tried it with the orginal datatyp (Number) und value NULL und so Oracle talk instead to me:
    "ORA-02291 integrity constraint (string.string) violated - parent key not found"

    In SQL-Plus i have no Problems to set up this SQLString with Value : NULL so it can't be possible the Problem is in Oracle.

    Know everyone how i say it correct that this Field is NULL ?

    Frontend - AccessXP (+MDW)
    Backend - Oracle 9iR2 (9.2.0.5.0)
    OleDB / ODBC - Oracle (9.2.0.4.0)
    ADO 2.7

    Please excuse me for my bad english.
    HTH Shura
    Last edited by Shura; 01-27-05 at 09:03.

  2. #2
    Join Date
    Jan 2005
    Posts
    8
    Hmm.. ok.
    I think i'm a little bit near on the answer.

    I have disabeld the contraint and start's the input.
    The effect was horrible for me.
    The input was'nt in the correct column.
    But why ? I have the correct order in the SQL Syntax.

    OK, so put every DB Column in the SQL Syntax too.
    But the result is the same.

    I will wrote the complete Syntax in hope someone knows a hit.


    Code:
    'INSERT INTO TBL_SYS_MENU
    '    (PK_SYSMENU, SYSMENU_T_VALUE, SYSMENU_T_NAME, SYSMENU_N_NODE,
    '     SYSMENU_T_PARENT,SYSMENU_N_TYPE,SYSMENU_N_SORT,
    '     SYSMENU_FK_SYSMENUDET,SYSMENU_T_BESCHREIB)
    'VALUES
    '    (SEQ_SYSMENU.NEXTVAL,:SYSMENU_T_VALUE,:SYSMENU_T_NAME,
    '     :SYSMENU_N_NODE,:SYSMENU_T_PARENT,:SYSMENU_N_TYPE,
    '     :SYSMENU_N_SORT,:SYSMENU_FK_SYSMENUDET,:SYSMENU_T_BESCHREIB)
    (SYSMENU_T_NAME is correct writen, i dont know this shown here with spaces)

    The parameters input to the array is correct, i have checked it with an extra text output.

    Param's Input to the Command Object:
    Code:
            Set cmOraDCUP = New ADODB.Command
            With cmOraDCUP
                .ActiveConnection = cnOraDCUP
                .CommandText = strSQL
                .CommandType = adCmdText
                    For i = LBound(m_parArray) To UBound(m_parArray)
                        .Parameters.Append .CreateParameter(m_parArray(i).parName, _
                                                            m_parArray(i).parType, _
                                                            m_parArray(i).parDirection, _
                                                            m_parArray(i).parSize, _
                                                            m_parArray(i).parValue)
                    Next i
                Set rsOraDCUP = .Execute(m_lngRecordsAffected)
            End With
    My Computer lives danger at this time.. pls help him.

    HTH Shura
    Last edited by Shura; 01-27-05 at 10:45.

  3. #3
    Join Date
    Jan 2005
    Posts
    8
    @Moderator: can you pls move this post to a VB / VBA Forum ?
    My second post (in the correct Forum) was deleted there.

    Thank you.
    Shura

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Works fine for me. But I see you have a constraint on that especific column, which will prohibite any possible values for null, are you aware of that ?

  5. #5
    Join Date
    Jan 2005
    Posts
    8
    Hi,

    thanks for interesting my problem.
    This Constraintfield is a Number(38) and accept NULL.
    I have tried it positive in SQL-Plus.

    On my investigation of this problem i came more and more to the result it must be a locatet in VB (ADO).
    The Programm didn't accept my parameternames und put the param's one by one into the serversyntax.
    Is the SQL String (i.e.):
    Code:
    select col0 from tbl1 where col1=:param1 and col2 =:param2;
    So VB do that so:
    1st param get: name "param1" value.....
    2nd param get: name "param2" value....
    It works fine!
    But:
    1st param get: name "param2" value...
    2nd param get: name "param1" value...
    So VB will but the param "param2" on the col1 clausel and "param1" to the col2 clausel.

    I can't believe it!
    Are i stupid ??
    HTH Shura

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •