how to insert data into sql server in asp.net website?

Question

I tried to insert data into sql server from my website build in vs 2008.For that I used button click event .I tried code shown in youtube but the code doesn't work .It shows error in my website.

The code in .aspx.cs file is

public partial class _Default : System.Web.UI.Page 
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)
{
    conn.Open();
}
protected void btnInsert_Click(object sender, EventArgs e)
{
    SqlCommand cmd = new SqlCommand("insert into Insert    values('"+txtCity.Text+"','"+txtFName.Text+"','"+txtLName.Text+"')",conn);
    cmd.ExecuteNonQuery();
    conn.Close();
    Label1.Visible =true;
    Label1.Text = "Your data inserted successfully";
    txtCity.Text = "";
    txtFName.Text = "";
    txtLName.Text = "";
}

} `

The error I recieved was NullReferenceException was unhandled by user code...Object reference not set to an instance of an object — user2653867
– user2653867, Commented Aug 6, 2013 at 10:35

Mike Perrenoud · Accepted Answer · 2013-08-06 12:00:31Z

11

Okay, let's fix this code up just a little. You're getting there:

var cnnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
var cmd = "insert into Insert values(@City,@FName,@LName)";
using (SqlConnection cnn = new SqlConnection(cnnString))
{
    using (SqlCommand cmd = new SqlCommand(cmd, cnn))
    {
        cmd.Parameters.AddWithValue("@City",txtCity.Text);
        cmd.Parameters.AddWithValue("@FName",txtFName.Text);
        cmd.Parameters.AddWithValue("@LName",txtLName.Text);

        cnn.Open();
        cmd.ExecuteNonQuery();
    }
}

A couple things to note about the modified code.

It's leveraging the using statement to ensure that resources are properly disposed.
It's parameterized to ensure that SQL Injection isn't a possibility.
It's not storing a connection object anywhere, get rid of that stored connection.

edited Aug 6, 2013 at 12:00

answered Aug 6, 2013 at 10:30

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

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

2 Comments

marc_s Over a year ago

Plus: you wouldn't need to call the cnn.Open() until just before the cmd.ExecuteNonQuery(). No need to have the connection open while you're defining the parameters.....

Mike Perrenoud Over a year ago

@marc_s, that's a fantastic observation! I believe I'll change my standard (which you see above :D) to be just that. Thanks a lot!

Mrinmoy Nandy · Accepted Answer · 2015-12-30 12:58:37Z

**

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=mrinmoynandy;User ID=**;Password=****");
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void SumbitBtn_Click(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand("insert into streg(Name,Father,Mother,Dob,Sex,Category,Maritial,Vill,Po,Ps,Dist,State,Pin,Country) values (@name,@father,@mother,@dob,@sex,@category,@maritial,@vill,@po,@ps,@dist,@state,@pin,@country)", con);
        cmd.Parameters.AddWithValue(@"name", StNumTxt.Text);
        cmd.Parameters.AddWithValue(@"father", FatNumTxt.Text);
        cmd.Parameters.AddWithValue(@"mother", MotNumTxt.Text);
        cmd.Parameters.AddWithValue(@"dob", DobRdp.SelectedDate);
        cmd.Parameters.AddWithValue(@"sex", SexDdl.SelectedItem.Text);
        cmd.Parameters.AddWithValue(@"category", CategoryDdl.SelectedItem.Text);
        cmd.Parameters.AddWithValue(@"maritial", MaritialRbl.SelectedItem.Text);
        cmd.Parameters.AddWithValue(@"vill", VillTxt.Text);
        cmd.Parameters.AddWithValue(@"po", PoTxt.Text);
        cmd.Parameters.AddWithValue(@"ps", PsTxt.Text);
        cmd.Parameters.AddWithValue(@"dist", DistDdl.SelectedItem.Text);
        cmd.Parameters.AddWithValue(@"state", StateTxt.Text);
        cmd.Parameters.AddWithValue(@"pin", PinTxt.Text);
        cmd.Parameters.AddWithValue(@"country", CountryTxt.Text);
        con.Open();        
        con.Close();        
    }
}
Thanks
Mrinmoy Nandy
Phone No.: +91 9800451398

**

This code will not actually execute the command - it just opens and closes a connection after building the command. You need at least a cmd.ExecuteNonQuery() or something.

Sasidharan · Accepted Answer · 2013-08-13 08:57:13Z

Creating procedure will avoid sql injection.

SQL

Create procedure insert
(@City,@FirstName,@LastName)
{
insert into tablename (City,FName,LName)
values(@City,@FirstName,@LastName)
}

C#

SqlConnection con=new sqlconnection("give ur connection string here");
sqlcommand cmd=new sqlcommand();
con.open();
cmd=new sqlcommand("insert",con);
cmd.commandtype=commandtype.storedprocedure;
cmd.parameters.addwithvalue("@City",txtCity.text);
cmd.parameters.addwithvalue("@FName",txtFName.text);
cmd.parameters.addwithvalue("@LNAme",txtLName.text);
cmd.ExecuteNonQuery();
con.close();

Collectives™ on Stack Overflow

how to insert data into sql server in asp.net website?

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Related