Run-time error '-2147217900(80040e14)

Posted on 2005-04-07
Medium Priority
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
  • 5
  • 3
LVL 53

Expert Comment

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

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

Author Comment

ID: 13726012

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

LVL 44

Expert Comment

ID: 13726017
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.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 53

Expert Comment

ID: 13726042
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

Dhaest earned 375 total points
ID: 13726065
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.

(see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcmdexecute.asp)

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

ID: 13726405
Why just a B-grade ?

PS: are you from Holland or from Belgium ?

Author Comment

ID: 13726435
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

ID: 13726503
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

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


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

839 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