VB6 Runtime Error -2147217900 (80040e14)

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 56848 Views - Last Post: 16 July 2012 - 08:44 PM Rate Topic: -----

#1 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

VB6 Runtime Error -2147217900 (80040e14)

Posted 01 March 2012 - 01:09 PM

Im getting a runtime error when trying to write to a database.

Error code -2147217900 (80040e14)
Syntax error in INSERT INTO statement.

Im not sure if the coding is correct, any help or suggestions would be much appreciated.

When I press the debug button the line highlighted is - conn.Execute insertsql

Here is my code

Private Sub cmdOrder_Click()

Dim insertsql As String

 
insertsql = " INSERT INTO Order (  Cust_ID, OrderDate ) Values ('" + txtCustID.Text + "' , '" + Format$(Now, "mm/dd/yyyy") + "' )"
   Set conn = New ADODB.Connection
        conn.ConnectionString = conString
        conn.Open
        conn.Execute insertsql
        conn.Close

frmCreateOrder.Show


End Sub




Is This A Good Question/Topic? 0
  • +

Replies To: VB6 Runtime Error -2147217900 (80040e14)

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 328
  • View blog
  • Posts: 1,990
  • Joined: 26-March 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 01 March 2012 - 01:27 PM

Just ensure your data types match your columns, for example, is your Cust_ID a string, because that's what you're passing in.

Also, ensure that you have specified everything you need to create a new record (i.e.: do any other columns need to be specified or are they allowed to be empty?).
Was This Post Helpful? 0
  • +
  • -

#3 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 01 March 2012 - 02:29 PM

The Cust_ID is a number in a text box which I'm trying to pass into the order table so that an Order_ID can be automatically generated.I then want to be able to add items to the order using an order-detail table.
The data type in the order table is number so I'm not sure wgwere I'm going wrong.
Was This Post Helpful? 0
  • +
  • -

#4 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 328
  • View blog
  • Posts: 1,990
  • Joined: 26-March 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 02 March 2012 - 12:47 AM

Try casting the text string as a number using the Cint or Clng function depending on your datatype and remove the quotes from around it.
Was This Post Helpful? 1
  • +
  • -

#5 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 02 March 2012 - 02:03 PM

Thanks for taking your time to get beck to me maj, I sorted the problem late last night. I was working today so didnt have the chance to post it was sorted until now.
It was a schoolboy error on my part - I hadnt thought about Order being a reserved word in SQL, so I changed the name of the table to Orders and the code worked fine! Thanks again.
Was This Post Helpful? 0
  • +
  • -

#6 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 328
  • View blog
  • Posts: 1,990
  • Joined: 26-March 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 03 March 2012 - 12:15 AM

I suppose you could have also wrapped it with [], which I believe is what you do with reserved words.

Shows my lack of SQL knowledge anyway! :)
Was This Post Helpful? 0
  • +
  • -

#7 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 11 July 2012 - 12:40 PM

The question was "How do I trap..." not how do I fix.

On Error GoTo does not trap this error. The error then shuts down the application which is NEVER a good thing.

So, how would the Run-time error '-214217900(80040e14)' be trapped? Every time I have seen this error was because of an ADODB connection error; which apparently are not trappable with VB6 error trapping schemes.

TIA
Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 11 July 2012 - 08:09 PM

@maj: you are correct.
@astrodon: you are incorrect. (sorry) The ADODB Connection object has an Errors collection. You can use On Error Goto and it will trap it. If you get this error, you can then iterate the Errors collection, use the Source property to figure out which layer of the attempt to make the connection caused the error, and handle it accordingly.

The reason for this is because you want to get at the SQL error that caused the problem, and it may be that if the connection returns an error at this level another object will raise an error of its own which won't tell you what the root cause of the error is. In this case it wasn't so; the error pretty much said what the problem is. But if the error is "Application-defined or object-defined error" that isn't going to be much help.
Was This Post Helpful? 0
  • +
  • -

#9 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 07:30 AM

I'm "incorrect"? That's funny - had to chuckle. What I am telling you is in fact true. But I will recap.

1. CONN.Errors.Count = 0
2. On Error Goto does NOT trap the error
3. An internal VB application error message giving the runtime error appears
4. Clicking [OK] shuts the application down.

