CODEWITHSUNDEEP
c#sqlvisual-studio-2010
user2627130
user2627130

Reputation: 25

error in coverting varchar to numeric in vs 2010

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

Upvotes: 0

Views: 432

Answers (3)

okrumnow
okrumnow

Reputation: 2416

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()

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294437

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?

Upvotes: 1

Suyash Khandwe
Suyash Khandwe

Reputation: 396

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.

Upvotes: 1

Related Questions