?
Solved

SQL Insert for MySQL with Parameters

Posted on 2007-08-12
2
Medium Priority
?
1,705 Views
Last Modified: 2013-11-05
Hi, can some tell me why this code:

Dim con As New OdbcConnection(ConnectionStrings("defaultCon").ConnectionString)
        Dim cmdText As String = "Insert Into dk_players(emailAddress,firstName,lastName,websiteURL,msnName,icqUin,aolIdentity,yahooIdentity,dob,location," & _
            "dateRegistered,status,displayName) Values (?emailAddress,?firstName,?lastName,?websiteURL,?msnName,?icqUin,?aolIdentity,?yahooIdentity,?dob," & _
            "?location,?dateRegistered,?status,?displayName)"

        Dim cmd As OdbcCommand = New OdbcCommand(cmdText, con)
        cmd.CommandType = CommandType.Text
        Dim regDate As Date = DateTime.Now

        cmd.Parameters.AddWithValue("?emailAddress", txtEmailAddress.Text)
        cmd.Parameters.AddWithValue("?firstName", txtRegistrantFirstName.Text)
        cmd.Parameters.AddWithValue("?lastName", txtRegistrantLastName.Text)
        cmd.Parameters.AddWithValue("?websiteURL", txtWebsiteURL.Text)
        cmd.Parameters.AddWithValue("?msnName", txtMSN.Text)
        cmd.Parameters.AddWithValue("?icqUin", txtICQ.Text)
        cmd.Parameters.AddWithValue("?aolIdentity", txtAOLIdentity.Text)
        cmd.Parameters.AddWithValue("?yahooIdentity", txtYahooIdentity.Text)
        cmd.Parameters.AddWithValue("?dob", txtDOB.Text)
        cmd.Parameters.AddWithValue("?location", txtLocation.Text)
        cmd.Parameters.AddWithValue("?dateRegistered", regDate)
        cmd.Parameters.AddWithValue("?status", 1)
        cmd.Parameters.AddWithValue("?displayName", txtDisplayName.Text)

        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

Produces with error:

System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emailAddress,'John'firstName,'Doe'lastName,'www.johndoe.com'websiteURL,''msnName' at line 1

Because I am stumped!!!

Cheers.
0
Comment
Question by:G0ggy
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19680039
Dim con As New OdbcConnection(ConnectionStrings("defaultCon").ConnectionString)
        Dim cmdText As String = "Insert Into dk_players(emailAddress,firstName,lastName,websiteURL,msnName,icqUin,aolIdentity,yahooIdentity,dob,location," & _
            "dateRegistered,status,displayName) Values (?,?,?,?,?,?,?,?,?," & _
            "?,?,?,?)"

odbccommand does not support named parameters, hence you must to like above, and add the parameters in the correct order.
0
 
LVL 11

Author Comment

by:G0ggy
ID: 19680081
Thank you so much!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

807 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