Why do I get System.Data.SqlClient.SqlInternalConnection.OnError... in ASP.NET?

Question

I am fairly new to ASP.NET and I am trying to save the data of my web form into the database. I have tested the database table connection with the Aspx file and it loads the data of the table(manually entered data) into the table correctly in the browser.

However, when I fill up the form and press Save, I get this error:Screenshot of error message

This is my code of aspx.cs.

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;
using System.Configuration;

public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
 {
    if(IsPostBack)
        {

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
        conn.Open();


        Response.Write("Database connection successful");


        conn.Close();
    }

}



protected void Button2_Click(object sender, EventArgs e)
{
    Response.Write("Database connection successful");

}

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
        conn.Open();

        string insertQuery = "insert into Table (Name, Address, Phone, Email, Source, Message) values (@name, @address, @phone, @email, @source, @message");
        SqlCommand com = new SqlCommand(insertQuery, conn);
        com.Parameters.AddWithValue("@name", Name.Text);
        com.Parameters.AddWithValue("@address", Address.Text);
        com.Parameters.AddWithValue("@phone", Phone.Text);
        com.Parameters.AddWithValue("@email", Email.Text);
        com.Parameters.AddWithValue("@source", Source.SelectedItem.ToString());
        com.Parameters.AddWithValue("@message", "text");

        com.ExecuteNonQuery();

        Response.Write("Data is saved");

        conn.Close();


    }

    catch (Exception ex)
    {

        Response.Write("Error:" + ex.ToString());

    }


}
}

My error says this:

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _Default.Button1_Click(Object sender, EventArgs e) in D:\documents\visual studio 2015\WebSites\Enquiry Tracking Sys\Default.aspx.cs:line 45 ClientConnectionId:6c142f6c-40d7-4977-b75d-8fb0d774b0c6 Error Number:156,State:1,Class:15

Answer 1

Modified : Possible issues may include user disabled, mixed mode disabled or user doesn't have permission on the DB

1. For mixed mode auth : http://www.lansweeper.com/kb/23/SQLserver-enable-mixed-authentication.html

2. To assign rights to your user name, execute something like this :

   use YourDatabase

   go

   exec sp_addrolemember 'db_owner', 'UserName'

   go

There are chances that your named pipes configuration is wrong/corrupted. Try resetting that or only using TCP/IP I guess.

Apart from that, You are missing a closing parenthesis in your insert statement.

 string insertQuery = "insert into Table (Name, Address, Phone, Email, Source, Message) values (@name, @address, @phone, @email, @source, @message)";

And, is your table name "Table" ? Seems odd.

Answer 2

Transport level errors are often linked to the connection to sql server being broken ... usually network.

Timeout Expired is usually thrown when a sql query takes too long to run.

So I would troubleshoot the link to your Sql Server and then monitor to see what queries are timing out.

Sounds like a SQL job is running, backup? That might be locking tables or restarting the service.

Connection problems with SQL Server in ASP.NET applications using out-of-process session state

Answer 3

We use service accounts in IIS to run a particular service. In my case, one of the SQL stored proc was missing the required service account permissions. We added the permissions to the stored procedure and the application started working.

Answer 4

    public partial class Main : System.Web.UI.Page
{
    //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);
    SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\my\Documents\Visual Studio 2013\Projects\DataBase\DataBase\App_Data\List.mdf;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into [Table](Id,fname,lname) values('"+TextBox3.Text+"','"+TextBox1.Text+"','"+TextBox2.Text+"')",con);
        cmd.ExecuteNonQuery();
        con.Close();
        Label1.Visible = true;
        Label1.Text = "Data Stored!!";
        TextBox1.Text = "";
        TextBox2.Text = "";

    }
}

}

Try using this code it worked form me. And avoid using the Connection string. And in web.config folder delete the content of the<connection> </connection>tags. As for the Data source, you will find it in the properties of the database which you are using. The SQL command is little bit different what we used to type.