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.

• 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.

Answer 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.

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

Answer 3

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

• 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.

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

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 6

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 = "";
}