Cannot insert values into database

Question

I am having problems getting my VB.net application to insert values into my MySQL. I can connect to the db, but cannot insert values. I have downloaded the DLL and have attached it as a reference. I have also taken the query written and plugged it directly into the db and it works. I have tried multiple ways of coding it from the examples I have found on this website, but I still cannot get it to work. This is the code I am trying to get to work:

Try

MysqlConn = New MySqlConnection("server=localhost;userid=User;password=******;database=Targets")

MysqlConn.Open()

            Dim sqlCommand As New MySqlCommand

            sqlCommand.CommandType = CommandType.Text

            sqlCommand.CommandText = "insert into Victims(UserName, Machine, IP) values('" + strUser + "','" + strMachine + "','" + strIPAddress + "')"

            Dim strRD As MySqlDataReader

            strRD = sqlCommand.ExecuteReader()

            MysqlConn.Close()

            MessageBox.Show("connected")

  Catch ex As MySqlException
            MessageBox.Show("error")

I have declared the variables used in the query statement earlier in the code. Thanks for any help you could offer.

Also, [beware of little bobby tables.](https://xkcd.com/327/) — Zohar Peled, Jun 08 '15 at 14:08
See this [previous SO question](http://stackoverflow.com/questions/9234753/inserting-data-into-a-mysql-table-using-vb-net) (scroll down for solution that works). — ChicagoMike, Jun 08 '15 at 14:20
ExecuteReader is for results that come back with multiple rows. ExecuteNonQuery is for queries that do not come back with results. — Feign, Jun 08 '15 at 14:59
as @Rahul stated, use `ExecuteNonQuery()` instead. `ExecuteReader()` is used when you are getting data returned from the database, such as in a select statement — RianBattle, Jun 08 '15 at 15:00
Thanks for all your help. I still cant get it to insert values even with the ExecuteNonQuery() change. I have looked at the other questions and they don't offer a solution either. I am not that good with databases so I am not entirely sure about the "little bobby tables" link. — rusty, Jun 08 '15 at 19:07

score 0 · Answer 1 · answered Jun 08 '15 at 20:56

0

You can try this instead..

Try

        MysqlConn = New MySqlConnection("server=localhost;userid=User;password=******;database=Targets")

        MysqlConn.Open()

        Dim query as String

        query = "INSERT INTO Victims(UserName, Machine, IP) VALUES('" + strUser + "','" + strMachine + "','" + strIPAddress + "')"

        Dim cmd As MySqlCommand
        cmd = New MySqlCommand(query,MysqlConn)
        cmd.ExecuteNonQuery()
        MysqlConn.Close()
        MessageBox.Show("connected")

Catch ex As MySqlException MessageBox.Show("error") End Try

answered Jun 08 '15 at 20:56

Dovah

30
6

Thank you for your help. I used your code and it does the same thing it was doing before. I am able to get the application connected but not insert the values. I see it is connected by running "show processlist;" in mysql and it shows a new sleep connection. I am guessing that means that the code didnt get to the MysqlConn.Close() part of the code. I receive the error message box when I try and insert values. – rusty Jun 09 '15 at 12:42
What's the error message? Like Invalid field value or something? – Dovah Jun 09 '15 at 16:58

score 0 · Accepted Answer · answered Jun 09 '15 at 14:49

Public Function InsertVal() As Boolean

    Dim iReturn As Boolean
    Using MysqlConn As New MySqlConnection(connectionString)
        Using sqlCommand As New MySqlCommand()
            With sqlCommand
                .CommandText = "INSERT INTO Victims (UserName, Machine, IP) VALUES(@User,@Machine,@IP)"
                .Connection = MysqlConn
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@User", lblDisplayUser.Text)
                .Parameters.AddWithValue("@Machine", lblDisplayMachine.Text)
                .Parameters.AddWithValue("@IP", lblDisplayIP.Text)

            End With
            Try
                MysqlConn.Open()
                sqlCommand.ExecuteNonQuery()
                iReturn = True
            Catch ex As MySqlException
                MsgBox(ex.Message.ToString)
                iReturn = False
            Finally
                MysqlConn.Close()
            End Try
        End Using
    End Using

    Return InsertVal

This is the solution that worked for me. I had to change it to a function and work off the detailed error messages. Thanks for everybody's help.

Just to help clarify this a little better, remember when using strings instead of parameters, the querystring's characters are all used. For instance Select * from myDB where Username is 'Larry O'Hare' will cause the statement to have problems by parsing the extra ' character... Using parameters fixes this issue, along with helping block hackers from using things like SQL injection. — JLane, Jun 09 '15 at 14:56

Cannot insert values into database

2 Answers2