DataAdapter.Fill(Dataset) shows timeout error. - ADO DAO RDO RDS

Mmm...

I think it could be a good idea to check first if the problems is related
with the DataSet or if it's the Stored procedure or if it's too much data.

Could try to run the same query using a SqlDataReader and a loop and check
if using that retrieves data ? (from there we can further research and check
if it's because of the dataset or ...)

HTH
Braulio

/// ------------------------------
/// Braulio Diez
///
/// http://www.tipsdotnet.com
/// ------------------------------




"Manohar" wrote:

> I am executing a stored procedure and returning a resultset. The
> stored procedure takes approx 40 secs to execute. I have set the
> connection and command timeout to 10000, but I am still getting the
> error, "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
>
> Here is the code for executing the stored procedure.
> private DataSet ExeCmdWithDataSet(string SQLText, string strdbconnect)
> {
> SqlConnection cn = new SqlConnection();
> cn.ConnectionString = strdbconnect;
> cn.Open();
>
> DataSet ds = new DataSet();
> SqlCommand cmd = new SqlCommand();
> cmd.CommandTimeout = 10000;
> cmd.CommandType = CommandType.Text;
> cmd.CommandText = SQLText;
> cmd.Connection = cn;
>
> cmd.Prepare();
>
> SqlDataAdapter da = new SqlDataAdapter(SQLText, cn);
> da.Fill(ds);
> cn.Close();
>
> return ds;
> }
>
> Please help.
>
> Thanks,
>
>

Hi,

you create SqlCommand and then SqlDataAdapter, but they have got no
asociation except query string. So Timeout is set to cmd, but not to command
da creates when you pass him the query string. You have to create
SqlDataAdapter like this:

SqlDataAdapter da = new SqlDataAdapter(cmd);

Now da uses cmd and it should work.

HTH
Pepa

"Manohar" wrote:

> I am executing a stored procedure and returning a resultset. The
> stored procedure takes approx 40 secs to execute. I have set the
> connection and command timeout to 10000, but I am still getting the
> error, "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
>
> Here is the code for executing the stored procedure.
> private DataSet ExeCmdWithDataSet(string SQLText, string strdbconnect)
> {
> SqlConnection cn = new SqlConnection();
> cn.ConnectionString = strdbconnect;
> cn.Open();
>
> DataSet ds = new DataSet();
> SqlCommand cmd = new SqlCommand();
> cmd.CommandTimeout = 10000;
> cmd.CommandType = CommandType.Text;
> cmd.CommandText = SQLText;
> cmd.Connection = cn;
>
> cmd.Prepare();
>
> SqlDataAdapter da = new SqlDataAdapter(SQLText, cn);
> da.Fill(ds);
> cn.Close();
>
> return ds;
> }
>
> Please help.
>
> Thanks,
>
>

You must modify below tow section

1.cmd.CommandTimeout = 10000
=>cmd.CommandTimeout = 0;
2.SqlDataAdapter da = new SqlDataAdapter(SQLText, cn);
=>SqlDataAdapter da = new SqlDataAdapter(cmd);

I am having the same issue in following code:
sqlConn.Open()
da = New SqlClient.SqlDataAdapter(command)
da.Fill(dt) '------Issue is here
FillValidationControlGrid(dt)
to avoid timeout crash i have set command timeout to 0 (zero), it has resolved the issue but taking so long time (3 minutes) to fill out datatable while my sql query is taking only 2 seconds.
How can i optimize the above code so that i should take few seconds to bind datatable through dataadapter. Please help me.