Run time error 3075

Hi,

I hope soemone can help me here.  There's a stored procedure that I'm calling from MS Access using ADO.  The module is below.  Now the stoed proc on the server has been changes and a new field added called X400_EMAIL_ADDRESS.

I don't understand the syntax/rules behind the code in the block of code that begins CurrentDb.Execute.  All I did to accomodate this new field was to replace the field named REFERENCE with X400_EMAIL_ADDRESS.  But that doesn't work!!! I get an error:

" Run time erro 3075 Stntax error ( missing operator) in query expression ' /o = XX ConsultingGroup/ ou=North America/ cn = Recipients/cn=PRIMAR'  - this is the value fo the X400_EMAIL_ADDRESS in the first record that the stored proc calls.

Can anybody help.

Thanks,

Andrew



'---- CommandTypeEnum Values ----
Const adCmdUnknown = 0
Const adCmdText = &H1
Const adCmdTable = &H2
Const adCmdStoredProc = &H4

Sub getEmailList()

    Dim oCmd            As Object
    Dim oRs             As Object
    Dim oField          As Object
    Dim sp_name         As String
   
Call CreateConnection("INFDEL01", "DATA")

    sp_name = "Dataman.ExpenseNotificationExtract"

    Set oCmd = Nothing
        'set up the command object
    Set oCmd = CreateObject("ADODB.Command")
        oCmd.CommandText = sp_name
        oCmd.CommandType = adCmdStoredProc
        oCmd.ActiveConnection = oConn
        oCmd.CommandTimeout = "60"
       
    'set up the recordset object
    Set oRs = CreateObject("ADODB.Recordset")
        oRs.CacheSize = 400
   
        oRs.Open oCmd
        oRs.MoveFirst
       
       
       
       
       
               
            While Not oRs.EOF
                'If oRs.BOF Then
                 '  oRs.MoveFirst
               ' End If
               'vbNewLine " " & " " & CStr(oRs.Fields(1).Value) & " " & CStr(oRs.Fields(2).Value)
               Debug.Print (oRs.Fields(4).Value)
              ' oRs.MoveNext
           ' Wend
               
           ' While Not oRs.EOF
               ' If oRs.BOF Then
                '   oRs.MoveFirst
               ' End If
               
               If oRs.EOF Then
               Call CloseConnection
               End If
               
               
               

                CurrentDb.Execute ("INSERT INTO GP_Email(" & _
                                                        "INSTANCE," & _
                                                        "PERSONAL_REFERENCE," & _
                                                        "PAYMENT_DATE," & _
                                                        "ACCOUNT_CODE," & _
                                                        "FIRST_NAME," & _
                                                        "SURNAME," & _
                                                        "REFERENCE," & _
                                                        "JOURNAL_NUMBER," & _
                                                        "JOUNRAL_LINE," & _
                                                        "SHEET_NUMBER," & _
                                                        "AMOUNT," & _
                                                        "CURRENCY_CODE," & _
                                                        "STATUS " & ")" & _
                                   " VALUES (" & _
                                            "'" & oRs.Fields(0).Value & "', '" & oRs.Fields(1).Value & "', " & _
                                            oRs.Fields(2).Value & ", '" & oRs.Fields(3).Value & "', " & _
                                            "'" & oRs.Fields(4).Value & "', '" & oRs.Fields(5).Value & "', " & _
                                            "'" & oRs.Fields(6).Value & "', " & oRs.Fields(7).Value & ", " & _
                                            oRs.Fields(8).Value & ", " & oRs.Fields(9).Value & ", " & _
                                            oRs.Fields(10).Value & ", '" & oRs.Fields(11).Value & "', " & _
                                            oRs.Fields(12).Value & ")" _
                                            )
                               


                oRs.MoveNext
           Wend

                     
    ' & replace("" & oRs.Fields(3).Value,"'","''") &

End Sub
AndrewMcLaughlinAsked:
Who is Participating?
 
Shiju SasidharanConnect With a Mentor Assoc Project ManagerCommented:
hi
u mean to say that Stored poc is difficult to get or understand  ?
ok no probs u can change the insert statement according to that
here is the samle data u retried from the data u gave. just  check it out whether each field get a valid value
i think some values make any sense according to the field

