Results 1 to 8 of 8

Thread: Conversion from sring "Update Error" to type 'integer is not valid

  1. #1

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    52

    Conversion from sring "Update Error" to type 'integer is not valid

    Hello,

    First i am loving this forum, it has been very helpful and learnt a fair bit going through past posts.

    But i am here again after several hours of trying to work this outi thought i better ask the professionals.

    Basially i would like to do the following:

    1) Query my SQL DB and look at the selected supplier from my combobox locate the SupplierID from the selected supplier.
    2) Update the DB fields for the selected supplier using the SQL Update command using the supplierID as main field.

    I beleive the error is relating to that the supplier is a integer and my lookup from the combo box is a string.

    In my code i have tried to convert the string to a integer but its not working or am i looking in the worng place.

    Code:
        Private Sub btnupdate_Click(sender As Object, e As EventArgs) Handles btnupdate.Click
            Dim Sqlcon As SqlConnection = New SqlConnection("Data Source=JAMESPC\SUPPORTDB;Initial Catalog=Support_DB;Persist Security Info=True;User ID=username;Password=password")
            Dim SQLcmd As SqlCommand
            Dim SupID As SqlCommand
            Dim result As Integer
            Dim SupIDResult As String = "Select SupplierID from Suppliers where SupplierName = '" & ComboBox1.Text & "'"
            Dim QueryOpen As String = "update suppliers set PhoneNumber ='" & PhoneNumberTextBox.Text & "' Notes ='" & NotesTextBox.Text & "' where SupplierID = '" & result & "'"
    
            Try
                SupID = New SqlCommand(SupIDResult, Sqlcon)
                SQLcmd = New SqlCommand(QueryOpen, Sqlcon)
                Sqlcon.Open()
                result = Convert.ToInt32(SupID.ExecuteScalar())
                'This is for testing so i can see it is the correct supplier ID ref against the SQLStudio lookup.
                MessageBox.Show("The ID is " & result, "Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
                SQLcmd.ExecuteNonQuery()
                MessageBox.Show("The supplier" & " " & ComboBox1.Text & " " & "has been updated.", "Supplier Created", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Sqlcon.Close()
            Catch ex As Exception
                MsgBox("Error while editing the suppliers record..." & " " & ex.Message, "Update Error", MessageBoxIcon.Exclamation)
                Me.Show()
            Finally
                Sqlcon.Close()
                Me.Close()
            End Try
    
        End Sub
    Errror:

    Name:  update error.JPG
Views: 522
Size:  23.2 KB

    I am also tring to work out trying to use SQL Parameters as i think this may help?

    Thanks Once Again.

    James

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    The error message is telling you that the text "Update Error" is not a number, but you are trying to use it as one.

    If you look at your code to see where that text appears, it is here:
    Code:
    MsgBox("Error while editing the suppliers record..." & " " & ex.Message, "Update Error", MessageBoxIcon.Exclamation)
    It seems that you have got the order of the parameters wrong, presumably because you have switched from MessageBox.Show to MsgBox


    I am also tring to work out trying to use SQL Parameters as i think this may help?
    They wouldn't help with this particular issue, but they are definitely a very good idea.

    For an explanation of why (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  3. #3

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    52

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    Grrr what a dumb **** i am i....... now i have spotted it. and the blooming error told me what to look for......

  4. #4

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    52

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    Hmm ok, now the problem is the insert command works to a point:

    But the issue is

    Code:
    where SupplierID = '" & result & "'"
    In debug i can see it giving the correct ID but on the.

    Code:
    SQLcmd.ExecuteNonQuery()
    Query is coming back as a 0

    Attachment 169143

    I am going to re-investigate this afternoon to see if i can solve the issue.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    Ah, you are appending Result to your SQL string before you have run the query, so it will still have its default value of 0.


    Also, in your Update statement in post #1 you are missing a comma here: "' Notes ='" ...but based on your last post I think you may have already fixed that.

  6. #6

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    52

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    Hi Si_the_geek,

    Thanks for your help so far, i thought that so moved
    Code:
     SQLcmd = New SqlCommand(QueryOpen, Sqlcon)
    Just before the
    Code:
     SQLCMD.ExecuteNonQuery
    but the debug is still coming back as "0"

    I have also added instead of converting the result to a integer but dont beleive its part of the problem.

    Code:
    Dim openreader As SqlDataReader = SupID.ExecuteReader()
                If openreader.Read() Then
                    result = openreader.GetValue(0)
                End If
                openreader.Close()
    Thanks James

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    It's not just where you read from QueryOpen that matters, it is also where you set the value of it. Currently that is before you have assigned something to Result


    By the way, there is a 'temporary' issue with the forums where it tries to submit posts twice, so you sometimes get a message saying you have to wait 30 seconds before you can post... it has already posted, so duplicates appear (which moderators then need to delete).

    Unfortunately in this case I made a minor mistake with the duplicate of post #4, so the image is no longer there.

  8. #8

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    52

    Re: Conversion from sring "Update Error" to type 'integer is not valid

    Worked it out before i re-checked the forum and as per si_the_geek response you are quite correct......

    Thanks for letting me know of the temporary issue i will keep my eyes open on this.

    Thanks for your quick response's and pointers in the correct direction.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width