Is there a server variable which will tell me how long the server took in returning a particular recordset. I've looked through MSDN SQL 2000 articles and could'nt / did'nt see anything. Or perhaps there is another way? Thanks!
I have a simple table (SQL Server 2005) which stores employees clock-ins and clock-outs throughout the day:
+------------------------------------------+ ¦ Employee ¦ PunchDateTime ¦ ActionType ¦ ¦----------+------------------+------------¦ ¦ John ¦ 2014/03/26 08:00 ¦ IN ¦ ¦ Mark ¦ 2014/03/26 08:12 ¦ IN ¦ ¦ John ¦ 2014/03/26 08:50 ¦ OUT ¦ ¦ John ¦ 2014/03/26 09:29 ¦ IN ¦ ¦ Mark ¦ 2014/03/26 10:35 ¦ OUT ¦ ¦ John ¦ 2014/03/26 10:55 ¦ OUT ¦ ¦ Mark ¦ 2014/03/26 11:42 ¦ IN ¦ ¦ John ¦ 2014/03/26 12:38 ¦ IN ¦ ¦ John ¦ 2014/03/26 16:21 ¦ OUT ¦ ¦ Mark ¦ 2014/03/26 16:49 ¦ OUT ¦ +------------------------------------------+
I want build a query that calculates time spent in and out. The end result should look like this:
+-------------------------------------------------------------+ ¦ Employee ¦ PunchDateTime ¦ ActionType ¦ TimeIn ¦ TimeOut ¦ ¦----------+------------------+------------+--------+---------¦ ¦ John ¦ 2014/03/26 08:00 ¦ IN ¦ - ¦ - ¦ ¦ Mark ¦ 2014/03/26 08:12 ¦ IN ¦ - ¦ - ¦ ¦ John ¦ 2014/03/26 08:50 ¦ OUT ¦ 00:40 ¦ - ¦ ¦ John ¦ 2014/03/26 09:29 ¦ IN ¦ - ¦ 00:39 ¦ ¦ Mark ¦ 2014/03/26 10:35 ¦ OUT ¦ 02:23 ¦ - ¦ ¦ John ¦ 2014/03/26 10:55 ¦ OUT ¦ 01:26 ¦ - ¦ ¦ Mark ¦ 2014/03/26 11:42 ¦ IN ¦ - ¦ 01:07 ¦ ¦ John ¦ 2014/03/26 12:05 ¦ IN ¦ - ¦ 01:10 ¦ ¦ John ¦ 2014/03/26 16:21 ¦ OUT ¦ 04:16 ¦ - ¦ ¦ Mark ¦ 2014/03/26 16:49 ¦ OUT ¦ 05:07 ¦ - ¦ +-------------------------------------------------------------+
I need the total time spent IN and total time spent OUT, for each day.
The time spent will be calculated only for valid pairs of INs and OUTs, eliminating those that can't be paired (two or more consecutive INs/OUTs).
The results need to be by user, per one day. If the interval is longer than one day, the total time spent must be calculated for each day.
I need to return a rowset from a stored procedure. The returned rows will have ten columns and need to be bound to a gridview. Here is a code snippet. 'Create a DataAdapter, and then provide the name of the stored procedure.MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", MyConnection) 'Set the command type as StoredProcedure. MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure 'Create and add a parameter to Parameters collection for the stored procedure.MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@condition_cl", _ SqlDbType.VarChar, 100)) 'Assign the search value to the parameter.MyDataAdapter.SelectCommand.Parameters("@condition_cl").Value = sqlwhere & orderby 'ASSIGN THE OUTPUT PARAMETERS. HERE IS WHERE I NEED HELP (?????????????????)
DS = New DataSet() 'Create a new DataSet to hold the records. MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") 'Fill the DataSet with the rows returned. 'Set the data source for the DataGrid as the DataSet that holds the rows.GridView1.DataSource = DS.Tables("TMPTABLE_QUERY").DefaultView GridView1.DataBind() MyDataAdapter.Dispose() 'Dispose of the DataAdapter. MyConnection.Close()
In my little research, I have seen examples of how to return a single value, but not multiple rows. I essentially have two problems. I'm not sure how my output parameters are to be defined and added. Do I need a separate 'Parameters.Add' statement for each column field value returned or can I do a single 'Parameters.Add' statement to define the whole row as an output parameter? Also, upon returning from the call to the SP, will I need a looping mechanism to populate the recordset for each individual record returned, or will the 'MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") suffice, as included in my code above? Thanks in advance.
SELECT * FROM v_reimbursement_report WHERE facility_key = @facility_key
/*IF @facility_key <> null PRINT "0" BEGIN SELECT * FROM v_reimbursement_report WHERE facility_key = @facility_key END*/
When I uncomment the commented lines, I can still get a recordset returned using the query analyzer. And I get the same recordset returned when I use VB ADO and leave the stored proc lines commented out. However, when I call the procedure using the same VB code with the stored proc lines uncommented, I get -1 returned for rs.recordcount:
Private Sub Main_Click() On Error GoTo Err_Main
Dim lsFacility As String Dim lsReportName As String Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Dim gconn As ADODB.Connection Dim param_facility_key As ADODB.Parameter
Dim gConnString As String
gConnString = "Trusted_Connection=Yes;UID=sa;PWD=yoyo;DATABASE=y ada;SERVER=ya;Network=dbnmpntw;Address=ya;DRIVER={ SQL Server};DSN=''" Set gconn = New ADODB.Connection Set rs = New ADODB.Recordset Set cmd = New ADODB.Command gconn.Open gConnString
Dear all, I have a big problem in using SQL Server stored procedures: When I have two select statement in the same procedure, the first one will use for returning specific information for the second one to select appropiate result to the client, but the stored procedure just return the first select statement recordset! What can I do? (I use VB Data Environment to access the stored procedures)
The stored procedure I created returns all records from the table if no parameters are passed, as expected. When I pass in the only parameter, I get 0 records returned when there should be one or more returned. I'm sure it's something simple in my syntax, but I don't see it.
This call returns all records: exec webservices_BENEFICIAL_USES_DM_SELECT
This call returns 0 records, when it should return 1: exec webservices_BENEFICIAL_USES_DM_SELECT @DISPOSAL_AREA_NAME='Cell 8'
Here is the stored procedure: ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT] -- Add the parameters for the stored procedure here @DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here IF @DISPOSAL_AREA_NAME IS NULL BEGIN SELECT* FROMBENEFICIAL_USES_DM END ELSE BEGIN SELECT* FROMBENEFICIAL_USES_DM WHEREDISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME' END
Hi All. My question is this. I have a complex stored procedure in SQLServer which works fine when I run it in Query Analyzer. However, whenI call it within my ASP script, it returns nothing, and sometimes locksup. If I change my script to call other existing stored procedures itworks fine. It's just with this particular stored proc. I have triedvarious DB calls in ASP, such as opening the recordset through an ADOconnection and through the Command object but to no avail. Here is mySQL:SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOALTER PROCEDURE dbo.sp_getProfessionalsByTypeID@typeID intASdeclare @catID intdeclare @userID intdeclare @strUserName varchar(100)declare @strFirstName varchar(100)declare @strLastName varchar(100)declare @strTypeName varchar(100)declare @strCategoryName varchar(100)declare @strPictureURL varchar(100)declare @sql varchar(1000)declare @a varchar(100)declare @b varchar(100)declare @c varchar(100)declare @d varchar(100)beginset @a=''set @b=''set @c=''set @d=''--Create Temp TableCREATE TABLE #QueryResult (nCatID int, nTypeID int, nUserID int,strUserName varchar(100), strFirstName varchar(100), strLastNamevarchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100))--Search QuerybeginINSERT #QueryResultSELECTdbo.tbl_musician_type.nCatID, dbo.tbl_musician_type.nTypeID,dbo.tbl_users.nUserID, dbo.tbl_users.strUserName,dbo.tbl_users.strLastName,dbo.tbl_users.strFirstName,dbo.tbl_musician_type.strTypeName, dbo.tbl_category.strCategoryName,dbo.tbl_professionals.strPictureURLFROMdbo.tbl_musician_type INNER JOINdbo.tbl_category ON dbo.tbl_musician_type.nCatID= dbo.tbl_category.nCategoryID INNER JOINdbo.tbl_profile ONdbo.tbl_musician_type.nTypeID = dbo.tbl_profile.nTypeID INNER JOINdbo.tbl_users ON dbo.tbl_profile.nUserID =dbo.tbl_users.nUserID LEFT OUTER JOINdbo.tbl_professionals ON dbo.tbl_users.nUserID= dbo.tbl_professionals.nUserIDWHEREdbo.tbl_musician_type.nTypeID = @typeIDend--Create Temp TableCREATE TABLE #QueryResult2 (ID int IDENTITY,nCatID int, nTypeID int,nUserID int, strUserName varchar(100), strFirstName varchar(100),strLastName varchar(100), strTypeName varchar(100), strCategoryNamevarchar(100),strPictureURL varchar(100), strArtist varchar(100),strAlbumTitle varchar(100), strRecordLabel varchar(100), strYearvarchar(100))--Now Declare the Cursor for Speakersdeclare cur_musicians CURSOR FOR--Combined Results Groupedselect distinct nCatID, nTypeID, nUserID, strUserName, strLastName,strFirstName, strTypeName, strCategoryName, strPictureURLFrom #QueryResultopen cur_musiciansfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURL--Loop Through Cursorwhile @@FETCH_STATUS = 0beginSELECT TOP 1 @a = strArtist, @b=strAlbumTitle,@c=strRecordLabel, @d=strYearFROM dbo.tbl_profile_discogwhere nTypeID = @typeID AND nCategoryID = @catID AND nUserID =@userIDinsert #QueryResult2select @catID as nCatID, @typeID as nTypeID, @userID as nUserID,@strUserName as strUserName, @strLastName as strLastName, @strFirstNamestrFirstName, @strTypeName as strTypeName, @strCategoryName asstrCategoryName, @strPictureURL as strPictureURL, @a ashighlightArtist, @b as highlightAlbumTitle, @c as highlightRecordLabel,@d as highlightYearfetch next from cur_musicians INTO @catID, @typeID, @userID,@strUserName, @strLastName, @strFirstName, @strTypeName,@strCategoryName, @strPictureURLset @a = ''set @b=''set @c=''set @d=''endselect * from #QueryResult2 TI--Clean Upclose cur_musiciansdeallocate cur_musiciansdrop table #QueryResultdrop table #QueryResult2endGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
Can anybody tell me why a) when running a stored proc from an asp page toreturn a recordset the command succeeds if the sp queries an existing tabledirectly, but b) if the stored proc populates results into a differenttable, temporary table, global temp table, or table variable, then queriesone of these, the asp page reports that the recordset object is closed. Ifusing a table, I have set grant, select, update, delete permissions for theasp page user account, so it doesn't appear to be a permissioning issue. Ifrun in Query Analyser the sp runs fine of course.Abridged asp code is as follows:StoredProc = Request.querystring("SP")oConn.ConnectionString = "Provider=SQLOLEDB etc"oConn.Openset oCmd = Server.CreateObject("ADODB.Command")oCmd.ActiveConnection = oConnoCmd.CommandText = StoredProcoCmd.CommandType = adCmdStoredProcoCmd.Parameters.Refresh'code here that populates the parameters of the oCmd object correctlySet oRs = Server.CreateObject("ADODB.Recordset")With oRS.CursorLocation = adUseClient.CursorType = adOpenStatic.LockType = adLockBatchOptimistic'execute the SP returning the result into a recordset.Open oCmdEnd With' Save data into IIS response objectResponse.ContentType = "text/xml"oRs.Save Response, adPersistXML'the line above fails with stored procs from example B below, reporting "notallowed when object is closed", but works with example ASP Example A - this one works fineCreate Proc spTestA ASSELECT ID FROM FileListGOSP Example B - this one doesn't work from ASP but runs fine in QACreate Proc spTestB ASDECLARE @Results Table (ID TinyInt)INSERT INTO @Results SELECT ID FROM FileListSELECT ID FROM @ResultsGOI can see the SP executing using profiler when the asp page is called forboth sp's above, so it doesn't appear to be a problem with the execution.It's something to do with returning the result set from the table variable.Thanks,Robin Hammond
I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.Here is my code:
<% ...... Set cmm = Server.CreateObject("ADODB.Command") Set cmm.ActiveConnection = Connect cmm.CommandType = adCmdStoredProc cmm.CommandText = "dbo.client_updates_proc" cmm.Parameters.Refresh cmm.Parameters(1) = client_id Set logRS = cmm.Execute() if not logRS.EOF then ...... %>
My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.
Hi, not too swift with anything other than simple SQL statements, soI'm looking for some help.Using SQL Server 2000 with this stored proc:(@varCust varchar(50))ASSET NOCOUNT ONSELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,d.WIdoc,d.Start, d.StartUser, d.Finish, d.FinishUserFROM tblWorkOrder w, tblDocs dWHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder ANDd.Customer = @varCustGOI'm trying to get a complete dataset so I can simply apply it as thedatasource to a datagrid in asp.net. I need to include a 'TimeSpan'column that is the difference between d.Start and d.Finish. I alsoneed it to present in hh:mm:ss format in the datagrid column. (A) isit possible to do this within the stored proc, and (B) how would "I"do that?Thanks!Kathy
create table a (id int, name varchar(10)); create table b(id int, sal int); insert into a values(1,'John'),(1,'ken'),(2,'paul'); insert into b values(1,400),(1,500);
select * from a cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal 1John500 1ken500 2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 2008-01-01 00:00:00.000 2008-01-01 00:01:00.000 2008-01-01 00:01:00.000 2008-01-01 00:01:00.000 2008-01-01 00:14:00.000 EVERY THING ABOVE HERE IS IN GROUP 1 2008-01-01 00:35:00.000 EVERY THING ABOVE HERE IS IN GROUP 2 2008-01-01 01:01:00.000 2008-01-01 01:03:00.000 2008-01-01 01:03:00.000 2008-01-01 01:04:00.000 EVERY THING ABOVE HERE IS IN GROUP 3 2008-01-01 01:29:00.000 EVERY THING ABOVE HERE IS IN GROUP 4 2008-01-01 01:41:00.000 EVERY THING ABOVE HERE IS IN GROUP 5 2008-01-01 02:25:00.000 2008-01-01 02:28:00.000 2008-01-01 02:31:00.000 2008-01-01 02:33:00.000 EVERY THING ABOVE HERE IS IN GROUP 6 Hope this is enough Info
I have a program that is automatically ran through a job. The program gets the most recent files that have been uploaded to a server. I would like to be able to query the database to see when the last time this job was ran successfully and set this date as the date to look for files newer than the last successful run date.
Could someone point me in the right direction to what tables this data is stored in on a 2005 SQL Server Database?
Hi All, I am having an issue with a JDBC based reporting tool when connected to my SQL2005 db. Basically I am getting erradic results. Let me explain the two cases. 1) The full result set gets returned. This is good. In my trace file, I can see the cursor getting created and chunks of 50 records returning to the program. The last chunk has 10 records in it (a total of 960 records). 2) Partial results returned. 10 records, which is strangely enough the last chunk size of the full result set. No errors are flagged. The vendor of the product ensures me that they cannot replicate the problem (even given my db), and I am having trouble identifying places/settings in SQL2005 that might help. If it makes a difference, the dataset is a query with many LEFT OUTER JOINS, but the problem seems to stem from the inclusion of a subquery which uses a function in its from clause. EG.
Code Snippet SELECT blah1, blah2 FROM table1 LEFT OUTER JOIN table2 ON .... ... WHERE EXISTS (SELECT id FROM func1(2,2) WHERE id = blah1)
If I remove the EXISTS clause, things seem to work well.
My @@version = Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86) Feb 13 2007 23:02:48 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Any help with this would be GREATLY appreciated. Thanks, Steele.
Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong (@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime) RETURNS VarChar(50) AS BEGIN DECLARE @TotalTime Numeric DECLARE @Minutes Numeric DECLARE @Hours Numeric DECLARE @Days Numeric DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL BEGIN SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate) SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24) SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60) END ELSE BEGIN SET @Days = DATEDIFF(Day, @StartTime, @EndTime) SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24) SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60) END
IF(@Days <0) BEGIN SET @Days = @Days - @Days - @Days END
IF (@Hours < 0) BEGIN SET @Hours = @Hours - @Hours - @Hours END
IF (@Minutes <0) BEGIN SET @Minutes = @Minutes - @Minutes - @Minutes END
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
Hi, I am trying to cycle through a table and trigger an event based on some critera. I am not sure how to do it. I am a classic VBA guy, so I might be way off: Dim myConnection As SqlConnection Dim myCommand As SqlCommand myConnection = New SqlConnection("MY SQL DATA SOURCE") myConnection.Open()
myCommand = New SqlCommand("SELECT * FROM history", myConnection) Dim dr = myCommand.ExecuteReader() Dim i As Integer = 1
While dr.read() i = i + 1 ' HOW DO I CYCLE THROUGH THE ROWS AND ASK IF A FIELD EQUALS A VALUE ' field name = "Tail"
hi I have 3 tables:Article,Source and File. Each article can have multiple filenames. The fields of table Article are:ArticleID,SourceID,ArticleDate,ArticleCategory The fields of table Source are:SourceID,SourceName the fields of Table File:ID,ArticleID,Filename Select*from Article inner join Source on Article.SourceID=source.SourceID order by ArticleDate I obtain a number of recordsets from the above query. Then for each recordset(Rs1),Let's say for the first recordset Rs1.MoveFirst I want to apply this query: Rs2.Open"Select SourceName,ArticleDate,File.Filename from [RS1] inner Join File on Article.ArticleID=File.ArticleID I want from the above query to have the Filenames corresponding to each Article because in my VB form I have 2 command buttons:one gives me the article's definition(Date,Source) and the other gives me the Filename of the current recordset(Article) The above SQL syntax is it correct?
SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName FROM Menu ORDER BY MenuDate DESC
This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).
Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!
I am using classic ASP. Records are grouped together by a GroupUnique number. Some groups are small with about 10 records, othere are larger at about 160.
For each record, I have about 50 columns of data that I need to display on a webpage. Because the 50 columns don't easily fit on the one page, I create two tables, each displaying 26 columns, the first columnn being an ID column. Due to the size of groups, sometimes the tables are very large - and when they get too big it overloads the server.
I think the main problem is the two tables. I use two recordsets (one of them is shown below - although instead of a SELECT * I do in fact name the columns needed for each table). I have to use two because the Recordsets don't like me using the ID column again - once it is used it is gone.
Is there a better way to store all of this information so that I can just use the one recordset? Possibly in an array? Is there a more efficient way of getting the data?
<% Dim Recordset4__MMColParam1 Recordset4__MMColParam1 = "1" If (Scramble.Fields.Item("GU").Value <> "") Then Recordset4__MMColParam1 = Scramble.Fields.Item("GU").Value End If %>
I am having some problems. I'm pretty new to sql and really dont know how to achieve more than the basic selects etc.,
My problem is that I have a recordset on one page http://www.photoghetto.com/photo-images/animals.asp that returns the results of all the images in one category. In this case it's animals and wildlife.
The user can click on any image and go to a page that shows a larger detail version of the image. http://www.photoghetto.com/photo-images/animals-photo-detail.asp
What I do is post the ProductID number to this page so that the selected thumb is shown. So for exmaple for the image of the wild cat it is http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6
My problem is that on the animals-photo-detail.asp the user has to be able to "scroll" through all the images from the category.
I.e should be able to hit the previous image button and see the stallion image, or the next button to see the butterfly etc., and thus scroll through all the images on this age if he/ she wishes to.
I understand the principles of having a results page and then being able to click on one of the results and getting a detail page. Such as I have it here. With the http://www.photoghetto.com/photo-images/animals.asp as a results page listing all the results of the category, and then when the user clicks on one of the results, goes to a detail page, for example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6.
My problem is that what I need is the recordset from the listing page to function on the detail page so that the user can scroll through the results in the same order that they were on the results page.
I have searched now for a couple of days online and every tutorial I find shows the same structure. Results Page > Detail Page.
The sql i am using on the detail page is simply,
<% Dim photos_rs__MMColParam photos_rs__MMColParam = "1" If (Request.QueryString("ProductID") <> "") Then photos_rs__MMColParam = Request.QueryString("ProductID") End If %>
So I understand why it will only display the one result since thats the detail page.
Is it possible to be able to scroll through the results using the previous and next buttons as I have setup in the display on the http://www.photoghetto.com/photo-images/animals-photo-detail.asp page. For example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=6.
Since the resutls are gathered from across the database its not possible to have a href tage that does a <<< http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6 -1) or a >>> http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6+1).
I guess it has to be something with a recordset index but does anyone know how to do it? And does anyone have the ability to help me do it?
My boss is kicking my butt now to get this thing online at some point today, and I'm turning to you guys for help if possible.
I'm sorry if this is a stupid question. I've really ran out of ideas.
This asp code displayes records in a combo box:<%openDB()call updateDB("usp_retrieveOptions",rs)if not rs.eof then%><tr><td width="66">Options</td><td width="137"><select name="select1" class="TextField1"><%i = 0do while not rs.eofif rs(0) <> Arr(i) thenresponse.write "<option value=" & rs(0) & ">" & rs(1)i = i + 1end ifrs.movenextloop%></select></td></tr><tr><td colspan="2" width="206"><center><table width="71" border="0" cellspacing="3" cellpadding="0"height="33"><tr><td width="9" height="30"><input type="submit" name="Assign" value="Assign"></td></tr></table></center></td></tr><%end ifcloseRS()closeDB()%>The call updateDB("usp_retrieveOptions",rs) invokessub updateDB(SQL,rs)set rs = objConn.Execute(SQL)end suband my usp_retrieveOptions stored procedure:create procedure usp_retrieveOptionsAS SET NOCOUNT ONSELECT OptionID, Description FROM OptionsReturnGOnow in my asp code when I try response.write rs.RecordCount I am getting-1 all the time. How do I solve the problem. Your help is kindlyappreciated.Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
Im doing a select that should retrieve a name from one table and display thenumber of correct bets done in the betDB (using the gameDB that has info onhow a game ended)I want the "MyVAR" value to be used in the inner select statement withouttoo much hassle. As you can see im trying to get the "MyVAR" to insert inthe bottom line of the code.Whats the quick fix to this one..?Thanks in advance :-)---------- code begin ----------select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)from GamesDBinner join GameBetDBon GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =GamesDB.awaygoalinner join memberDBon memberDB.memberID = GameBetDB.memberIDwhere GamesDB.gameID=GameBetDB.gameIDand GameBetDB.memberID= MyVAR ) as wins from memberDB---------- code end ----------
I am connecting to the database as following: set con = server.createobject("adodb.connection") con.open "connectionstring" set rs = con.execute("select * from tablename")
I am able to display the records but if I want to give adopenstatic to the above connection, how can I do so?