How do I lengthen the time out for SQL queries?

I'd like to make the connection time unlimited for a certain sql query to SQL Server from ASP.NET, how do I do this? I already tried setting "Connect Timeout=0" in the sql connection string, this doesn't work...

Please help.Have you tried setting the CommandTimeout property for the SqlCommand object?

SqlCommand.CommandTimeout = iSecondsForTimeout

|||Thanks, that was it.sql

How do I implement paging please? - DataReader Object

Hi, I am a beginner in ASP.NET 2.0

I am creating a simple blog website.

I am using a DataReader object to collect all of the entries in the database (in one full sweep) and then display them by adding rows to a table and populating them.

I would like to enable paging so that only 5 blog entries at a time are displayed. The user should be able to page backwards and forwards 5 at a time.

Please can somebody give me some ideas of how I should do this?

Should I read all of the data into some kind of list so that all of the data is then readily available? An arraylist or something?

I realise that the DataReader object reads in one continuous stream so I couldn't expect it to read backwards and forwards from the database.

I include a portion of my code below which reads the database.

Many thanks.

Dim objCommand As New SqlCommand
objCommand.Connection = objConnection
objConnection.Open()
objCommand.CommandText = "SELECT BlogID, Date, Blog FROM BlogSite2_Blogs ORDER BY Date DESC"

Dim objReader As SqlDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim dateDateTime As Date
Dim strBlogEntry As String
Dim intBlogID As Integer

Do While objReader.Read()
intBlogID = objReader.GetValue(0)
dateDateTime = objReader.GetDateTime(1)
strBlogEntry = objReader.GetString(2)

CreateBlogRow(intBlogID, dateDateTime, strBlogEntry)
Loop

objReader.Close()

check this link

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

|||

Hi

I would do it in Database with stored procedure.

In the SP described bellow you Just need to pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the resultset.

CREATE PROCEDURE ReturnPage(@.Select varchar(1000), @.OrderByvarchar(1000), @.StartRowint, @.EndRowint)ASBEGIN declare @.ColListvarchar(2000);declare @.Where varchar(2000);declare @.iint;declare @.i2int;declare @.tmpvarchar(1000);declare @.decvarchar(1000);declare @.fvarchar(100);declare @.dvarchar(100);declare @.Symbolchar(2);declare @.SQLvarchar(5000);declare @.Sortvarchar(1000);set @.Sort = @.OrderBy +', 'set @.dec =''set @.Where =''set @.SQL =''set @.i = charindex(',' , @.Sort)while @.i != 0begin set @.tmp =left(@.Sort,@.i-1)set @.i2 = charindex(' ', @.tmp)set @.f =ltrim(rtrim(left(@.tmp,@.i2-1)))set @.d =ltrim(rtrim(substring(@.tmp,@.i2+1,100)))set @.Sort =rtrim(ltrim(substring(@.Sort,@.i+1,100)))set @.i = charindex(',', @.Sort)set @.symbol =casewhen @.d ='ASC'then'>'else'<'end +casewhen @.i=0then'='else''end set @.dec = @.dec +'declare @.' + @.f +' sql_variant; 'set @.ColList =isnull(replace(replace(@.colList,'>','='),'<','=') +' and ','') + @.f + @.Symbol +' @.' + @.fset @.Where = @.Where +' OR (' + @.ColList +') 'set @.SQL = @.SQL +', @.' + @.f +'= ' + @.fend set @.SQL = @.dec +' ' +'SET ROWCOUNT ' +convert(varchar(10), @.StartRow) +'; ' +'SELECT ' +substring(@.SQL,3,7000) +' from (' + @.Select +') a ORDER BY ' + @.OrderBy +'; ' +'SET ROWCOUNT ' +convert(varchar(10), 1 + @.EndRow - @.StartRow) +'; ' +'select * from (' + @.Select +') a WHERE ' +substring(@.Where,4,7000) +' ORDER BY ' + @.OrderBy +'; SET ROWCOUNT 0;'exec(@.SQL)END

Pls refer toEfficient and DYNAMIC Server-Side Paging with T-SQLfor details.

|||

Thank you very much indeed for this Young Fang. I don't understand it all but I shall have a good read and no doubt, I shall learn a lot of new things :)

How do I get the value of a column in the last row of my table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Hard to get the last row in a relational database. Last means many things: last inserted, last in clustered index, last in another sort, last in a particular index, last in the data page.

Does your table have an Identity column, timestamp, CreatedDate that defaults to getDate()? You need something.

|||

In relational databases the data is by definition unordered, therefore the last data row entered is functionally the same as the first row entered.

That being said, if you have an inserted date/time column you could select from the table where the column equals the max(datetime) value in the table.

How do I get the value of a column in the last row of a table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Define last row?

maybe this will do what you want but it is kind of dangerous because you might pick up the inserted row from another connection

Select Max(CallID) from Calls

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||i do want to pick up the last inserted row from another connection|||

There is no "last" row because SQL Server stores data as it comes in within its own storage schema. There is only a last row if you do a order of the query executed. There could be also a last *physical* row if the table includes a clustered index, which is physically ordered.

But if you want to have the last row in a resultsset, you have to order it backwards and get the TOP 1

e.g. SELECT TOP 1 SomeColumn From SomeTable Order by SomeOtherorthesamecolumn DESC

