Solved

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

Posted on 2011-02-25
8
589 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

828 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