[SOLVED] MySQL Update Query - What am I doing wrong?

[kismetgerald]

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:MMs"))
.Parameters.AddWithValue("@ccAuthorizedUseEnd", Format(DateTimePickerEndDate.Value, "yyyy-MM-dd HH:MMs"))
.Parameters.AddWithValue("@ccZipcode", TextBoxZipCode.Text)
.Parameters.AddWithValue("@dateModified", Format(DateTime.Now, "yyyy-MM-dd HH:MMs"))
.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]

[brandonio21]

When you run this code does it return with "Card info NOT updated!" or "Card info SUCCESSFULLY updated!"?

[brandonio21]

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()

[kismetgerald]

Thanks for the response, when I run the code it's returning the ELSE statement: "Card info NOT updated!"

[kismetgerald]

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!"

[brandonio21]

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:

"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.

[kismetgerald]

You were right about "MAYBE"...lol. It didn't work, I got a

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?

[kismetgerald]

I tried the idea I had, but got the same exception.

[kismetgerald]

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.

[brandonio21]

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!

[kismetgerald]

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.

[brandonio21]

I think we both learned something new this time.

Agreed.