Updating MySQL database in xampp using Visual Studio or Visual Basic

Question

I tried everything that I can do - I'm a beginner at programming. I wanted to update my database in xampp using VB or Visual Studio but can't seem to do it.

This is my code for that form.:

Imports MySql.Data.MySqlClient
Public Class Form4
    Public MysqlConn As MySqlConnection
    Public cmd As New MySqlCommand
    Public da As New MySqlDataAdapter
    Public Sub MysqlConnection()
        MysqlConn = New MySqlConnection()

        'Connection String
        MysqlConn.ConnectionString = "server=localhost;" _
        & "user id=root;" _
        & "password=;" _
        & "database=bank"

        'OPENING THE MysqlConnNECTION
        MysqlConn.Open()

    End Sub
    Public Sub add()
        Dim sql As String
        Dim TempTable As New DataTable

        sql = "update cbank set Balance = Balance +  " & TextBox2.Text & "where AccountID = " & TextBox1.Text & "and PIN = " & TextBox3.Text & ";"
        'bind the connection and query
        With cmd
            .Connection = MysqlConn
            .CommandText = sql
        End With
        da.SelectCommand = cmd

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        MysqlConnection()
        add()

    End Sub

End Class

There is no error it just that there is no output also

I do not see the connection to xampp in your question. It looks like pure ADO.net used in vb.net code. Vb.net is the programming language. Visual Studio is an Integrated Develop Environment (IDE) where you can code in several different languages. — Mary
– Mary, Commented May 18, 2019 at 17:21

Mary · Accepted Answer · 2019-05-18 17:50:47Z

1

Keep you data objects local so you can be sure they are closed and disposed. A Using...End Using block ensures this even if there is an error. You can make your connection string a form level variable so you can use it anywhere but that is the only form level variable you need.

You can pass your connection string directly to the constructor of the connection.

You can pass your sql command text and the connection directly to the constructor of the command.

Please always use Parameters. Not only wil it save you from misplacing quotes but it will help ensure that correct datatypes are sent to the database. The most important thing is it helps protect you database from sql injection which can destroy your database. I had to guess at the datatypes in your database. Check the database and adjust the code accordingly.

Private ConnString As String = "server=localhost;user id=root;password=;database=bank"
Private Sub add()
    Using cn As New MySqlConnection(ConnString)
        Using cmd As New MySqlCommand("update cbank set Balance = Balance + @Balance where AccountID = @ID and PIN = @PIN;", cn)
            cmd.Parameters.Add("@Balance", MySqlDbType.Decimal).Value = CDec(TextBox2.Text)
            cmd.Parameters.Add("@ID", MySqlDbType.Int32).Value = CInt(TextBox1.Text)
            cmd.Parameters.Add("@PIN", MySqlDbType.Int32).Value = CInt(TextBox3.Text)
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

answered May 18, 2019 at 17:50

Mary

15.1k3 gold badges20 silver badges27 bronze badges

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

1 Comment

Roldan Jr Gudiaga Wagwag Over a year ago

wow thank you very much this fix my problem so i think whats happening is im not using parameters and what happen on my code before is there is the sql injection and the language didnt understand what is my command thats why its not updating but now its working... from now on for future uses imma go ahead and use paramaeters for sql command moving on forward tnx for the help

Turbo · Accepted Answer · 2019-05-18 11:10:24Z

0

Quotes problem.

Imports MySql.Data.MySqlClient
Public Class Form4
    Public MysqlConn As MySqlConnection
    Public cmd As New MySqlCommand
    Public da As New MySqlDataAdapter
    Public Sub MysqlConnection()
        MysqlConn = New MySqlConnection()

        'Connection String
        MysqlConn.ConnectionString = "server=localhost;" _
        & "user id=root;" _
        & "password=;" _
        & "database=bank"

        'OPENING THE MysqlConnNECTION
        MysqlConn.Open()

    End Sub
    Public Sub add()
        Dim sql As String
        Dim TempTable As New DataTable

        sql = "update cbank set Balance = Balance +  " & TextBox2.Text & "where AccountID = " & TextBox1.Text & "and PIN = " & TextBox3.Text & ";"
        bind the connection and query
        With cmd
            .Connection = MysqlConn
            .CommandText = sql
        End With
        da.SelectCommand = cmd

    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Me.Hide()
        Form2.Show()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        MysqlConnection()
        add()

    End Sub

    Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub
End Class

answered May 18, 2019 at 11:10

Turbo

13411 bronze badges

1 Comment

Mary Over a year ago

Never encourage a new user to concatenate SQL statements. Always use parameters. You are risking Sql injection.

Collectives™ on Stack Overflow

Updating MySQL database in xampp using Visual Studio or Visual Basic

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related