Problems with MySQL insert using Parameters

Question

Hello everyone,

For the last couple of days I've been having problems inserting data into my MySQL database. I'm using parameters to insert the data and have done so in the past without any problems, but for some reason I haven't been able to get it to work this time.

I have a form that the user inputs data to be inserted into the database.  This data is then pushed into an array and sent to my class (CastProduction.VB) via a Public Sub.  The data than pushed into a datarow and than pushed into a datatable. Once this is done I send the dataset and sql insert statement to a private sub that inserts the data into the database.

Below is the Public sub called from the user form.

Public Sub getCastProduction(ByRef arr() As String)
        Dim ds As New DataSet 'create dataset to store datatable

        Dim insertSql As String = _
        "INSERT INTO castproductiondata(PartID, PartDescription, CustID, ProductionDate, GoodParts, RejectParts, Operator, Material, MatLot, Curative, CurLot, Pigment, PigLot, Comments) " & _
        "VALUES(@PartID, @Partdescription, @CustID, @ProductionDate, @GoodParts, @RejectParts, @Operator, @Material, @MatLot, @Curative, @CurLot, @Pigment, @PigLot, @Comments)"

        Dim dt As DataTable = ds.Tables.Add("CastProduction") 'create datatable for cast production data

        With dt.Columns
            .Add("PartID", Type.GetType("System.Int32"))
            .Add("PartDescription", Type.GetType("System.String"))
            .Add("CustID", Type.GetType("System.Int32"))
            .Add("ProductionDate", Type.GetType("System.DateTime"))
            .Add("GoodParts", Type.GetType("System.Int32"))
            .Add("RejectParts", Type.GetType("System.Int32"))
            .Add("Operator", Type.GetType("System.String"))
            .Add("Material", Type.GetType("System.String"))
            .Add("MatLot", Type.GetType("System.String"))
            .Add("Curative", Type.GetType("System.String"))
            .Add("CurLot", Type.GetType("System.String"))
            .Add("Pigment", Type.GetType("System.String"))
            .Add("PigLot", Type.GetType("System.String"))
            .Add("Comments", Type.GetType("System.String"))
        End With

        Try
            Dim newrow As DataRow = dt.NewRow 'create new row in CastProduction Table of the ds dataset
            'fill new datarow

            newrow("PartID") = CInt(arr(0))
            newrow("PartDescription") = arr(1)
            newrow("CustID") = CInt(arr(2))
            newrow("ProductionDate") = arr(3)
            newrow("GoodParts") = CInt(arr(4))
            newrow("RejectParts") = CInt(arr(5))
            newrow("Operator") = arr(6)
            newrow("Material") = arr(7)
            newrow("MatLot") = arr(8)
            newrow("Curative") = arr(9)
            newrow("CurLot") = arr(10)
            newrow("Pigment") = arr(11)
            newrow("Piglot") = arr(12)
            newrow("Comments") = arr(13)
            dt.Rows.Add(newrow) 'push datarow into dataTable

            Console.WriteLine("Part ID: " & dt.Rows(0)("PartID"))
            InsertCastData(insertSql, ds)

        Catch exError As MySqlException
            MsgBox("Error Receiving Array: " & exError.StackTrace.ToString)
        End Try
    End Sub

The below is the Private Sub that is used to insert the data into the database.

 Private Sub InsertCastData(ByRef sql As String, ByRef ds As DataSet)
        Dim con As New MySqlConnection(ConStr)
        Try
            Dim ra As New Integer

            Dim insertCmd As New MySqlCommand(sql, con)

            With insertCmd.Parameters
                .Add("@PartID", MySqlDbType.Int16).Value = "1" 'ds.Tables("CastProduction").Rows(0)("PartID")
                .Add("@PartDescription", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("Partdescription")
                .Add("@CustID", MySqlDbType.Int16).Value = "1" 'ds.Tables("CastProduction").Rows(0)("CustID")
                .Add("@ProductionDate", MySqlDbType.Date).Value = CDate("9-29-2011") 'ds.Tables("CastProduction").Rows(0)("ProductionDate")
                .Add("@GoodParts", MySqlDbType.Int16).Value = "1" 'ds.Tables("CastProduction").Rows(0)("GoodParts")
                .Add("@RejectParts", MySqlDbType.Int16).Value = "1" 'ds.Tables("CastProduction").Rows(0)("RejectParts")
                .Add("@Operator", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("Operator")
                .Add("@Material", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("Material")
                .Add("@MatLot", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("MatLot")
                .Add("@Curative", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("Curative")
                .Add("@CurLot", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("CurLot")
                .Add("@Pigmnet", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("Pigment")
                .Add("@PigLot", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("PigLot")
                .Add("@Comments", MySqlDbType.VarChar).Value = "1" 'ds.Tables("CastProduction").Rows(0)("Comments")
            End With

            con.Open()
            Console.WriteLine("Cast Insert Connection Open!")

            ra = insertCmd.ExecuteNonQuery()
            MsgBox(ra & " production record has been successfully entered.")

        Catch ex As MySqlException
            Debug.WriteLine("Error: " & ex.Message.ToString)
        Finally
            con.Close()
            Console.WriteLine("Cast Insert Connnection Closed!")
        End Try

    End Sub

Hopefully someone can help me with this problem.

 

Thanks in advance for the help.

-Car

 

 

 

 

Answer 1

what is the exact error, and on what line does it occur?

is there a compelling reason to use the dataset? from your code it doesn't seem so, and one potential source of problems is the difference between DataSet and MySQL type definitions.

Answer 2

Hello Car Welcome to MSDN.

Can you please give the error (exception) if any .

Did you debug the code where it is creating problem (pls provide the results) ?.

---

Want to add MVP with my name.

Answer 3

I am getting this error on line 145.

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Error: Fatal error encountered during command execution.

 

Answer 4

what is the exact error, and on what line does it occur?

is there a compelling reason to use the dataset? from your code it doesn't seem so, and one potential source of problems is the difference between DataSet and MySQL type definitions.

I have no compelling reason for using a dataset to insert the data. Mainly I have been trying to use datasets because I want to get more comfortable using them.

 

-Car

Answer 5

does it give an Error Number?

 

I'd run right up to the ExecuteNonQuery, and examine the value of each of your parameters to make sure that they are all correctly formatted (especially the date). could it be for instance that "Pigment" is mispelled in your insertCmd.Parameters block? it isn't in your insert statement, so if you pass the parameter "Pigmnet" as you are, it will fail.

Answer 6

Damn.  I knew it was going to be something stupid.  The misspelling of the parameter "Pigment" was the problem.  I also removed the dataset and used the array sent from the user form to fill the parameter values.

 

Thank you very much for your help.  I have been stuck on this problem for a couple of days and have tried other forums without any help or response to my request for help. So again THANK YOU for you help.

 

-Car

Answer 7

Glad you got it going.

 

i don't mean to poision you agaist datasets; they definitely have their uses, but they are best used with Data/TableAdapters, and i'm not sure if your MySQL provider implements these objects for your datasource. if you have some intense in-memory data manipulation to do, then they are great thought.

Good luck, and remember to mark your thread as answered.