CODEWITHSUNDEEP
c#.netdatagridview
ROM
ROM

Reputation: 265

Error in inserting data in database from datagrid view in C#

I have a slight problem don't know what it is

I am trying the following query to insert the data from my datagridview to my database The problem is that when I am executing the query it gives me the following error

Incorrect syntax near ','.

However the data is successfully inserted in the database. I tried to find the answers here but to no avail. Somebody please point out the error.

My code:

private void button2_Click(object sender, EventArgs e)
{
   SqlConnection conn = new SqlConnection(@"Data Source HERE");
   conn.Open();

   int rn = 0;

   while (rn < dgvmain.Rows.Count)
   {
       SqlCommand cmd = new SqlCommand("Insert into mainTab(Token_number,ItemType,Quantity,Amount) values(" + dgvmain.Rows[rn].Cells[0].Value + ",'" + dgvmain.Rows[rn].Cells[1].Value + "'," + dgvmain.Rows[rn].Cells[2].Value + "," + dgvmain.Rows[rn].Cells[3].Value + ")", conn);

       cmd.ExecuteNonQuery();
       rn++;
   }
}

Upvotes: 0

Views: 564

Answers (2)

Sercan Ozdemir
Sercan Ozdemir

Reputation: 4692

This is so wrong. Please change it like : 

       while (rn < dgvmain.Rows.Count)
       {
           SqlCommand cmd = new SqlCommand("Insert into mainTab(Token_number,ItemType,Quantity,Amount) values(@tok,@itm,@qua,@amo)", conn);
           cmd.Parameters.AddWithValue("@tok", dgvmain.Rows[rn].Cells[0].Value);
           cmd.Parameters.AddWithValue("@itm", dgvmain.Rows[rn].Cells[1].Value);
           cmd.Parameters.AddWithValue("@qua", dgvmain.Rows[rn].Cells[2].Value);
           cmd.Parameters.AddWithValue("@amo", dgvmain.Rows[rn].Cells[3].Value);
           cmd.ExecuteNonQuery();
           rn++;
       }

Upvotes: 1

marc_s
marc_s

Reputation: 755321

Try something like this:

private void button2_Click(object sender, EventArgs e)
{
        // put your SqlConnection into a using block
        using (SqlConnection conn = new SqlConnection(@"Data Source HERE"))
        {
            // define query with parameters
            string queryStmt = "INSERT INTO dbo.mainTab(Token_number, ItemType, Quantity, Amount) " +
                               "VALUES(@TokenNumber, @ItemType, @Quantity, @Amount)";

            // put your SqlCommand into a using block
            using (SqlCommand cmd = new SqlCommand(queryStmt, conn))
            {
                // Add parameter definitions to SqlCommand
                cmd.Parameters.Add("@TokenNumber", SqlDbType.Int);
                cmd.Parameters.Add("@ItemType", SqlDbType.Int);
                cmd.Parameters.Add("@Quantity", SqlDbType.Decimal);
                cmd.Parameters.Add("@Amount", SqlDbType.Decimal);

                int rn = 0;

                // now open - as late as possible!
                conn.Open();

                // iterate
                while (rn < dgvmain.Rows.Count)
                {
                    // set parameter values
                    cmd.Parameters["@TokenNumber"].Value = Convert.ToInt32(dgvmain.Rows[rn].Cells[0].Value);
                    cmd.Parameters["@ItemType"].Value = Convert.ToInt32(dgvmain.Rows[rn].Cells[1].Value);
                    cmd.Parameters["@Quantity"].Value = Convert.ToDecimal(dgvmain.Rows[rn].Cells[2].Value);
                    cmd.Parameters["@Amount"].Value = Convert.ToDecimal(dgvmain.Rows[rn].Cells[3].Value);

                    // execute INSERT statement
                    cmd.ExecuteNonQuery();

                    rn++;
                }

                conn.Close();
            }
        }
}

Upvotes: 2

Related Questions