C# incorrect insert float data

Question

I insert float like in the below code. But it saved incorrectly. for example, I insert 14.5 or 14,5 it saved 145 please help me

string table_no = gbox1.Text.Trim();
string gametime = time_hour.Text.Trim() + ":" + time_minute.Text.Trim();
string localdate = DateTime.Now.ToShortDateString();
float money = float.Parse(txtbox_1.Text, CultureInfo.InvariantCulture);

try
{
      connection.Open();
      baza("insert into stollar (id, table_no, gametime, localdate,  money) values ('" + txt1.Text + "','" + table_no + "', '" + gametime + "', '" + localdate + "', '" + money + "' )");
      connection.Close(); 
}

Do not use string concatenation for your sql variables! Use parameterized sql instead. Your column types should also be correct and use the native type, not the string representation. The parameters should also use the native values (not string representation) for each value. — Igor, Oct 03 '17 at 15:40
why dont you store numbers as numbers instead of converting at saving and casting at loading ? — Franck, Oct 03 '17 at 15:41
You shouldn't EVER directly inser user input into a SQL statement. Use a parametrized query and pass those values (after some validation) as parameters. — xxbbcc, Oct 03 '17 at 15:41
You're relying on *a lot* of implicit conversion between strings and numerics from two different systems here (C# and SQL). If the precision of your data matters to you, maintain your data types explicitly. — David, Oct 03 '17 at 15:42
Side note: It looks like you might be trying to reuse a SqlConnection instance across methods/classes (who knows). Don't do that either, create and dispose your SqlConnection instances on an as needed basis. Connection pooling is built in for most providers. — Igor, Oct 03 '17 at 15:43
@Igor The question has nothing to do with why you'd use prepared statements. It explicitly asks why the OP is getting the wrong value in the database. The issue may or may not be in the SQL. — Justin Niessner, Oct 03 '17 at 15:47
@JustinNiessner yes I said that wwhy it happend& and how can I solve this problem& — iLLkeeN Nemo, Oct 03 '17 at 15:56
@iLLkeeNNemo: For starters, double-check the data types of your database columns. If something is a numeric type, don't use quotes when inserting data. If it's not a numeric type, don't expect it to be treated as one. Additionally, and very importantly, begin using parameterized queries instead of directly concatenating string values into your SQL code. Not only does this help protect you from SQL injection attacks, but this also gives you more control over the syntax of your SQL and the types of the values being used. — David, Oct 03 '17 at 16:03
@David I tried that method but problem dont be solved `connection.Open(); SqlCommand command = new SqlCommand("insert into stollar (money) values('" + "@money" + "')", connection); command.Parameters.Add("@money", SqlDbType.Float); command.Parameters["@money"].Value = float.Parse(txtbox_1.Text, CultureInfo.InvariantCulture); command.ExecuteNonQuery(); connection.Close();` — iLLkeeN Nemo, Oct 03 '17 at 16:17
@iLLkeeNNemo: And what *specifically* was the problem when you tried that? (At a quick glance, your SQL syntax is wrong and highly over-thought. It should simply be: `... values (@money) ...` There's no need to break up the string and you shouldn't enclose parameter names in quotes.) — David, Oct 03 '17 at 16:18
`const string sql = @"INSERT INTO stollar (id, money) VALUES (@id, @money)"; using(SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int){Value = int.Parse(txt1.Text)}); cmd.Parameters.Add(new SqlParameter("@money", SqlDbType.Float){Value = float.Parse(txtbox_1.Text, CultureInfo.InvariantCulture)}); conn.Open(); cmd.ExecuteNonQuery(); }` I tried this, but problem not solved yet. Noe I insert 0,03 but it added 3.00 — iLLkeeN Nemo, Oct 03 '17 at 16:32
@iLLkeeNNemo: You're trying to insert 2 values into 5 columns, so that SQL command is entirely invalid to begin with. — David, Oct 03 '17 at 16:34
@David not fix `const string sql = @"INSERT INTO stollar (id, money) VALUES (@id, @money)"; using(SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int){Value = int.Parse(txt1.Text)}); cmd.Parameters.Add(new SqlParameter("@money", SqlDbType.Float){Value = float.Parse(txtbox_1.Text, CultureInfo.InvariantCulture)}); conn.Open(); cmd.ExecuteNonQuery(); }` — iLLkeeN Nemo, Oct 03 '17 at 16:46
@iLLkeeNNemo: *Define "not fix"*. You need to explain the problem. "It doesn't work" isn't an explanation of what's wrong. — David, Oct 03 '17 at 16:48
@David The problem is that although the value I want to add is 0.03, it is registered as 3. — iLLkeeN Nemo, Oct 03 '17 at 16:55
@iLLkeeNNemo: This sounds like a culture setting problem. What specific value does `float.Parse(txtbox_1.Text, CultureInfo.InvariantCulture)` produce? What was in `txtbox_1.Text`? Perhaps you need a *specific* culture instead of `CultureInfo.InvariantCulture`? — David, Oct 03 '17 at 16:56
@David like what? The value in the TextBox is equal to *float* values such as 0.03 or 1.55 — iLLkeeN Nemo, Oct 03 '17 at 17:04

score 1 · Answer 1 · answered Oct 03 '17 at 16:07

Do not use string concatenation for your sql variables! Use parameterized sql instead. Your column types should also be correct and use the native type, not the string representation. The parameters should also use the native values (not string representation) for each parameter.

This is a code fix that most likely can fix your problem but you need to change your schema to ensure that you are using native types. This will fix your problem where you are storing a localized string representation of a value instead of the native type. Here are my guesses as to the correct data type.

id - should probably be an int marked with Identity if you want the Db to auto generate this. In that case change the schema and do not pass a value in your insert.
table_no - probably a varchar, be sure to specify the correct length in the parameter.
gametime - should probably be of type Time, pass a TimeSpan to the value
localdate - I recommend datetime2 but datetime could also be used
money - use decimal and specify the precision and the scale in your schema. Be sure those match what you set on the parameter.

Adjusted code

const string sql = @"INSERT INTO stollar (id, table_no, gametime, localdate,  money) VALUES (@id, @table_no, @gametime, @localdate, @money)";
using(SqlConnection conn = new SqlConnection(/*your connection string from app.config or web.config*/))
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int){Value = int.Parse(txt1.Text)});
    cmd.Parameters.Add(new SqlParameter("@table_no", SqlDbType.VarChar, 100){Value = gbox1.Text.Trim()});
    cmd.Parameters.Add(new SqlParameter("@gametime", SqlDbType.Time){Value = TimeSpan.FromMinutes(int.Parse(time_hour.Text.Trim()) * 60 + int.Parse(time_minute.Text.Trim()))});
    cmd.Parameters.Add(new SqlParameter("@localdate", SqlDbType.DateTime){Value = DateTime.Now});
    cmd.Parameters.Add(new SqlParameter("@money", SqlDbType.Decimal){Precision = 10, Scale = 2, Value = decimal.Parse(txtbox_1.Text, CultureInfo.InvariantCulture)});

    conn.Open();
    cmd.ExecuteNonQuery();
}

@iLLkeeNNemo - `it does not work` is something you hear from a user that does not have a clue how a PC works and what information they need to be providing. You are a programmer and can do better than that. Read through [ask] and update your question accordingly, you should also provide a [mcve]. — Igor, Oct 03 '17 at 17:46

C# incorrect insert float data

1 Answers1