Insertion on table in Visual Studio

Question

I want to insert some data in a database that fetches the information to insert from the Form that I created. I am using the following command:

String query = @"INSERT INTO Visits(Name,Surname,DocType,DocNumber,Gender,Company,Delivery,Entrance,Visiting) VALUES( " 
                + NameBox.Text.Split(null)[0]+ " , " 
                + SurnameBox.Text.Split(null)[0] + " , " 
                + type + " , " 
                + Convert.ToDouble(idNBox.Text) + " , " 
                + gender + "," + Companybox.Text + " , "
                + delivery + " , " + DateTime.Now + " ," 
                + VisitingCombo.Text 
                + " )";

The table was created with the following structure,

CREATE TABLE [dbo].[Visits] (
[Name]     NVARCHAR (50) NOT NULL,
[Surname]  NVARCHAR (50) NOT NULL,
[DocType]  NVARCHAR (50) NOT NULL,
[IdNumber] BIGINT NOT NULL,
[Gender]   NCHAR (1)     NOT NULL,
[Company]  NVARCHAR (50) NOT NULL,
[Delivery] BIT           NOT NULL,
[Entrance] DATE          NOT NULL,
[Out]      DATE          NULL,
[Visting]  NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([IdNumber] ASC, [DocType] ASC, [Entrance] ASC),
FOREIGN KEY ([Visting]) REFERENCES [dbo].[Person] ([Name]));

When running the code I try to insert the following error appears,

System.Data.SqlClient.SqlException: 'Incorrect syntax near ','.'

For the insertion I used some code that I found in a response by Nicholas Carey that could solve my problem. How to directly execute SQL query in C#? Have example batch file

I know that the error occurs in the very first value of the query. What am I doing wrong in here?

Are you sure you don't have commas in any of your textboxes ? Just do MessageBox.Show(query) after declaration to test it. Also always use parameterised queries as Olaksandr suggested - that saves a lot of headaches later. — Vytautas Plečkaitis, Apr 18 '18 at 10:16
@Jerodev this would be the query [link](https://imgur.com/a/6yllK) — André Filipe Ferreira, Apr 18 '18 at 10:20

Sasha · Accepted Answer · 2018-04-18T10:22:50.643

You should not use string concatenation for SQL query building, especially if it's partially received from user input. Please be aware of SQL Injection attack.

Instead use parameterized query:

var cmd = new SqlCommand(@"INSERT INTO Visits(Name,Surname,DocType,DocNumber,Gender,Company,Delivery,Entrance,Visiting)
VALUES(@Name,@Surname,@DocType,@DocNumber,@Gender,@Company,@Delivery,@Entrance,@Visiting)", yourDbConnection);

cmd.Parameters.Add(new SqlParameter("@Name", NameBox.Text.Split(null)[0]));
//... Add other parameters in the same way

cmd.ExecuteNonQuery();

PS: Your direct problem is missing quotes around string values probably, but DO NOT concatenate that in any case!

score 1 · Answer 2 · answered Apr 18 '18 at 10:17

its very bad solution.

it can be hack.

its better use ADO.NET

 using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionCS"].ConnectionString))
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("INSERT INTO Visits(Name,Surname,DocType,DocNumber,Gender,Company,Delivery,Entrance,Visiting) VALUES(@Name,@Surname,@DocType,@DocNumber,@Gender,@Company,@Delivery,@Entrance,@Visiting)");
        using (SqlCommand com = new SqlCommand(sb.ToString(), con))
        {
            con.Open();
            com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = NameBox.Text.Split(null)[0];
            //Add Other Parameter
            //...
            com.ExecuteScalar();
        }
    }

score 1 · Answer 3 · answered Apr 18 '18 at 10:20

Short Answer

In your query, you missed to put the string (varchar) values in quotes '.

Example:

String query = @"INSERT INTO Visits(Name,Surname,DocType,DocNumber,Gender,Company,Delivery,Entrance,Visiting) VALUES( " 
               + "'" + NameBox.Text.Split(null)[0]+ "' , "

Better Answer:

You query suffer from SQL injection attack and maintainability issue. You should consider parameterizing your query to avoid them.

Example:

String query = @"INSERT INTO Visits(Name,Surname,DocType,DocNumber,Gender,Company,Delivery,Entrance,Visiting) VALUES( " 
               + "@Name, " ...

command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = NameBox.Text;

Remaining parameters is left for you to practice and learn.

score 0 · Answer 4 · answered Apr 18 '18 at 10:24

i would suggest you to check the quotation marks are proper or not use single quote inside double quote , put a debugger on the String query variable & then check what the variable contains is it having a proper query or not , & change as per the error in the varible value

score 0 · Answer 5 · answered Apr 18 '18 at 10:25

Long queries tend to get really messy really quickly. As Oleksandr said the way that you are passing information into an INSERT query is pretty dangerous. Try something like this :

string query = "INSERT INTO Visits(Name,Surname,DocType,DocNumber,Gender,Company,Delivery,Entrance,Visiting) VALUES(@name, @surname, @docType, @docNumber, @gender, @company, @delivery, @entrance, @visiting)";
string param = new {@name = NameBox.Text, @surname = SurnameBox.Text, ... @visiting = VisitingCombo.Text};

This means that any information that is passed to your database is passed as string, instead of someone being able to pass through unwanted SQL queries.

The reason you were getting a syntax exception is most likely because if you are passing straight text to a query you must wrap it in 'single quotes' .

Paramaterising your input both protects you from SQL Injection/Poisoning and avoids issues relating to data type declaration in your query.

score 0 · Answer 6 · answered Apr 18 '18 at 10:42

0

Please use stored procedure to solve this type of error. It is also fast while saving the data into data base.

answered Apr 18 '18 at 10:42

pankaj singh

95
10

Insertion on table in Visual Studio

6 Answers6