INSTANCE = 'NA1'
PERSONAL_REFERENCE='NA1000054'
PAYMENT_DATE=20040707
ACCOUNT_CODE='720000054      '
FIRST_NAME='Mark'
SURNAME='Rowland'
X400_EMAIL_ADDRESS='000054042610088'
JOURNAL_NUMBER=/o=XX Consulting Group/ou=North America/cn=Recipients/cn=PRIMAR
JOUNRAL_LINE=1088
SHEET_NUMBER=1
AMOUNT=200426
CURRENCY_CODE='402.69'
STATUS=USD

;-)
Shiju



0
 
RogueSolutionsCommented:
You need to be sure that none of your data contains an apostrophe ( ' ) or the coding will fail.

The bit at the bottom shows how to get rid of the '.  You change them to '' (which SQL ignores)

Do the [ replace("" & ]  and [ ,"'","''") ] around each of the Fields in that big mass of text (ignore the square brackets)

This may not be the whole problem though.

0
 
RogueSolutionsCommented:
Having reread, you still have a field named REFERENCE in the Execute.

Change it for the new field name.  Be careful to keep the comma in there.

Try this before the change above
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
aelatikCommented:
Is this a typo or is this correct ? "JOUNRAL_LINE"
0
 
AndrewMcLaughlinAuthor Commented:
No that's all correct - no TYPO.

Rogue solutions - when I do change the Field name REFERENCE to X400_EMAIL_ADDRESS i get that error.  That's why I wondered about syntax.  The new field does not contain apostrophes - but it does have obliques and equal signs.

Whats going wrong?

Thanks,

Andrew
0
 
aelatikCommented:
try this :

---- CommandTypeEnum Values ----
Const adCmdUnknown = 0
Const adCmdText = &H1
Const adCmdTable = &H2
Const adCmdStoredProc = &H4

Sub getEmailList()

    Dim oCmd            As Object
    Dim oRs             As Object
    Dim oField          As Object
    Dim sp_name         As String
   
Call CreateConnection("INFDEL01", "DATA")

    sp_name = "Dataman.ExpenseNotificationExtract"

    Set oCmd = Nothing
        'set up the command object
    Set oCmd = CreateObject("ADODB.Command")
        oCmd.CommandText = sp_name
        oCmd.CommandType = adCmdStoredProc
        oCmd.ActiveConnection = oConn
        oCmd.CommandTimeout = "60"
       
    'set up the recordset object
    Set oRs = CreateObject("ADODB.Recordset")
        oRs.CacheSize = 400
   
        oRs.Open oCmd
        oRs.MoveFirst
       
       
       
       
       
               
            While Not oRs.EOF
                'If oRs.BOF Then
                 '  oRs.MoveFirst
               ' End If
               'vbNewLine " " & " " & CStr(oRs.Fields(1).Value) & " " & CStr(oRs.Fields(2).Value)
               Debug.Print (oRs.Fields(4).Value)
              ' oRs.MoveNext
           ' Wend
               
           ' While Not oRs.EOF
               ' If oRs.BOF Then
                '   oRs.MoveFirst
               ' End If
               
               If oRs.EOF Then
               Call CloseConnection
               End If
               
               
                Dim SQL As String
                   
                    SQL = SQL & "INSERT INTO GP_Email"
                    SQL = SQL & "("
                    SQL = SQL & "INSTANCE,"
                    SQL = SQL & "PERSONAL_REFERENCE,"
                    SQL = SQL & "PAYMENT_DATE,"
                    SQL = SQL & "ACCOUNT_CODE,"
                    SQL = SQL & "FIRST_NAME,"
                    SQL = SQL & "SURNAME,"
                    SQL = SQL & "X400_EMAIL_ADDRESS,"
                    SQL = SQL & "JOURNAL_NUMBER,"
                    SQL = SQL & "JOUNRAL_LINE,"
                    SQL = SQL & "SHEET_NUMBER,"
                    SQL = SQL & "AMOUNT,"
                    SQL = SQL & "CURRENCY_CODE,"
                    SQL = SQL & "STATUS,"
                    SQL = SQL & ")"
                    SQL = SQL & " VALUES "
                   
                    SQL = SQL & "'" & UnQuote(oRs.Fields(0).Value) & "',"
                    SQL = SQL & "'" & UnQuote(oRs.Fields(1).Value) & "',"
                    SQL = SQL & oRs.Fields(2).Value & ","
                    SQL = SQL & "'" & UnQuote(oRs.Fields(3).Value) & "',"
                    SQL = SQL & "'" & UnQuote(oRs.Fields(4).Value) & "',"
                    SQL = SQL & "'" & UnQuote(oRs.Fields(5).Value) & "',"
                    SQL = SQL & "'" & UnQuote(oRs.Fields(6).Value) & "', "
                    SQL = SQL & oRs.Fields(7).Value & ","
                    SQL = SQL & oRs.Fields(8).Value & ","
                    SQL = SQL & oRs.Fields(9).Value & ","
                    SQL = SQL & oRs.Fields(10).Value & ","
                    SQL = SQL & "'" & UnQuote(oRs.Fields(11).Value) & "',"
                    SQL = SQL & oRs.Fields(12).Value
                    SQL = SQL & ")"
                   
                    CurrentDb.Execute (SQL)
                               


                oRs.MoveNext
           Wend

                     
    ' & replace("" & oRs.Fields(3).Value,"'","''") &

End Sub

Function UnQuote(xVal)
    UnQuote = Replace(xVal, "'", "''")
End Function
0
 
AndrewMcLaughlinAuthor Commented:
I get an error with the word Unquote - do I need a special reference to use this word?
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Hi

make sure that none of ur recordset filelds have NULL value.
also try to replace single quotes with two single quotes for all string fileds (as suggested by aelatik )
Eg:

         if IsNull(oRs.Fields(3)) Then
               replaced_value=  ""
         Else
              replaced_value=  Replace(oRs.Fields(3),"'","''")
         EndIf

;-)
Shiju
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi
it is better to check for NULL value
for non-string fields aswell
Eg:( For non-string fields)

         if IsNull(oRs.Fields(7)) Then
               new_value=  new_value_if_Null_is_Found
         Else
               new_value=  oRs.Fields(7)
         EndIf

