I am trying to import a csv file that is filled with data, into a table in MySQL. The permissions for the user connection have access to all commands such as insert. The code works fine with no errors, and I print a confirmation after each insert. But still, no data is inserted into the table.
StreamReader theFile = new StreamReader("C:\\Users\\Bob\\Documents\\NewInventory.csv");
{
string aLine = null;
while ((aLine = theFile.ReadLine()) != null)
{
try
{
string[] fields = aLine.Split(',');
Item_ID = int.Parse(fields[0]);
Invent_id = int.Parse(fields[1]);
Itemsize = fields[2];
Color = fields[3];
Curr_price = decimal.Parse(fields[4]);
Qoh = int.Parse(fields[5]);
}
catch (Exception ex)
{
}
String sql = "SELECT * FROM item WHERE item_ID=?";
OdbcCommand Command = new OdbcCommand(sql, connection);
Command.Parameters.Add("@ID", OdbcType.Int).Value = Item_ID;
//If query returns a row - there is already an item in the table.
if (Command.ExecuteReader().HasRows)
{
Console.WriteLine("Item ID " + Item_ID + " is already in the database");
}
else
{
String sql3 = "INSERT INTO item (item_id, invent_id, itemsize, color, curr_price, qoh) VALUES( ?, ?, ?, ?, ?, ?) ";
OdbcCommand Command3 = new OdbcCommand(sql3, connection);
Command3.Parameters.Add("@ID", OdbcType.Int).Value = Item_ID;
Command3.Parameters.Add("@INVID", OdbcType.Int).Value = Invent_id;
Command3.Parameters.Add("@SZ", OdbcType.VarChar).Value = Itemsize;
Command3.Parameters.Add("@COL", OdbcType.VarChar).Value = Color;
Command3.Parameters.Add("@PR", OdbcType.Decimal).Value = (decimal)Curr_price;
Command3.Parameters.Add("@QOH", OdbcType.Int).Value = Qoh;
Console.WriteLine("Item ID " + Item_ID + " was added");
}
}
Console.WriteLine("\n\n\nPress ENTER to continue");
Console.ReadLine();
connection.Close();
theFile.Close();
}
UPDATE (Exception Error)
System.Data.Odbc.OdbcException occurred
HResult=0x80131937
Message=
Source=
StackTrace:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcParameter.Bind(OdbcStatementHandle hstmt, OdbcCommand command, Int16 ordinal, CNativeBuffer parameterBuffer, Boolean allowReentrance)
at System.Data.Odbc.OdbcParameterCollection.Bind(OdbcCommand command, CMDWrapper cmdWrapper, CNativeBuffer parameterBuffer)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ProjectLab1.Program.Main(String[] args) in C:\Users\Bob\source\repos\ProjectLab1\ProjectLab1\Program.cs:line 84
You are not executing the SQL code. You have to add this line:
Command3.ExecuteNonQuery();
Anyway, try to always create your connections with using
as that will ensure that all resources have been disposed. Here you have a good example on how to code that:
I was able to solve my issue. My issue was that my OdbcCommand was not running my query. I had to insert it like so...
OdbcCommand Command3 = new OdbcCommand("INSERT INTO item (item_id, invent_id, itemsize, color, curr_price, qoh) VALUES( ?, ?, ?, ?, ?, ?) ", connection);
I also needed to insert the int rowsAffected = Command3.ExecuteNonQuery();
as NicoRiff stated
User contributions licensed under CC BY-SA 3.0