Error converting datatype varchar to numeric in SqlDataAdapter

Question

I'm trying to retrieve an amount from SQL.

I have a table tbVendas with a column ValorTotalVendas of datatype DECIMAL.

I have the following select query

SELECT IdVendas AS 'Venda' 
FROM tbVendas 
WHERE ValorTotalVendas = '@valorTotal'

And below my command

SqlCommand comando = new SqlCommand(strSql, conn);
comando.Parameters.Add("@valorTotal", SqlDbType.Decimal);
comando.Parameters["@valorTotal"].Value = Convert.ToDecimal(txbValorFiltro.Text);

DataTable vendas = new DataTable();

conn.Open();

SqlDataAdapter sqdDA = new SqlDataAdapter(strSql, conn);
sqdDA.Fill(vendas);

But I get this error:

Error converting datatype varchar to numeric

Can anyone please help me?

What's the value of txbValorFiltro.Text and what's the decimal separator in your Operative System? — hardkoded
– hardkoded, Commented Mar 28, 2018 at 17:40
Did you check to make sure all the values can actually be converted to numeric? Seems like the most obvious step to do. And don't store numeric values in the database as non-numeric. — mason
– mason, Commented Mar 28, 2018 at 17:41
You need to remove the single quotes around the parameter name: SELECT IdVendas AS 'Venda' FROM tbVendas WHERE ValorTotalVendas = @valorTotal. Otherwise, it sees it as the string instead of seeing it as a parameter name. — Andrew Morton
– Andrew Morton, Commented Mar 28, 2018 at 17:47

Aousaf Rashid · Accepted Answer · 2018-03-28 18:27:56Z

1

Your select statement looks as follows :

SELECT IdVendas AS 'Venda' FROM tbVendas WHERE ValorTotalVendas = '@valorTotal'

Take a clear look at '@valorTotal'.In any sql statement, '' is used to pass a value to any column or so.But when you use '@' , it means you are passing a parameter to the column/cell so that you can use it in future for multiple operations.

So the basic problem is,you want to pass a parameter but you are passing it as a value instead.So,either you pass a value or a parameter :

  SELECT IdVendas AS 'Venda' FROM tbVendas WHERE ValorTotalVendas = 'YourValueHere'

 ///Or

 SELECT IdVendas AS 'Venda' FROM tbVendas WHERE ValorTotalVendas = @parameterHere

Hope you understand.

answered Mar 28, 2018 at 18:27

Aousaf Rashid

5,7986 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thiago Coelho · Accepted Answer · 2018-03-28 17:47:35Z

0

Rafael,

You must remove the single quote of your query:

"SELECT IdVendas AS 'Venda' FROM tbVendas WHERE ValorTotalVendas = @valorTotal"

The SQL Server is interpreting '@valorTotal' as a VARCHAR instead of DECIMAL.

Thanks.

answered Mar 28, 2018 at 17:47

Thiago Coelho

1011 silver badge5 bronze badges

Comments

Eray Balkanli · Accepted Answer · 2018-03-28 17:48:17Z

0

You can try to add a safe-mechanism like below to prevent potential bata-based future errors:

decimal amount;
bool checkValue = Decimal.TryParse(txbValorFiltro.Text, out amount);

if(checkValue)
   comando.Parameters["@valorTotal"].Value = amount;
else
   comando.Parameters["@valorTotal"].Value = -1; //or null, if field is nullable

answered Mar 28, 2018 at 17:48

Eray Balkanli

8,04013 gold badges60 silver badges94 bronze badges

Comments

Abdul Qayyum · Accepted Answer · 2018-03-28 17:49:16Z

0

try this

SELECT IdVendas AS 'Venda' FROM tbVendas WHERE ValorTotalVendas = @valorTotal

you are facing the error because of the '@valorTotal' , you will no more face this issue after changing it to @valorTotal

edited Mar 28, 2018 at 17:49

answered Mar 28, 2018 at 17:47

Abdul Qayyum

1,0628 silver badges15 bronze badges

6 Comments

Aousaf Rashid Over a year ago

It would be better if you explain this :)

Abdul Qayyum Over a year ago

@zackraiyan just explained.

Aousaf Rashid Over a year ago

Sorry but not much of an explanation ... But i appreciate you posted an answer...You can take a look at mine if u want :) .. We're all here to help each other right ?:)

Abdul Qayyum Over a year ago

@zackraiyan when the issue is identified, you can definitely explain it. we have answered the question wat too long before you started :)

Aousaf Rashid Over a year ago

,i don't mean to hurt u man :( ... I just pointed out so that u get a few more upvotes :(

|

Collectives™ on Stack Overflow

Error converting datatype varchar to numeric in SqlDataAdapter

4 Answers 4

Comments

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related