Error in assignment on insert statement: 80040e07

[RubenV]

I get a run time error -2147217913 (80040e07) when executing this statement:

strUpdate = "INSERT INTO Related_Entries (Related_To_Id," & _
"Related_To_Number,Related_From_Id," & _
"Related_From_Number,""Desc"") " & _
"VALUES ('" & objMaxRec.GetFieldValue("IDentification") & "'," & _
"'" & objMaxRec.GetFieldValue("Contact_Number") & "'," & _
"'" & dgContacts.Columns(3) & "'," & _
"'" & dgContacts.Columns(4) & "'," & _
"'" & relationType & "');"

Does anyone have an idea what the cause could be and how to solve it.

Thanks for your time.

 

[PatrickIRL]

In reality, posting a question like this will very unlikely get an answer straight off, the more detail the better.

If you debug the statement and see what strUpdate holds are there any errors that stand out immediately??

Patrick

 

[fredericofonseca]

Why don't people search their error documentation!!.

DB_E_CANTCONVERTVALUE 80040E07
A literal value in the command text cannot be converted to the type of the associated column for reasons other than data overflow. The error string should contain the offending constant.

This is one option. one of the others relates to Oracle and Date values.

Please state access method used (ADO/RDO/DAO/OO40), how the SQL is executed (connection, recordset, command object or other)

and offcourse use debug/msgbox to show the FULL string being executed.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RubenV]

First of all, sorry guys for this perhaps uncomplete post.

Please forgive me but I was interrupted at the time of writing this.

Ok, I already did print out the messagebox but as I said, I got interrupted in writing this post.
Here is further information, thanks for your patience.

the messagebox result:
INSERT INTO Related_Entries (Related_To_Id,Related_To_Number,Related_From_Id,Related_From_Number,”Desc”)
VALUES (‘1039669’,’0’,’040603005910960349966C’,’2’,’Contractor’);

There are no errors in the data!

this is the code for executing the query using ADO:

strUpdate = "INSERT INTO Related_Entries (Related_To_Id," & _
"Related_To_Number,Related_From_Id," & _
"Related_From_Number,""Desc"") " & _
"VALUES ('" & objMaxRec.GetFieldValue("IDentification") & "'," & _
"'" & objMaxRec.GetFieldValue("Contact_Number") & "'," & _
"'" & dgContacts.Columns(3) & "'," & _
"'" & dgContacts.Columns(4) & "'," & _
"'" & relationType & "');"

Dim updateConnection As New adodb.Connection updateConnection.Open "DEMElocal", "MASTER", "cheers"
Dim updateCommand As New adodb.Command
With updateCommand
.ActiveConnection = updateConnection
.CommandText = strUpdate
.CommandType = adCmdText
End With
updateCommand.Execute


HOWEVER, could it be that the error in assignment might be related to the following?

The table contains some system generated fields that are automatically filled in when working with the product's interface.
When I'm doing this with a VB form, I actually have no idea if these fields are filled in too. However they are read only and I cannot change the field's properties.

PERHAPS now a stupid question, but fredericofonseca:
how can I catch the error string containing the offending constant?

Again, my apologies for my previous post.
I hope you guys can still help me.

Regards,
Ruben.

 

[johnwm]

......
"'" & relationType & "');"
[COLOR=red]Debug.Print strUpdate[/color]
Dim updateConnection As New adodb.Connection   
......

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[fredericofonseca]

To catch the errors try the following code where DBConn is the ADODB.Connection object

  If Err.Number <> 1 Then
    For iErrCnt = 1 To DBConn.Errors.Count
      sError = sError & "-" & _
        DBConn.Errors(iErrCnt - 1).Description & "-" & _
        DBConn.Errors(iErrCnt - 1).NativeError & "-" & _
        DBConn.Errors(iErrCnt - 1).SQLState & vbCrLf
    Next iErrCnt
    If sError = "" Then
      sError = Err.Description
    End If
    MsgBox sError
    sError = ""
  End If

Regarding your particular error it may be possible that one of the fields you are trying to update is a NUMERIC type, and if so the values corresponding to tht field should NOT have the single quotes around it.

Regarding the other fields on the table as long as they can have NULL values, and they are not part of a key theyv should not give you an error.


As you are doing an insert I would also change your .execute to be .Execute , , adExecuteNoRecord

I would also change
Dim updateCommand As New adodb.Command
to
Dim updateCommand As adodb.Command
set updateCommand = new adodb.Command

And it may also be of benefit to you in the future if you start using DSNless connections instead of what you have now.It will give you more flexibility.



And finally look at faq709-1526 for the reasons why you should change your SQL to use PARAMETERS instead.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RubenV]

Here is the debug.print but it's the same as the errormessage...
I don't know if it was this you wanted me to do but here it is:

INSERT INTO Related_Entries (Related_To_Id,Related_To_Number,Related_From_Id,Related_From_Number,"Desc") VALUES ('040603005910960349966C','0','040603005910960349966C','2','dealer');

 

[RubenV]

THANKS fredericofonseca !!!

I really appreciate your help and advice.
I will dig into this immediately and I will let you guys know how it works out.

Thanks again,
Ruben.

 

[RubenV]

Sorry, just a small question.
Where exactly should I post the error code?

 

[fredericofonseca]

Did you use my code? e.g. did you LOOP the error collection for ALL errors?


As I said most probably a wrong use of quotes. Manually change your SQL to be (Hardcode it before the "With updateCommand")

INSERT INTO Related_Entries (Related_To_Id,Related_To_Number,Related_From_Id,Related_From_Number,"Desc") VALUES ('040603005910960349966C',0,'040603005910960349966C',2,'dealer')

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[johnwm]

If your Related_To_Number and Related_From_Number are numeric fields then remove the single quotes round their values

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[fredericofonseca]

Just a full example.

Private Sub cmdDelete_Click()
On Error GoTo error_handler
Dim iErrCnt As Long

  If sDeleteSql = "" Then
    Set adoDeleteComm = New ADODB.Command
  
    sDeleteSql = "sp_delete_country"
    adoDeleteComm.CommandType = adCmdStoredProc
    adoDeleteComm.CommandText = sDeleteSql
    adoDeleteComm.ActiveConnection = DBConn
  End If
    
  adoDeleteComm.Parameters("@pais").Value = country.Text
  adoDeleteComm.Execute

  Exit Sub
error_handler:
  If Err.Number <> 1 Then
    For iErrCnt = 1 To DBConn.Errors.Count
      sError = sError & "-" & _
        DBConn.Errors(iErrCnt - 1).Description & "-" & _
        DBConn.Errors(iErrCnt - 1).NativeError & "-" & _
        DBConn.Errors(iErrCnt - 1).SQLState & vbCrLf
    Next iErrCnt
    If sError = "" Then
      sError = Err.Description
    End If
    MsgBox sError
    sError = ""
  End If
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RubenV]

fredericofonseca,

no I didn't loop through it yet.
Should I do like this

ON ERROR GOTO (yourcode)
.execute

YOUR CODE

The problem is solved now though.
The problem was this:
Related_.._Id is a string field
Related_To_Number is a string field but for some very awkwardreason they set Related_FROM_Number to be a numeric field...

I don't understand why but that's probably the reason why I missed it.