error in coverting varchar to numeric in vs 2010

Question

the error says, "Error converting data type varchar to numeric."

This is my set of codes:

    private void btnSearchCustomer_Click(object sender, EventArgs e)
    {
        //Get Customer Records
        DataSet dsCustomer = new DataSet();
        dsCustomer = GetRecords("Customers");

        frmBasicSearch newSearch = new frmBasicSearch();

        newSearch.myDataSet = dsCustomer;
        newSearch.ShowDialog();

        int myRowPosition = newSearch.myRowPosition;

        if (myRowPosition != -1) //will display the value inside the textboxes
        {
            //concuntinated values
            this.txtCustomerNo.Text = dsCustomer.Tables["Customers"].Rows[myRowPosition]["CustomerNo"].ToString();

            this.txtCustomerName.Text = dsCustomer.Tables["Customers"].Rows[myRowPosition]["CustomerName"].ToString();

            this.txtCustomerAddress.Text = dsCustomer.Tables["Customers"].Rows[myRowPosition]["CustomerAddress"].ToString();

            groupProduct(true); //this will activate the buttons from the Product Section
        }

        cn.Close();

        cn.Open();           

        SqlCommand cmdInsert = new SqlCommand();           

        cmdInsert.Connection = cn;
        cmdInsert.Transaction = trnOrder;
        cmdInsert.CommandType = CommandType.Text;
        cmdInsert.CommandText =
            "INSERT INTO ShoppingCart " +
            "(OrderDate, CustomerNo, CustomerName, CustomerAddress, PurchaseOrderNo, AgentNo, AgentName, InvoiceNo, TotalAmount, OrderStatus) " +
            "VALUES ('" +
            dtpOrderDate.Value.Date.ToString() + "', '" +
            txtCustomerNo.Text + "', '" +
            txtCustomerName.Text + "', '" +
            txtCustomerAddress.Text + "', '" +
            DBNull.Value + "', '" +
            DBNull.Value + "', '" +
            DBNull.Value + "', '" +
            DBNull.Value + "', '" +
            DBNull.Value + "', '" +
            "''Void'); " +
            "SELECT TOP 1 ShoppingCartNo FROM ShoppingCart " +
            "ORDER BY ShoppingCartNo DESC;";            

        cmdInsert.ExecuteNonQuery();

        cn.Close();                       
    }

the highlighted error part is the

int nShoppingCart = Convert.ToInt16(cmdInsert.ExecuteScalar().ToString());

I cannot seem to know where is the problem? thank you for helping

and here is my data schema,

ShoppingCartNo is in primary key and the is identity is auto incriminated by 1

enter image description here

You should always use [parameterized queries](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. — Soner Gönül, Jan 17 '14 at 14:46
Apart from considering parameterized queries, I would mention that you can get the last identity value for ShoppingCartNo with `SELECT SCOPE_IDENTITY()`. Also that column is an Int on sql server so that could be bigger than Int16, have you checked what is the current value of the identity? The range for Int16 is -32,768 to 32,767 — Daniel J.G., Jan 17 '14 at 14:51
@user2627130 What is the value of first column of the first row in your `INSERT` query **exactly**? — Soner Gönül, Jan 17 '14 at 14:53
Parameterized queries help against SQL injection. It's also cleaner IMO. Look here : http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection — Plue, Jan 17 '14 at 14:56

score 1 · Answer 1 · answered Jan 17 '14 at 14:48

1

ExecuteScalar is typically used in case where your SQL part is returning some rows. Try using ExecuteNonQuery instead while inserting.

Then use your select part with ExecuteScalar. that should work.

Why -

Right now, you have 2 queries - when your run your 2 queries in Management studio - you'd get 'N rows affected' & your ShoppingCartNo. As per your current code, only the first part is returned (due to ExecuteScalar) which results in type conversion error.

answered Jan 17 '14 at 14:48

Suyash Khandwe

386
3
11

@user2627130 - Did you try using 2 separate parts ? Please update your question with the modified code. – Suyash Khandwe Jan 17 '14 at 14:54

okrumnow · Accepted Answer · 2014-01-17T15:01:34.877

1

ExecuteScalar executes a SELECT statement and returns the first column of the firest returned row. For INSERT statements use ExecuteNonQuery. It will return an integer, containing the number of inserted lines, so no conversion is needed, simply write

int nShoppingCart = cmdInsert.ExecuteNonQuery();

The error comes from inserting the string 'NULL' into the numeric column TotalAmount.

"'" + DbNull.Value + "'"

will result in 'NULL' (probably, I didn't check, but it definitely is a string, not a numeric value).

You do not need to insert NULL values into columns that can hold NULL values. Simply do not insert these columns, jjust insert the columns containing data.

After that, execute a query using ExecuteScalar, using the mentioned SCOPE-IDENTITY()

edited Jan 17 '14 at 15:01

answered Jan 17 '14 at 14:49

okrumnow

2,346
23
39

@SuyashKhandwe You're right, i overlooked the second statement – okrumnow Jan 17 '14 at 14:55
@user2627130 I included the (likely) reason for the error and a way to avoid it. – okrumnow Jan 17 '14 at 15:02

score 1 · Answer 3 · answered Jan 17 '14 at 14:54

First and foremost: this is not the proper way to retrieve the auto-incremented key. Use SCOPE_IDENTITY() instead, or use OUTPUT clause of INSERT. the way you're doing it is incorrect from multiple points of view:

it returns the wrong ID. Two threads insert, both select the same ID (last).
it returns the wrong ID if the IDENTITY was reset
it can return empty result set if the (only) row is deleted between the INSERT and SELECT.

So why don't just use SCOPE_IDENTITY() instead?

i could not understant the scope_identity(), where or what should i replace that with? — user2627130, Jan 17 '14 at 15:01

error in coverting varchar to numeric in vs 2010

3 Answers3