ADO.NET Connection Problem

Question

Hi i am getting this error while tryin to connect my web site.

Unspecified error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Unspecified error.

Maybe i am doing something wrong while opening and closing connections.I always get this error if 5-10 user enter same time to site.When a user enter site i am updating or inserting new records for statistics ?.

// iam using db class for connect ...

  public OleDbConnection baglan()
{
    OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath("~/App_Data/manisaweb.mdb"));
    baglanti.Open();
    return (baglanti);
}
   //********************************************************************
//Sql Sorgu Çalıştırma
public int cmd(string sqlcumle)
{
    OleDbConnection baglan = this.baglan();
    OleDbCommand sorgu = new OleDbCommand(sqlcumle, baglan);
    int sonuc = 0;

    try
    {
        sonuc = sorgu.ExecuteNonQuery();
    }
    catch (OleDbException ex)
    {
        throw new Exception(ex.Message + " (" + sqlcumle + ")");
    }
    finally
    {
        sorgu.Connection.Close();
    }
    return (sonuc);
}
//********************************************************************
//Kayıt Sayısı Bulma
public string GetDataCell(string sql)
{
    DataTable table = GetDataTable(sql);
    if (table.Rows.Count == 0)
        return null;
    return table.Rows[0][0].ToString();
}

//Kayıt Çekme
public DataRow GetDataRow(string sql)
{
    DataTable table = GetDataTable(sql);
    if (table.Rows.Count == 0) return null;
    return table.Rows[0];

}

//DataTable ye veri çekme
public DataTable GetDataTable(string sql)
{
    OleDbConnection baglan = this.baglan();
    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, baglan);
    DataTable dt = new DataTable();

    try
    {
        adapter.Fill(dt);

    }
    catch (OleDbException ex)
    {
        throw new Exception(ex.Message + " (" + sql + ")");
    }
    finally
    {
        adapter.Dispose();
        baglan.Close();
    }
    return dt;
}

//Datasete veri çekme
public DataSet GetDataSet(string sql)
{
    OleDbConnection baglan = this.baglan();
    OleDbDataAdapter adapter = new OleDbDataAdapter(sql, baglan);
    DataSet ds = new DataSet();
    try
    {
        adapter.Fill(ds);
    }
    catch (OleDbException ex)
    {
        throw new Exception(ex.Message + " (" + sql + ")");
    }
    finally
    {
        ds.Dispose();
        adapter.Dispose();
        baglan.Close();
    }
    return ds;
}

AND For STATISTICS in main.master.cs every page_load event

public void Istatistik()
{


    string IpAdres = Request.ServerVariables["REMOTE_ADDR"].ToString();//Ip Adresini alıyoruz.
    string Tarih = DateTime.Now.ToShortDateString();
    lblOnlineZiyaretci.Text = Application["OnlineUsers"].ToString();//Online ziyaretçi

    //Ogüne Ait Hit Bilgi Güncelleme
    DataRow drHit = GetDataRow("Select * from SayacHit Where Tarih='" + Tarih + "'");
    if (drHit == null)
    {
        //Bugüne ait kayıt yoksa bugunün ilk siftahını yap
        cmd("Insert into SayacHit(Tarih,Tekil,Cogul) values('" + Tarih + "',1,1)");
    }
    else
    {

        string SayfaAdi = Page.ToString().Replace("_aspx", ".aspx").Remove(0, 4); //Sayfa adını alıyoruz.
        if (SayfaAdi == "default.aspx")//Güncelleme işlemini sadece anasayfadaysa yapıyoruz
        {
            //Bugüne ait kayıt varsa Çoğulu 1 artırıyoruz.
            cmd("Update SayacHit set Cogul=Cogul+1 Where Tarih='" + Tarih + "'");
        }

        //Tekil artımı için önce Ip kontrolü yapıyoruz.
        DataRow drIpKontrol = GetDataRow("select * from SayacIp Where Ip='" + IpAdres + "'");
        if (drIpKontrol == null)
        { //Eğer ip yoksa tekilide artırabiliriz. Ip kayıtlı ise artırma işlemi yapmıyoruz.
            cmd("Update SayacHit set Tekil=Tekil+1 Where Tarih='" + Tarih + "'");
        }
    }


    //Giren Kişinin IP sini Kaydetme
    DataRow drIp = GetDataRow("Select * from SayacIp Where Ip='" + IpAdres + "'");
    if (drIp == null)
    {
        cmd("Insert into SayacIp(Ip,Tarih) values('" + IpAdres + "','" + Tarih + "')");
    }


    //Ekrana Bilgileri Yazdırabiliriz
    DataRow drSonuc = GetDataRow("Select * from SayacHit Where Tarih='" + Tarih + "'");
    lblBugunTop.Text = drSonuc["Cogul"].ToString();
    //lblBugunTekil.Text = drSonuc["Tekil"].ToString();
    //Dün Bilgilerini Çekme
    //DataRow drDun = GetDataRow("Select * from SayacHit Where Tarih='" + DateTime.Now.AddDays(-1).ToShortDateString() + "'");
    DataRow drGenel = GetDataRow("Select SUM(Tekil) as Toplam from SayacHit");
    //if (drDun != null)
    //{
    //    lblDunTop.Text = drDun["Tekil"].ToString();
    //}
    //else
    //{
    //    lblDunTop.Text = "0";
    //}

    lblGenelTop.Text = drGenel["Toplam"].ToString();
    lblIPAdresi.Text = IpAdres;
}

AND Then there is 2 section in Default.aspx its loads at page load event news and articles.

    db veri = new db();

    rptNews.DataSource = veri.GetDataTable("select top 5 KullaniciAdiSoyadi,Ozet,Baslik,Tarih,IcerikID,Icerik from Icerik a inner join Kullanici d on a.KullaniciID=d.KullaniciID where KategoriID = 1 and Durum = 1 Order by IcerikID Desc ");
    rptNews.DataBind();
    rptArticle.DataSource = veri.GetDataTable("select top 5 Ozet,Baslik,Tarih,IcerikID,Icerik from Icerik where KategoriID = 2 and Durum = 1 Order by IcerikID Desc ");
    rptArticle.DataBind();

SO THERE IS SO MANY UPDATE , INSERT AND SELECT QUERY IN EVERY PAGE LOAD EVENT. If this is my problem there is another way of doing whole this things ?

thanx everyone.

Answer 1

I would second Henk's #2 suggestion: failure to properly close database connections. If you are using DataSets or DataReaders, sending inline SQL or calling stored procedures, you must close the connections in code when you are through with them. If you don't close the connection, the number of available connections is gradually maxed out, and no new database requests can be made. It is only after enough users' sessions time out and the connections are released that other users can obtain a connection.

You have the exact symptom of unclosed connections: everything works fine for the first few users. But gradually, as more users log in, or as the original users move about the web app, opening ever more database connections, that everything locks up.

One way to diagnose this would be to use a database profiler tool (Profiler for SQL Server) to examine the database connections as they are opened and closed.