Fatal Error when inserting data into a Mysql DB

Caraldur

Member
Joined
Oct 23, 2009
Messages
19
Programming Experience
Beginner
Hello everyone,

I need some assistance with some of my code for inserting data into a mysql database. My submit form stores the field values in an array and passes the array to a public sub that creates a dataset and datatable. The array is than used to fill at datarow which is then pushed into the new datatable. After this is done I pass the dataset and the SQL statement to a private sub used to insert the data into the mysql database.
I keep getting a "Fatal error encountered during command execution." I have tried inserting data without using the parameters and the insert works except for the date but that I can figure out. When I use parameters I get the above error. Below is the Class file I am using to handle the retrieving and submitting of data to the database.

VB.NET:
Imports MySql.Data.MySqlClient

Public Class CastProduction

    Dim ConStr As String = My.Settings.workorder_dbConStr
    Dim con As New MySqlConnection

    Public Function getMaterialList() As DataSet
        'declare MySQL select statement
        Dim sql As String = "Select * from material where material.`Pigment` = " & "0" & " and material.`Curative` = " & "0" & ""
        Dim ds As New DataSet ' create dataset to store table data
        con = New MySqlConnection(ConStr) 'create MySQL Connection
        Try
            Dim taMaterialList As New MySqlDataAdapter
            taMaterialList.SelectCommand = New MySqlCommand(sql, con)
            con.Open()
            taMaterialList.Fill(ds, "MaterialInfoList")
            con.Close()

            sql = "select * from material where material.`Pigment`= " & "1" & " and material.`Curative` = " & "0" & ""
            taMaterialList.SelectCommand = New MySqlCommand(sql, con)
            con.Open()
            taMaterialList.Fill(ds, "PigmentInfoList")
            con.Close()

            sql = "select * from material where material.`Pigment`= " & "0" & " and material.`Curative` = " & "1" & ""
            taMaterialList.SelectCommand = New MySqlCommand(sql, con)
            con.Open()
            taMaterialList.Fill(ds, "CurativeInfoList")
            con.Close()

            sql = "select partID, partNumber from partinfo"
            taMaterialList.SelectCommand = New MySqlCommand(sql, con)
            con.Open()
            taMaterialList.Fill(ds, "PartInfoList")
            con.Close()
        Catch ex As Exception
            MsgBox("Material List: " & ex.Message.ToString)
        End Try
        Return ds
    End Function

    Public Function getPartInfo(ByRef id As Integer) As DataSet
        Dim sql As String = "Select partinfo.*, customers.`CustID`,customers.`CustName` from partinfo Left Join customers on partinfo.`CustID` = customers.`CustID` where partinfo.`PartID` = " & id
        Dim ds As New DataSet
        con = New MySqlConnection(ConStr)

        Try
            Dim taPartInfo As New MySqlDataAdapter
            taPartInfo.SelectCommand = New MySqlCommand(sql, con)
            con.Open()
            Console.WriteLine("Partinfo Connection Open")
            taPartInfo.Fill(ds, "PartInfo")
        Catch ex As Exception
            MsgBox("Part List: " & ex.Message.ToString)
        End Try
        con.Close()
        Console.WriteLine("PartInfo Connection Closed")
        Return ds

    End Function

    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(New DataColumn("PartID"))
            .Add(New DataColumn("PartDescription"))
            .Add(New DataColumn("CustID"))
            .Add(New DataColumn("ProductionDate"))
            .Add(New DataColumn("GoodParts"))
            .Add(New DataColumn("RejectParts"))
            .Add(New DataColumn("Operator"))
            .Add(New DataColumn("Material"))
            .Add(New DataColumn("MatLot"))
            .Add(New DataColumn("Curative"))
            .Add(New DataColumn("CurLot"))
            .Add(New DataColumn("Pigment"))
            .Add(New DataColumn("PigLot"))
            .Add(New DataColumn("Comments"))
        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.Message.ToString)
        End Try
    End Sub

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

            With insertCmd.Parameters
                .Add("@PartID", MySqlDbType.Int16).Value = ds.Tables("CastProduction").Rows(0)("PartID")
                .Add("@PartDescription", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("Partdescription")
                .Add("@CustID", MySqlDbType.Int16).Value = 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 = ds.Tables("CastProduction").Rows(0)("GoodParts")
                .Add("@RejectParts", MySqlDbType.Int16).Value = ds.Tables("CastProduction").Rows(0)("RejectParts")
                .Add("@Operator", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("Operator")
                .Add("@Material", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("Material")
                .Add("@MatLot", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("MatLot")
                .Add("@Curative", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("Curative")
                .Add("@CurLot", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("CurLot")
                .Add("@Pigmnet", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("Pigment")
                .Add("@PigLot", MySqlDbType.VarChar).Value = ds.Tables("CastProduction").Rows(0)("PigLot")
                .Add("@Comments", MySqlDbType.VarChar).Value = 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 Number: " & ex.Number & " - " & ex.Message.ToString)
        Finally
            con.Close()
            Console.WriteLine("Cast Insert Connnection Closed!")
        End Try

    End Sub
  
End Class

Thanks in advance for any help.

-Car
 
Top Bottom