Solved

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

Posted on 2011-02-25
8
590 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
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.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
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…

679 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