How to insert decimal value from textbox into database with c#

Question

I'm using a SQL Server database and Windows Forms application. I have 2 textboxes and 1 combobox. Users inputs their name and working rate into the textboxes and they chose year from combobox. I try to insert those values into my database.

EX:

2018    Mike    39,72     WORKERS

In database:

year --> nvarchar(4)
name --> nvarchar(50)
rate --> decimal(18,2)
type --> nvarchar(50)

My code is like:

SqlCommand cmd = new SqlCommand("INSERT INTO USERS(YEAR,NAME,RATE,TYPE) VALUES ('" + yearcombo.Text + "','" + name.Text + "','" + Convert.ToDecimal(rate.Text, CultureInfo.CurrentCulture) + ",'" + "WORKERS" + "')", connection);

I get this error:

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

I added an example of how the insert would be vulnerable to my answer, please have a look. — Ashkan Mobayen Khiabani
– Ashkan Mobayen Khiabani, Commented Mar 20, 2019 at 12:23
WHY do you store a year (which is clearly a numerical value) as nvarchar(4) ?!?!?! Use the most appropriate datatype - always - and here this would definitely be an INT (more than a nvarchar(4)) .... — marc_s
– marc_s, Commented Mar 20, 2019 at 12:38

Ashkan Mobayen Khiabani · Accepted Answer · 2019-03-20 12:21:45Z

11

This kind of insert is vulnerable to injection and as you have already discovered it is hard to add values like decimal, DateTime, ... to add columns, use Parameters.Add

SqlCommand cmd = new SqlCommand("INSERT INTO USERS(YEAR,NAME,RATE,TYPE) VALUES (@year, @name,@rate,@type)", connection);

cmd.Parameters.Add("@year", SqlDbType.Int).Value = int.Parse(yearcombo.Text);
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = name.Text;
cmd.Parameters.Add("@rate", SqlDbType.Decimal).Value = Convert.ToDecimal(rate.Text, CultureInfo.CurrentCulture);
cmd.Parameters.Add("@type", SqlDbType.VarChar).Value = "WORKERS";

Imagine that somebody changes the yearcombo value (as simply as using inspect element in chrome for example) and set it to:

"2019,'a',1,'Something');insert into ARESTRICTEDTABLE(somefield) values('somecolumn'); update AnotherTable set SomeField = 'somevalue');, insert into users(year,name,rate,type),(2019,"

then your command text would be:

"insert into users (year,name,rate,type) (2019,'a',1,'Something');insert into ARESTRICTEDTABLE(somefield) values('somecolumn'); update AnotherTable set SomeField = 'somevalue');, insert into users(year,name,rate,type),(2019," ,'" + name.Text + "','" + Convert.ToDecimal(rate.Text, CultureInfo.CurrentCulture) + ",'" + "WORKERS" + "')"

edited Mar 20, 2019 at 12:21

answered Mar 20, 2019 at 12:05

Ashkan Mobayen Khiabani

34.3k35 gold badges111 silver badges187 bronze badges

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

8 Comments

Darth Sucuk Over a year ago

it gives me Error converting data type nvarchar to numeric.

Ashkan Mobayen Khiabani Over a year ago

@DarthSucuk As i don't know your exact table structure, somethings might not match it. for example if year column is a nvarchar, you should change the first parameter like this: cmd.Parameters.Add("@year", SqlDbType.NVarChar).Value = yearcombo.Text; and so on, check and make sure that your column types match the parameter type.

Darth Sucuk Over a year ago

I fix it when I write 37,2 it gives an error but when I write 37.2 it inserts but the main problem is it stored with comma.How can I fix it?

Ashkan Mobayen Khiabani Over a year ago

just replace the comma with. before trying to convert it: Convert.ToDecimal(rate.Text.Replace(',','.'), CultureInfo.CurrentCulture)

Darth Sucuk Over a year ago

it not fix it I did you said but now on it get 37,62 to 3762,00

|

S. Schenkel · Accepted Answer · 2019-03-20 12:04:49Z

7

You should use SqlParameter with a query containing parameters : It should be like this :

SqlCommand cmd = new SqlCommand("INSERT INTO USERS(YEAR,NAME,RATE,TYPE) VALUES (@YEAR, @NAME, @RATE, @TYPE)", connection);
cmd.Parameters.Add(new SqlParameter("@YEAR", yearcombo.Text));
cmd.Parameters.Add(new SqlParameter("@NAME", name.Text));
cmd.Parameters.Add(new SqlParameter("@RATE", Convert.ToDecimal(rate.Text, CultureInfo.CurrentCulture)));
cmd.Parameters.Add(new SqlParameter("@TYPE", "WORKERS"));

I didn't test it but it convert your parameter to a correct decimal for your query. Also it is better to use SqlParameter for security issues (preventing SQL Injection)

answered Mar 20, 2019 at 12:04

S. Schenkel

19110 bronze badges

1 Comment

Darth Sucuk Over a year ago

it gives me Error converting data type nvarchar to numeric.

Collectives™ on Stack Overflow

How to insert decimal value from textbox into database with c#

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related