Problem With Insert into MySQL DB using VB.Net 2005

10 Jan 2006 11:34 AM

Materialised

Hello everyone,
I am having a issue inserting values into a MYSQL table, and for the life of
me, I can figure out why. I know the connection is successful, however I am
getting errors.

The table has 4 fields, all of them are Text values.

Here is my code:

Dim myCommand As New MySqlCommand
myCommand.Connection = conn
myCommand.CommandText = "INSERT INTO Order(ONUM, Password, Email, Process) "
_
& "Values (" _
& "'" & txtOrder.Text & "', " _
& "'" & txtPassword.Text & "', " _
& "'" & txtEmail.Text & "', " _
& "'" & comStatus.SelectedItem & "')"

Try
conn.Open()
myCommand.ExecuteNonQuery()
Catch myerror As MySqlException
MsgBox("There was an error updating the database: " & myerror.Message)
End Try

And the error i recieve from it is as follows:
#42000You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'Order(ONUM, Password, Email, Process) Values ('353643', '353623542',
'test@here.' at line 1

I have also noticed that some of the data I enter on my form is shortened.
i.e the email address I actually entered on the form was t***@here.com
however the error message only displays test@here.
Could anyone help me with my issue?
Regards

10 Jan 2006 1:39 PM

zacks

I think your problem is that the combobox.selecteditem property is an
object, and you will either need to apply the .Text method or assign it
to an onject and use the .ToString method.

10 Jan 2006 1:53 PM

Ken Tucker [MVP]

Hi,

Like Zack said you should try comStatus.SelectedItem.ToString or
comStatus.Text in your insert command. A good place for questions about the
mysql dot net connector is here.

http://forums.mysql.com/list.php?38

Ken
-------------------
Show quoteHide quote

"Materialised" <materiali***@privacy.net> wrote in message
news:42hkdjF1iocjaU1@individual.net...
> Hello everyone,
> I am having a issue inserting values into a MYSQL table, and for the life
> of me, I can figure out why. I know the connection is successful, however
> I am getting errors.
>
> The table has 4 fields, all of them are Text values.
>
> Here is my code:
>
> Dim myCommand As New MySqlCommand
> myCommand.Connection = conn
> myCommand.CommandText = "INSERT INTO Order(ONUM, Password, Email, Process)
> " _
> & "Values (" _
> & "'" & txtOrder.Text & "', " _
> & "'" & txtPassword.Text & "', " _
> & "'" & txtEmail.Text & "', " _
> & "'" & comStatus.SelectedItem & "')"
>
> Try
> conn.Open()
> myCommand.ExecuteNonQuery()
> Catch myerror As MySqlException
> MsgBox("There was an error updating the database: " & myerror.Message)
> End Try
>
> And the error i recieve from it is as follows:
> #42000You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'Order(ONUM, Password, Email, Process) Values ('353643', '353623542',
> 'test@here.' at line 1
>
> I have also noticed that some of the data I enter on my form is shortened.
> i.e the email address I actually entered on the form was t***@here.com
> however the error message only displays test@here.
> Could anyone help me with my issue?
> Regards
>

10 Jan 2006 2:41 PM

Brian Wakem

Materialised wrote:
Show quoteHide quote

> Hello everyone,
> I am having a issue inserting values into a MYSQL table, and for the life of
> me, I can figure out why. I know the connection is successful, however I am
> getting errors.
>
> The table has 4 fields, all of them are Text values.
>
> Here is my code:
>
> Dim myCommand As New MySqlCommand
> myCommand.Connection = conn
> myCommand.CommandText = "INSERT INTO Order(ONUM, Password, Email, Process) "

Order is a reserved word. You will find you cannot even execute 'SELECT
* FROM Order'. You need to put backticks around `Order`, or chose a
more suitable table name.

--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png