Run-time error '-2147217900(80040e14)

Posted on 2005-04-07
Last Modified: 2013-12-25
I've been looking for a solution in this channel but i did not find it.
So a get an error when a want to ad een record to a MySQL db.

Here is the error:
Run-time error '-2147217900(80040e14)':
you have an error in your SQl syntax;check the manual that corresponds to you mysql
 version for the right syntax to use near values'(6,'Test')' at line 1.

This is the code where i get the error;
Private Sub Command1_Click()
Dim inputgenre As String
Dim laatste As Integer
rs.Open "select * from Genres", conn
laatste = rs.RecordCount + 1
inputgenre = InputBox("Welke genre ?", "Voeg genre toe!")
conn.Execute "INSERT INTO Genres(GenreID,Genre), values(" & laatste & ",'" & inputgenre & "')", , adExecuteNoRecords <-- error
MsgBox rs.RecordCount
End Sub

The type of fields in the db are Int for genreID and varchar for Genre.

Can someone help me ?
Question by:jphermans
    LVL 53

    Expert Comment

    If you perform it wihtout adExecuteNoRecords, do you get an error then ?

    conn.Execute "INSERT INTO Genres(GenreID,Genre), values(" & laatste & ",'" & inputgenre & "')"

    Author Comment


    I've checked it out and it is the same.
    What is "adExecuteNoRecords" ?

    LVL 44

    Expert Comment

    if laatste is a NUMBER, and the field in the table expects an INT value, then you do not use the '...' to enclose the value in the SQL.  This is the explanation of Dhaest's answer.  The code he posted should rsolve the error.

    LVL 53

    Expert Comment

    Probable an error in your sql : you have a , before values .. leave it out

    conn.Execute "INSERT INTO Genres(GenreID,Genre) values(" & laatste & ",'" & inputgenre & "')"
    LVL 53

    Accepted Solution

    adExecuteNoRecords: If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset. Some application languages allow you to ignore this return value if no Recordset is desired.


    So I guess that's also on the wrong place.
    conn.Execute "INSERT INTO Genres(GenreID,Genre) values(" & laatste & ",'" & inputgenre & "')", adExecuteNoRecords
    LVL 53

    Expert Comment

    Why just a B-grade ?

    PS: are you from Holland or from Belgium ?

    Author Comment

    Sorry , have I give you a B-grade ?
    That was wrong can i change it ?

    I'm from Belgium and when i look at you naam i think that you are also
    from belgium .

    LVL 53

    Expert Comment

    Yep, thats correct. I'm an consultent born and working in Belgium :)

    To change the grade, take a look at this example, but don't bother about it

    Author Comment

    Next time you solved a problem for me, I'll give you a "A".


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now