AndrewMcLaughlin
asked on
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
sp_name = "Dataman.ExpenseNotificati
Set oCmd = Nothing
'set up the command object
Set oCmd = CreateObject("ADODB.Comman
oCmd.CommandText = sp_name
oCmd.CommandType = adCmdStoredProc
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = "60"
'set up the recordset object
Set oRs = CreateObject("ADODB.Record
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
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
sp_name = "Dataman.ExpenseNotificati
Set oCmd = Nothing
'set up the command object
Set oCmd = CreateObject("ADODB.Comman
oCmd.CommandText = sp_name
oCmd.CommandType = adCmdStoredProc
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = "60"
'set up the recordset object
Set oRs = CreateObject("ADODB.Record
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
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
Change it for the new field name. Be careful to keep the comma in there.
Try this before the change above
Is this a typo or is this correct ? "JOUNRAL_LINE"
ASKER
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
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
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
sp_name = "Dataman.ExpenseNotificati
Set oCmd = Nothing
'set up the command object
Set oCmd = CreateObject("ADODB.Comman
oCmd.CommandText = sp_name
oCmd.CommandType = adCmdStoredProc
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = "60"
'set up the recordset object
Set oRs = CreateObject("ADODB.Record
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).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(1).Valu
SQL = SQL & oRs.Fields(2).Value & ","
SQL = SQL & "'" & UnQuote(oRs.Fields(3).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(4).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(5).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(6).Valu
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).Val
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
---- 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
sp_name = "Dataman.ExpenseNotificati
Set oCmd = Nothing
'set up the command object
Set oCmd = CreateObject("ADODB.Comman
oCmd.CommandText = sp_name
oCmd.CommandType = adCmdStoredProc
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = "60"
'set up the recordset object
Set oRs = CreateObject("ADODB.Record
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).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(1).Valu
SQL = SQL & oRs.Fields(2).Value & ","
SQL = SQL & "'" & UnQuote(oRs.Fields(3).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(4).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(5).Valu
SQL = SQL & "'" & UnQuote(oRs.Fields(6).Valu
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).Val
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
ASKER
I get an error with the word Unquote - do I need a special reference to use this word?
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
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
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
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
Instead of CurrentDb.Execute (SQL) use debug.print SQL and analyze the SQL statements. Try figuring out what the problem is that way
ASKER
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
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
ASKER
The error message was:
RUN TIME ERROR 3134 - Syntax error in INSERT INTO STATEMENT
RUN TIME ERROR 3134 - Syntax error in INSERT INTO STATEMENT
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
sp_name = "Dataman.ExpenseNotificati
Set oCmd = Nothing
'set up the command object
Set oCmd = CreateObject("ADODB.Comman
oCmd.CommandText = sp_name
oCmd.CommandType = adCmdStoredProc
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = "60"
'set up the recordset object
Set oRs = CreateObject("ADODB.Record
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
" SURNAME,X400_EMAIL_ADDRESS
" 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_
Else
sContent = oRs.Fields(i)
End If
Case 7 'JOURNAL_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOURNAL_
Else
sContent = oRs.Fields(i)
End If
Case 8 'JOUNRAL_LINE
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOUNRAL_
Else
sContent = oRs.Fields(i)
End If
Case 9 'SHEET_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_SHEET_NU
Else
sContent = oRs.Fields(i)
End If
Case 10 'AMOUNT
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_AMOUNT_F
Else
sContent = oRs.Fields(i)
End If
Case 12 'STATUS
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_STATUS_F
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
'================
'---- 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
sp_name = "Dataman.ExpenseNotificati
Set oCmd = Nothing
'set up the command object
Set oCmd = CreateObject("ADODB.Comman
oCmd.CommandText = sp_name
oCmd.CommandType = adCmdStoredProc
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = "60"
'set up the recordset object
Set oRs = CreateObject("ADODB.Record
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
" SURNAME,X400_EMAIL_ADDRESS
" 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_
Else
sContent = oRs.Fields(i)
End If
Case 7 'JOURNAL_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOURNAL_
Else
sContent = oRs.Fields(i)
End If
Case 8 'JOUNRAL_LINE
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOUNRAL_
Else
sContent = oRs.Fields(i)
End If
Case 9 'SHEET_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_SHEET_NU
Else
sContent = oRs.Fields(i)
End If
Case 10 'AMOUNT
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_AMOUNT_F
Else
sContent = oRs.Fields(i)
End If
Case 12 'STATUS
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_STATUS_F
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
ASKER
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
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
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
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
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
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
ASKER
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???
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???
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
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
ASKER
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
Thanks,
Andrew
The status field is missing here - which is simply a one digit number. Why would that be?
INSERT INTO GP_Email(INSTANCE,PERSONAL
Thanks,
Andrew
This needs to be between quotes : /o=XX Consulting Group/ou=North America/cn=Recipients/cn=P
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=P
what is the type of the Field STATUS ?
it has got the value USD
Can u specify all fields which are string (varchar)
;-)
Shiju
what is the type of the Field JOURNAL_NUMBER ?
it has got the value /o=XX Consulting Group/ou=North America/cn=Recipients/cn=P
what is the type of the Field STATUS ?
it has got the value USD
Can u specify all fields which are string (varchar)
;-)
Shiju
hi Andrew
Does ur Procedure
>> sp_name = "Dataman.ExpenseNotificati
returns the fields in the same order as u specified in the INSERT Statement ?
;-)
Shiju
Does ur Procedure
>> sp_name = "Dataman.ExpenseNotificati
returns the fields in the same order as u specified in the INSERT Statement ?
;-)
Shiju
ASKER
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
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
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
;-)
Shiju
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
;-)
Shiju
ASKER
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
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
hi Andrew
well, the problem got solved ?
;-)
Shiju
well, the problem got solved ?
;-)
Shiju
ASKER
AH - No, I know whatthe problem is? Which is progress. The syntax is just too fiddly. Could you suggest something?
Thanks very much,
Andrew
Thanks very much,
Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
" SURNAME,REFERENCE,X400_EMA
" 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_
Else
sContent = oRs.Fields(i)
End If
Case 8 'JOURNAL_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOURNAL_
Else
sContent = oRs.Fields(i)
End If
Case 9 'JOUNRAL_LINE
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOUNRAL_
Else
sContent = oRs.Fields(i)
End If
Case 10 'SHEET_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_SHEET_NU
Else
sContent = oRs.Fields(i)
End If
Case 11 'AMOUNT
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_AMOUNT_F
Else
sContent = oRs.Fields(i)
End If
Case 13 'STATUS
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_STATUS_F
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
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
" SURNAME,REFERENCE,X400_EMA
" 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_
Else
sContent = oRs.Fields(i)
End If
Case 8 'JOURNAL_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOURNAL_
Else
sContent = oRs.Fields(i)
End If
Case 9 'JOUNRAL_LINE
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_JOUNRAL_
Else
sContent = oRs.Fields(i)
End If
Case 10 'SHEET_NUMBER
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_SHEET_NU
Else
sContent = oRs.Fields(i)
End If
Case 11 'AMOUNT
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_AMOUNT_F
Else
sContent = oRs.Fields(i)
End If
Case 13 'STATUS
If IsNull(oRs.Fields(i)) Then
sContent = Your_new_Value_If_STATUS_F
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
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.