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.
Thanks in advance for any help.
-Car
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 ClassThanks in advance for any help.
-Car
