10 Replies - 2714 Views - Last Post: 21 September 2010 - 11:18 AM Rate Topic: -----

#1 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

MySQL 'UPDATE' not working nor outputting error.

Posted 17 September 2010 - 07:02 AM

Hey.

I have made this code, which is to update the MySQL db, the only problem is that even though i got try on, it dosnt catch any erorrs, and the code is not working, meaning it dosn't update anything in the db.

code:
    Private Sub updateSqlData()
        Dim nSQL As String
        If problemBoks.Text.Contains(Environment.NewLine) Or noteBoks.Text.Contains(Environment.NewLine) Then
            Dim newProblem As String = problemBoks.Text.Replace(ControlChars.Lf, "<br>")
            Dim newNote As String = noteBoks.Text.Replace(ControlChars.Lf, "<br>")

            nSQL = "UPDATE kunder SET Adresse='" & adresseBoks.Text & _
                                    "', Postnr='" & postnrBoks.Text & _
                                    "', Town='" & byBoks.Text & _
                                    "', Tlf='" & tlfBoks.Text & _
                                    "', Email='" & emailBoks.Text & _
                                    "', Type='" & pctypeBoks.Text & _
                                    "', Model='" & modelBoks.Text & _
                                    "', OS='" & osBoks.Text & _
                                    "', Ram='" & ramBoks.Text & _
                                    "', CPU='" & cpuBoks.Text & _
                                    "', Password='" & pwBoks.Text & _
                                    "', Problem='" & newProblem & _
                                    "', Note='" & newNote & "' WHERE Navn='" & kundeCombo.SelectedText & "';"

            myCommand.Connection = myConnection
            myCommand.CommandText = nSQL

            Try
                myCommand.ExecuteNonQuery()
            Catch myerror As MySqlException
                MsgBox("Error: " & myerror.Message)
            End Try

        End If
    End Sub


IF anybody could point out where i did the error, i whould be happy.

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL 'UPDATE' not working nor outputting error.

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 17 September 2010 - 08:42 AM

Can we see your string use use for myConnection?

Also, you have this all wrapped entirely in an if ... then block. It appears you could use the if...then to set the newproblem and newnote variables because if they don't contain the newline character, the code in the block won't be executed.

Try this:
Private Sub updateSqlData()
    Dim nSQL As String
    If problemBoks.Text.Contains(Environment.NewLine) Or noteBoks.Text.Contains(Environment.NewLine) Then
        Dim newProblem As String = problemBoks.Text.Replace(ControlChars.Lf, "<br>")
        Dim newNote As String = noteBoks.Text.Replace(ControlChars.Lf, "<br>")
    End if
        nSQL = "UPDATE kunder SET Adresse='" & adresseBoks.Text & _
                                "', Postnr='" & postnrBoks.Text & _
                                "', Town='" & byBoks.Text & _
                                "', Tlf='" & tlfBoks.Text & _
                                "', Email='" & emailBoks.Text & _
                                "', Type='" & pctypeBoks.Text & _
                                "', Model='" & modelBoks.Text & _
                                "', OS='" & osBoks.Text & _
                                "', Ram='" & ramBoks.Text & _
                                "', CPU='" & cpuBoks.Text & _
                                "', Password='" & pwBoks.Text & _
                                "', Problem='" & newProblem & _
                                "', Note='" & newNote & "' WHERE Navn='" & kundeCombo.SelectedText & "';"

        myCommand.Connection = myConnection
        myCommand.CommandText = nSQL

        Try
            myCommand.ExecuteNonQuery()
        Catch myerror As MySqlException
            MsgBox("Error: " & myerror.Message)
        End Try

End Sub



EDIT:
As a side note, you don't really need to check to see if a string contains something to do a replace, if it finds the occurrence, it will replace it with what you specify.

In other words, your code would just be
Private Sub updateSqlData()
    Dim nSQL As String
    Dim newProblem As String = problemBoks.Text.Replace(ControlChars.Lf, "<br>")
    Dim newNote As String = noteBoks.Text.Replace(ControlChars.Lf, "<br>")
   
        nSQL = "UPDATE kunder SET Adresse='" & adresseBoks.Text & _
                                "', Postnr='" & postnrBoks.Text & _
                                "', Town='" & byBoks.Text & _
                                "', Tlf='" & tlfBoks.Text & _
                                "', Email='" & emailBoks.Text & _
                                "', Type='" & pctypeBoks.Text & _
                                "', Model='" & modelBoks.Text & _
                                "', OS='" & osBoks.Text & _
                                "', Ram='" & ramBoks.Text & _
                                "', CPU='" & cpuBoks.Text & _
                                "', Password='" & pwBoks.Text & _
                                "', Problem='" & newProblem & _
                                "', Note='" & newNote & "' WHERE Navn='" & kundeCombo.SelectedText & "';"

        myCommand.Connection = myConnection
        myCommand.CommandText = nSQL

        Try
            myCommand.ExecuteNonQuery()
        Catch myerror As MySqlException
            MsgBox("Error: " & myerror.Message)
        End Try

