Need help w/ MySQL 5, ADO.Net - getting serialization error

[alreadyused]

I'm stupmed; I'm trying to use MySQL 5 w/ DataSets and VB.Net 2003, and am to the point where I'm ready to update the database.

 

I'm reading the Microsoft Press ADO.NET book by David Scheppa (starting on pg 408 if that helps anyone); I've also looked at the example by Mike Hillyer here:

http://www.vbmysql.com/articles/vb_mysql_tutorial-part4.html#part14

that uses user input from textboxes.

 

What I'm trying to do is write the generic update routines so I can pass it a row that has changed and have those changes sent to the db, assuming that I have already taken care of changing the row in the datatable. Here's my code, which is basically what is in the book...

 

Public Sub SubmitChangesByHand(ByVal tbl As DataTable)
       Dim cmdUpdate As MySqlCommand = CreateUpdateCommand()
       Dim cmdInsert As MySqlCommand = CreateInsertCommand()
       Dim cmdDelete As MySqlCommand = CreateDeleteCommand

       Dim row As DataRow
       Dim intRows As Integer
       Dim dvrs As DataViewRowState

       dvrs = DataViewRowState.ModifiedCurrent Or DataViewRowState.Deleted Or DataViewRowState.Added
       For Each row In tbl.Select("", "", dvrs)
           Select Case row.RowState
               Case DataRowState.Modified
                   intRows = SubmitUpdate(row, cmdUpdate)
               Case DataRowState.Added
                   intRows = SubmitInsert(row, cmdInsert)
               Case DataRowState.Deleted
                   intRows = SubmitDelete(row, cmdDelete)
           End Select
           If intRows = 1 Then
               row.AcceptChanges()
           Else
               row.RowError = "Update attempt failed"
           End If
       Next

   End Sub

 Private Function CreateInsertCommand() As MySqlCommand
       Dim strSQL As String
       strSQL = "INSERT INTO [vendorparts] " & _
           "(intJobSerial, intUnitSerial, intRevSerial, dblSection, dblPartSerial) " & _
           "VALUES (?intJobSerial, ?intUnitSerial, ?intRevSerial, ?dblSection, ?dblPartSerial)"
       Dim cmd As New MySqlCommand(strSQL, _cn)
       Dim pc As MySqlParameterCollection = cmd.Parameters

       pc.Add("?intJobSerial", MySqlDbType.UInt32)
       pc.Add("?intUnitSerial", MySqlDbType.UInt32)
       pc.Add("?intRevSerial", MySqlDbType.UInt32)
       pc.Add("?dblSection", MySqlDbType.Double)
       pc.Add("?dblPartSerial", MySqlDbType.Double)
       pc.Add("?strAssy", MySqlDbType.String)

       Return cmd
   End Function

   Private Function SubmitInsert(ByVal row As DataRow, ByVal cmd As MySqlCommand) As Integer
       Dim pc As MySqlParameterCollection = cmd.Parameters
       pc("?intJobSerial").Value = row("intJobSerial")
       pc("?intUnitSerial").Value = row("intUnitSerial")
       pc("?intRevSerial").Value = row("intRevSerial")
       pc("?dblSection").Value = row("dblSection")
       pc("?dblPartSerial").Value = row("dblPartSerial")
       pc("?strAssy").Value = row("strAssy")

       Return cmd.ExecuteNonQuery
   End Function

 

On

Return cmd.ExecuteNonQuery

in SubmitInsert, the error thrown is:

Only byte arrays and strings can be serialized by MySQLBinary

...

 

I have no clue where to even go to look for what I need to do here. Any suggestions anyone? My first thought is I have to change either the data types I'm sending or use something other than MySQLBinary for serializing, but that's all new to me... is that the right direction?

[alreadyused]

resolved

 

I didn't do anything with this over the weekend. I read a little more and still didn't find the answer.

 

Saw the error again this morning, and so then I tried replacing all of the parameter's with MySqlDbType.String as the datatype instead of the actual datatype; I'm passing strings, doubles, unsigned ints, and dates, and all works ok.

'Replacement
pc.Add("?intJobSerial", MySqlDbType.String)
pc.Add("?intUnitSerial", MySqlDbType.String)
pc.Add("?intRevSerial", MySqlDbType.String)
pc.Add("?dblSection", MySqlDbType.String)
pc.Add("?dblPartSerial", MySqlDbType.String)
pc.Add("?strAssy", MySqlDbType.String)

 

So, that tells me that command object expects a string and then handles casting to the appropriate datatype itself.

 

If anyone knows more about that and would like expand on that, that would be cool, because I would like to better understand what's going on... but for now, this is working and fixed.

Edited March 20, 2006 by alreadyused