+- BP Forums (https://bpforums.info)
+-- Forum: Archived Forums (https://bpforums.info/forumdisplay.php?fid=55)
+--- Forum: Archived Forums (https://bpforums.info/forumdisplay.php?fid=56)
+---- Forum: VB.NET (Visual Basic 2010/2008) (https://bpforums.info/forumdisplay.php?fid=8)
+----- Forum: Programming Help (https://bpforums.info/forumdisplay.php?fid=9)
+----- Thread: [SOLVED] MySQL Update Query - What am I doing wrong? (/showthread.php?tid=678)
[SOLVED] MySQL Update Query - What am I doing wrong? - kismetgerald - 10-17-2012
Hello guys,
You should all know that my learning of the Visual Basic language is improving. I can't stress enough how much Brandon's tutorials have helped me.
So, I'm working on a query to update records in my MySQL database. For some strange reason, the DB isn't being updated and I can't figure out what's wrong. Any assistance would be really appreciated, thanks.
HERE'S MY CODE:
[code2=vbnet]Private Sub updateCard()
Call encryptCard()
Dim ConnectionString As String = String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password)
Dim myQuery As String = "UPDATE cc_master " & _
"SET ccType = @ccType, ccNumber = @ccNumber, ccExpireMonth = @ccExpireMonth, " & _
"ccExpireYear = @ccExpireYear, ccAuthorizedUseStart = @ccAuthorizedUseStart, " & _
"ccAuthorizedUseEnd = @ccAuthorizedUseEnd, ccZipcode = @ccZipcode, dateModified = @dateModified, modifiedBy = @modifiedBy " & _
"WHERE ccID = @ccID"
Using dbConn As New MySqlConnection(ConnectionString)
Using dbComm As New MySqlCommand()
With dbComm
.Connection = dbConn
.CommandType = CommandType.Text
.CommandText = myQuery
.Parameters.AddWithValue("@ccID", ListViewCard.SelectedItems(0).ToString)
.Parameters.AddWithValue("@ccType", ComboBoxCardType.Text)
.Parameters.AddWithValue("@ccNumber", encryptedCard)
.Parameters.AddWithValue("@ccExpireMonth", TextBoxExpireMonth.Text)
.Parameters.AddWithValue("@ccExpireYear", TextBoxExpireYear.Text)
.Parameters.AddWithValue("@ccAuthorizedUseStart", Format(DateTimePickerStartDate.Value, "yyyy-MM-dd HH:MM
s")).Parameters.AddWithValue("@ccAuthorizedUseEnd", Format(DateTimePickerEndDate.Value, "yyyy-MM-dd HH:MM
s")).Parameters.AddWithValue("@ccZipcode", TextBoxZipCode.Text)
.Parameters.AddWithValue("@dateModified", Format(DateTime.Now, "yyyy-MM-dd HH:MM
s")).Parameters.AddWithValue("@modifiedBy", FormLogin.TextBoxUsername.Text)
End With
Try
Dim affectedRow As Integer
dbConn.Open()
affectedRow = dbComm.ExecuteNonQuery()
If affectedRow > 0 Then
MessageBox.Show("Card info SUCCESSFULLY updated!")
Else
MessageBox.Show("Card info NOT updated!")
End If
Catch ex As Exception
MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
End Try
End Using
End Using
End Sub[/code2]
Re: MySQL Update Query - What am I doing wrong? - brandonio21 - 10-17-2012
When you run this code does it return with "Card info NOT updated!" or "Card info SUCCESSFULLY updated!"?
Re: MySQL Update Query - What am I doing wrong? - brandonio21 - 10-17-2012
Also: Try adding an adapter into your code. I'm not sure if it actually does anything, but you can try inserting this:
[code2=vbnet]Dim dbAdapter As New MySqlDataAdapter
dbAdapter.SelectCommand = dbComm[/code2]
Right after Dim affectedRow As Integer
and
Right before dbConn.Open()
Re: MySQL Update Query - What am I doing wrong? - kismetgerald - 10-17-2012
Thanks for the response, when I run the code it's returning the ELSE statement: "Card info NOT updated!"
Re: MySQL Update Query - What am I doing wrong? - kismetgerald - 10-17-2012
Brandon, even with the addition of the adapter as suggested, no rows are being affected. Code is still returning the ELSE statement: "Card info NOT updated!"
Re: MySQL Update Query - What am I doing wrong? - brandonio21 - 10-17-2012
kismetgerald Wrote:Brandon, even with the addition of the adapter as suggested, no rows are being affected. Code is still returning the ELSE statement: "Card info NOT updated!"So this means that everything is working properly, but the Query is simply not doing anything.
Well, the only thing that I can think of is this line:
kismetgerald Wrote:"WHERE ccID = @ccID"Here, you provide a direct link between ccID (Which is an INT in the database) with @ccID (Which is a string in your application).
So, instead of using "ListViewCard.SelectedItems(0).ToString" as your ccID Parameter, I think you should switch the line to:
[code2=vbnet].Parameters.AddWithValue("@ccID", CInt(ListViewCard.SelectedItems(0).ToString))[/code2]
Maybe that will do something. Maybe.
Re: MySQL Update Query - What am I doing wrong? - kismetgerald - 10-17-2012
You were right about "MAYBE"...lol. It didn't work, I got a
Quote:InvalidCastException: Conversion from string "ListViewItem: {12}" to type 'Integer' is not valid.
What about this:
Since I'm populating my form via the SelectedIndexChanged event of the Listview, what if I created a variable called ccID, for example, and assigned the selected index (which in this case is 0) - then use the variable in the parameter?
Would this work?
Re: MySQL Update Query - What am I doing wrong? - kismetgerald - 10-17-2012
I tried the idea I had, but got the same exception.
Re: MySQL Update Query - What am I doing wrong? - kismetgerald - 10-17-2012
Brandon,
Alas, I figured out the problem. I noticed that in all the exceptions I was getting, the selectedindex value was being refered to as "ListViewItem={12}" but never the actual value of "12". So I removed the .ToString and used .Text instead and viola - it worked. Also, the adapter wasn't needed so I removed it. The query still executed without it.
SO HERE'S THE SOLUTION:
[code2=vbnet].Parameters.AddWithValue("@ccID", CInt(ListViewCard.SelectedItems(0).Text))[/code2]
Thank you so very much, I really appreciate the help.
Re: [SOLVED] MySQL Update Query - What am I doing wrong? - brandonio21 - 10-18-2012
Ah fantastic! Okay. I assumed that the ListView operated just like a ListBox. However, I guess that is not the case! I'm glad you figured it out! Sorry I didn't figure that out!
Re: [SOLVED] MySQL Update Query - What am I doing wrong? - kismetgerald - 10-18-2012
No need to apologize buddy. I've learned a lot from you, and I really appreciate your willingness to assist. I think we both learned something new this time.
Re: [SOLVED] MySQL Update Query - What am I doing wrong? - brandonio21 - 10-18-2012
kismetgerald Wrote:I think we both learned something new this time.Agreed.