RE: FN-FORUM: SQL Server Query Problem

date posted 15th January 2004 13:59

Yep, should do;

I use this code for returning a dataset.

Public Function RunExecuteDataSet(ByVal llngRegID As Long, ByVal
lstrLicenceKey As String, ByVal lstrSP As String, ByVal lclnSPParams As
Collection, ByRef lstrMsg As String, ByRef lclsData As DataSet) As
Boolean

Dim lsqlCon As SqlClient.SqlConnection
Dim lsqlCommand As SqlClient.SqlCommand
Dim lxmlReader As XmlReader

If fblnConnect(SERVER_Server, SERVER_Database, SERVER_Username,
SERVER_Password, lsqlCon, lstrMsg) =3D False Then
'failed to connect
lstrMsg =3D ""
Return False
End If

'connected,
lsqlCommand =3D New SqlClient.SqlCommand(lstrSP, lsqlCon)

Dim lclsParam As SqlParameter
Dim lclsSPParam As SPParam

Dim lclsDA As New SqlDataAdapter()

'see if there is an active transaction;

With lsqlCommand
.Parameters.Clear()

For Each lclsSPParam In lclnSPParams
.Parameters.Add(lclsSPParam.Parameter)
Next

[EMAIL REMOVED] llngRegID)
If lstrLicenceKey Is Nothing Then lstrLicenceKey =3D ""
[EMAIL REMOVED] lstrLicenceKey)


.CommandType =3D CommandType.StoredProcedure
.CommandTimeout =3D SERVER_QueryTimeOut

Try
lxmlReader =3D .ExecuteXmlReader()
Catch lclsException As Exception When Err.Number 0
lstrMsg =3D Err.Description
Return False
End Try


End With

'load this xml into the xml reader
lclsData.ReadXml(lxmlReader)

'close connection?
lsqlCommand.Connection.Close()


Return True

End Function


There's a connection function in there which simply connects using the
appropriate constant values and then creates a command object. I never
directly query the database - always use a stored procedure. Much
quicker, safer and secure.



Andy
IWD2 LLP
[EMAIL REMOVED]
=20
-----Original Message-----
From: John Kyle [EMAIL REMOVED]
Sent: 15 January 2004 14:20
To: Andy Macnaughton-Jones
Subject: Re: FN-FORUM: SQL Server Query Problem


I am using the default connection timeout (which I am guessing is 30
seconds) and I am using the executeReader command in c#. If I change the
timeout to 120 seconds then will this sort this out???

Best Regards

John Kyle

----- Original Message -----
From: "John Kyle" [EMAIL REMOVED]
To: [EMAIL REMOVED]
Sent: Thursday, January 15, 2004 2:04 PM
Subject: FN-FORUM: SQL Server Query Problem


>
> Hello
>
> I am running a heavy going text search sql server query; I have=20
> already
full
> text indexed the tables in question and the query takes about 36=20
> seconds
to
> run completely in SQL query analyzer. However, asp.net this causes
problems
> and spits out the following error:
>
> System.Data.SqlClient.SqlException: Timeout expired. The timeout=20
> period elapsed prior to completion of the operation or the server is=20
> not
responding
>
> Has anybody have any recommendations on how to resolve this issue?
>
> Best Regards
>
> John Kyle
>
>
> --
> ** Get all the Freelance Work you Can Handle * The Web Design Business

> Kit will show you proven tactics and strategies for marketing your=20
> business, winning bids, managing projects and pricing your work. Free=20
> Shipping Worldwide.
> Read more & get free chapters at:
http://www.sitepoint.com/launch/b7c91e/3/4
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>=20
--
** Get all the Freelance Work you Can Handle * The Web Design Business
Kit will show you proven tactics and strategies for marketing your
business, winning bids, managing projects and pricing your work. Free
Shipping Worldwide.
Read more & get free chapters at:
http://www.sitepoint.com/launch/b7c91e/3/4

To advertise here: http://www.freelancers.net/advertising.html