How to insert quotation marks properly into SQL Server in C# code behind

Question

In SQL Server Management Studio, the query that I created is

CREATE TABLE MyTable
(
    EmployeeID int,
    EmployeeDivision varchar(25),
    EmployeeFName varchar(25),
    EmployeeLName varchar(25),
    EmployeeBudget1 int,
    EmployeeBudget2 int,
    EmployeeBudget3 int,
    EmployeeBudget4 int,
    EmployeeBudget5 int,
    EmployeeBudget6 int,
    EmployeeBudget7 int,
    EmployeeBudget8 int,
    EmployeeBudget9 int,
    EmployeeBudget10 int,
    EmployeeBudget11 int,
    EmployeeBudget12 int,
    EmployeeBudget13 int,
    EmployeeBudget14 int,
    EmployeeBudget15 int
);

INSERT INTO MyTable ('12345','DIVISION','FIRST','LAST','' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' );

My question is, how do I write this in C#?

I only created 4 set of textbox that will get that value of ID, Division, First Name, Last Name of each employee, and the rest will be null for Budgets 1-15

Here's my code:

SqlCommand cmd = new SqlCommand("insert into BUDGETTING_A values (' " + txtBoxEmpID.Text + "', + '" + cmbBoxDivision.Text + "','" + txtBoxFamilyName.Text + "', '" + txtBoxFirstName.Text + ' " +  '','','','','','','','','','','','','','','"')"+, con);

I am having trouble in terms of doing this, any instructions in writing these off?

Don't write your queries like that. You open yourself up to SQL injection. Use [parameters](https://stackoverflow.com/questions/10898737/parameterize-sql-query). — ProgrammingLlama, Dec 07 '17 at 04:56
Try using [Entity Framework](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/overview) — Jeroen Heier, Dec 07 '17 at 05:01
And one more thing, you are trying to insert empty string value to the column with **`int`** type. Use **`null`**. — Ullas, Dec 07 '17 at 05:01
@JeroenHeier if he can't construct a proper Sql Query what makes you think that he knows `EF` that's just one option.. — MethodMan, Dec 07 '17 at 05:02
@JoshuaMBacer there are several things wrong about your Table Create, and your query. you should construct a query using parameters. If you are creating the Table from C# code, you should also create a stored procedure on the database end and pass values into your table using `Parameters` 2nd you create a table you should have a primary key as well as make that column `Auto Increment` thus avoiding duplicate keyId entry's 3rd. Your Insert statement is open to Sql Injection also only Insert the values you need , meaning the table other fields should allow null. Read some basic Sql as well — MethodMan, Dec 07 '17 at 05:06

score 1 · Answer 1 · answered Dec 07 '17 at 05:04

The safest and best way is to use SqlParameter like the following example :

SqlCommand cmd = new SqlCommand("insert into BUDGETTING_A values (@BoxEmpID ,....) ",con);
cmd.Parameters.AddWithValue("@BoxEmpID", txtBoxEmpID.Text);
cmd.Parameters.AddWithValue("@OtherField", OtherValue);
.
.
.
cmd.ExecuteNonQuery();

score 0 · Answer 2 · edited Dec 07 '17 at 05:04

0

If you dont have values dont specify them if they are nullable

INSERT INTO MyTable (EmployeeID ,EmployeeDivision ,EmployeeFName,EmployeeLName) 
values ('12345','DIVISION','FIRST','LAST')

Above query is just for your reference only. Write the query properly to work with db.

edited Dec 07 '17 at 05:04

Ullas

11,450
4
33
50

answered Dec 07 '17 at 05:03

Pon Saravanan

525
5
12

How to insert quotation marks properly into SQL Server in C# code behind

2 Answers2