none
error converting data type varchar to numeric c# RRS feed

  • Question

  • SqlConnection connn = new Sql Connection(conString);

    connn.Open();

    if (connn.State == System.Data.ConnectionState.Open)

    {

    string q = "Insert into CashIn values ('" + cidTP.Value.ToString("yyy-MM-dd") + "','" + tbtr.Text.ToString() + "')";

    SqlCommand cmd = new SqlCommand(q, connn);

    cmd.ExecuteNonQuery();

    }


    Wednesday, March 11, 2020 10:42 AM

All replies

  • Hello,

    When performing this type of operation make sure the data types are correct and you use parameters.

    In the code sample below a method is used, each parameter is value is passed into the method to ensure they are correct, these values are assigned to parameters of the command object which in turn will format them according to type e.g. for a string surround values with apostrophes and escape  apostrophes in a string, for dates surround the date with apostrophes etc. Get in the habit to take the extra step as per below so you know if a date is expected a date is provided e.g. in the method signature DateTime OrderDate so that means it's a valid date

    public void AddOrder(int CustomerId, DateTime OrderDate, string Invoice)
    {
    	using (var cn = new SqlConnection { ConnectionString = ConnectionString })
    	{
    		using (var cmd = new SqlCommand { Connection = cn })
    		{
    
    			cmd.CommandText = "INSERT INTO Orders (CustomerId,OrderDate,Invoice) VALUES (@CustomerId,@OrderDate,@Invoice)";
    			cn.Open();
    			cmd.Parameters.AddWithValue("@CustomerId", CustomerId);
    			cmd.Parameters.AddWithValue("@OrderDate", DateTime.Now);
    			Invoice = GenerateInvoice(cn); // A method to generate a invoice number
    			cmd.Parameters.AddWithValue("@Invoice", Invoice);
    			cmd.ExecuteNonQuery();
    		}
    	}
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 11, 2020 10:58 AM
    Moderator
  • hello friend

    I think Your column numbers of the table and passing values mismatch.So try to provide the fields lke

    string q = "Insert into CashIn(field1,field2) values ('" + cidTP.Value.ToString("yyy-MM-dd") + "'," + tbtr.Text.ToString() + ")";


    Wednesday, March 11, 2020 11:52 AM
  • hello friend

    I think Your column numbers of the table and passing values mismatch.So try to provide the fields lke

    string q = "Insert into CashIn(field1,field2) values ('" + cidTP.Value.ToString("yyy-MM-dd") + "','" + tbtr.Text.ToString() + "')";

    Hopefully you realize that doing this opens up issues such as malformed values in the query as I stated in my first reply. A query should with values should always use parameters.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 11, 2020 12:41 PM
    Moderator
  • Hi vixyv,
    I viewed the description, the issue is caused by putting string into numeric field.
    As Kareninstructor said, you can use named parameters instead of concatenating strings.
    If the fields can be null you'd really be much better using a parametrised query,that will also be a lot safer as it guards against SQL injection attacks.
    And you don't have to convert values and code is much more readable.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 12, 2020 7:44 AM
  • Hi @Kareninstructor thanks for your reply

    I reply according to the question .You may pass the value with parameter with inline query or using strored procedure .The problem is that mismatch of type .

    Thanks & regards

    Laxmidhar sahoo

    Thursday, March 12, 2020 11:31 AM
  • hi @Daniel thanks a lot

    You both are correct . @vxyv,send The numeric value through the textbox value within single quoted .So the problem is happing.

    Thanks & regards

    Laxmidhar sahoo

    Thursday, March 12, 2020 12:19 PM
  • Hi @Kareninstructor thanks for your reply

    I reply according to the question .You may pass the value with parameter with inline query or using strored procedure .The problem is that mismatch of type .

    Thanks & regards

    Laxmidhar sahoo

    A Mismatch would not happen as per my reply and the following.

    • A developer knows their database table schema
    • Using parameters makes sense to guard against malformed data passed to the table.
    • A developer passes the correct type to a parameter
    • Using SSMS to write all queries with parameters e.g. DECLARE and ensure the query works, from here the DECLARE params become parameters to strongly type data.

    Example 

    Insert and get new key, string concatenation is only used to keep the statement on the screen.

    public int AddCustomer(string companyName, string contactName)
    {
        using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
        {
            using (var cmd = new SqlCommand() {Connection = cn})
            {
                cmd.CommandText =
                    "INSERT INTO Customer  (CompanyName, ContactName) " + 
                    "VALUES (@CompanyName, @ContactName);" + 
                    "SELECT CAST(scope_identity() AS int);";
    
                cmd.Parameters.AddWithValue("@CompanyName", companyName);
                cmd.Parameters.AddWithValue("@ContactName", contactName);
    
                cn.Open();
                return (int) cmd.ExecuteScalar();
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Thursday, March 12, 2020 1:43 PM
    Moderator
  • I see two problems in your code.

    First of all, you always need to use parameters (but I suggest to use more verbose method of Add instead of AddWithValue as the latter takes some guesses).

    Also, the insert command needs to list the columns you're inserting the values into. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 12, 2020 3:12 PM
    Moderator
  • Hi vixyv,
    Has your problem been solved? If it is resolved, we suggest that you mark it as the answer. So it can help other people who have the same problem find a solution quickly.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 10, 2020 8:21 AM