Error converting varchar-Value '..' to int-Datatype

Question

My code is the following:

void countVR(int kk){
        string selectString = "SELECT COUNT(*) FROM cllohn" + (Jahr % 100).ToString("00") + " WHERE lzVSTR="+kk;
        if(filtsel == selstr){
            using (IDbConnection con = DatabaseFactory.Connect("CL" + (Jahr % 100).ToString("00"), true)) {
                //con.Open();
                using (IDbCommand cmd = con.CreateCommand()) {
                    cmd.CommandText = selectString;
                    using (IDataReader reader = cmd.ExecuteReader()) {
                        while(reader.Read()){
                            zeilenCount = reader.GetAsInteger(0);
                            //System.Diagnostics.Debug.WriteLine("Zeilen in " + kk + ": " +zeilenCount);
                        }
                    }
                }
            }
        }

This is the Error i get:

System.Transactions Critical: 0 : http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/UnhandledUnbehandelte AusnahmeCONTELDA2.exeSystem.Data.Odbc.OdbcException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089ERROR [22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim Konvertieren des varchar-Werts '..' in den int-Datentyp.

if i add " AND lzBEST='VR'" to the SELECT Statement, it works again, but i want to count all entries for lzVSTR=kk (whatever the number may be) not only the ones with lzBEST='VR'

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Nov 06 '18 at 11:52
it worked when i added ' around kk after i checked the datatype again, it was indeed varchar, i guess it confused me that it worked when i added something else — Kandrina, Nov 06 '18 at 12:02
Please read the first link I provided. The short term solution you have chosen does work, for now. But it is **very dangerous**. As in - "if I used your app I could delete all of your data" dangerous. — mjwills, Nov 06 '18 at 12:04

auxoutin · Accepted Answer · 2018-11-06T15:07:42.323

Maybe change the data type on the db to an integer of some sort? Why does this differ from your input? Anyway the below should work. You should always make sure you utilize the parameters property within a SqlCommand to avoid SQL injection.

void countVR(int kk)
{
    string selectString = "SELECT COUNT(*) FROM cllohn" + (Jahr % 100).ToString("00") + " WHERE lzVSTR=@KK";
    if (filtsel == selstr)
    {
        using (IDbConnection con = DatabaseFactory.Connect("CL" + (Jahr % 100).ToString("00"), true))
        {
            //con.Open();
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = selectString;
                SqlParameter sqlParameter = new SqlParameter()
                {
                    Value = kk,
                    ParameterName = "@KK"
                };
                cmd.Parameters.Add(sqlParameter);
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        zeilenCount = reader.GetInt32(0);
                        //System.Diagnostics.Debug.WriteLine("Zeilen in " + kk + ": " +zeilenCount);
                    }
                }
            }
        }
    }
}

i get an error, telling me that there is in Parameter for IDbCommand — Kandrina, Nov 06 '18 at 12:38

score -5 · Answer 2 · answered Nov 06 '18 at 12:03

-5

The Datatype in the Database was varchar, so i added ' around the kk variable, now it works

answered Nov 06 '18 at 12:03

Kandrina

13
5

4

This is not a sustainable solution. You must use parameters to avoid SQL Injection. – mjwills Nov 06 '18 at 12:04
I also highly recommend using parameters to avoid SQL Injection! You also have to advantage that you don't have to deal with datatypes, escaping, ... – user8527410 Nov 06 '18 at 12:24

Error converting varchar-Value '..' to int-Datatype

2 Answers2