Inserting values into a SQL Server database using ado net via C

Question

I have created a simple program to insert values into the table  regist   but I keep getting the error      Incorrect syntax near       on cmd ExecuteNonQuery       private void button1 Click object sender  EventArgs e           SqlConnection cn   new SqlConnection  Data Source DELL-PC initial catalog AdventureWorks2008R2   User ID sa Password sqlpass Integrated Security SSPI            SqlCommand cmd   new SqlCommand  INSERT INTO dbo regist        FirstName  Lastname  Username  Password  Age  Gender Contact         VALUES         textBox1 Text   textBox2 Text   textBox3 Text   textBox4 Text   comboBox1 Text  comboBox2 Text  textBox7 Text         cn          cn Open          cmd ExecuteNonQuery          cn Close        I am new to this and I am really confused

User · Answer

private void button1 Click object sender  EventArgs e        SqlConnection con   new SqlConnection        con ConnectionString    data source CHANCHAL SQLEXPRESS initial catalog AssetManager user id GIPL-PC GIPL password        con Open        SqlDataAdapter ad   new SqlDataAdapter  select   from detail1   con       SqlCommandBuilder cmdbl   new SqlCommandBuilder ad       DataSet ds   new DataSet  detail1        ad Fill ds   detail1        DataRow row   ds Tables  detail1   NewRow        row  Name     textBox1 Text      row  address    textBox2 Text      ds Tables  detail1   Rows Add row       ad Update ds   detail1        con Close        MessageBox Show  insert secussfully

User · Answer

As I said in comments - you should always use parameters in your query - NEVER EVER concatenate together your SQL statements yourself   Also  I would recommend to separate the click event handler from the actual code to insert the data   So I would rewrite your code to be something like  In your web page s code-behind file  yourpage aspx cs   private void button1 Click object sender  EventArgs e          string connectionString    Data Source DELL-PC initial catalog AdventureWorks2008R2   User ID sa Password sqlpass Integrated Security SSPI           InsertData connectionString                   textBox1 Text Trim     -- first name                  textBox2 Text Trim     -- last name                  textBox3 Text Trim     -- user name                  textBox4 Text Trim     -- password                  Convert ToInt32 comboBox1 Text    -- age                  comboBox2 Text Trim    -- gender                  textBox7 Text Trim       -- contact     In some other code  e g  a databaselayer cs    private void InsertData string connectionString  string firstName  string lastname  string username  string password                         int Age  string gender  string contact           define INSERT query with parameters     string query    INSERT INTO dbo regist  FirstName  Lastname  Username  Password  Age  Gender Contact                           VALUES   FirstName   Lastname   Username   Password   Age   Gender   Contact             create connection and command     using SqlConnection cn   new SqlConnection connectionString       using SqlCommand cmd   new SqlCommand query  cn                    define parameters and their values         cmd Parameters Add   FirstName   SqlDbType VarChar  50  Value   firstName          cmd Parameters Add   Lastname   SqlDbType VarChar  50  Value   lastName          cmd Parameters Add   Username   SqlDbType VarChar  50  Value   userName          cmd Parameters Add   Password   SqlDbType VarChar  50  Value   password          cmd Parameters Add   Age   SqlDbType Int  Value   age          cmd Parameters Add   Gender   SqlDbType VarChar  50  Value   gender          cmd Parameters Add   Contact   SqlDbType VarChar  50  Value   contact              open connection  execute INSERT  close connection         cn Open            cmd ExecuteNonQuery            cn Close              Code like this    is not vulnerable to SQL injection attacks performs much better on SQL Server  since the query is parsed once into an execution plan  then cached and reused later on  separates the event handler  code-behind file  from your actual database code  putting things where they belong - helping to avoid  overweight  code-behinds with tons of spaghetti code  doing everything from handling UI events to database access - NOT a good design

User · Answer

you should remove last comma and as nrodic said your command is not correct   you should change it like this    SqlCommand cmd   new SqlCommand  INSERT INTO dbo regist        FirstName  Lastname  Username  Password  Age  Gender Contact        VALUES        textBox1 Text  textBox2 Text  textBox3 Text  textBox4 Text  comboBox1 Text comboBox2 Text textBox7 Text         cn

User · Answer

Remove the comma      Gender Contact         VALUES                        -----------------here

User · Answer

Following Code will work for  Inserting values into a SQL Server database using ado net via C       Your Connection string string connectionString    Data Source DELL-PC initial catalog AdventureWorks2008R2   User ID sa Password sqlpass Integrated Security SSPI        Collecting Values string firstName  Name       lastName  LastName       userName  UserName       password  123       gender  Male       contact  Contact   int age 26       Query to be executed string query    Insert Into dbo regist  FirstName  Lastname  Username  Password  Age  Gender Contact                           VALUES   FN   LN   UN   Pass   Age   Gender   Contact             instance connection and command     using SqlConnection cn   new SqlConnection connectionString       using SqlCommand cmd   new SqlCommand query  cn                    add parameters and their values         cmd Parameters Add   FN   System Data SqlDbType NVarChar  100  Value   firstName          cmd Parameters Add   LN   System Data SqlDbType NVarChar  100  Value   lastName          cmd Parameters Add   UN   System Data SqlDbType NVarChar  100  Value   userName          cmd Parameters Add   Pass   System Data SqlDbType NVarChar  100  Value   password          cmd Parameters Add   Age   System Data SqlDbType Int  Value   age          cmd Parameters Add   Gender   System Data SqlDbType NVarChar  100  Value   gender          cmd Parameters Add   Contact   System Data SqlDbType NVarChar  100  Value   contact              open connection  execute command and close connection         cn Open            cmd ExecuteNonQuery            cn Close

[c#] Inserting values into a SQL Server database using ado.net via C#

Examples related to c#

Examples related to sql

Examples related to sql-server

Examples related to ado.net

Examples related to executenonquery