IConvertable Error on Update Statement

tpra21

Active member
Joined
Oct 21, 2006
Messages
26
Programming Experience
1-3
I am trying to update two rows in a table based on the ReminderID, which is selected by the user from a ComboBox. The COmboBox is binded to the ReminderID field in the Reminders table. The following is the code in Error:


Dim StrUpdate AsString = "UPDATE Reminders SET DueDate = ? , Message = ? WHERE ReminderID = ?"
Dim cmdUpdate AsNew System.Data.OleDb.OleDbCommand(StrUpdate, OleDbConnection2)
cmdUpdate.Parameters.Add("@ReminderID", System.Data.OleDb.OleDbType.Integer, 0, "ReminderID").Value = ComboBox1.SelectedItem
cmdUpdate.Parameters.Add("@Message", System.Data.OleDb.OleDbType.VarWChar, 255, "Message").Value = txtMessage.Text
cmdUpdate.Parameters.Add("@DueDate", System.Data.OleDb.OleDbType.VarWChar, 255, "DueDate").Value = txtDate.Text

I am getting the following exception/error:

System.InvalidCastException: Object must implement IConvertible.

I think the error corresponds to the "value" statements at the end of my parameters, but not sure.

Any help would be awesome.

Thanks, Adam
 
The ComboBox1.SelectedItem will not return the ID you were hoping for so I am pretty sure that is the problem.

I never use bound controls so I am not sure how you reference the ID you need but someone will be along soon who can.

It might be ComboBox1.SelectedItem.ValueMember
 
Thanks for the insight. I agree, I tihnk the combobox is my problem, but I am just starting to get into VB.net DB programming, so I am not sure how to fix it. I'll continue to mess with it.

Thanks again, Adam
 
I changed the ComboBox to ValueMember and now get the following Exception:

Input value not in correct format. My DB field that is bound to the comboBox is integer. Code looks like this:

Dim StrUpdate As String = "UPDATE Reminders SET DueDate = ? , Message = ? WHERE ReminderID = ?"
Dim cmdUpdate As New System.Data.OleDb.OleDbCommand(StrUpdate, OleDbConnection2)
cmdUpdate.Parameters.Add("@ReminderID", System.Data.OleDb.OleDbType.Integer, 0, "ReminderID").Value = ComboBox1.ValueMember
cmdUpdate.Parameters.Add("@Message", System.Data.OleDb.OleDbType.VarWChar, 255, "Message").Value = txtMessage.Text
cmdUpdate.Parameters.Add("@DueDate", System.Data.OleDb.OleDbType.VarWChar, 255, "DueDate").Value = txtDate.Text
 
I changed the ComboBox to ValueMember and now get the following Exception:

Input value not in correct format. My DB field that is bound to the comboBox is integer. Code looks like this:

Dim StrUpdate AsString = "UPDATE Reminders SET DueDate = ? , Message = ? WHERE ReminderID = ?"
Dim cmdUpdate AsNew System.Data.OleDb.OleDbCommand(StrUpdate, OleDbConnection2)
cmdUpdate.Parameters.Add("@ReminderID", System.Data.OleDb.OleDbType.Integer, 0, "ReminderID").Value = ComboBox1.ValueMember

That's because it should be ComboBox1.SelectedItem.ValueMember
 
I have actually changed it to:

ComboBox1.SelectedValue

plus I forgot to set the ValueMember of the comboBox. Of course, now I have a message box saying that I am updating the DB (with no exceptions thrown), yet my information is not getting updated in the DB. I have checked the value of the COmboBox at runtime, and it is holding the correct values. Any ideas?

Thanks, Adam
 
Does the update command that I am using (cmdUpdate) need to be specified as the data adapter's update command in order to work correctly?
 
VB.NET:
Dim StrUpdate AsString = "UPDATE Reminders SET DueDate = ? , Message = ? WHERE ReminderID = ?"
Dim cmdUpdate AsNew System.Data.OleDb.OleDbCommand(StrUpdate, OleDbConnection2)
cmdUpdate.Parameters.Add("@ReminderID", System.Data.OleDb.OleDbType.Integer, 0, "ReminderID").Value = ComboBox1.SelectedItem
cmdUpdate.Parameters.Add("@Message", System.Data.OleDb.OleDbType.VarWChar, 255, "Message").Value = txtMessage.Text
cmdUpdate.Parameters.Add("@DueDate", System.Data.OleDb.OleDbType.VarWChar, 255, "DueDate").Value = txtDate.Text

There's a few problems here. First the parameters in an OleDb query must be added to the collection in the same order that they appear in the SQL statement. So the Parameters should go..

DueDate
Message
ReminderID

Secondly make sre you are using the correct OleDbType, in the duedate parameter you are using the type VarWChar are you sure that it shouldn't be OleDbType.Date?

Finally yes you do have to add the update command to the UpdateCommand of the dataadapter.
 
Thank you all so much!! vis781, you where right on the money with everything you said. Here is the code that works perfect.

VB.NET:
Dim StrUpdate AsString = "UPDATE Reminders SET DueDate = ? , Message = ? WHERE (ReminderID = ?)"
Dim OleDbCommand1 AsNew System.Data.OleDb.OleDbCommand(StrUpdate, OleDbConnection2)
Me.OleDbDataAdapter1.UpdateCommand = OleDbCommand1
OleDbCommand1.Parameters.Add("@DueDate", System.Data.OleDb.OleDbType.Integer, 255, "DueDate").Value = txtDate.Text
OleDbCommand1.Parameters.Add("@Message", System.Data.OleDb.OleDbType.VarWChar, 255, "Message").Value = txtMessage.Text
OleDbCommand1.Parameters.Add("@ReminderID", System.Data.OleDb.OleDbType.Integer, 0, "ReminderID").Value = CInt(ComboBox1.SelectedValue)
 
Try
OleDbConnection2.Open()
OleDbCommand1.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
OleDbConnection2.Close()
MessageBox.Show("The Record Have Been Updated")
EndTry


Thanks again guys!!!
 
Back
Top