End Sub


Also one last note, if your code contains an environment.newline (which is a CR and an LF) and you use the replace ControlChars.LF, you will still have a CR in your code giving you

start of string
<br>end of string

instead of
start of string<br>end of string

This post has been edited by CharlieMay: 17 September 2010 - 08:51 AM

Was This Post Helpful? 0
  • +
  • -

#3 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 17 September 2010 - 07:02 PM

My connection string:
myConnection.ConnectionString = "SERVER=localhost;UID=root;Pwd=Password0;DATABASE=havasit"


About the first code of yours, I think you misunderstood me, or maybe I didnt make myself clear.

This part of the code is to UPDATE the data to the MySQL, no matter if it has Newline in it or not.
The newline part was to being able to upload multilined data into the SQL.

This post has been edited by Hypermx: 17 September 2010 - 07:03 PM

Was This Post Helpful? 0
  • +
  • -

#4 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5302
  • View blog
  • Posts: 27,188
  • Joined: 10-May 07

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 17 September 2010 - 07:09 PM

can you output this value, WHERE Navn='" & kundeCombo.SelectedText & "', & then validate that the condition is actually being met with a SELECT statement.
Was This Post Helpful? 1
  • +
  • -

#5 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 18 September 2010 - 01:34 AM

uhm is it me or you dont open the connections
Was This Post Helpful? 0
  • +
  • -

#6 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 18 September 2010 - 04:43 AM

I open the connection in another part of my source, the code posted above is just the part that don't work.

The INSERT and SELECT codes are working fine, so I guess it's not a problem with the connection, however this code dont work.

I could post my full code, but I'm not sure what it would help if it's THIS code.

This post has been edited by Hypermx: 18 September 2010 - 04:44 AM

Was This Post Helpful? 0
  • +
  • -

#7 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 20 September 2010 - 03:31 PM

View Postno2pencil, on 17 September 2010 - 06:09 PM, said:

can you output this value, WHERE Navn='" &amp; kundeCombo.SelectedText &amp; "', &amp; then validate that the condition is actually being met with a SELECT statement.


Yes, This does fit in the database, Ive manually checked.

However, If it were to be wrong, It would show me a error message with syntax error in the sql query, and it dosn't.

Thats why it's soo strange for me, since it dosn't show a error message, nor does it UPDATE the database.

This post has been edited by Hypermx: 20 September 2010 - 03:32 PM

Was This Post Helpful? 0
  • +
  • -

#8 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5302
  • View blog
  • Posts: 27,188
  • Joined: 10-May 07

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 20 September 2010 - 06:44 PM

View PostHypermx, on 20 September 2010 - 04:31 PM, said:

View Postno2pencil, on 17 September 2010 - 06:09 PM, said:

can you output this value, WHERE Navn='" &amp; kundeCombo.SelectedText &amp; "', &amp; then validate that the condition is actually being met with a SELECT statement.

However, If it were to be wrong, It would show me a error message with syntax error in the sql query, and it dosn't.

I don't think you quite got what I was saying.

Say you had the following two values under the column id, 1 & 2. id is your defining, unique column. Now you throw an update command at it through sql.

update text where id='3';

The above statement will not throw an error, it will return successful. However the success doesn't mean that data is updated into the database, it means that the issued command didn't fail. Since there is no id with a value of 3, there is nothing to update. SQL doesn't know what you are trying to do, it does it & returns a value. Check what you are trying to do at the command prompt, once you get the difference between an update & a non update, you'll have found the problem. Then (& only then) can you replicate this into code.
Was This Post Helpful? 0
  • +
  • -

#9 Hypermx  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 06-August 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 21 September 2010 - 05:47 AM

Oh. I made a msgbox with the sql it was going to send, and noticed the kundeCombo.SelectedText didn't output anything, Instead i used kundeCombo.text, to get the current text.

And it works now. :)

Thanks.
Was This Post Helpful? 1
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 21 September 2010 - 06:06 AM

Which is what no2pencil was trying to have you check here
Was This Post Helpful? 1
  • +
  • -

#11 no2pencil  Icon User is online

  • Toubabo Koomi
  • member icon

Reputation: 5302
  • View blog
  • Posts: 27,188
  • Joined: 10-May 07

Re: MySQL 'UPDATE' not working nor outputting error.

Posted 21 September 2010 - 11:18 AM

I'm glad someone was listening... er reading.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1