How to insert data from ASP.Net to MS SQL Server?

Question

This question has been asked for several times here. I read posted questions but I still have problem. I'm trying to insert values from ASP.Net form to SQL Server. I created a sample website to work on inserting data into Sql table. SQL Database's name is "TestDatabaseDB" which has one table called "Person". Person table has 4 columns. They are ID, FirstName, LastName, NationalID. The type of ID is "int". I set "Is Identity:Yes". So SQL will assign an ID to each inserted record. It just doesn't work. When I click the button nothing happens. It must insert data into database table or clears the textboxes at least but it doesn't. I tried

SqlConnection conn= new SqlConnection(@"Data source=.\SQLEXPRESS; AttachDBFilename=""|DataDirectory|\TestWebSiteDB.mdf""; integrated user=true; User Instance=true") 

It didn't work. So I changed that into:

SqlConnection conn = new SqlConnection("Data Source=. ; Database=TestWebSiteDB; Integrated Security=true");

Didn't make any difference. Here is my code:

using System;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnRegister_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=. ; Database=TestWebSiteDB; Integrated Security=true");
        SqlCommand insert = new SqlCommand("insert into Person(FirstName, LastName, NationalID) values(@Name, @Surname, @ID)" ,conn);
        insert.Parameters.AddWithValue("@Name", txtboxName.Text);
        insert.Parameters.AddWithValue("@Surname", txtboxFamilyName.Text);
        insert.Parameters.AddWithValue("@ID", txtboxNationalCode.Text);
        try
        {
            conn.Open();
            insert.ExecuteNonQuery();
        }
        catch
        {
            LMsg.Text="Error when saving on database";
            conn.Close();
        }
        txtboxName.Text="";
        txtboxFamilyName.Text = "";
        txtboxNationalCode.Text = "";
    }
}

Any help would be appreciated.

Solution 1

Try this:

protected void Register_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DB_Users.mdf;Integrated Security=True");
    SqlCommand insert = new SqlCommand("insert into tbl_users(name, username, password,email) values(@name, @username, @password,@email)", conn);
    insert.Parameters.AddWithValue("@name", txtname.Text);
    insert.Parameters.AddWithValue("@username", txtusername.Text);
    insert.Parameters.AddWithValue("@password", txtpassword.Text);
    insert.Parameters.AddWithValue("@email", txtemail.Text);
    try
    {
        conn.Open();
        insert.ExecuteNonQuery();
        lbl_msg.Text = "Register done !";
      //lbl_msg.Text = "ثبت نام با موفقیت انجام شد";
    }
    catch (Exception ex)
    {
        lbl_msg.Text = "Error: "+ex.Message;
      //lbl_msg.Text = "خطا در ارتباط با پایگاه داده";
        conn.Close();
    }
}

It works for me.

Solution 2

You need to track what error you are getting as follows. Because it is not possible to help you without the actual error.

catch(Exception ex)
    {
        LMsg.Text=ex.Message;
    }

Also you need to use finally in your code for closing connection rather than closing it into the catch block.

finaly
{
   conn.Close();
}

Comments

[Jon Skeet]

"It didn't work" is never enough information. Presumably you're getting an exception at some point - which you'll have more details of if you catch it and log it in full.

[Sumit Gupta]

did you try SqlConnection conn = new SqlConnection("Data Source=.\SQLExpress ; Database=TestWebSiteDB; Integrated Security=true"); provided you use SQLExpress as your Database on local machine that runs your application. otherwise change "." with the IP address of that machine.

[jdev]

what is your Sql Server version ?(e.g. 2008, 2012..)

[abramlimpin]

Remove the try catch first so that we can identify the error from the page.

[Behnaz Mardanzadeh]

@JonSkeet Well I simply meant "It didn't work". Because I don't get any error. It just doesn't insert data when click on register button.

[Behnaz Mardanzadeh]

I'm trying to make a connection through code. I don't want to use Server Explorer on VS.

[jdev]

i know but you should first find what is your connection string. you can copy generated connection string and paste it to your code.