I import to Datagridview with Excel. Then I want to save this data to the SQL database, it saves, but at the end it gives an error:

Must declare the scalar variable "@ID".

Can I solve this?

private void btn_save_Click(object sender, EventArgs e) { //dataGridView1.ColumnCount = 6; foreach (DataGridViewRow row in dataGridView1.Rows) { string constring = @"Data Source=192.168.0.3\VADCASQL;Initial Catalog=dbexcel;User Id=sa;Password=***;"; for (int i = 0; i < dataGridView1.Rows.Count; i++) using (SqlConnection con = new SqlConnection(constring)) { using (SqlCommand cmd = new SqlCommand("INSERT INTO test VALUES(@ID, @Pavadinimas, @Kiekis, @Darbuotojas, @Papildymas, @Akcija)", con)) { if(dataGridView1.Rows[i].Cells[0].Value==null) { MessageBox.Show("XXXXXX"); //con.Close(); // this.Close(); //con.Open(); //cmd.ExecuteNonQuery(); //con.Close(); } else cmd.Parameters.AddWithValue("@ID", dataGridView1.Rows[i].Cells[0].Value); if (dataGridView1.Rows[i].Cells[1].Value == null) { cmd.Parameters.AddWithValue("@Pavadinimas", dataGridView1.Rows[i].Cells[1].Value = DBNull.Value); } else cmd.Parameters.AddWithValue("@Pavadinimas", dataGridView1.Rows[i].Cells[1].Value); if (dataGridView1.Rows[i].Cells[2].Value == null) { cmd.Parameters.AddWithValue("@Kiekis", dataGridView1.Rows[i].Cells[2].Value = DBNull.Value); } else cmd.Parameters.AddWithValue("@Kiekis", dataGridView1.Rows[i].Cells[2].Value); if (dataGridView1.Rows[i].Cells[3].Value == null) { cmd.Parameters.AddWithValue("@Darbuotojas", dataGridView1.Rows[i].Cells[3].Value = DBNull.Value); } else cmd.Parameters.AddWithValue("@Darbuotojas", dataGridView1.Rows[i].Cells[3].Value); if (dataGridView1.Rows[i].Cells[4].Value == null) { cmd.Parameters.AddWithValue("@Papildymas", dataGridView1.Rows[i].Cells[4].Value = DBNull.Value); } else cmd.Parameters.AddWithValue("@Papildymas", dataGridView1.Rows[i].Cells[4].Value); if (dataGridView1.Rows[i].Cells[5].Value == null) { cmd.Parameters.AddWithValue("@Akcija", dataGridView1.Rows[i].Cells[5].Value = DBNull.Value); } else cmd.Parameters.AddWithValue("@Akcija", dataGridView1.Rows[i].Cells[5].Value); con.Open(); cmd.ExecuteNonQuery(); con.Close();
  • Sir, make the creation of a command with parameters from a loop (this needs to be done only once), and in the loop, leave only the assignment of values ​​to the parameters (with correct processing of the NULL value) and everything will be right away. - Alexander Muksimov
  • Saved in the database but threw out such an error: The parameterized query '(@ID nvarchar (4000), @ Pavadinimas nvarchar (4000), @Kiekis nvarchar' expects the parameter '@ID', which was not supplied. - Vadim
  • Well, now I’ll lay out the code for how to communicate - Alexander Muksimov
  • Thank you, I look forward to it :) - Vadim

1 answer 1

If you put your code in order according to this scheme

 void btn_save_Click(object sender, EventArgs e) { /* ... */ //Один раз создаем комманду с параметрами вне циклов string constring = @"Data Source=192.168.0.3\VADCASQL;Initial Catalog=dbexcel;User Id=sa;Password=***;"; SqlConnection con = new SqlConnection(constring); //Окрываете Connection в Вашем случае это можно сделать один раз на весь массив из dataGridView1 try { con.Open(); SqlCommand cmd = new SqlCommand("INSERT INTO test VALUES(@ID, @Pavadinimas, @Kiekis, @Darbuotojas, @Papildymas, @Akcija)", con); SqlParameter prmId = cmd.Parameters.Add("Id", SqlDbType.UniqueIdentifier); /* SqlParameter prmPavadinimas = cmd.Parameters.Add("Id", SqlDbType.Ваш тип); и так далее для всех параметров .... */ //Перенос данных из dataGridView1 в базу for (int i = 0; i < dataGridView1.Rows.Count; i++) { if(dataGridView1.Rows[i].Cells[0].Value !=null) { prmId.Value = dataGridView1.Rows[i].Cells[0].Value; //Если нужно то делаем Guid из Rows[i].Cells[0].Value } else { //Нет Id MessageBox.Show("XXXXXX"); continue; } if (dataGridView1.Rows[i].Cells[1].Value == null) { prmPavadinimas.Value = DBNull.Value; } else { prmPavadinimas.Value = dataGridView1.Rows[i].Cells[1].Value); //Возможно, что здесь потребуется явное приведение к типу } //И так далее для всех параметров /* ..... */ //Выполнение команды (Его лучше сделать в отдельном try{} catch{} try { cmd.ExecuteNonQuery(); } catch(Exception ee) { MessageBox.Show(ee.Message); } } } catch( Exception ee) { //Обрабатываете возможное исключение при открытии MessageBox.Show(ee.Message); } finally { con.Close(); } }

That will all work. In your version you did not create the parameters that the command you created required for zero values ​​of the parameters

  • @ Vadim, if I helped you, do not forget to vote for my answer :) - Alexander Muksimov
  • I will vote without right, the table does not save: Failed convert parameter. - Vadim
  • Super preserved, fixed UnicIdentifier on INT and it all worked. Thank you so much GENIUS! - Vadim
  • @ Vadim Please, glad I helped :) - Alexander Muksimov
  • And how do you @ Alexander Muksimov think that it will be possible to import without a single column, that column in the sql primary key and the number automatically adds (numbering takes place) - Vadim