BTW, this is a public newsgroups, as long as you have MSN Alerts activated you will get a notice everytime a new answer arrives. Private communication should only be done if the thread is extended immensly due to details asking and answering back and forth, but also then the answer and the solution should be posted back here, to help other which might be in the same situation with a similar question.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );


int valueinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql

How do I get rid of this problem

Hi, I did a web page for the intranet where I work using ASP and a database in SQL Server, the connection line is the following:
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=sqloledb;Server=zapopan02;database=data;U ser Id=sa"
The problem comes the first time I try to establish the connection after 5 min. (aprox) of inactivity.
I get the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Timeout expired
Then, I refresh the page and it works fine (and keeps working until I stop using the page for a while)
Could someone pleaseee tell me how to get rid of this problem?
Thank in advance
-RichardGenerally some sort of problem finding the DSN. Could be lost in the global.asa file, or perhaps this is not a SYSTEM DSN.

May refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/4/70.asp&NoWebContent=1) about connection pooling FAQ.|||For those who might have the same problem that I did, here is the answer to my own question:
There is a parameter named Connect Timeout wich is 15 seconds by default, that means that if after 15 sec the connection has not been established, oledb returns an error.
So, I changed the connection line to the following:
oConn.Open " Provider=sqloledb;Server=zapopan02;database=data;U
ser Id=sa;Connection Timeout=30"
And it worked, but anyway, I dont feel so satisfied because the problem is not 100% solved, I dont get the error anymore and the page opens but its still slow the first time.
Thanks anyway
- Richard

How do I get a procedure OUTPUT-parameter...

In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT...
however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...

this is the sp:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier OUTPUT,
@.strHeading nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400)
AS
SET @.uidArticleId = newid()
INSERT INTO tblArticles
VALUES(@.uidArticleId ,@.strHeading,@.strAbstract,@.strText,@.dtDate,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAuthorEmail,@.strKeywords)

my asp code is something like this:

...
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strHeading", SqlDbType.NVarChar, 300));
sqlcmdInsertNewsArticle.Parameters["@.strHeading"].Value = strHeading.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strAbstract", SqlDbType.NVarChar, 600));
sqlcmdInsertNewsArticle.Parameters["@.strAbstract"].Value = strAbstract.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.strText", SqlDbType.NVarChar, 4000));
sqlcmdInsertNewsArticle.Parameters["@.strText"].Value = strText.Text;

...

sqlcmdInsertNewsArticle.Connection.Open();
sqlcmdInsertNewsArticle.ExecuteNonQuery();
sqlcmdInsertNewsArticle.Connection.Close();

How do I do if I want to catch the OUTPUT-parameter (@.uidArticleId)?

anyone?Start by defining another parameter in the Parameters collection:

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@.uidArticleId", SqlDbType.UniqueIdentifier));
sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Direction = ParameterDirection.Output;

Then, after you call ExecuteNonQuery, grab the value of that parameter:

Dim id as GUID = sqlcmdInsertNewsArticle.Parameters["@.uidArticleId"].Value;

I didn't test this code so you may need to tweak it, but that's the idea.

Don|||thanks... that would probably work if I just could get the %#&¤ connection to work... :)

(see: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=553656 )

How do I export a report using c#?...

Hi there.
Can anyone tell me how to export a report to PDF using only c#? I have seen lots of examples of how to do it using asp.net but I am only interested in using c#.
Thanks in advance to anyone who can help me out!Hi
Try this code

CrystalReport1 cr = new CrystalReport1();
ExportOptions exportOpts = new ExportOptions();

DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions();

// Set the export format.
exportOpts.ExportFormatType = ExportFormatType.WordForWindows;
exportOpts.ExportDestinationType =ExportDestinationType.DiskFile;

// Set the disk file options.
diskOpts.DiskFileName = "C:\\Documents and Settings\\ceeb\\Desktop\\111.doc";
exportOpts.ExportDestinationOptions = diskOpts;
cr.Export(exportOpts);

All the best
:thumb:|||Thanks for the help.

In the end i did it like this:

public void exportReport(String reportName, String exportName)
{
try
{
//Create a ReportDocument and load in the location of the report
ReportDocument report = new ReportDocument();

report.Load(reportName);

//Create a DiskFileDestinationOptions object so you can set where the
//pdf is written to
DiskFileDestinationOptions diskFileDestinationOptions = new DiskFileDestinationOptions();

//append a filename to the export path and set this file as the filename property for
//the DestinationOptions class
diskFileDestinationOptions.DiskFileName = exportName;

//set the required report ExportOptions properties
ExportOptions exportOptions = report.ExportOptions;
exportOptions.DestinationOptions = diskFileDestinationOptions;
exportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
exportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;

//Once the export options have been set for the report, the report can be exported. The Export command
//does not take any arguments

// Export the report
report.Export();
Console.WriteLine(reportName + " exported successfully as " + exportName);
}
catch (Exception e)
{
Console.WriteLine(e.Message.ToString());
}

}|||I am trying to do the same thing, the only issue is , I either want to pass a data set (only a few records , not the whole table) or pass a parameter to the crystal report (like employee id) so I can filter what the report/pdf file end up displaying.

I posted the code in asp.net - scroll to crystal reports threads.

any suggestions??

bev