CODEWITHSUNDEEP
mysqlvb.net
Riski Febriansyah
Riski Febriansyah

Reputation: 437

cannot insert path file address to mysql using vb.net

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

Upvotes: 1

Views: 686

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460168

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

Upvotes: 1

Related Questions