CODEWITHSUNDEEP
c#sqlsql-server
Marek
Marek

Reputation: 3575

SqlCommand - conversion from varchar to int

Hello I have got this code :

SqlCommand sc2 = new SqlCommand("SELECT ... WHERE akce=" + zakce.Text, spojeni);

spojeni.Open();

object vysledek2 = sc2.ExecuteScalar(); // This is the exception line

I'm receving following Exception:

System.Data.SqlClient.SqlException (0x80131904)
Conversion failed when converting the varchar value '137000-01' to data type int.

On the exception line when I set the breakpoint on vysledek2 is null and then the exception occurs.

Upvotes: 0

Views: 211

Answers (3)

Brett Sanderson
Brett Sanderson

Reputation: 308

        const string sqlSelect = @"SELECT ... WHERE akce=@akce";
        using (spojeni = new SqlConnection(connectionString)) 
        using(var command = new SqlCommand(sqlSelect,spojeni))
        {
            command.Parameters.AddWithValue("@akce", zakce.Text);
            command.Connection.Open();
            object vysledek2 = command.ExecuteScalar();
        }

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166476

Firstly, try changing 

SqlCommand sc2 = new SqlCommand("SELECT SUM(ISNULL(payments,0)) AS sumpaymentsFROM clientpayments WHERE akce=" + zakce.Text, spojeni);

to something like

SqlCommand sc2 = new SqlCommand("SELECT SUM(ISNULL(payments,0)) AS sumpaymentsFROM clientpayments WHERE akce='" + zakce.Text + "'", spojeni);

Secondly, have a look at what SQL Injection is and how to use parametereized queries.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063338

Never. Ever. Concatenate. Input.

SqlCommand sc2 = new SqlCommand("SELECT SUM(ISNULL(payments,0)) AS sumpaymentsFROM clientpayments WHERE akce=@acke", spojeni);
sc2.Parameters.AddWithValue("acke", zakce.Text);

Also - commands, connections, etc are all IDisposable - you should use using around each of them.

Upvotes: 5

Related Questions