CODEWITHSUNDEEP
c#sqlvisual-studio-2010
user2627130
user2627130

Reputation: 25

Error converting data type varchar to numeric. 1-17-2014

I am having a problem inserting a record, 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 + "', '" +
            txtPONo.Text + "', '0', 'Agent', '" +
            txtInvoiceNo.Text + "', '" +
            lblTotal.Text + "', 'Void'); " +
            "SELECT TOP 1 ShoppingCartNo FROM ShoppingCart " +
            "ORDER BY ShoppingCartNo DESC;";

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

        txtOrderNo.Text = nShoppingCart.ToString();

        cmdInsert.ExecuteNonQuery();

        cn.Close();
}

the highlighted part is the

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

I cannot seem to know where is the problem? thank you for your help.

Upvotes: 0

Views: 451

Answers (3)

Daryl
Daryl

Reputation: 339

Try adding following part

Convert.ToInt16(lblTotal.Text)

Upvotes: 0

mohitg.2k8
mohitg.2k8

Reputation: 61

I think you have taken "CustomerNo" field in database numeric field and you are trying to insert varchar or string value in that field as i am able to see your code in which you are putting "txtCustomerNo.Text" which will contain string value. You should convert your value fisrt in int or whatever you have taken your database field.

Hopefully this will be helpful for you.

Upvotes: 2

Martin Dixon
Martin Dixon

Reputation: 28

Can you run the script without the Convert method. Replace it with:

string nShoppingCart = cmdInsert.ExecuteScalar().ToString();

Then see what nShoppingCart value is, and see if that would ever convert to an integer.

Upvotes: 0

Related Questions