This IS in FACT what takes place.
Was This Post Helpful? 0
  • +
  • -

#10 donnellkar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 28-February 12

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 02:23 PM

Thanks for trying to sort this problem astrodon, but if you had read this thread you would have realised that the problem was sorted on the 2nd of March 2012, please read above
Was This Post Helpful? 0
  • +
  • -

#11 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 02:23 PM

Well then. Since you say it's a FACT, in capital letters no less, I had to see if I had told the several hundred people in the 50-odd classes that I have taught on the subject the wrong thing, even after all these years have gone by. So, I put this code together:
Option Explicit

Private Sub Form_Load()
On Error GoTo ErrHandle

Dim insertsql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\nwind.mdb"
cn.Open
insertsql = " INSERT INTO Orders ( Customer, OrderDate ) Values ('Bob Rodes' , '" & Format$(Now, "mm/dd/yyyy") & "' )"
cn.Execute insertsql
cn.Close
'Set rs = New ADODB.Recordset
'rs.Open "select * from customers", cn

Stop
Exit Sub

ErrHandle:

MsgBox "Happily chugging away in the error handler"
End Sub

I put a breakpoint on the MsgBox line, and did the following in the Immediate window:
? cn.Errors.Count
1
? cn.Errors.Item(0).Number
-2147217900 
? Hex(-2147217900)
80040E14
? cn.Errors.Item(0).Description
The INSERT INTO statement contains the following unknown field name: 'Customer'.  Make sure you have typed the name correctly, and try the operation again.

I believe that this at least makes clear why I've said you're wrong. Perhaps you would care to demonstrate why you believe the facts are as you state them, rather than just stating them as you have. After all, my demonstration clearly contradicts your version of the facts, and a chuckle just isn't a demonstration. No matter how much you had to do it.
Was This Post Helpful? 0
  • +
  • -

#12 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 02:51 PM

Hold on now...

I was taking exception to your saying that what I was mistaken by what I experienced; which means in fact that in your opinion, I did not experience the error, etc. Not about any of the machinations of the coding, etc. other than these facts

?CONN.Errors.Count returns 0
and On Error GoTo DOES NOT trap the error - which it does not.

The error message presents itself without benefit of going to ANY error handler and 'happily' shuts down the application. I cannot intentionally reproduce the error, it simply presents itself occasionally. I can only assume it to be some other event than by an ADODB.Connection, because CONN.Errors.Count = 0 by every strategically placed Debug.Print CONN.Errors.Count. What ever is generating the error is not from a db connection.

So, back to the original question: how then may this error be trapped? As it is avoiding all the error traps placed in every procedure and event called or triggered.
Was This Post Helpful? 0
  • +
  • -

#13 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 04:10 PM

You haven't explained what you experienced, but the statement that these errors are "not trappable with VB6 error trapping schemes" is incorrect. (Granted, you only said it was apparently true.)

My code example is an example of how ADODB object errors may be trapped. It intentionally reproduces the error. If this code supports your position in your environment (error won't trap), I'd be most interested to hear about it.
Was This Post Helpful? 0
  • +
  • -

#14 astrodon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 14-February 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 06:17 PM

[quote]
You haven't explained what you experienced...
[\quote]

Are you seriuos?

Sorry I wasted your time.

L8R
Was This Post Helpful? 0
  • +
  • -

#15 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: VB6 Runtime Error -2147217900 (80040e14)

Posted 13 July 2012 - 06:57 PM

let me clarify that. You haven't explained HOW you experienced what you experienced, and I would like to know.

I can split hairs with you, too, you know: I never said that you were incorrect in what you experienced. I said that the (apparent) conclusion that you derived from it was incorrect. But I don't want to do that. I want to find out how you got the result you describe. I could tell you I saw a purple cow, and get offended when you didn't take my statement at face value, but I expect you'd want me to provide evidence before you accepted my statement as fact.

Now, suppose you stop being prickly, relax a bit, and explain how it is that you got the result you describe. I'm not saying it's impossible, rather, I'm curious how it might have come about. Of course if you're too angry to do that, then God bless and see you at the next rodeo.

This post has been edited by BobRodes: 13 July 2012 - 07:07 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2