SQL Connection timeout while trying to fill a dataset

  • Thread starter Thread starter Robert Brown
  • Start date Start date
R

Robert Brown

Hi All..

I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.

I have seen a lot of posts mentioning to set the commandtimeout
property, but how do you do that on an SQLAdapter? I setting a
connection timeout on the SQL Connector, but that didn't help.

Here is my code.. Can anyone help???? BTW: I am returning about 1
million records (hope the dataset can handle it...

Dim SQLLink As String =
"server=(local);Trusted_Connection=Yes;Database=FTO;Connection
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(SQLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems.Ordernum = tbl_OrderLog.FOrdernum "
sSql += "where tbl_OrderLog.StartTD <= '" & DateRange1 & "' order by
tbl_orderlog.Ordernum"

daSQL = New SqlDataAdapter(sSql, SQLLink)
daSQL.Fill(dsOrderItems, "t1")


Thanks,
Robert
 
Robert

You can explicitly create and assign the SelectCommand to the DataAdapter
rather than using SQL in the constructor, or this should work...

daSQL = New SqlDataAdapter(sSql, SQLLink)
daSQL.SelectCommand.CommandTimeout = 120
daSQL.Fill(dsOrderItems, "t1")

though I haven't tested it

Regards

Paul
 
Hi Robert,
I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.
Click to expand...

\\\watch typos not checked
dim cmd as new sqlcommand(sSql, SQLLink)
cmd.CommandTimeout = 60
daSQL = New SqlDataAdapter(cmd)
daSQL.Fill(dsOrderItems, "t1")
Click to expand...
///
I hope this helps,

Cor
 
Hi All..

I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.

I have seen a lot of posts mentioning to set the commandtimeout
property, but how do you do that on an SQLAdapter? I setting a
connection timeout on the SQL Connector, but that didn't help.

Here is my code.. Can anyone help???? BTW: I am returning about 1
million records (hope the dataset can handle it...

Dim SQLLink As String =
"server=(local);Trusted_Connection=Yes;Database=FTO;Connection
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(SQLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems.Ordernum = tbl_OrderLog.FOrdernum "
sSql += "where tbl_OrderLog.StartTD <= '" & DateRange1 & "' order by
tbl_orderlog.Ordernum"

daSQL = New SqlDataAdapter(sSql, SQLLink)
daSQL.Fill(dsOrderItems, "t1")


Thanks,
Robert
Click to expand...

Hey there Robert

Dare I ask why you are retrieving so many records? There is probably
another way to acheive your goal

Sam
 
Thanks Paul.. It worked perfectly..

Thanks to the others that replied as well..

Robert
 
Hi Samuel.

I wish there is a better way. What I am doing is archiving at the end
of 3 months records from one database to an identical database, but
keeping what is in the second database as ther archival records. This
is all part of a VB.NET program written for a client.

If someone can suggest an easier way, then I would love to hear about
it..

Robert
 
Hi Samuel.

I wish there is a better way. What I am doing is archiving at the end
of 3 months records from one database to an identical database, but
keeping what is in the second database as ther archival records. This
is all part of a VB.NET program written for a client.

If someone can suggest an easier way, then I would love to hear about
it..

Robert
Click to expand...

If its just a data copy process in SQL server, then I think there is
no need to use VB to do anything. I'm no DBA so you might want to post
in here:
comp.databases.ms-sqlserver

Good luck

Sam
 
Back
Top