How to insert to mysql from asp.net using vb?

Question

I am trying to insert into a mysql table. Here is my code...

        Dim cmdText = "INSERT INTO complete_log ('cust_id', 'business', 'note', 'date_stamp') values (@cid,@bus,@notey,@datey)"
    Dim conn As New MySqlConnection("server=theipofserver;user id=id;password=mypass;database=thedatabase")
    Using cmd = New MySqlCommand(cmdText, conn)
        conn.Open()
        Dim testy As String
        testy = TextBox1.Text
        Dim dater = Date.Today + TimeOfDay
        cmd.Parameters.AddWithValue("@cid", c_id)
        cmd.Parameters.AddWithValue("@bus", c_business)
        cmd.Parameters.AddWithValue("@notey", testy)
        cmd.Parameters.AddWithValue("@datey", dater)
        cmd.ExecuteNonQuery()
    End Using

But, I get the following error:

MySql.Data.MySqlClient.MySqlException: 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 ''cust_id', 'business', 'note', 'date_stamp') values ('ID','null','first one'' at line 1

the table I am trying to insert into has 4 columns that are all varchars with plenty of space (varchar(100))

Here are more details on the error... error occurs on line 31...

Line 29:             cmd.Parameters.AddWithValue("@notey", testy)
Line 30:             cmd.Parameters.AddWithValue("@datey", dater)
Line 31:             cmd.ExecuteNonQuery()
Line 32:         End Using
Line 33:         TextBox1.Font.Bold = True

So, what am I doing wrong here? Any tips on better ways to insert into mysql tables would be greatly appreciated!

Do not forget to close the connection (better enclose in using statement) — Steve
– Steve, Commented Jul 22, 2013 at 15:19

juergen d · Accepted Answer · 2016-08-10 21:51:26Z

3

Don't use quotes around table or column names.

INSERT INTO complete_log (`cust_id`, `business`, `note`, `date_stamp`)
values (@cid,@bus,@notey,@datey)

If you want to escape table and column names then use backticks. But you only need to escape reserved words in MySQL.

edited Aug 10, 2016 at 21:51

answered Jul 22, 2013 at 15:16

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Feign Over a year ago

Well, I feel silly now... Thanks a lot juergen! I'll mark this as the answer in a few minutes.

Comfortably Numb · Accepted Answer · 2013-07-22 15:16:19Z

1

Do not specify column names in apostrophies

Instead of

Dim cmdText = "INSERT INTO complete_log ('cust_id', 'business', 'note', 'date_stamp') values (@cid,@bus,@notey,@datey)"

Do

Dim cmdText = "INSERT INTO complete_log (cust_id, business, note, date_stamp) values (@cid,@bus,@notey,@datey)"

answered Jul 22, 2013 at 15:16

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Comments

Sachin · Accepted Answer · 2013-07-22 15:17:25Z

1

Change

INSERT INTO complete_log ('cust_id', 'business', 'note', 'date_stamp')

to

INSERT INTO complete_log (cust_id, business, note, date_stamp)

answered Jul 22, 2013 at 15:17

Sachin

41.1k7 gold badges94 silver badges107 bronze badges

Collectives™ on Stack Overflow

How to insert to mysql from asp.net using vb?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related