Unable to import Data into MySQL from Visual Studio with C#

0

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
c#
mysql
visual-studio
asked on Stack Overflow Nov 8, 2017 by Fusion • edited Nov 8, 2017 by NicoRiff

2 Answers

0

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:

https://www.dotnetperls.com/odbcconnection

answered on Stack Overflow Nov 8, 2017 by NicoRiff
0

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

answered on Stack Overflow Nov 9, 2017 by Fusion

User contributions licensed under CC BY-SA 3.0