-
Jun 12th, 2019, 04:44 AM
#1
Thread Starter
Member
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:
I am also tring to work out trying to use SQL Parameters as i think this may help?
Thanks Once Again.
James
-
Jun 12th, 2019, 05:02 AM
#2
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).
-
Jun 12th, 2019, 05:51 AM
#3
Thread Starter
Member
-
Jun 12th, 2019, 06:12 AM
#4
Thread Starter
Member
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.
-
Jun 12th, 2019, 06:44 AM
#5
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.
-
Jun 12th, 2019, 07:17 AM
#6
Thread Starter
Member
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
-
Jun 12th, 2019, 07:54 AM
#7
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.
-
Jun 12th, 2019, 08:05 AM
#8
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|