Sql Exception cant solve and run the program

Question

This is My Asp.net CodeBehindFile.

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Configuration; 
using System.Data.SqlClient; 
using System.Data; 
namespace binding 
{ 
public partial class dataFromTable : System.Web.UI.Page 
{ 
protected void Page_Load(object sender, EventArgs e) 
{ 
stringstr=ConfigurationManager.ConnectionStrings["DefaultConnection"]
.ConnectionString; 
using (SqlConnection con = new SqlConnection(str))
 { 
SqlCommand com = new SqlCommand("Select ID,CityID,CityName,Country from  
City", con);        
con.Open();
DropDownList1.DataSource = com.ExecuteReader(); 
DropDownList1.DataTextField = "CityName"; 
DropDownList1.DataValueField = "CityID"; DropDownList1.DataBind(); }

} 
} 
}

It shows 2 Errors First one is:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code.

Second Error is:

connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=26285; handshake=912;

This may well be a duplicate of: http://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes — Steven Brookes, Oct 16 '15 at 12:10

score 0 · Answer 1 · answered Oct 16 '15 at 12:12

You could use a SqlDataAdapter to fill an offline DataTable which you can use as DataSource for your DropDownList. Also, use the using-statement for objects implementing IDisposable:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable tblCities = new DataTable();
        string conStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (var con = new SqlConnection(conStr))
        using (var com = new SqlCommand("Select ID,CityID,CityName,Country from City", con))
        using (var da = new SqlDataAdapter(com))
            da.Fill(tblCities); // you dont need to open/close the connection with Fill

        DropDownList1.DataSource = tblCities;
        DropDownList1.DataTextField = "CityName";
        DropDownList1.DataValueField = "CityID";
        DropDownList1.DataBind();
    }
}

Don't databind it on every postback, otherwise it prevents events and cancels the selection. Use the !IsPostBack-check as i've done above..

Dear tim, This Code "SqlDataAdapter" is not working.Again it shows the same error — Dharsini, Oct 16 '15 at 13:40
Dear Tim,Please give another Solution.I am using visual studio2013 for web and sqlserver 2014 . — Dharsini, Oct 16 '15 at 16:51

Sql Exception cant solve and run the program

1 Answers1