CODEWITHSUNDEEP
c#sql.netsql-server
Nirmala
Nirmala

Reputation: 1

An exception of type 'System.Data.SqlClient.SqlException' occured in 'System.Data.dll but was not handled in user code

I have just started working on c#.net. below is my code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;


public partial class Default2 : System.Web.UI.Page
{
    //SqlConnection con = new SqlConnection("Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=register;Integrated Security=True");

protected void Page_Load(object sender, EventArgs e)
{

}

private const string strconneciton = "Data Source=ADMIN-PC\\SQLEXPRESS;Initial Catalog=register;Integrated Security=True";

SqlConnection con = new SqlConnection(strconneciton);

protected void Button1_Click(object sender, EventArgs e)
{
     con.Open();
     SqlCommand cmd = new SqlCommand("insert into user(uname, address, 
email, number) values('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + 
TextBox3.Text + "', '" +TextBox4.Text+ "')", con);

     cmd.ExecuteNonQuery();
     con.Close();
 }
}

and I am getting this error

An exception of type 'System.Data.SqlClient.SqlException' occured in 'System.Data.dll but was not handled in user code


please help. I am using microsoft sql server management studio.

Upvotes: 0

Views: 326

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Do not hardcode sql queries:

  1. They are difficult to read (so you can easily commit syntax error: in your case number is MS Sql's reserved word and should be put as [number])
  2. They are prone to errors (e.g. what if TextBox2.Text contains an apostroph, ')
  3. They are vulnerable to Sql Injection

I suggest extracting a method:

private void CoreInsert() {
  //Done: wrap IDisposable into using, do not close explicitly
  //TODO: do not hardcode strConnection, but read from settings
  using (SqlConnection con = new SqlConnection(strConnection)) {
    con.Open();

    // Make sql 
    //   1. Readable: can you see a problem with "Number" now? 
    //   2. Parametrized 
    string sql = 
      @"insert into [user](
          uname, 
          address, 
          email, 
          [number]) -- <- number is MS SQL's reserved word, put it as [number]
        values(
          @prm_uname, 
          @prm_address, 
          @prm_email, 
          @prm_number)";

    //Done: wrap IDisposable into using
    using (SqlCommand cmd = new SqlCommand(sql, con)) {
      cmd.Parameters.AddWithValue("@prm_uname", TextBox1.Text);
      cmd.Parameters.AddWithValue("@prm_address", TextBox2.Text);
      cmd.Parameters.AddWithValue("@prm_email", TextBox3.Text);
      //TODO: check actual field's type here 
      cmd.Parameters.AddWithValue("@prm_number", TextBox4.Text);

      cmd.ExecuteNonQuery();
    }    
  }
}

Then call the method

protected void Button1_Click(object sender, EventArgs e) {
  CoreInsert();
}

Upvotes: 2

ReadyFreddy
ReadyFreddy

Reputation: 923

Use Parameters function to add values to your command strings, always. I think it's problem about you are sending value "number" as char and you may define it as int at your database. So you can try to delete single qutoes from Textbox4.Text.

But if you want to make it better, use parameters. Like that.

SqlCommand cmd = new SqlCommand("insert into user(uname, address, 
email, number) values(@uname,@address,@email,@number)", con);
cmd.Parameters.AddWithValue("@uname", TextBox1.Text);
cmd.Parameters.AddWithValue("@address", TextBox2.Text);
cmd.Parameters.AddWithValue("@email", TextBox3.Text);
cmd.Parameters.AddWithValue("@number", TextBox4.Text);
cmd.ExecuteNonQuery();

This will also prevent any SQL Injection problems.

Upvotes: 0

Related Questions