;-)
Shiju
0
 
aelatikCommented:
Instead of CurrentDb.Execute (SQL) use debug.print SQL and analyze the SQL statements. Try figuring out what the problem is that way
0
 
AndrewMcLaughlinAuthor Commented:
aelatik,

Sorry about my first comment - didn't copy the unquote function into the module!!!

Now when I do a debug print and put a stop after oRs MoveBext I can see than there is no problem now except that it doesn't like the CurrentDb.Execute(SQL) line - do you know what might be wrong with this?  I'm doign this in Access 2000 VBA

Also I do have a coupe of null values in one of the fields -   where do I insert shijusn's code to take care of that.

Thanks,

Andrew
0
 
AndrewMcLaughlinAuthor Commented:
The error message was:

RUN TIME ERROR 3134 - Syntax error in INSERT INTO STATEMENT
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi try this

'================
'---- CommandTypeEnum Values ----
Const adCmdUnknown = 0
Const adCmdText = &H1
Const adCmdTable = &H2
Const adCmdStoredProc = &H4

Sub getEmailList()

    Dim oCmd            As Object
    Dim oRs             As Object
    Dim oField          As Object
    Dim sp_name         As String
   
Call CreateConnection("INFDEL01", "DATA")

    sp_name = "Dataman.ExpenseNotificationExtract"

    Set oCmd = Nothing
        'set up the command object
    Set oCmd = CreateObject("ADODB.Command")
        oCmd.CommandText = sp_name
        oCmd.CommandType = adCmdStoredProc
        oCmd.ActiveConnection = oConn
        oCmd.CommandTimeout = "60"
       
    'set up the recordset object
    Set oRs = CreateObject("ADODB.Recordset")
        oRs.CacheSize = 400
   
        oRs.Open oCmd
        oRs.MoveFirst
       
                     
            While Not oRs.EOF
                'If oRs.BOF Then
                 '  oRs.MoveFirst
               ' End If
               'vbNewLine " " & " " & CStr(oRs.Fields(1).Value) & " " & CStr(oRs.Fields(2).Value)
               Debug.Print (oRs.Fields(4).Value)
              ' oRs.MoveNext
           ' Wend
               
           ' While Not oRs.EOF
               ' If oRs.BOF Then
                '   oRs.MoveFirst
               ' End If
               
               If oRs.EOF Then
               Call CloseConnection
               End If


Dim sQL As String
Dim sContent As String
Dim i As Integer

