Inserting data in c#

Question

SQL Server database table is :

inward_id, int(Primary Key),
gate_inward_done, varchar(50),
gate_entry_no, varchar(100),
gate_entry_date, date,
inward_date, date,
vendor_code, varchar(max),
vendor_name, varchar(max),
dc_no, varchar(max),
dc_date, date,
bill_received, varchar(max),
invoice_no, varchar(max),
invoice_date, date,
inv_total_amount, numeric(18,2),
gst_applicable, varchar(max),
cgst, numeric(18,2),
sgst, numeric(18,2),
igst, numeric(18,2),
inv_net_amount, numeric(18,2),
nu_freight, numeric(18,2),
fraction_amount, numeric(18,2),
approvar_department, varchar(max),
vc_remarks_master, varchar(max)

When I press button save in C# application, data is not saved in database and shows error "converting data varchar to numeric".

This is my code:

SqlCommand cmd1 = con.CreateCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "INSERT INTO inward_master values ('"+cmbGateEntry.Text+"','"+txtGateEntryNo.Text+"','"+dtpGateEntryDate.Value.ToString("yyyy-MM-dd")+"','"+dtpInwardDate.Value.ToString("yyyy-MM-dd")+"','"+cmbVendorCode.Text+"','"+txtVendorName.Text+"','"+txtDcNo.Text+"','"+dtpDcDate.Value.ToString("yyyy-MM-dd") +"','"+cmbbill_received.Text+"','"+txtInvoice_No.Text+"','"+dtpInvoiceDate.Value.ToString("yyyy-MM-dd") +"','"+ txtinv_total_amount.Text +"','"+cmbgst_applicable.Text+"','" + txtcgst.Text + "','" + txtsgst.Text + "','" + txtigst.Text + "','" + txtinv_net_amount.Text + "','" + txtnu_freight.Text + "', '" + txtfraction_amount.Text + "','"+cmbApproverDept.Text+"','"+txtvc_remarks_master.Text+"')";

cmd1.ExecuteNonQuery();}

Please give solution. Thanks in advance..

Make sure your count of text boxes is equal to your column count — ojlovecd, Jan 07 '19 at 05:53
Please use [sql parameters](https://stackoverflow.com/questions/25820944/how-do-i-re-write-a-sql-query-as-a-parameterized-query). At the moment your code is vulnerable to SQL injection and "unexpected behaviour". — ProgrammingLlama, Jan 07 '19 at 05:54
@ojlovecd it's also ok but may be error in converting numeric to varchar or varchar to numeric — Dharamraj Patel, Jan 07 '19 at 05:56
Possible duplicate of [Error converting data type varchar to numeric in c# program](https://stackoverflow.com/questions/34737823/error-converting-data-type-varchar-to-numeric-in-c-sharp-program) — Vishwa Ratna, Jan 07 '19 at 05:56
@DharamrajPatel, your parameter sequence and your db table sequence is wrong, either same sequence give or define db column before values in insert statemtent. — Ajay2707, Jan 07 '19 at 06:02
@DharamrajPatel , you can try my solution. I have created Demo for this and it works for me .. — Nikunj, Jan 07 '19 at 06:57

score 0 · Answer 1 · answered Jan 07 '19 at 05:54

A it is a bad practice to use Concatenation for the query use parameterized approach it is better and a cleaner approach than the one you are using.

And B the error clearly states that you are trying to insert a string value to Numeric Column which in your case is something like this you have used '" + txtfraction_amount.Text + "' which should be " + txtfraction_amount.Text + "(removed the single quotes as they are used for string) as its a numeric value(this is just an example correct the query and it will work).

Here is a good example/explanation of a parameterized query.

score 0 · Answer 2 · answered Jan 07 '19 at 05:55

0

If inward_id is auto increment, you must use this:

INSERT INTO inward_master (fields....) VALUES(values...)

answered Jan 07 '19 at 05:55

CodeMan

671
6
13

Inserting data in c#

2 Answers2