Solved

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

Posted on 2011-02-25
8
588 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
ID: 34978433
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
ID: 34978471
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
ID: 34978648
>> 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Parth48
ID: 34978690
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34979064
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
ID: 34979096
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
ID: 34995131
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
ID: 34996521
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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