SQL Connection timeout while trying to fill a dataset

[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_Connec tion=Yes;Databa se=FTO;Connecti on
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(S QLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems. Ordernum = tbl_OrderLog.FO rdernum "
sSql += "where tbl_OrderLog.St artTD <= '" & DateRange1 & "' order by
tbl_orderlog.Or dernum"

daSQL = New SqlDataAdapter( sSql, SQLLink)
daSQL.Fill(dsOr derItems, "t1")
Thanks,
Robert

[Paul Hatcher]

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.SelectCom mand.CommandTim eout = 120
daSQL.Fill(dsOr derItems, "t1")

though I haven't tested it

Regards

Paul

"Robert Brown" <rb****@edium.c om> wrote in message
news:bd******** *************** **@posting.goog le.com...

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_Connec tion=Yes;Databa se=FTO;Connecti on
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(S QLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems. Ordernum = tbl_OrderLog.FO rdernum "
sSql += "where tbl_OrderLog.St artTD <= '" & DateRange1 & "' order by
tbl_orderlog.Or dernum"

daSQL = New SqlDataAdapter( sSql, SQLLink)
daSQL.Fill(dsOr derItems, "t1")
Thanks,
Robert

[Cor]

Hi Robert,

I am experiencing Timeouts after about 30-40 seconds when trying to
fill a dataset from SQL.
\\\watch typos not checked
dim cmd as new sqlcommand(sSql , SQLLink)
cmd.CommandTime out = 60
daSQL = New SqlDataAdapter( cmd) daSQL.Fill(dsOr derItems, "t1")

///
I hope this helps,

Cor

[Samuel Hon]

rb****@edium.co m (Robert Brown) wrote in message news:<bd******* *************** ***@posting.goo gle.com>...

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_Connec tion=Yes;Databa se=FTO;Connecti on
Timeout=600"

Dim SQLConn As SqlConnection = New SqlConnection(S QLLink)
Dim daSQL As SqlDataAdapter

'Get OrderItem
sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
tbl_OrderItems. Ordernum = tbl_OrderLog.FO rdernum "
sSql += "where tbl_OrderLog.St artTD <= '" & DateRange1 & "' order by
tbl_orderlog.Or dernum"

daSQL = New SqlDataAdapter( sSql, SQLLink)
daSQL.Fill(dsOr derItems, "t1")
Thanks,
Robert

Hey there Robert

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

Sam