4 Replies - 450 Views - Last Post: 27 December 2017 - 12:48 PM Rate Topic: -----

#1 bfharry   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 23-December 17

UPDATE MySQL column value using VB.NET

Posted 27 December 2017 - 11:47 AM

I am trying to update a value in an existing column in my MySQL database tabel.

My code:
    Sub ChangeSelectedSubjectName()
        Dim connString As String = "Database=xxxxxx;Data Source=xxxxxx; port=xxxx;" _
                 & "User Id=xxxxxx;Password=xxxxxx;"
        Dim conn As New MySqlConnection(connString)
        Dim cmd As New MySqlCommand()

        Try
            conn.Open()
            cmd.ExecuteNonQuery()


            'EDIT DATA IN DB
            If ListBox1.SelectedItem = TextBox2.Text Then

                ListBox1.Items.Clear()
            Else
                cmd.CommandText = "UPDATE `teacher_subjects` SET `holdnavn` = '" & TextBox2.Text & "' WHERE `teacher_subjects`.`ID` = " & subjectID.Text & ";"
                ListBox1.Items.Clear()
            End If


        Catch ex As Exception

            MessageBox.Show("Error While retrieving records On table..." & ex.Message, "Load Records")

        Finally
            conn.Close()
            LoadSavedSubjects()


            TextBox2.Visible = False
            Button5.Visible = False
            Button1.Enabled = True
            Label1.Visible = True
        End Try
    End Sub



.. But for some reason it is not updating my column value.
I think it may be because I need to declare that I am sending a command to my Database.. I am not sure though..

If I put this line of code:
cmd.CommandText = "UPDATE `teacher_subjects` SET `holdnavn` = '" & TextBox2.Text & "' WHERE `teacher_subjects`.`ID` = " & subjectID.Text & ";"


Into my database like following:
UPDATE `teacher_subjects` SET `holdnavn` = 'Matematik 2.C' WHERE `teacher_subjects`.`ID` = 3;"

It will update my column value.

So I have so far found out. It is not my PHP code but most likely something wrong with my VB.net code.
Can you help me debug my error? - However I get no errors in the app. It act like the command has been sended and column is now updated, but it really has not..

What have I done wrong?

Is This A Good Question/Topic? 0
  • +

Replies To: UPDATE MySQL column value using VB.NET

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14158
  • View blog
  • Posts: 56,748
  • Joined: 12-June 08

Re: UPDATE MySQL column value using VB.NET

Posted 27 December 2017 - 12:33 PM

Have you considered using parameters?
http://www.dreaminco...-command-setup/
Was This Post Helpful? 1
  • +
  • -

#3 bfharry   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 23-December 17

Re: UPDATE MySQL column value using VB.NET

Posted 27 December 2017 - 12:37 PM

View Postmodi123_1, on 27 December 2017 - 12:33 PM, said:

Have you considered using parameters?
http://www.dreaminco...-command-setup/


I will look into it, thank you.
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5239
  • View blog
  • Posts: 14,033
  • Joined: 18-April 07

Re: UPDATE MySQL column value using VB.NET

Posted 27 December 2017 - 12:40 PM

Yeah you have to set your CommandText first, make sure your command object knows about your connection object and THEN run ExecuteQuery.

' Open connection first
conn.Open()

' Setup the command object. 
Dim cmd As New MySqlCommand

' Notice how we pass the connection variable to it?
' This associates the command object with its connection to run queries against
cmd.Connection = conn

' Next we tell it the query to run
cmd.CommandText = "UPDATE `teacher_subjects` SET `holdnavn` = '" & TextBox2.Text & "' WHERE `teacher_subjects`.`ID` = " & subjectID.Text & ";"

' Lastly we execute it
cmd.ExecuteNonQuery()



So be sure to check out the order of things here. We set the command to attach to the connection, we setup our command and then execute it. :)
Was This Post Helpful? 1
  • +
  • -

#5 bfharry   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 23-December 17

Re: UPDATE MySQL column value using VB.NET

Posted 27 December 2017 - 12:48 PM

View PostMartyr2, on 27 December 2017 - 12:40 PM, said:

Yeah you have to set your CommandText first, make sure your command object knows about your connection object and THEN run ExecuteQuery.

[ code]
' Notice how we pass the connection variable to it?
' This associates the command object with its connection to run queries against
cmd.Connection = conn


Thank you!
Now I know that. Thank you and it works now!

#1 modi123_1 http://www.dreaminco...-command-setup/
- Your topic is added to my favorites ;)/> Thanks.


Working code:

    Sub ChangeSelectedSubjectName()
        Try
            conn.Open()

            ' Setup the command object. 
            Dim cmd As New MySqlCommand

            ' Notice how we pass the connection variable to it?
            ' This associates the command object with its connection to run queries against
            cmd.Connection = conn

            ' Next we tell it the query to run
            If ListBox1.SelectedItem = TextBox2.Text Then
                'No need to update column.
                ListBox1.Items.Clear()
            Else
                cmd.CommandText = "UPDATE `teacher_subjects` SET `holdnavn` = '" & TextBox2.Text & "' WHERE `teacher_subjects`.`ID` = " & subjectID.Text & ";"

                ListBox1.Items.Clear()
            End If

            ' Lastly we execute it
            cmd.ExecuteNonQuery()



        Catch ex As Exception

            MessageBox.Show("Error While retrieving records On table..." & ex.Message, "Load Records")

        Finally
            conn.Close()
            LoadSavedSubjects()


            TextBox2.Visible = False
            Button5.Visible = False
            Button1.Enabled = True
            Label1.Visible = True
        End Try
    End Sub


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1