ADO Recordset Filter Property Too Slow
Mar 13, 2008
Hello all,
I have seen many references to this problem on the internet, but no solutions...I am hoping someobdy can help me.
I am trying to select 1 record from a huge SQL SERVER table (approximately 1 million records) using VB6 and ADO. I cannot use the Recordset.Find property because I am searching on more than one field, and the Recordset.Seek property is not supported.
So I am left with the dreaded Recordset.Filter property, which takes an extremely long time to find the record. I would think it wouldn't be so slow, because the fields I am searching on are the primary key.
Surely it shouldn't take SQL Server several minutes to look up one record by the primary key?
The code is basically this:
CON.Open "Provider=SQLOLEDB.1;Server=TheServer;Database=TheDatabase"
RS.Open TableName, CON, nRSCursorMode, nRSLockMode, adCmdTableDirect
RS.Filter = "Field1='ABC' AND Field2='DEF'" ' < several minutes go by
Where the primary key for the table consists of "FIeld1" and "Field2".
I have tried every combination of Cursor Mode and Lock Mode, but none are fast.
Ideally the SQLOLEDB provider should implement the Recordset's .Seek method, but that doesn't seem to be the case. If I can't look up a value using an index, how else would I do this?
thanks
View 6 Replies
ADVERTISEMENT
Dec 29, 2007
Hi everyone,
I'm using a stored procedure in SQL Server to generate multiple recordsets. The recordset are however generated conditionally. create procedure GetData
(
@a bit
@b bit
)
Select * from Customers
IF(@a=1)
Select * from Products
IF(@b=1)
Select * from Details ------------Issue:The adapter returns the table named as Table, Table1(optional) and Table2(optional). It might be that Table1 is products or Table1 is Details.How can I map my tablename property in this scenario where the sequence of tables is not known.
View 3 Replies
View Related
Jul 23, 2005
Dear All,(No aswers on access newsgroups)Access2000.adp connected to SQL-server 2000MainForm unbound: seachform on companies meeting a complex combination ofcriteria.SubFrom and ListBox to display the searchresult ( 2 outputs to test what'sbest).Searchresult is stored in a creatable ADO-recordset (see code) and then1. Converted to string (GetString) to be set as valuelist for a listbox.2. bound to the subform.The Listbox shows the resultdata correctly but is limited in length.The subform shows the records but all fields are empty or displays #errorHow should I bound the controls correctly to display the fieldvalues?If I let them unbound the show up empty.If I set them to the recordsets' fieldnames it show #error.On my Notebook using MSDE there is no errormessageOn my Desktop i get error 7965 not a valid recordset property.CAN'T FIND WHAT'S WRONG! Cursortype ??FilipCODE'Store result in creatable ADO-recordset: Add fieldsWith ResultRSWith .Fields.Append "CompID", adInteger.Append "CompName", adVarChar, 255.Append "BasicPrice", adVarChar, 255End With.CursorLocation = adUseClient.CursorType = adOpenStatic.OpenEnd With[color=blue][color=green]>> Code for Search[/color][/color]'Set Rowsource Listbox and subformIf ResultRS.RecordCount > 0 ThenstrSearchResult = ResultRS.GetString(adClipString, , ";", ";")Me.Formulier1.Form.Visible = TrueSet Me.Formulier1.Form.Recordset = ResultRSMe.CmbSearchResult.Visible = TrueMe.CmbSearchResult.RowSource = strSearchResultEnd IfSearchEnd:'Clear memoryIf rs.State = adStateOpen Thenrs.CloseSet rs = NothingEnd IfIf ResultRS.State = adStateOpen ThenResultRS.CloseSet ResultRS = NothingEnd If
View 1 Replies
View Related
Jul 23, 2005
Hi all...I've been stumped by this for days. Bit of ASP code: (IIS)Set LocalConn = CreateObject("ADODB.Connection")LocalConn.CursorLocation = adUseClientLocalConn.CommandTimeout = 0LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is,see below).LocalConn.Errors.ClearCountDuplicatesSQL = "select * from dbo.tbl1 WHERE DESC LIKE 'aaa'"oRss.OpenCountDuplicatesSQL,LocalConn,adOpenDynamic,adLockR eadOnly,acCmdTextthis hangs and then results with "timeout expired".the same SQL works flawlessly if tested on query analyzer, with thesame connection Login.And I found, that If I remove the WHERE clause, it works withoutproblems..., both in ASP and Query analyzer.I have another connection running in parallel to this one, Serverside, but to a different database on the same server. If I disable it,the above code works. But, I need it open all the time...if its the two connections conflicting, how come the code above workswith a simple Select but not with a WHERE criteria on it, under thesame two connection conditions?~Thanks for insights.
View 9 Replies
View Related
Nov 1, 2006
Hello All, Can someone please help me with this problem. I have created additional profile properties for my users in the aspnet_profile table. One of the properties is named 'Division'. The division property is how I segregate users, for example, my company has multiple divisions and, the information that is displayed is dynamic according to which divison the user belongs to. Amost all of my stored proceedures are filtered by checking the value of Profile.Division of the logged in user. This system works great. The problem is, now I am working on user management and I need to create a Gridview that will display all of the users for a particular division but, I have no idea how to filter a query against the aspnet database to get the results I am looking for. Here is a small example:SELECT aspnet_Users.UserName, aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesStringFROM aspnet_Profile INNER JOIN aspnet_Users ON aspnet_Profile.UserId = aspnet_Users.UserId******** Here is the part I need help with *********************WHERE PROFILE.DIVISION = KOr, can it even be done? Can you filter a query on the value of a profile property?Anyone have any ideas?Thanks in adavance for your help
View 2 Replies
View Related
Jun 16, 2006
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
View 5 Replies
View Related
Apr 17, 2008
Hi,
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Thanks for any hints.
View 3 Replies
View Related
May 7, 2008
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
View 10 Replies
View Related
Apr 15, 2008
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
View 5 Replies
View Related
Jan 26, 2006
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
View 14 Replies
View Related
Feb 15, 2007
Hello,
I have a group I'll call G4.
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
=IIF(ReportItems!Textbox66.Hidden = False, True, False)
...the report chokes on it.
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
View 1 Replies
View Related
Jul 10, 2007
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"
If dr(i) = ? Then
MsgBox("ok")
End If
End While
dr.Close()
myConnection.Close()
View 3 Replies
View Related
Jun 22, 2008
Hello, what is the best way to iterate both forward and back through my SQLDataAdapter/SQLDataReader in code?
View 1 Replies
View Related
Jul 24, 2001
In VB to move a recordset, we used .MoveNext. How about in SQL?
How can I compare two fields in SQL?
View 1 Replies
View Related
Mar 12, 2006
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?
View 2 Replies
View Related
Aug 18, 2006
Here's my SQL Statement (I'm using MS SQL 2000):
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!
Mike
View 3 Replies
View Related
Jul 11, 2007
Hi,
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
%>
<%
Dim Recordset4
Dim Recordset4_numRows
Set Recordset4 = Server.CreateObject("ADODB.Recordset")
Recordset4.ActiveConnection = conn
Recordset4.Source = "SELECT * FROM Table1 WHERE GroupUnique = " + Replace(Recordset4__MMColParam1, "'", "''") + ""
Recordset4.CursorType = 0
Recordset4.CursorLocation = 2
Recordset4.LockType = 1
Recordset4.Open()
Recordset4_numRows = 0
%>
<%
Dim Repeat4__numRows
Dim Repeat4__index
Repeat4__numRows = -1
Repeat4__index = 0
Recordset4_numRows = Recordset4_numRows + Repeat4__numRows
%>
View 4 Replies
View Related
Sep 2, 2005
Hi guys,
my first post. So please don't kill me!
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
%>
<%
Dim photos_rs
Dim photos_rs_numRows
Set photos_rs = Server.CreateObject("ADODB.Recordset")
photos_rs.ActiveConnection = MM_photo_STRING
photos_rs.Source = "SELECT * FROM PHOTOCOLLECTIONS WHERE ProductID = " + Replace(photos_rs__MMColParam, "'", "''") + ""
photos_rs.CursorType = 0
photos_rs.CursorLocation = 2
photos_rs.LockType = 3
photos_rs.Open()
photos_rs_numRows = 0
%>
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.
-SOM
View 9 Replies
View Related
Mar 27, 2007
is there any thing like a recordset concept in sql server,
where i could loop through and update each rows
Thanks,
MG
View 5 Replies
View Related
Nov 23, 2005
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 ***
View 4 Replies
View Related
Jul 20, 2005
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 ----------
View 1 Replies
View Related
Aug 25, 2006
Hi allHow do i declare a recordset and fetch records from an sql server?TIAGuy
View 3 Replies
View Related
Jun 22, 2004
Hello,
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?
Thanks in advance,
Uday.
View 1 Replies
View Related
Nov 24, 2001
Hello,
I have some problems to edit a recordset in an ActiveX DTS using Vbscript.
Here is an example of the script :
dim varsql, varset, varconn
set varconn = CreateObject("ADODB.Connection")
set varset = CreateObject("ADODB.Recordset")
varconn.Open = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Enregistrement3;user id = 'sa';password=''"
varsql = "SELECT * "
varsql = varsql & "FROM mytable "
varset.Open varsql, varconn, 3,3
msgbox "How much : " & varset.recordcount
if varset.recordcount >0 then
do while varset.eof
varset.edit
.........................
varset.update
varset.movenext
loop
end if
varset.close
varconn.close
Does someone see what is wrong ?
I allways get -1 for the varset.recordcount ! (I checked there are some records into the table).
I don't think it is a problem of user's right as it works with the same user configuration using a SQL action requery.
If I modify the open statement like this :
varset.Open varsql, varconn, 1
varset.recordcount contains the good number of records but the recordset is read noly and can't be modified...
Any Help will be very wellcome !
TIA
View 1 Replies
View Related
Sep 29, 2000
Is possible use DTS for return Data in recordset in the VB ?
I am asking this , why I have access the database INFORMIX, and several things do not work with Stored Procedure in the Informix
thank you in advance
View 1 Replies
View Related
Aug 30, 1999
What I have to do is loop through a table with about 900 records, do computations on each record and update a database, can someone help me out.
here is the code that I am using right now on another site with coldfusion but i want to convert it all to a stored proc for speed reasons.
<!--- Query the Stores Table --->
<cfquery name="Zip2" datasource="#application.data#" username="#application.username#" password="#application.password#">
exec Stores_GetStoreZipInfo
</cfquery>
<!--- Loop through the STORE table --->
<cfloop query="zip2">
<cfset Lat1 = #zip1.lat#>
<cfset Lg1 = #zip1.long#>
<cfset Lat2 = #zip2.lat#>
<cfset Lg2 = #zip2.long#>
<!--- Do the actual distance calculation between the user's zipcode each store's zipcode --->
<cfset DistLat = 69.1 * (Lat2-Lat1)>
<cfset DistLong = 69.1 * (Lg2-Lg1) * #cos(Lat1 / 57.3)#>
<cfset Dist = ((#abs(DistLat)# * 2) + (#abs(DistLong)# * 2)) * 0.5>
<!--- Update the DISTANCE field on STORE table --->
<cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#">
exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#'
</cfquery>
</cfloop>
View 1 Replies
View Related
Apr 19, 1999
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records.
I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time.
I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410.
The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start.
This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
View 1 Replies
View Related
Aug 2, 2004
hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!
View 3 Replies
View Related
Nov 12, 2004
I have a reference table that looks like this
id | value
==========
1,abc
1,def
1,ghi
2,def
2,jkl
I want these values to go horizontally into another table matched on id, to look like this:
id | value
========
1,abc def ghi
2, def jkl
I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?
View 1 Replies
View Related
Dec 18, 2004
Hello,
I was wondering if there is a way to randomise a recordset once you have performed a query.
eg I want my query return a set of records based upon criteria supplied and then either randomise the order of the recordset or only return 3 random records - which every is simplest to do.
Can this be done in SQL or do I need to handle it outside of my SQL query in my business logic?
Many Thanks
View 1 Replies
View Related
Oct 26, 2005
Newbie to SQL Server here.
I'm opening a Access 2003 form with a ADO recordset with the following code;
Function SetFloaterDataSource()
Dim RecSourceFloater As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
RecSourceFloater = "SELECT tbl_UserVarHSTUFloatStatByEelink.* " _
& "From tbl_UserVarHSTUFloatStatByEelink " _
& "WHERE (((tbl_UserVarHSTUFloatStatByEelink.idCalendar)=" & MyTSCalcIdCal & "));"
'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = RecSourceFloater
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set WeekDay.Form.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Function
When I edit the form I noticed that I could not go back to a recently added record because it was not available, so I concluded that I had to force an update, which I do in the before update event of my form, as follows;
'Update the underlying recordset
Me.Recordset.Update
It works great on my test server here, but when I instal it on the SQL Server 2000 - I get an error 'EOF' or 'BOF' is true......... so it fails
Any thougths?
View 1 Replies
View Related
Mar 10, 2004
Hi ...
This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...
I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.
Any comments are appreciated,
Thanks,
Chris
void CTestApp::TestDatabaseUpdateBatch1a(void)
{
int nDataCount = 0;
long nIndex = 0;
long nIndex2 = 0;
CString csMessage;
CString csErrorMessage;
CString csTemp;
CString csSQL;
BOOL bIsOpen;
BOOL bIsEmpty;
long nCount = 0;
int nTemp = 0;
int nLimit = 0;
int nTempInt = 0;
long nTempLong = 0;
double nTempDouble = 0;
HRESULT hResult;
SYSTEMTIME st;
int i = 0;
string strTemp;
_variant_t sval;
_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
COleSafeArray colesaFieldList;
COleSafeArray colesaDataList;
vector<COleSafeArray> *pvecDataList;
pvecDataList = new vector<COleSafeArray>;
COleDateTime oledtCurrentDate = COleDateTime::GetCurrentTime();
// Convert the OleDateTime to the varient
// COleVariant vCurrentDateTime(oledtCurrentDate);
COleVariant vCurrentDateTime;
CMxTextParse *pMxTextParse = NULL;
CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();
CFrameWnd* pChild = pMainFrame->GetActiveFrame();
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();
pView->WriteLog("Start TestDatabaseUpdateBatch1a.");
pView->WriteLog("Load table using AddNew() and UpdateBatch().");
// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;
try
{
// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));
// Replace Data Source value with your server name.
bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';"
"Initial Catalog='AlphaNumericData';"
"User Id=cmacgowan;Password=cmacgowan");
// Open the ado connection
pConnection->Open(bstrConnect,"","",adConnectUnspecified);
// Create an instance of the database
pRecordset.CreateInstance(__uuidof(Recordset));
// Select the correct sql string. Note that we are creating an
// empty string by doing a select on the primary key. We are only
// doing inserts and we do not want to bring data back from the
// server
csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL";
// csSQL = "SELECT * FROM dbo.DICastRaw1Hr";
pRecordset->PutRefActiveConnection(pConnection);
pRecordset->CursorLocation = adUseClient;
pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);
// Test to see if the recordset is connected
if(pRecordset->GetState() != adStateClosed)
{
// The recordset is connected, we will see if we are
// at the end
if((pRecordset->BOF) && (pRecordset->GetadoEOF()))
{
// The recordset is empty
bIsEmpty = false;
}
if(pRecordset->GetadoEOF())
{
bIsOpen = false;
}
else
{
// disconnect the database
pRecordset->PutRefActiveConnection(NULL);
}
}
// disconnect the database
// pRecordset->PutRefActiveConnection(NULL);
// Disassociate the connection from the recordset.
pRecordset->PutRefActiveConnection(NULL);
// Set the count
nCount = 1;
// now we will scroll through the file
while(nCount > 0)
{
nCount--;
nDataCount = 10;
// test that we got some data
if (nDataCount >= 0)
{
// Start the insert process
// m_pRecordset->AddNew();
COleSafeArray warningList;
//int index, listIndex = -1, bitIndex; // indexing variables
// long lowIndex, highIndex, arrayIndex[2];
VARIANT vFieldList[25];
VARIANT vValueList[25];
int nFieldIndex = 0;
int nValueIndex = 0;
// Setup the fields
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Name");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Section");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Code");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Latitude");
nFieldIndex++;
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Longitude");
nFieldIndex++;
pView->WriteLog("Set data using AddNew() ...");
// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is
// a wrapper for VARIANTs themselves. If you need to create a
// variant, you can say:
COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();
// Convert the OleDateTime to the varient
COleVariant vCurrentDateTime2(oledtCurrentDate2);
//Set the DATE variant data type.
memset(&st, 0, sizeof(SYSTEMTIME));
st.wYear = 2000;
st.wMonth = 1;
st.wDay = 1;
st.wHour = 12;
// vect is a vector of COleSafeArrays containing the records
for(i = 0; i < 10; i++)
{
// Setup the data
nValueIndex = 0;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE");
nValueIndex++;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");
nValueIndex++;
vValueList[nValueIndex].vt = VT_I4;
vValueList[nValueIndex].dblVal = 100 + nFieldIndex;
nValueIndex++;
vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 11.11 + nFieldIndex;
nValueIndex++;
vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 22.22 + nFieldIndex;
nValueIndex++;
// Add the record to the recordset
pRecordset->AddNew(vFieldList, vValueList);
}
pView->WriteLog("Call UpdateBatch().");
// Re-connect.
pRecordset->PutRefActiveConnection(pConnection);
// Send updates.
pRecordset->UpdateBatch(adAffectAll);
// Close the recordset and the connection
pRecordset->Close();
pConnection->Close();
}
}
}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
pView->WriteLog("Error processing TestDatabase().");
}
catch(...)
{
csMessage = "Undefined exception handled. Error message details ";
hResult = GetAdoErrorMessage(m_pConnection,
&csErrorMessage);
csMessage += csErrorMessage;
csMessage += "method: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()";
AfxMessageBox(csMessage);
}
csTemp.Format("Last Row %03d DIcastId = %s ", nIndex, strTemp.c_str());
pView->WriteLog(csTemp);
pView->WriteLog("End TestDatabaseUpdateBatch1.");
}
View 3 Replies
View Related
May 9, 2008
I cant seem to display the Customer_nickname from my Customer_recordset....... And the errors says that " Object is required" can anyone help me on this..?
View 1 Replies
View Related