sQL = " INSERT INTO GP_Email(INSTANCE,PERSONAL_REFERENCE,PAYMENT_DATE,ACCOUNT_CODE,FIRST_NAME," & _
      " SURNAME,X400_EMAIL_ADDRESS,JOURNAL_NUMBER,JOUNRAL_LINE, SHEET_NUMBER,AMOUNT,CURRENCY_CODE," & _
      " STATUS) VALUES("
  For i = 0 To 12
    sContent = ""
    Select Case i
        Case 0, 1, 3, 4, 5, 6, 11
            If IsNull(oRs.Fields(i)) Then
                sContent = ""
            Else
                sContent = Replace(oRs.Fields(i), "'", "''")
            End If
            sQL = sQL & "'" & sContent & "',"
        Case Else
            Select Case i
                Case 2 'PAYMENT_DATE
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_PAYMENT_DATE_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 7 'JOURNAL_NUMBER
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_JOURNAL_NUMBER_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 8 'JOUNRAL_LINE
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_JOUNRAL_LINE_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 9 'SHEET_NUMBER
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_SHEET_NUMBER_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 10 'AMOUNT
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_AMOUNT_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 12 'STATUS
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_STATUS_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
            End Select
            If i <> 12 Then
                sQL = sQL & sContent & ","
            Else
                sQL = sQL & sContent
            End If
    End Select
  Next i
  sQL = sQL & ")"
                   
  CurrentDb.Execute (SQL)
  oRs.MoveNext
Wend
End Sub


;-)
Shiju
0
 
AndrewMcLaughlinAuthor Commented:
Shiju,

Thanks, but that still returns an error highlighting the CurrentDb.Execute ( sQL) - Run time error 3075

Do you know what the original problem error message 3134 means?

Thanks,

Andrew
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Hi AndrewMcLaughlin

Can u print the Content of sQL  u obtained just before executing  the statement

>> CurrentDb.Execute ( sQL)

so that we can get more deep into the problem

;-)
Shiju
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Hi

if u get 3075 or 3134  then its sure that problem lies in the query itself

3075 occurs when -> The expression you typed is not valid for the reason indicated in the message
3134 occurs when -> You entered an SQL statement that has an invalid INSERT INTO statement.
                        Possible causes: A reserved word or argument name is misspelled or missing or Punctuation is incorrect.

;-)
Shiju
0
 
AndrewMcLaughlinAuthor Commented:
Hi Shiju,

Ok I'v done that - about two hundred records are returned to the immediate window - out of a possible 2000.  It's not very clear why I only get 200???
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi  AndrewMcLaughlin

i said if u give the query formed in variable sQL that would be helpful to make corrections
that is get the result from
         Debug.Print sQL
before the statement   >> CurrentDb.Execute ( sQL)

;-)
Shiju
0
 
AndrewMcLaughlinAuthor Commented:
Ah - ha , so we see what the infor that causes the problem looks like -

The status field is missing here - which is simply a one digit number.  Why would that be?


 INSERT INTO GP_Email(INSTANCE,PERSONAL_REFERENCE,PAYMENT_DATE, ACCOUNT_CODE,FIRST_NAME, SURNAME,X400_EMAIL_ADDRESS,JOURNAL_NUMBER, JOUNRAL_LINE,SHEET_NUMBER,AMOUNT,CURRENCY_CODE, STATUS) VALUES('NA1','NA1000054',20040707,'720000054      ','Mark','Rowland','000054042610088',/o=XX Consulting Group/ou=North America/cn=Recipients/cn=PRIMAR,1088,1,200426,'402.69',USD)

Thanks,

Andrew

0
 
aelatikCommented:
This needs to be between quotes : /o=XX Consulting Group/ou=North America/cn=Recipients/cn=PRIMAR
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Hi AndrewMcLaughlin

what is the type of the Field JOURNAL_NUMBER ?
        it has got the value /o=XX Consulting Group/ou=North America/cn=Recipients/cn=PRIMAR
what is the type of the Field STATUS ?
        it has got the value USD

Can u specify all fields which are string (varchar)

;-)
Shiju
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi Andrew

Does ur Procedure
   >> sp_name = "Dataman.ExpenseNotificationExtract"
returns the fields in the same  order as u specified in the INSERT Statement ?

;-)
Shiju

0
 
AndrewMcLaughlinAuthor Commented:
When I link to teh table that the stored proc reads from in Access I get the following field types - numbers have field size of long integer

