Inserting datagridveiw data into sql database. "there was an error parsing the query"

Question

EDITED!:

I am writing a program with a function of loading a excel file into a datagridview. Excel file contains product list with ten columns. As for now it works fine. A file is loaded fine. But just after this load a program should fill a SQL Server CE database with data from datagridview. Therefore next time i open this form a datagridview should be filled with data from database. (This excel loading function is to update a product list when my company changes something).

I have a problem with inserting this data into database.

I have an error:

There was an error parsing the query. [Token line number = 1, Token line offset = 67, Token in error = Taq]

DNA is a word in a cell of Excel file (line 1 column 3 ("ITEM"). Full content of cell is AB-AB-0192/A Taq DNA Polymerase (licensed). I think that a problem is connected somehow with space before TAQ. I tested this: when i delete space there, a problem info changes from Taq to DNA. So how can I avoid this? All columns in excel file are set as text, and SQL Server CE database column is of type nvarchar.

EDIT!

Ok guys you put me on the right path:)

This works:

string strQuery = @"INSERT INTO TabelaProdukty VALUES (@VD, @ItemCode, @Item, @Qty, @Ppcur, @StandardPrice, @CeMarked, @Description, @Description2, @Edma)";
        sqlconnection.Open();
        using (System.Data.SqlServerCe.SqlCeCommand comm = new System.Data.SqlServerCe.SqlCeCommand(strQuery, sqlconnection))
        {
          comm.Parameters.Add("@VD", SqlDbType.NVarChar);
          comm.Parameters.Add("@ItemCode", SqlDbType.NVarChar);
          comm.Parameters.Add("@Item", SqlDbType.NVarChar);
          comm.Parameters.Add("@Qty", SqlDbType.NVarChar);
          comm.Parameters.Add("@Ppcur", SqlDbType.NVarChar);
          comm.Parameters.Add("@StandardPrice", SqlDbType.NVarChar);
          comm.Parameters.Add("@CeMarked", SqlDbType.NVarChar);
          comm.Parameters.Add("@Description", SqlDbType.NVarChar);
          comm.Parameters.Add("@Description2", SqlDbType.NVarChar);
          comm.Parameters.Add("@Edma", SqlDbType.NVarChar);

          for (int i = 0; i < dataGridView1.Rows.Count; i++)
          {

            comm.Parameters["@VD"].Value =  dataGridView1.Rows[i].Cells["VD"].Value;
            comm.Parameters["@ItemCode"].Value = dataGridView1.Rows[i].Cells["ItemCode"].Value;
            comm.Parameters["@Item"].Value = dataGridView1.Rows[i].Cells["ITEM"].Value;
            comm.Parameters["@Qty"].Value = dataGridView1.Rows[i].Cells["QUANTITY"].Value;
            comm.Parameters["@Ppcur"].Value = dataGridView1.Rows[i].Cells["PPCUR"].Value;
            comm.Parameters["@StandardPrice"].Value = dataGridView1.Rows[i].Cells["STANDARD_SELL_PRICE"].Value;
            comm.Parameters["@CeMarked"].Value = dataGridView1.Rows[i].Cells["CE-MARKED"].Value;
            comm.Parameters["@Description"].Value =  dataGridView1.Rows[i].Cells["ITEM_DESCRIPTION"].Value;
            comm.Parameters["@Description2"].Value = dataGridView1.Rows[i].Cells["ITEM_DESCRIPTION2"].Value;
            comm.Parameters["@Edma"].Value = dataGridView1.Rows[i].Cells["EDMA"].Value;


            comm.ExecuteNonQuery();

          }
          sqlconnection.Close();

Database is filled with the proper data and when I restart program database is already filled. Now I just need to clear database before adding new content.

One problem though. Getting message: The data was truncated while converting from one datatype to another. [Name of function (if known) = ]

Yes, there is a massive problem in your statement - you're not using parameters! and thus you're wide open for SQL injection attacks. Don't concatenate together your SQL statements! NEVER EVER . Use parameterized queries - always. — marc_s
– marc_s, Commented Apr 1, 2013 at 16:04
Is it something like this: sqladapter.InsertCommand.Parameters.Add("@ITEM", SqlDbType.VarChar) ? — Łukasz Motyczka
– Łukasz Motyczka, Commented Apr 1, 2013 at 16:09
Use sqladapter.InsertCommand.AddWithValue(@item, @paramvalue); instead unless also you're not telling what Values( to add in your Insert statement — MethodMan
– MethodMan, Commented Apr 1, 2013 at 16:20

briskovich · Accepted Answer · 2013-04-01 16:29:41Z

1

 SqlCeCommand cmd = new SqlCeCommand();
                cmd.CommandText =  "your insert statemnt (@param1.@param2,@param3) " ;


 cmd.Connection = this.sqlConnection1; //initialize your connection on page load 
                this.sqlConnection1.Open();

                  // add params 
                cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = your_control.Text; 
                cmd.Parameters.Add("@param2", SqlDbType.VarChar).Value = your_control.Text; 
                cmd.Parameters.Add("@param3", SqlDbType.VarChar).Value = your_control.Text; 

 cmd.ExecuteNonQuery();

this.sqlConnection1.Close();

All you then have to do is just pull your values out of your dataset after you declare them .

edited Apr 1, 2013 at 16:29

answered Apr 1, 2013 at 16:18

briskovich

7001 gold badge12 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

Since the OP is using SQL Server Compact, you'll need to use SqlCeConnection and SqlCeCommand (not SqlConnection and SqlCommand.....)

Łukasz Motyczka Over a year ago

This solution is a base to what i did. Thanks. Had to change it though because during iteration i added same parameter, so had to define parameter outside iteration and just leave there value definition.

briskovich Over a year ago

excellent, I just perform the addition of the parameters and the the execute piece in the loop. Glad I could help :)

