Click here to Skip to main content
15,906,626 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
String ConnectionString = "Data Source=Lenovo-PC\\SQLSERVER;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection Conn = new SqlConnection(ConnectionString);
String Query = " INSERT INTO EMPLOYEE 1(Name,Father,CNIC,Mobile,City,State) VALUES (" +
" '" + textName.Text + "', " +
" '" + textFather.Text + "', " +
" '" + textCNIC.Text + "', " +
" '" + textMobile.Text + "', " +
" '" + textCity.Text + "', " +
" " + textState.Text + ", " + ")";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);

the error is:

Incorrect syntax near '1'.
Posted
Updated 27-Apr-15 1:37am
v2
Comments
scottgp 27-Apr-15 7:38am    
in this part of the statement, 'INSERT INTO EMPLOYEE 1(Name' is the 1 supposed to be there, and if so , should there be a space between 'EMPLOYEE' and '1'?
Naveen Kumar Tiwari 27-Apr-15 7:52am    
This is Very Bad style of code writing..Error finding is very hard of such type of pattern...I suggest you dear improve your code...

It means your first line was having a incorrect connection string.

However for best practices declare connection string in Web.config or app.config like below.

XML
<?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <connectionStrings>
            <add name="CONSTRING" connectionString="Data Source=Lenovo-PC\\SQLSERVER;Initial Catalog=Google;Integrated Security=True"/>
        </connectionStrings>
    </configuration>


then declare a variable and add that connection string

C#
public string conn = ConfigurationManager.ConnectionStrings["CONSTRING"].ConnectionString;
   }


then write your code as below

C#
SqlConnection Conn = new SqlConnection(conn);
String Query = " INSERT INTO EMPLOYEE 1(Name,Father,CNIC,Mobile,City,State) VALUES (" +
" '" + textName.Text + "', " +
" '" + textFather.Text + "', " +
" '" + textCNIC.Text + "', " +
" '" + textMobile.Text + "', " +
" '" + textCity.Text + "', " +
" " + textState.Text + ", " + ")";
SqlDataAdapter da = new SqlDataAdapter(Query, Conn);
DataTable dt = new DataTable();
da.Fill(dt);
 
Share this answer
 
NOOOO!!!

You are wide open to sql insertions. That's a very, very bad thing!

An the error is in
" INSERT INTO EMPLOYEE 1


wither the 1 shouldn't be there, or if the table is called EMPLOYEE 1 then add square braces to sql knows it's a single term:

using (SqlConnection conn = new SqlConnection())
{ // added using so the dispose of conn is called

    String Query =
        " INSERT INTO [EMPLOYEE 1](Name,Father,CNIC,Mobile,City,State) VALUES (@name,@father,@cnic,@mobile,@city,@state);";

    using (SqlCommand command = conn.CreateCommand())
    {

        command.CommandType = CommandType.Text;
        command.CommandText = Query;
        command.Parameters.AddRange(new[]
        {
            new SqlParameter("@name", SqlDbType.NVarChar) {Value = textName.Text},
            new SqlParameter("@father", SqlDbType.NVarChar) {Value = textFather.Text},
            new SqlParameter("@cnic", SqlDbType.NVarChar) {Value = textCNIC.Text},
            new SqlParameter("@mobile", SqlDbType.NVarChar) {Value = textMobile.Text},
            new SqlParameter("@city", SqlDbType.NVarChar) {Value = textCity.Text},
            new SqlParameter("@state", SqlDbType.NVarChar) {Value = textState.Text},
        });

        conn.Open();

        command.ExecuteNonQuery();

        conn.Close();
    }

}


You don't need the data adapter. Inserts do not return values. The return a single int telling you how many rows were affected.

Look into SQL Injection. Using parameters should protect you to a good degree. Any parameters are encoded so as to disallow injection.

On the subject of SQL Injection:
http://en.wikipedia.org/wiki/SQL_injection[^]

My father is '; drop table * ;'. His mates called him 'droppy' for short.

Please use this solution as an addition to the first. Debasish Mishra is correct about the connection string best practice.


EDIT: forgot to open the connection #^_^#
 
Share this answer
 
v3
Comments
Member 11645178 27-Apr-15 7:55am    
when i wrote this code:

String ConnectionString = "Data Source=Lenovo-PC\\SQLSERVER;Integrated Security=True;" +
"Initial Catalog=Google";
SqlConnection conn = new SqlConnection(ConnectionString);

String Query = " INSERT INTO [EMPLOYEE 1](Name,Father,CNIC,Mobile,City,State) VALUES (@name,@father,@cnic,@mobile,@city,@state);";
SqlCommand command = conn.CreateCommand();

command.CommandType = CommandType.Text;
command.CommandText = Query;
command.Parameters.AddRange(new[]
{
new SqlParameter("@name",SqlDbType.NVarChar){Value = textName.Text},
new SqlParameter("@father",SqlDbType.NVarChar){Value = textFather.Text},
new SqlParameter("@cnic",SqlDbType.NVarChar){Value = textCNIC.Text},
new SqlParameter("@mobile",SqlDbType.NVarChar){Value = textMobile.Text},
new SqlParameter("@city",SqlDbType.NVarChar){Value = textCity.Text},
new SqlParameter("@state",SqlDbType.NVarChar){Value = textState.Text},
}
);

command.ExecuteNonQuery();

now the error is:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Andy Lanng 27-Apr-15 7:56am    
Oh yeah! - sorry - will update answer
Member 11645178 27-Apr-15 8:07am    
using (SqlConnection conn = new SqlConnection())
{ // added using so the dispose of conn is called

String Query =
" INSERT INTO [EMPLOYEE 1](Name,Father,CNIC,Mobile,City,State) VALUES (@name,@father,@cnic,@mobile,@city,@state);";

using (SqlCommand command = conn.CreateCommand())
{

command.CommandType = CommandType.Text;
command.CommandText = Query;
command.Parameters.AddRange(new[]
{
new SqlParameter("@name", SqlDbType.NVarChar) {Value = textName.Text},
new SqlParameter("@father", SqlDbType.NVarChar) {Value = textFather.Text},
new SqlParameter("@cnic", SqlDbType.NVarChar) {Value = textCNIC.Text},
new SqlParameter("@mobile", SqlDbType.NVarChar) {Value = textMobile.Text},
new SqlParameter("@city", SqlDbType.NVarChar) {Value = textCity.Text},
new SqlParameter("@state", SqlDbType.NVarChar) {Value = textState.Text},
});

conn.Open();

command.ExecuteNonQuery();

conn.Close();
}

}


now the error is:

The ConnectionString property has not been initialized.
what about this line (String ConnectionString = "Data Source=Lenovo-PC\\SQLSERVER;Integrated Security=True;" +"Initial Catalog=Google";)
its not important?
Andy Lanng 27-Apr-15 8:12am    
Yes it is. My answer was supposed to take the connection string aspect from the first solution by debasish mishra, but I forgot to add it.

Alternatively, you can add that line and replace 'new SqlConnection()' with 'new SqlConnection(ConnectionString)';

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900