0

I'm new here but I need some help. I need to update a SQL Server database from C# with Windows Forms, but I'm having problems. I looked it up but still can't find the right answer. I need to do insert and update by pressing a button for changing or filling the database from the datagridview. I've created a separate function for both I am using this code;

private void InsertPositionen()
{
    string qry = ""; 

    SqlCommand insert = new SqlCommand(qry, con);

    try
    {
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            qry = "INSERT INTO BelegePositionen (BelID, BelPosId, Artikelnummer, Menge, Preis) VALUES( " + dataGridView1.Rows[i].Cells["BelID"] + ", " 
                   + dataGridView1.Rows[i].Cells["BelPosId"] + ", " 
                   + dataGridView1.Rows[i].Cells["Artikelnummer"] + ", " 
                   + dataGridView1.Rows[i].Cells["Menge"] + ", " 
                   + dataGridView1.Rows[i].Cells["Preis"];
        }

        insert.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private void UpdatePositionen()
{
        string updt = "";

        SqlCommand update = new SqlCommand(updt, con);

        try
        {
            for (int i = 0; i < dataGridView1.Rows.Count -1; i++)
            {
                updt = "UPDATE BelegePositionen SET BelID =  "
                    + dataGridView1.Rows[i].Cells["BelID"] +
                    ", BelPosID = "
                    + dataGridView1.Rows[i].Cells["BelPosID"] +
                    ", Atrikelnummer = "
                    + dataGridView1.Rows[i].Cells["Artikelnummer"] +
                    ", Menge = "
                    + dataGridView1.Rows[i].Cells["Menge"] +
                    ", Preis = " 
                    + dataGridView1.Rows[i].Cells["Preis"];
            }

            update.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Done!");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
}
Improve this question
6
  • Can you tell us what type of Column are you inserting? is it float, varchar, binary, date etc? Commented Jul 24, 2020 at 7:39
  • 1
    SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables Commented Jul 24, 2020 at 7:41
  • I'm using 3 int one varchar(50) and one money data type Commented Jul 24, 2020 at 7:41
  • So you said: 'I need some help', 'I'm having problems' and 'can't find the right answer' ... what is your question, though? ^^ Exceptions/errors/something not working as expected? Please be more precisely! Commented Jul 24, 2020 at 7:57
  • Well i'm getting the error "Arithmetic overflow error when converting expression to money data type The instruction has ended" Commented Jul 24, 2020 at 8:01

2 Answers 2

Reset to default
1

You should really NOT do your SQL stuff like this!! This leaves your code wide open for SQL injection vulnerabilities! Stop that - right now!

Instead - use parametrized queries - like this:

private void InsertPositionen()
{
    string qry = "INSERT INTO BelegePositionen (BelID, BelPosId, Artikelnummer, Menge, Preis) " +  
                 "VALUES(@BelId, @BelPosId, @ArtNr, @Menge, @Preis);";
 
    SqlCommand insert = new SqlCommand(qry, con);
    
    // define the parameters
    insert.Parameters.Add("@BelId", SqlDbType.Int);
    insert.Parameters.Add("@BelPosId", SqlDbType.Int);
    insert.Parameters.Add("@ArtNr", SqlDbType.Int);  // maybe this is a string? 
    insert.Parameters.Add("@Menge", SqlDbType.Int);
    insert.Parameters.Add("@Preis", SqlDbType.Decimal, 20, 4);

    try
    {
        // in the loop, only *set* the parameter's values
        
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            insert.Parameters["@BelId"].Value = 1;
            insert.Parameters["@BelPosId"].Value = 2;
            insert.Parameters["@ArtNr"].Value = 3;
            insert.Parameters["@Menge"].Value = 4;
            insert.Parameters["@Preis"].Value = 99.95;

            insert.ExecuteNonQuery();
        }   
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Does this answer a question? (I did not found one...)
0

Your Question is quite vague as you state you are having problems, but not quite sure what problems you are having. It will help if you can describe what problems you are having.

In addition to what @marc_c said about sql injection, I can't see how you manage your connection to the database.

From the code it looks like you could run into a situation where you are leaving connection strings open, or not opening them at all.

using the using(...) { } will close the connections when you are done with it.

private void InsertPositionen()
{
    //using the using statement you will insure that the connection is closed and resources released
    using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.db))
    {
        string cmd = "INSERT INTO BelegePositionen (BelID, BelPosId, Artikelnummer, Menge, Preis) " +
         "VALUES(@BelId, @BelPosId, @ArtNr, @Menge, @Preis);";

        //using the using statement will ensure any reasources are released when exiting the code block
        using (SqlCommand insert = new SqlCommand(cmd, connection))
        {
            // define the parameters
            insert.Parameters.Add("@BelId", SqlDbType.Int);
            insert.Parameters.Add("@BelPosId", SqlDbType.Int);
            insert.Parameters.Add("@ArtNr", SqlDbType.Int);  // maybe this is a string? 
            insert.Parameters.Add("@Menge", SqlDbType.Int);
            insert.Parameters.Add("@Preis", SqlDbType.Decimal, 20, "4");

            try
            {
                //open the connection
                insert.Connection.Open();

                // in the loop, only *set* the parameter's values
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    insert.Parameters["@BelId"].Value = dataGridView1.Rows[i].Cells["BelID"];
                    insert.Parameters["@BelPosId"].Value = dataGridView1.Rows[i].Cells["BelPosId"];
                    insert.Parameters["@ArtNr"].Value = dataGridView1.Rows[i].Cells["Artikelnummer"];
                    insert.Parameters["@Menge"].Value = dataGridView1.Rows[i].Cells["Menge"];
                    insert.Parameters["@Preis"].Value = dataGridView1.Rows[i].Cells["Preis"];

                    insert.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                MessageBox.Show("Done!");
            }
        }
    }
}
Improve this answer

1 Comment

Thank you @CobyC .The connection isn't shown becasue i have it in a event , and i didn't use it there because it's a function which i call on later in that event.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.