Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Unanswered: Update query (one for the VB programmers)

    Hi there,

    I am having trouble with an update query in oracle 9i. I am connecting to oracle via the oracle provider, from a vb6 program.

    I have created an update query, which seems to work in Access 2000 (by 'linking' to the oracle database) but, strangely, does not work in VB (using ADO2.5 and the oracle provider).

    Can anyone suggest what might be wrong with this query/Code snippet. I'm tearing my hair out here!

    For purposes of clarity, the database in Oracle is called SCXDOWNLOAD and the table is called WESNET_SOURCES.

    Any help you can give would be much appreciated. The error message I get (it's an error coming from the Oracle provider/driver is): ORA-00942: table or view does not exist.... Hmmm.... It's as if it thinks i'm passing a SELECT query rather than an UPDATE query.

    Since there is a space restriction, I will post the code in the in the next message... Sorry.

    Regards,

    Mark Wills
    Mark Wills.

  2. #2
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25
    Code:
        Dim strUpdate As String, strNewPointName As String, strNewOutstation As String, strNewWesnetID As String
        Dim lngRecordsAffected As Long
        
        strNewPointName = Me.txtPoint.Text
        strNewOutstation = Me.txtOutstation.Text
        strNewWesnetID = Me.txtWesnet.Text
            
        strUpdate = "UPDATE SCXDOWNLOAD_WESNET_SOURCES SET SCXDOWNLOAD_WESNET_SOURCES.POINTNAME = '" & strNewPointName & "', " _
            & "SCXDOWNLOAD_WESNET_SOURCES.OUTSTATIONNAME = '" & strNewOutstation _
            & "', SCXDOWNLOAD_WESNET_SOURCES.WESNETID = '" & strNewWesnetID & "' " _
            & "WHERE (((SCXDOWNLOAD_WESNET_SOURCES.POINTNAME)='" & m_strPointName & "') AND " _
            & "((SCXDOWNLOAD_WESNET_SOURCES.OUTSTATIONNAME)='" & m_strOutstation & "') " _
            & "AND ((SCXDOWNLOAD_WESNET_SOURCES.WESNETID)='" & m_strWesnetID & "'))"
        
        ' replace any ' symbols with "
        strUpdate = Replace(strUpdate, "'", Chr(34), , , vbBinaryCompare)
        
        Debug.Print strUpdate
        Globals.objConn.Execute strUpdate, lngRecordsAffected, adCmdText
        Unload Me
    Please see my post above - this routine fails with 'table or view not found'. Bizarre. Any ideas?

    The variables in question are filled with normal strings - nothing is 'null' etc.

    Regards,

    Mark Wills
    Mark Wills.

  3. #3
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25
    I fixed it! It doesnt like double quotes (") - it only wants single quotes. Thats my fault for using crappy, non-compliant SQL DB's like Access!

    Mark Wills.

  4. #4
    Join Date
    Aug 2002
    Location
    LaGrange, NC
    Posts
    1

    Reply to VB coder message

    Mark,

    Try prefacing your table with the schema name (i.e., [SCHEMA OWNER].TABLE_NAME. Logging in as anyone other than the schema owner will produce the table or view does not exist message.

    Let me know if this helps.

    RDS

Posting Permissions

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