Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-25
8
Medium Priority
?
594 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…

719 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