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);
}
}