1

I am trying to insert data into my SQL Server database in C#.

I am fairly new at programming, and would like to learn the correct way of doing so.

So far, I have a database class :

public SqlConnection connection()
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "DESKTOP-UPVVOJP";
    builder.InitialCatalog = "Lagersystem";
    builder.IntegratedSecurity = true;

    return new SqlConnection(builder.ToString());
}

Product class file :

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int ProductStock { get; set; }
    public int ProductCategoryID { get; set; }
    public int ProductEmployeeID { get; set; }
    public DateTime ProductCreatedDate { get; set; }

    // Constructor
    public Product(string productname, int productstock, int productcategoryid, int productemployeeid)
    {
        ProductName = productname;
        ProductStock = productstock;
        ProductCategoryID = productcategoryid;
        ProductEmployeeID = productemployeeid;
        ProductCreatedDate = DateTime.Now;
    }
}

Insert method in my program :

static void InsertProduct(string productname, int productstock, int productcategoryid, int productemployeeid)
{
    Database db = new Database();

    SqlConnection conn = db.connection();

    using (SqlCommand command = new SqlCommand(@"INSERT INTO Products (ProductName, ProductStock, ProductCategoryID, ProductEmployeeID, ProductCreatedDate)
                                                 VALUES('{0}', {1}, {2}, {3}, '{4}')", conn))
    {
        string formattet = string.Format(productname, productstock, productcategoryid, productemployeeid, DateTime.Now);
        Console.WriteLine(formattet);

        conn.Open();
        command.ExecuteNonQuery();
        conn.Close();
    }
}

and my main method :

static void Main(string[] args)
{
    while (true)
    {
        Console.Write("Indtast Produktnavn :");
        string productname = Console.ReadLine();

        Console.Write("Hvor mange er der på lager? : ");
        int productstock = int.Parse(Console.ReadLine());

        Console.Write("Hvilken kategori ID hører produktet til? : ");
        int productcategoryid = int.Parse(Console.ReadLine());

        Console.Write("Hvilket medarbejder ID har oprettet produktet? : ");
        int productemployeeid = int.Parse(Console.ReadLine());

        try
        {
            InsertProduct(productname, productstock, productcategoryid, productemployeeid);
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message);
            Console.ReadKey();
        }
    }
}

My database has the following setup :

CREATE TABLE Products 
(
    ProductID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    ProductName nvarchar(32) NOT NULL,
    ProductStock INT,
    ProductCategoryID INT,
    ProductEmployeeID int NOT NULL,
    ProductCreatedDate datetime NOT NULL DEFAULT GETDATE()
);

By running this, I can enter the data "productname, stock, categoryid, employeeid"

As "Jacket, 10, 1, 2" this should work as i have a category with the id 1. and a employee with the id 2.

When I run the program, I get an error

Incorrect syntax near '1'

My guess falls hard on some error in this area :

VALUES('{0}', {1}, {2}, {3}, '{4}')"
Improve this question
2
  • 1
    Start by forgetting you ever saw anyone use String.Format() to insert values directly into SQL queries, because that's called "SQL Injection" and is not the right way to do things - it leads to issues like Little Bobby Tables. Instead you want to use SqlCommand.Parameters so that values can be inserted using their correct data types and you don't have to worry about escaping crazy things like apostrophes in strings like Jack O'Neill. Commented Sep 19, 2020 at 12:17
  • 2
    In your previous question your code uses parameters correctly. Why did you change your technique here? And please start marking the appropriate response as the answer rather copy/pasting the code provided as your own "answer". Give a little credit for the free code others provide. Commented Sep 19, 2020 at 12:55

2 Answers 2

Reset to default
2

I would recommend using query parameters, like so:

conn.Open();

SqlCommand command = new SqlCommand(
    @"INSERT INTO Products
        (ProductName, ProductStock, ProductCategoryID, ProductEmployeeID, ProductCreatedDate)
        VALUES(@ProductName, @ProductStock, @ProductCategoryID, @ProductEmployeeID, getDate())", 
    conn
);
command.Parameters.Add("@ProductName", productname);
command.Parameters.Add("@ProductStock", productstock);
command.Parameters.Add("@ProductCategoryID", productcategoryid);
command.Parameters.Add("@ProductEmployeeID", productemployeeid);

command.ExecuteNonQuery();
conn.Close();

Note that you can compute the current date/time directly in the query, using SQL Server function getDate() rather than pass it from c#.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

And additionally, I'd recommend not using these deprecated overloads of .Add - use the one where you explicitly specify the data type of the parameter, instead: command.Parameters.Add("@ProductName", SqlDbType.NVarChar, 32).Value = productname;
0

Thank you, by using this correct way, its working flawless. you rock!

        static void InsertProduct(string productname, int productstock, int productcategoryid, int productemployeeid)
        {
            Database db = new Database();
            SqlConnection conn = db.connection();
            conn.Open();
            using SqlCommand command = new SqlCommand(@"INSERT INTO Products
                (ProductName, ProductStock, ProductCategoryID, ProductEmployeeID, ProductCreatedDate)
                VALUES(@ProductName, @ProductStock, @ProductCategoryID, @ProductEmployeeID, getDate())",conn);
            {
                command.Parameters.Add(new SqlParameter("@ProductName", productname));
                command.Parameters.Add(new SqlParameter("@ProductStock", productstock));
                command.Parameters.Add(new SqlParameter("@ProductCategoryID", productcategoryid));
                command.Parameters.Add(new SqlParameter("@ProductEmployeeID", productemployeeid));

                command.ExecuteNonQuery();
                conn.Close();
            }
        }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.