cannot insert path file address to mysql using vb.net

Question

i have problem to insert this text to mysql in vb.net

C:\Users\Riski\Documents\Visual Studio 2012\Projects\Remainder\Remainder\images\activ\

and this my source for insert

 Try
                Dim tbimg As String
                tbimg = tbimgpath.text
                'Prepare Connection and Query
                dbconn = New MySqlConnection("Server=localhost;Database=team;Uid=root;Pwd=")

                'OPEN THE DB AND KICKOFF THE QUERY
                dbconn.Open()
                DS = New DataSet
                DA = New MySqlDataAdapter("INSERT INTO tb_team_user (id_team_user,user_ip,user_team,user_image_path) values (null,'" & lip.Text & "','" & tbteam.Text & "','" & tbimg & "')", dbconn)
                DA.Fill(DS, "tb_info_activity")

                'DONE CLOSE THE DB
                dbconn.Close()

                Application.Restart()

            Catch ex As Exception
                MsgBox("cannot connect to database!" & vbCrLf & vbCrLf & ex.Message)
            End Try

if i write without symbol '\' and ':' the insert work fine but if i write with symbol and the insert give me warning just "check the manual that corresponds"

how resolve this?

thanks

Tim Schmelter · Accepted Answer · 2015-03-23 09:13:43Z

1

Don't use string concatenation to build your sql query. Otherwise you are open for sql injection and other issues like this. The backslash introduces escape characters like \r for carriage return. So just use sql-parameters with the correct types:

Using dbconn = New MySqlConnection("connectionstring")
    Dim insertSql = "INSERT INTO tb_team_user (id_team_user,user_ip,user_team,user_image_path)" & _
                    "VALUES (null,@user_ip,@user_team,@user_image_path)"
    Using da As New MySqlDataAdapter()
        da.InsertCommand = New MySqlCommand(insertSql, dbconn)
        da.InsertCommand.Parameters.Add("@user_ip", MySqlDbType.Int32).Value = Int32.Parse(lip.Text)
        da.InsertCommand.Parameters.Add("@user_team", MySqlDbType.VarChar).Value = tbteam.Text
        da.InsertCommand.Parameters.Add("@user_image_path", MySqlDbType.VarChar).Value = tbimg.Text
        ' .. '
    End Using
End Using

Apart from that, why do you use an INSERT-sql for DataAdaper.Fill? You need a Select.

So maybe you want to use MySqlCommand.ExecuteNonQuery instead:

Using dbconn = New MySqlConnection("connectionstring")
    Dim insertSql = "INSERT INTO tb_team_user (id_team_user,user_ip,user_team,user_image_path)" & _
                    "VALUES (null,@user_ip,@user_team,@user_image_path)"
    Using cmd As New MySqlCommand(insertSql, dbconn)
        cmd.Parameters.Add("@user_ip", MySqlDbType.Int32).Value = Int32.Parse(lip.Text)
        cmd.Parameters.Add("@user_team", MySqlDbType.VarChar).Value = tbteam.Text
        cmd.Parameters.Add("@user_image_path", MySqlDbType.VarChar).Value = tbimg.Text
        dbconn.Open()
        Dim insertedCount As Int32 = cmd.ExecuteNonQuery()
    End Using
End Using

edited Mar 23, 2015 at 9:13

answered Mar 23, 2015 at 8:55

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

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

2 Comments

Riski Febriansyah Over a year ago

sorry, I'm just a beginner so still less neatly makes the code, I've tried to follow the code that you gave but I get a warning: refrerence object not set to an instance of an object.

Tim Schmelter Over a year ago

@RiskiFebriansyah: i have edited the first part of my answer with the MySqlDataAdapter. But i'm fairly sure that you need the second. You cannot use DA.Fill without a SelectCommand.

Collectives™ on Stack Overflow

cannot insert path file address to mysql using vb.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related