How to convert values of a datatable from string to integer

Question

I'm making a fine calculator in a library system. In it,I need to calculate the fine of a given day. For this,I use a data table to load the fine amounts and then,I need to calculate the total fine amount by adding each fine amount.But I'm having a problem in parsing the fine values which are in string format to integer.Here is a screenshot of the error.

Here is a screenshot of the error.

And here is the code which I used to convert the string values to integer and calculate the total fine.

int sum1 = 0;
int myNum;       
String Display;

private void btnNext_Click(object sender, EventArgs e)
{
    try
    {
        if (rbtnToday.Checked == true)
        {
            DateTime today = DateTime.Today;
            Con.Open();
            String select_today_query = "SELECT Fine FROM BookReceiveMem WHERE RecDate='" + today + "'";
            Sqlda = new SqlDataAdapter(select_today_query, Con);
            DataTable Dt = new DataTable();
            Sqlda.Fill(Dt);
            Con.Close();
            foreach (DataRow row in Dt.Rows)
            {
                myNum = int.Parse(Dt.Columns[0].ToString());
                sum1 = sum1 + myNum;
            }
            Display = sum1.ToString();
            MessageBox.Show("Today Fine Amount is= " + Display, "Today Fine Calculation", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
}

Is there any method to solve this problem?

Please do not create SQL queries by joining strings together. You should always use parametrised queries, otherwise you have a significant security risk from SQL injection. — Dragonthoughts, Sep 17 '18 at 09:07
@KalinduPrabash you are asking about links to ADO.NET docs and tutorials. In fact, it's *this code* that's converting the value to a string, then trying to parse it. There are dozens if not hundreds of duplicate questions about this - don't do it. Don't call `.ToString()` on that field. — Panagiotis Kanavos, Sep 17 '18 at 09:53
In your table `BookReceiveMem` what is the datatype for field `Fine`? — Jamiec, Sep 17 '18 at 09:54
@KalinduPrabash what are you trying to do in the first place? If you only want to retrieve a sum, you don't need to use DataTable or `SqlDataAdapter` at all. Use `SELECT SUM(Fine) ...` with a SqlCommand and use ExecuteScalar to retrieve the result — Panagiotis Kanavos, Sep 17 '18 at 09:57
@KalinduPrabash I suspect this is a course assignment. If you submit an answer using Datatable like this you'll fail. Even if you manage to get the correct *value* by accident, a good instructor would still fail you. You were asked to get a sum from the database using ADO.NET. To show you understand how SQL works, you'll use a `SUM()`, not try to load the data into a DataTable. For 100 values, `SUM()` will be 100+ times faster. You'd be expected to know the difference between the various ADO.NET classes as well. The class that executes commands is `SqlCommand`. — Panagiotis Kanavos, Sep 17 '18 at 10:15
Always use parameterized sql and avoid string concatenation to add values to sql statements. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204) — Igor, Sep 17 '18 at 10:56

Jamiec · Answer 1 · 2018-09-17T10:11:16.250

Databases are really good at calculating things like a sum. If Fine is an integer in your database you can get it to sum all the rows and just return that:

SELECT SUM(Fine) As TotalFine 
FROM BookReceiveMem
WHERE RecDate = @recDate

Which you could call from code without ever needing a DataTable as follows:

using(var cmd = Con.CreatCommand())
{
    cmd.CommandText = "SELECT SUM(Fine) As TotalFine FROM BookReceiveMem WHERE RecDate = @recDate";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add("@recDate",DateTime.Today);
    var result = (int)cmd.ExecuteScalar();
    MessageBox.Show("Today Fine Amount is= " + result, "Today Fine Calculation", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

Note that the above uses a Parameterized Query which is much safer than using string concatenation

However, a more direct answer to your question:

myNum = int.Parse(Dt.Columns[0].ToString());

Here you're getting the Column and trying to turn that to an integer. What you actually meant to do was get the row value (and you dont need to Parse it - it's already an integer!)

myNum = (int)row["Fine"];

How to convert values of a datatable from string to integer

1 Answers1