Parameter causes InvalidCastException when used with update statement

[pfpc]

I am trying to use the following update statement with parameters

This works

Code: Select all

Dim StrPublicHolidayREFNUM As String = Session("PublicHolidayREFNUM").ToString
            mySqlUpdateCommandPublicHolidays.CommandText = "UPDATE ATTENDANCE_PublicHolidays " & _
                                                            "SET Holiday = 'ggg', ObservedDate = 'ff', ObservedBy = 'fff', FULL_HALF = 1 " & _
                                                            "WHERE REFNUM = ?"

            ' Set the Update command
            MySqlDataAdapterPublicHolidays.UpdateCommand = (mySqlUpdateCommandPublicHolidays)
            mySqlUpdateCommandPublicHolidays.ParameterCheck = True

            ' Update any changes made to the database and run the commmand
            MySqlConnection.Open()
            mySqlUpdateCommandPublicHolidays.Prepare()
            mySqlUpdateCommandPublicHolidays.Parameters(0).Value = StrPublicHolidayREFNUM
            mySqlUpdateCommandPublicHolidays.ExecuteNonQuery()
            MySqlConnection.Close()

This doesn't

Code: Select all

Dim StrPublicHolidayREFNUM As String = Session("PublicHolidayREFNUM").ToString
            mySqlUpdateCommandPublicHolidays.CommandText = "UPDATE ATTENDANCE_PublicHolidays " & _
                                                            "SET Holiday = ?, ObservedDate = 'ff', ObservedBy = 'fff', FULL_HALF = 1 " & _
                                                            "WHERE REFNUM = ?"

            ' Set the Update command
            MySqlDataAdapterPublicHolidays.UpdateCommand = (mySqlUpdateCommandPublicHolidays)
            mySqlUpdateCommandPublicHolidays.ParameterCheck = True

            ' Update any changes made to the database and run the commmand
            MySqlConnection.Open()
            mySqlUpdateCommandPublicHolidays.Prepare()
           mySqlUpdateCommandPublicHolidays.Parameters(0).Value = StrPublicHolidayName         mySqlUpdateCommandPublicHolidays.Parameters(1).Value = StrPublicHolidayREFNUM
            mySqlUpdateCommandPublicHolidays.ExecuteNonQuery()
            MySqlConnection.Close()

I have added the strPublicHoliday name parameter
Both are strings
I get the following error
Message "Cannot convert parameter value of type 'System.String' to MySQL type 'MySqlType.Int'." String
The data adapter is set up with the following SQL Command
UPDATE ATTENDANCE_PublicHolidays
SET Holiday = :Holiday,
ObservedDate = ?,
ObservedBy = ?,
FULL_HALF = ?
WHERE REFNUM = ?

The framwork is .net on a Mysql database



The Insert and delete statements work fine - ie

Code: Select all

' Insert the choosen user from the registered users listbox and Update the database
            mySqlInsertCommandPublicHolidays.CommandText = "INSERT INTO ATTENDANCE_PublicHolidays(Holiday, ObservedDate, ObservedBy, FULL_HALF) " & _
                                                            "VALUES (?, ?, ?, ?)"

            ' Set the Insert command
            MySqlDataAdapterPublicHolidays.InsertCommand = (mySqlInsertCommandPublicHolidays)

            mySqlInsertCommandPublicHolidays.ParameterCheck = True
            ' Update any changes made to the database and run the commmand
            MySqlConnection.Open()
            mySqlInsertCommandPublicHolidays.Prepare()
            mySqlInsertCommandPublicHolidays.Parameters(0).Value = StrPublicHolidayName
            mySqlInsertCommandPublicHolidays.Parameters(1).Value = StrPublicHolidayDate
            mySqlInsertCommandPublicHolidays.Parameters(2).Value = StrPublicHolidayLocation
            mySqlInsertCommandPublicHolidays.Parameters(3).Value = StrPublicHolidayFullHalf
            mySqlInsertCommandPublicHolidays.ExecuteNonQuery()
            MySqlConnection.Close()

[Serious]

Try following lines:

Code: Select all

mySqlUpdateCommandPublicHolidays.Parameters(0).MySqlType = MySqlType.VarChar

If this doesn't help send us definition of your database objects and we'll perform testing of your code.

[Guest]

Thanks Serious

This now works as follows

Code: Select all

 ' Update the choosen user into the database
            mySqlUpdateCommandPublicHolidays.CommandText = "UPDATE ATTENDANCE_PublicHolidays " & _
                                                                  "SET Holiday = ?, " & _
                                                                  "ObservedDate = ?, " & _
                                                                  "ObservedBy = ?, " & _
                                                                  "FULL_HALF = ? " & _
                                                                  "WHERE REFNUM = ? "

            ' Set the Update command
            MySqlDataAdapterPublicHolidays.UpdateCommand = (mySqlUpdateCommandPublicHolidays)
            mySqlUpdateCommandPublicHolidays.ParameterCheck = True

            ' Update any changes made to the database and run the commmand
            MySqlConnection.Open()
            mySqlUpdateCommandPublicHolidays.Prepare()
            mySqlUpdateCommandPublicHolidays.Parameters(0).MySqlType = CoreLab.MySql.MySqlType.VarChar
            mySqlUpdateCommandPublicHolidays.Parameters(0).Value = Convert.ToString(StrPublicHolidayName)
            mySqlUpdateCommandPublicHolidays.Parameters(1).MySqlType = CoreLab.MySql.MySqlType.Date
            mySqlUpdateCommandPublicHolidays.Parameters(1).Value = StrPublicHolidayDate
            mySqlUpdateCommandPublicHolidays.Parameters(2).MySqlType = CoreLab.MySql.MySqlType.VarChar
            mySqlUpdateCommandPublicHolidays.Parameters(2).Value = StrPublicHolidayLocation
            mySqlUpdateCommandPublicHolidays.Parameters(3).MySqlType = CoreLab.MySql.MySqlType.Double
            mySqlUpdateCommandPublicHolidays.Parameters(3).Value = StrPublicHolidayFullHalf
            mySqlUpdateCommandPublicHolidays.Parameters(4).MySqlType = CoreLab.MySql.MySqlType.Int
            mySqlUpdateCommandPublicHolidays.Parameters(4).Value = StrPublicHolidayREFNUM
            mySqlUpdateCommandPublicHolidays.ExecuteNonQuery()
            MySqlConnection.Close()

            ' Finished with the command
            mySqlUpdateCommandPublicHolidays.Dispose()