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.

Answer 1

protected void btnRegister_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Data Source=. ; Database=TestWebSiteDB; Integrated Security=true");
    SqlDataAdapter dap = new SqlDataAdapter("insert into Person(FirstName, LastName, NationalID) values(@Name, @Surname, @ID)", con);
    dap.InsertCommand(txtboxName.Text, txtboxFamilyName.Text,txtboxNationalCode.Text);

    txtboxName.Text="";
    txtboxFamilyName.Text = "";
    txtboxNationalCode.Text = "";
}

Answer 2

Try following steps:

Step1: Instead of putting you query in your C# file, declare a stored procedure for it like bellow:

CREATE PROCEDURE [dbo].[ADD_PERSON_SP]
    /*Type of this variables should be their column types*/
    @firstName varchar(MAX),
    @lastName varchar(MAX),
    @nationalID varchar(MAX)
AS
BEGIN
    INSERT INTO [dbo].[Person] (FirstName, LastName, NationalID)
           VALUES (@firstName,@lastName,@nationalID)
END 

Step2: Using Stored Procedure where you need:

SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("ADD_PERSON_SP", con);
com.Parameters.AddWithValue("@firstName", txtboxName.Text);
com.Parameters.AddWithValue("@lastName", txtboxFamilyName.Text);
com.Parameters.AddWithValue("@nationalID", txtboxNationalCode.Text);
com.CommandType = CommandType.StoredProcedure;
try
{
    con.Open();
    com.ExecuteNonQuery();
}
catch (Exception)
{
    throw;
}
finally
{
    if (con.State == ConnectionState.Open)
        con.Close();
}

Keep in mind to:

1. Do not put db related stuffs in your C# files
2. Try to choose better names for your variables(specially for controls)
3. This may help you with connectionString

I hope that help

Answer 3

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.

Answer 4

Actually the list view has a default method call onsorting. It will automatically sort the list view. onsorting will call the function as below.The function no need put any statement.

protected void Sorting(object sender,ListViewSortEventArgs e)
{

}

For the link button in list view u just simply put the tag like that:

<asp:ListView ID="DisplayContent" runat="server" onSorting="Sorting">
<asp:LinkButton ID="Name" runat="server" CommandName="Sorting" CommandArgument="Name" Text="Name" />

Answer 5

check your connection string. in conn.open() you get exception ?

To create a data connection to a SQL Server database

In Server Explorer/Database Explorer click Connect to Database.

In the Choose Data Source dialog box, select Microsoft SQL Server, and then click OK.

If the Add Connection dialog box opens, and the Data source is not Microsoft SQL Server, click Change to open the Choose/Change Data Source dialog box

. For more information, see Choose/Change Data Source Dialog Box. Select a server name from the drop-down list, or type the name of the server where the database you want to access is located.

Based on the requirements of your database or application, select either Windows Authentication or use a specific user name and password to log on to the SQL Server (SQL Server Authentication).

For more information, see Add/Modify Connection (Microsoft SQL Server).

Select the database you want to connect to from the drop-down list.

Click OK.

then copy generated connection string to your program. when you install sql server the server name and the setting you choose for installing .affect your connection string.

for more inforamtion on installing sql server see the installing sql server express edition

and also check this for connecting asp.net application to sql server Asp.net connection to SQL Server

Answer 6

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();
}