marc_s · Accepted Answer · 2013-04-01 16:18:41Z

Try something like this - use parameters in your ADO.NET query! And put all the disposable items like SqlCeConnection and SqlCeCommand into using(...) { .... } block to ensure that they're properly disposed:

private void button1_Click(object sender, EventArgs e) // wczytanie excela 
{
    OpenFileDialog openFileDialog1 = new OpenFileDialog();

    var dialogResult = openFileDialog1.ShowDialog();
    string sWybranyPlik;

    if (dialogResult  == DialogResult.OK)
    {
      sWybranyPlik = openFileDialog1.FileName;

      try
      {
         using(OleDbConnection ExcelConnection = new OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sWybranyPlik + "';Extended Properties=Excel 8.0;"))
         {
         OleDbDataAdapter OleDBAdapter = new OleDbDataAdapter("select * from [Tabelle1$]", ExcelConnection);

         OleDBAdapter.Fill(DtSet.Tables[0]);
         dataGridView1.DataSource = DtSet.Tables[0];

             -- recommendation: always explicitly *specify* the columns of the table
             -- that you're inserting into
         string strQuery = @"INSERT INTO TabelaProdukty(col1, col2, col3,....., colN)
                     VALUES (@VD, @ItemCode, @Item, @Qty, @Ppcur, @StandardPrice, @CeMarked, @Description, @Description2, @Edma)";

                 using(sqlconnection = new SqlCeConnection("Data Source = C:\\Users\\user\\Documents\\Visual Studio 2010\\Projects\\BMGRP\\Oferty BMGRP\\Oferty BMGRP\\bin\\Debug\\BazaDanych.sdf"))
         using(SqlCeCommand comm = new SqlCeCommand(strQuery, sqlconnection))
         {
             comm.Parameters.AddWithValue("@VD", dataGridView1.Rows[i].Cells["VD"].Value);
             comm.Parameters.AddWithValue("@ItemCode", dataGridView1.Rows[i].Cells["ItemCode"].Value);
             comm.Parameters.AddWithValue("@Item", dataGridView1.Rows[i].Cells["ITEM"].Value);
             comm.Parameters.AddWithValue("@Qty", dataGridView1.Rows[i].Cells["QUANTITY"].Value);
             comm.Parameters.AddWithValue("@Ppcur", dataGridView1.Rows[i].Cells["PPCUR"].Value);
             comm.Parameters.AddWithValue("@StandardPrice", dataGridView1.Rows[i].Cells["STANDARD_SELL_PRICE"].Value);
             comm.Parameters.AddWithValue("@CeMarked", dataGridView1.Rows[i].Cells["CE-MARKED"].Value);
             comm.Parameters.AddWithValue("@Description", dataGridView1.Rows[i].Cells["ITEM_DESCRIPTION"].Value);
             comm.Parameters.AddWithValue("@Description2", dataGridView1.Rows[i].Cells["ITEM_DESCRIPTION2"].Value);
             comm.Parameters.AddWithValue("@Edma", dataGridView1.Rows[i].Cells["EDMA"].Value);

             sqlconnection.Open();
             comm.ExecuteNonQuery();
             sqlconnection.Close();
         }

         ExcelConnection.Close();
          }      
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.ToString());
      }
    }

Thank you for your help, I used this solution also as a basis.

Collectives™ on Stack Overflow

Inserting datagridveiw data into sql database. "there was an error parsing the query"

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related