Solved

Sometimes i can't display data to my datagrid from sql server , what can i do ??

Posted on 2011-02-25
8
586 Views
Last Modified: 2012-05-11
i want to display data to my datagrid from sql server , but i can't , so what can i do ??

is it matter of connection time out in sql server ??

please refer my below coding for sql server connection ...

public DataTable Table(string sqlQuery)
        {
            // coding for sql server connection 
            DataTable dtable = new DataTable();
            try
            {
                SqlConnection connection = SqlServerConnection(); // this will open sql server 2000
                connection.ConnectionTimeout.Equals(100000);
                SqlCommand dbcommand = new SqlCommand(sqlQuery.ToString(), connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(dbcommand);
                //create data table
                dataAdapter.Fill(dtable);
            }
            catch (Exception ex)
            {

            }
            finally
            { 
            
            }

            /* coding for Ms Access 2007 connection
            DataTable dtable = new DataTable();
            try
            {
                OleDbConnection Accessconnection = MsAccessConnection(); // this will open sql server 2000
                Accessconnection.ConnectionTimeout.Equals(10000);
                OleDbCommand Accesscommand = new OleDbCommand(sqlQuery.ToString(), Accessconnection);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(Accesscommand);
                //create data table
                dataAdapter.Fill(dtable);
            }
            catch (Exception ex)
            {

            }
             */
            return dtable;
        }

       //get sql server connection
        public SqlConnection SqlServerConnection()
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();
            return connection;
        }

Open in new window


is there anything wrong in the above coding ??
or i have to specify sql server connection time out ??
0
Comment
Question by:Parth48
  • 3
  • 3
  • 2
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Try running the same query directly in SQL Server using SQL Server Management Studio (SSMS) and check how much time it takes for the query to complete its execution.
Value noted above is your Execution time and make sure your connection timeout value is greater that the above value to get it work..
By default, connection timeout would be 30 seconds.
0
 

Author Comment

by:Parth48
Comment Utility
when i running the same query directly in sql server then run successfully ...

what can i do ??
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> when i running the same query directly in sql server then run successfully ...

How much time it took for executing completely..
And are you doing any cursor or any other ORDER or GROUP operations in your front end code..
0
 

Author Comment

by:Parth48
Comment Utility
no only i have to find out that after sometime sql server connection automatically time out or not ??
or if it is then how can i increase connection time out time ???
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
No, SQL Server will not time out..
Since commandtimeout (the time application will wait for response from SQL Server) is specified at Application level, it will time out and disconnect the session thereby not fetching results from SQL Server.

So increasing timeout value would help..
More info about commandTimeout here:
http://classicasp.aspfaq.com/general/how-do-i-increase-timeout-values.html
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
this line:

Accessconnection.ConnectionTimeout.Equals(10000);


is NOT setting the ConnectionTimeout to 10000, but is TESTING whether the ConnectionTimeout value is equal to 10000.  In order to set the ConnectionTimeout value to 10000, you should use this:

Accessconnection.ConnectionTimeout = 10000;

AW
0
 

Author Comment

by:Parth48
Comment Utility
hi @Arthur_Wood:

error in below line ...

Accessconnection.ConnectionTimeout = 10000;

Open in new window


Error :


Property or indexer 'System.Data.Common.DbConnection.ConnectionTimeout' cannot be assigned to -- it is read only....

What can i do now ? how can i define connectiontimeout property for sql server connection ?
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
Comment Utility
The Connection Timeout must be set in the Connection string:

       public SqlConnection SqlServerConnection()
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();
            return connection;
        }


modify the connection string here, as

Connection String +"Connection Timeout = 100"

       public SqlConnection SqlServerConnection()
        {
            SqlConnection connection = new SqlConnection(ConnectionString +"Connection Timeout = 100"
);
            connection.Open();
            return connection;
        }

try it like that.

AW

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now