INSTANCE_CODE  text
PERSONAL_REFERENCE text
PAYMENT_DATE number
ACCOUNT_CODE text
FIRST_NAME text
SURNAME text
X400_EMAIL_ADDRESS text
JOURNAL_NUMBER number
JOURNLA_LINE number
SHEET-NUMBER number
AMOUNT currency
CURRENCY_CODE text
STATUS number
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi Andrew
  u specified the Fields in the Table,  ok thats fine
i would like to know the order in which u r retrieving the fields in the "stroed procedure"
My question is r u retrieving the fileds in Stroed Procedure as in this order
 INSTANCE, PERSONAL_REFERENCE,PAYMENT_DATE,ACCOUNT_CODE,FIRST_NAME, SURNAME,X400_EMAIL_ADDRESS,JOURNAL_NUMBER,JOUNRAL_LINE, SHEET_NUMBER,AMOUNT,CURRENCY_CODE, STATUS

;-)
Shiju


0
 
AndrewMcLaughlinAuthor Commented:
Shiju,

I've just checked and they are coming back as follows ( I didn't write the proc so had to check with by systems people)

The code in my first post worked fine.  Then I tried to simply substitute REFERENCE with "X400_EMAIL_ADDRESS" - paying no atention to syntax

Hope you can advise a solution.

Thanks,

Andrew


INSTANCE,
PERSONAL_REFERENCE,
PAYMENT_DATE,
ACCOUNT_CODE,
FIRST_NAME,
SURNAME,
REFERENCE,
X400_EMAIL_ADDRESS,
JOURNAL_NUMBER,
JOURNAL_LINE,
SHEET_NUMBER,
AMOUNT,
CURRENCY_CODE,
STATUS
0
 
Shiju SasidharanAssoc Project ManagerCommented:
hi Andrew

  well, the problem got solved ?

;-)
Shiju

0
 
AndrewMcLaughlinAuthor Commented:
AH - No, I know whatthe problem is?  Which is progress.  The syntax is just too fiddly.  Could you suggest something?

Thanks very much,

Andrew
0
 
AndrewMcLaughlinAuthor Commented:
Shuiju.

That works now - thanks.  The clouds have at last opened up!!

I had to adust your code.  I see that it handles Text and numbers separately and I had to insert the new field.  So my code now looks like below.  It pulls all the data into the table.  However - for some reason when Instance equals UK1 it does not pull through the sheet number into the table - do you know what the problems is with that?

Also,  the AMOUNT field is actually a CURRENCY type field - do I need to add more code for this.

More generally - can you explain what your code is doing and where can I find examples?

Thanks very very much.

Andrew
Dim sQL As String
Dim sContent As String
Dim i As Integer

sQL = " INSERT INTO GP_Email(INSTANCE,PERSONAL_REFERENCE, PAYMENT_DATE,ACCOUNT_CODE,FIRST_NAME," & _
      " SURNAME,REFERENCE,X400_EMAIL_ADDRESS,JOURNAL_NUMBER, JOUNRAL_LINE,SHEET_NUMBER,AMOUNT,CURRENCY_CODE," & _
      " STATUS) VALUES("
  For i = 0 To 13
    sContent = ""
    Select Case i
        Case 0, 1, 3, 4, 5, 6, 7, 12
            If IsNull(oRs.Fields(i)) Then
                sContent = ""
            Else
                sContent = Replace(oRs.Fields(i), "'", "''")
            End If
            sQL = sQL & "'" & sContent & "',"
        Case Else
            Select Case i
                Case 2 'PAYMENT_DATE
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_PAYMENT_DATE_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 8 'JOURNAL_NUMBER
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_JOURNAL_NUMBER_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 9 'JOUNRAL_LINE
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_JOUNRAL_LINE_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 10 'SHEET_NUMBER
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_SHEET_NUMBER_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 11 'AMOUNT
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_AMOUNT_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
                Case 13 'STATUS
                    If IsNull(oRs.Fields(i)) Then
                        sContent = Your_new_Value_If_STATUS_Found_Null
                    Else
                        sContent = oRs.Fields(i)
                    End If
            End Select
            If i <> 13 Then
                sQL = sQL & sContent & ","
            Else
                sQL = sQL & sContent
            End If
    End Select
  Next i
  sQL = sQL & ")"
 
 
  Debug.Print sQL
       
  CurrentDb.Execute (sQL)
 

 
  oRs.MoveNext
Wend
End Sub

0
All Courses

From novice to tech pro — start learning today.