error in converting numeric values

Question

Sql Table : stocks

Colomn Name    |  Data Type
------------------------------
Stock_no       |  nvarchar(15)
Quantity       |  int 
Gem.Weight     |  float
Cost           |  decimal(18,2)

My stock insert form code:

private void stocks_Click(object sender, EventArgs e)
{
    try
    {
        cmd = new SqlCommand("INSERT INTO Stocks VALUES('" + txt_stock_no.Text + "', '"
             + txt_qty.Text + "','" + txt_gem_weight.Text + "', '" + txt_cost.Text + "')", conn);

        MessageBox.Show("You've inserted successfully!", "Successful Message", MessageBoxButtons.OK, MessageBoxIcon.Information);                 
        conn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

I think the the error should be with my '.text' has issues.. i tried making changing with it even though its not working.

Help you with what? You forgot to tell us your actual problem and also you might want to choose either java or c#. — OH GOD SPIDERS, Dec 29 '17 at 09:39
What is the error message. it should be `VALUES(" + txt_stock_no.Text + " ` instead of `VALUES('" + txt_stock_no.Text + "'` BTW you should parameterized query to do so — Mohit S, Dec 29 '17 at 09:39
The **very first** thing to do is stop building your SQL like this. Use parameterized SQL, *always*. The approach you're using is vulnerable to SQL injection attacks - if you don't know what they are, read up on them before going any further. I strongly suspect that if you start using parameters, the problem will just go away... — Jon Skeet, Dec 29 '17 at 09:40
how to convert this '" + txt_cost.Text + "' to numeric format. so that my data will be inserted properly — nikimini sileen, Dec 29 '17 at 09:40
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, Dec 29 '17 at 09:45
Using SQL parameters will solve your problem, by forcing you to convert the Text properties beforehand. And it's an important security issue. — H H, Dec 29 '17 at 10:37

score 1 · Accepted Answer · 2017-12-29T11:07:18.440

1

Don't insert values directly from text boxes, your code is vulnerable to SQL Injection this way.
You have to validate the user inputs for these values from the text boxes. For example, the text box txt_stock_no should allow only integer values.
It would be better to list also the columns' names in the insert statement, not just the values, in case you missed or forget the order of them. and also for readability.
Then, Use Parameterized-Queries.

Something like this:

string commandText = "INSERT INTO Stocks VALUES(@stock_no, @txt_qty,@txt_gem_weight,@txt_cost)";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@stock_no", SqlDbType.Int);
    command.Parameters["@stock_no"].Value = txt_stock_no.Text;

    ....
    // do the same for other parameters
}

Update::

SqlCommand command = new SqlCommand(commandText, conn);
command.Parameters.Add("@stock_no", SqlDbType.Int);
command.Parameters["@stock_no"].Value = txt_stock_no.Text;

....
// do the same for other parameters

edited Dec 29 '17 at 11:07

answered Dec 29 '17 at 09:50

THANK YOU SO MUCH FOR SHARING THIS – nikimini sileen Dec 29 '17 at 10:27
I WILL INSERT THIS WAY, HOPE IT WORKS – nikimini sileen Dec 29 '17 at 10:28
i am getting an error for this 'connectionString' only – nikimini sileen Dec 29 '17 at 11:03
Line Error CS0103 The name 'connectionString' does not exist in the current context – nikimini sileen Dec 29 '17 at 11:05
@nikiminisileen `connectionString` is the your database connection string, the one you passed to your variable `conn`. – Dec 29 '17 at 11:06
@nikiminisileen - You can remove it and use the code in my updated answer. – Dec 29 '17 at 11:07
please one last question... you have given me a code for an integer datatype right which means Stock_no is an int so the code works.. how will it work for a varchar value and decemal (18,2). please help me – nikimini sileen Dec 29 '17 at 12:10

maddy23285 · Answer 2 · 2017-12-29T10:01:51.840

-2

Replace your code with this:

cmd = new SqlCommand("INSERT INTO Stocks VALUES('" + txt_stock_no.Text + "', "+ txt_qty.Text + "," + txt_gem_weight.Text + "," + txt_cost.Text + ")", conn);
int rowseffected=cmd.ExecuteNonQuery();
//rest of your code goes here...

However, this is not recommended. This is query is vulnerable to SQL injection. Use parameters instead, you will not face issue like this again.

edited Dec 29 '17 at 10:01

answered Dec 29 '17 at 09:43

maddy23285

738
6
16

1

However, this is not recommended. This is query is vulnerable to SQL injection. Use parameters instead, you will not face issue like again. – maddy23285 Dec 29 '17 at 09:45
2

Wrong. Parameters are nowhere here, and the Text property will be converted using whichever locale the OP is using. – Steve Dec 29 '17 at 09:46
@Steve, use of parameters is not a question here. nikimini is asking why this query is not working. I am answering to that only. He is not using ORM also. So, should I point that also? – maddy23285 Dec 29 '17 at 09:49
In that case you are also wrong steve. Use of ORM like hibernate is nowhere here. They are much much better than use of paramters :-P – maddy23285 Dec 29 '17 at 09:52
1

You are teaching a very wrong way to create an SQL command. You should never do that and explain why the OP shouldn't do it in that way. By the way if the user types "111,15" (comma instead of a point for decimals) the result of the automatic conversion executed by the db engine could be very wrong (and we don't even start talking about dates conversion) – Steve Dec 29 '17 at 09:52
error says: ERROR CONVERTING DATA TYPE VARCHAR TO NUMERIC – nikimini sileen Dec 29 '17 at 10:24

error in converting numeric values

2 Answers2