Easier way to get data from the CSV file and substitute data from other tables in columns with foreign key

Viewed 24

Anyone can help? All day I can not understand why I get an error.

Error is:

SqlParameter with ParameterName '@FKTO' is not included in SqlParameterCollection

Maybe there is an easier way to get data from the CSV file and substitute data from other tables in columns with foreign key.

private void insertInTab()
{
        try
        {
            sqlConnection = new SqlConnection(ConnectionMSSQLServer);

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            const string strQueryRechnung = @"IF NOT EXISTS (SELECT Menge, Einheit, BetragBrutto, Beginn, Ende, tblFkto.IdFKTO AS IdF, tblGeraet.Id_Geraet AS IdG
                                                                FROM tblRechnung, tblFkto, tblGeraet
                                                                WHERE 
                                                                      tblFkto.FKTO = @FKTO
                                                                AND   tblGeraet.Bezeichnung = @Bezeichnung
                                                                AND   Menge = @Menge
                                                                AND   Einheit = @Einheit
                                                                AND   BetragBrutto = @BetragBrutto
                                                                AND   Beginn = @Beginn
                                                                AND   Ende = @Ende) 
                                                               INSERT INTO tblRechnung (Menge, Einheit, BetragBrutto, Beginn, Ende, IdFKTO, IdGeraet) 
                                                               VALUES (@Menge, @Einheit, @BetragBrutto, @Beginn, @Ende, tblFkto.IdFKTO, tblGeraet.Id_Geraet);";


            using (sqlCommand = new SqlCommand(strQueryRechnung, sqlConnection))
            {
                sqlCommand.Parameters.Add("@tblFkto.IdFKTO", SqlDbType.Int);
                sqlCommand.Parameters.Add("@blGeraet.Id_Geraet", SqlDbType.Int);
                sqlCommand.Parameters.Add("@Menge", SqlDbType.Int);
                sqlCommand.Parameters.Add("@Einheit", SqlDbType.NVarChar, 50);
                sqlCommand.Parameters.Add("@BetragBrutto", SqlDbType.SmallMoney);
                sqlCommand.Parameters.Add("@Beginn", SqlDbType.DateTime);
                sqlCommand.Parameters.Add("@Ende", SqlDbType.DateTime);

                sqlConnection.Open();

                for (int i = 2; i < dgvCSVRechnung.Rows.Count; i++)
                {
                    sqlCommand.Parameters["@FKTO"].Value = dgvCSVRechnung.Rows[i].Cells[0].Value;
                    sqlCommand.Parameters["@Bezeichnung"].Value = dgvCSVRechnung.Rows[i].Cells[2].Value;
                    sqlCommand.Parameters["@Menge"].Value = Int32.Parse((string)dgvCSVRechnung.Rows[i].Cells[3].Value);
                    sqlCommand.Parameters["@Einheit"].Value = dgvCSVRechnung.Rows[i].Cells[4].Value;
                    sqlCommand.Parameters["@BetragBrutto"].Value = Decimal.Parse((string)dgvCSVRechnung.Rows[i].Cells[5].Value);
                    sqlCommand.Parameters["@Beginn"].Value = DateTime.ParseExact(dgvCSVRechnung.Rows[i].Cells[6].Value.ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture);
                    sqlCommand.Parameters["@Ende"].Value = DateTime.ParseExact(dgvCSVRechnung.Rows[i].Cells[7].Value.ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture);

                    sqlCommand.ExecuteNonQuery();
                }
            }

            sqlConnection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Fehler!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
}
0 Answers
Related