Hi
I am using SqlCeResult and i want to give paging on that and read in help that you can do paging on ResultSet so . I need code example so that i can understand how to implement this in resultSet.
Thanks & Regards
Vishal
How can i get the current row index of SqlCeResultSet specially after calling Seek method?
Is there anyway to filter a existing SQLCEReusltSet?
Hi
I have a table with an identity column. This is set to increase by 1 and start on 1, and is the first column of the table.
When I insert a record into the resultset, I get an error stating that it can't be DbNull.
Th record is created from a resultset with the query "Select * from table", so the id should be included
But as I understand it, it should be increasing automatically, right? Becuase that's the behaviour of the normal sql Insert command. I have seen an article that just left the id column in the record alone before inserting it, but it didn't help me.
How can I sidestep this issue?
I am currently working with typed sqlceresultsets. I noticed that each resultset manages its own databaseconnection. I am considering to change this to a situation where I can reuse a databaseconnection on more than one sqlresultset.
Will this result in better performance? for example, when I have 2 updates for wicht I need 2 resultsets, wouldn't it be better to use 1 connection and transaction instead of each resultset creating/disposing its own? I would realy like to hear some oppinions. thanks!
Hi,
I have an updateable sqlceresultset, which i use to bind to a datagrid. The user can modify values in the grid. I wish to update these values in the database only when the user has finished making these changes and clicks "Accept" button. But with the sqlceresultset, it looks like with every modification in the grid, the resultset is updated and change is written to the sdf database file . Please suggest how to work around this.
Thanks
Hi I have written an Windows Mobile Application that insert 1000 records on SQL Compact 3.1 but I am having performance issues. If I use SqlCeResultSet and SqlCeUpdatableRecord in C# tha insert take 7 seconds to complete. But if I write a C++ application that uses OLEDB it takes only 4 seconds.
How can I improve the performance of the C# application? Also I would like to know if there is any possibility to use the same OLEDB objects that I use in C++ in a C# application.
Thanks a lot
I have created my connection correctly
if (_deviceConn.State == ConnectionState.Closed)
_deviceConn.Open();
_deviceCMD = new SqlCeCommand("UPDATE metar SET observation='"+ Value1 +"',lastupdate='"+ Value2 +"',Valid_Time='"+ Value3+"' WHERE aerodrome_oaci_code='"+ FilterValue +"'", _deviceConn);
int RecordsAffected = _deviceCMD.ExecuteNonQuery();
The code executes fine, in fact the recordsaffected returns 1, but Nothing happend on database side. Another thing is, when i run the project on release mode, a copy of the SDF file is there, so, which of the sdf files the project is actually updating?, cause im querying both (the one in my project source path and the one copied to release folder) and both have the same data witout any modification.
My Copy to output directory setting is = Copy if newer.
If i try this with query analizer works just fine.
Any ideas?
Hi,
I've noticed that after the first record from a ResultSet is deleted, HasRows property throws an exception like "The current row was deleted." Method ReadFirst() throws similar exception as well.
More details:
1. Create a ResultSet: ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive)
2. Let's say, there is no records in the resultset. Insert two new ones. Then call: ReadAbsolute(0); Delete(); HasRows; and the exception is thrown.
3. Then you can call ReadFirst() or Read with the same result.
4. Now call ReadAbsolute(0). It returns true. Call HasRows, ReadFirst() or Read() and you will get the exception each time.
I tried to explain this with the fact that Delete() doesn't change the current position of the ResultSet. But I don't see why that would mess the HasRows property or why after ReadAbsolute(0) retuns true, HasRows or ReadFirst() doesn't work.
Could anybody tell me what the problem is?
Thank you.
Hi,
I am trying to develope a Pocket PC application using MS Visual Studio 2005 (C#)and the database is in the device (.sdf file).
Am trying to retreive a single row from a table containing around 10000 rows. I would like to know the fastest method to retreive the values.
whether it is faster to use an SQLDataAdapter to retreive the values to a dataset or datatable OR retreive the values to a SQLCEResultSet OR is there any other faster retreival method.
Please advice
Thanks,
Anujith
Hi,
I've noticed that after the first record from a ResultSet is deleted, HasRows property throws an exception like "The current row was deleted." Method ReadFirst() throws similar exception as well.
More details:
1. Create a ResultSet: ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive)
2. Let's say, there is no records in the resultset. Insert two new ones. Then call: ReadAbsolute(0); Delete(); HasRows; and the exception is thrown.
3. Then you can call ReadFirst() or Read with the same result.
4. Now call ReadAbsolute(0). It returns true. Call HasRows, ReadFirst() or Read() and you will get the exception each time.
I tried to explain this with the fact that Delete() doesn't change the current position of the ResultSet. But I don't see why that would mess the HasRows property or why after ReadAbsolute(0) retuns true, HasRows or ReadFirst() doesn't work.
Could anybody tell me what the problem is?
Thank you.
Hello,
I'm trying to develop a VB.NET (2005) form with just a ComBobox and a TextBox.
I bind a combobox with the SqlCeResultset (Scrollable | Updatable ). Do the same with the TextBox.
Combobox1.DataSource = rsResultSet
TextBox1.DataBindings.Add("Text", rsResultSet, "Obs")
This works (when I change the Combo item the textbox changes as well) but any changes to the textbox text do not reach the DB.If i change the Combobox item and back the old data is still there. If i look at the result
set it does appear as updateable.
How can I update the SqlCeResultset when I change the TextBox text?
Thanks
I have a SqlCeResult pointing to a table :
Public conexion As Data.SqlServerCe.SqlCeConnection = New Data.SqlServerCe.SqlCeConnection("Data Source=iPAQ File StoreGestionAlmacenN.sdf")
Public ComandoCargaProductos As Data.SqlServerCe.SqlCeCommand = conexion.CreateCommand
Public ResultSetProductos As Data.SqlServerCe.SqlCeResultSet
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
conexion.Open()
'**********************************************************
'************Conexion con Tabla Productos******************
'ComandoCargaProductos.CommandText = "SELECT * from [Productos]"
ComandoCargaProductos.CommandType = Data.CommandType.TableDirect
ComandoCargaProductos.IndexName = "PK__Productos__0000000000000041"
ComandoCargaProductos.CommandText = "Productos"
ResultSetProductos = ComandoCargaProductos.ExecuteResultSet(Data.SqlServerCe.ResultSetOptions.Scrollable Or Data.SqlServerCe.ResultSetOptions.Updatable)
End Sub
And I want to obtain the schema of the table which my resultset is pointing
Dim tabla As Data.DataTable
tabla = Me.ResultSetProductos.GetSchemaTable
But the datatable that i obtain donīt seem to have the schema that i need, for example donīt have the primary keys i have define and also donīt have any constrains......
Does anybody know the problem?
Thanks for your help
i have result set from such query :
resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable;
queryText =
SELECT agvb_id as ID
,agvb_cat_id as Category
,agv_brn_id as Brand
,agvb_brn_name as BrandName
,agv_brn_area as Area
,agv_brn_sku_qty as SkuQuantity
,agv_brn_qty as Quantity
,agv_orderliness as Orderliness
,agvb_importance as Importance
FROM AGENTS_VISITS_BRANDS
WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id)
AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id)
ORDER BY agvb_brn_name
if i open resultSet with such qury it looses its updatable option.
If i remove from where 1 param (no mater which) everything runs ok.
Hi,
Inside a single transaction I'm inserting/updating multiple records into multiple tables, in this order:
table 1 record 1
table 2 record 1
table 3 record 1
table 1 record 2
table 2 record 1
table 1 record 3
table 2 record 3
table 3 record 3
Now I'm getting an unspecified error on a certain table:
Unspecified error [ 3,-1300704038,-1834095882,activitypointerBase,x lock (x blocks),PAG (idx): 1078 ]
From msdn I see that:
PAG (idx) means a lock on an index page.
x lock means an exclusive lock:
Indicates a data modification, such as an insert, an update, or a deletion. Ensures that multiple updates cannot be made to the same resource at the same time. (I assume that multiple updates within the SAME transaction can be made, only multiple updates from different transaction cannot be made, right?)
I cannot find any reference to this error message and don't know what the numbers mean. Maybe it relates to data that can be found in the sys.lock_information table like explained here, http://technet.microsoft.com/en-us/library/ms172932.aspx, but I'm not sure.
Furthermore, the sys.lock_information table is empty. I haven't been able to reproduce the problem myself. I only received an error log and the database to investigate it.
So, does anybody have an idea what this error message means and what I can do to troubleshoot this?
Thanks,
Jeffry
I have an updateable and sensitive SqlCeResultSet that is bound to a DataGrid via a BindingSource. All updates to the resultset are programmatic. Changes to an existing record in the resultset are visible in the DataGrid and I am getting corresponding ListChanged events fired from the BindingSource. When I perform an database update independent of the resultset that changes resultset membership, I can peform a ReadLast and see the additional records; however the ListChanged event does not fire. Also, when I explicitly "create" a new record in the resultset I do not get a ListChanged event and in both cases the DataGrid does not display the records. Re-binding to the existing resultset does not appear to work either. Only when I create a new resultset and bind do the new or records display.
Using Compact Framework 2.0.
Any suggestions are appreciated.
Is there any way to implement a paging scheme such that only the required records are transferred from SQL Server to the asp.net app?
The only support I can find such as the DataAdaptor.Fill will bring all the records back from SQL Server and then create the page...
This obviously still takes time and memory based on the entire query, not the page size.
Any ideas?
I have a table with a lot of records. I want to make paging without passing all the data back to a dataset. I know how to select the top n rows but how do I select 10-20 for example.
View 3 Replies View RelatedHello,
How can I do paging in my SQL Server stored procedure.
I have thought of a way, but I need to :
"SELECT TOP @Count..."
which is not allowed :S
What can I do to get around this?
I have noticed that the server i'm running SQL2k on is starting to page out of the norm. I can see that the regsvc and sqlservr svc are showing high page faults/sec. I have 3 gigs of ram and set the max that sql can use to 2 gigs. It is currently using only 168 MB and still will show high paging at random times. I know I can add more ram but that doesn't seem to be the problem. I have also stopped unnecessary services at the os level.
Any other suggestions to fix this?
Thanks in advance.
Hi there
I've managed to make it query that return a dataset that have 2 views utilising the "Filter" in RS. I treat this as a single record with multiple views.
Now let say if I have a stored precedure that pass 2 parameters one is called state and year and accepting 'ALL' for every possibility of State and Year and construct that into single dataset with 2 views similar like above.
How do I breakdown this in the reporting services so it will have paging?
This is a simple dataset:
RECORDID, ReportViewType, State, Year, VALUE
1, "VIEW1", "NSW", 1, null
1, "VIEW2", null, null, 10000
2, "VIEW1", "NSW", 2, null
2, "VIEW2", null, null, 11000
3, "VIEW1", "VIC", 1, null
3, "VIEW2", null, null, 11003
4, "VIEW1", "VIC", 2, null
4, "VIEW2", null, null, 11001
I would like to break down (paging) this per recordid. Each page obviosuly has 2 views using the same data set with different FILTER.
Do I need to put into a LIST then inside that list put 2 TABLES? Is this possible?!?!
Thanks
I have created a stored proc for paging on a datalist which uses a objectDataSource.
I have a output param itemCount which should return the total rows. Them I am creating a temp table to fetch the records for each page request. My output param works fine if I comment out all the other select statements. But returns null with them. Any help would be appreciated.
CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]( @categoryID int, @pageIndex INT, @numRows INT, @itemCount INT OUTPUT )AS
SELECT @itemCount= COUNT(*) FROM CMRC_Products where CMRC_Products.CategoryID=@categoryID Declare @startRowIndex INT; Declare @finishRowIndex INT; set @startRowIndex = ((@pageIndex -1) * @numRows) + 1; set @finishRowIndex = @pageIndex * @numRows
DECLARE @tCat TABLE (TID int identity(1,1),ProductID int, CategoryID int, SellerUserName varchar(100), ModelName varchar(100), Medium varchar(50),ProductImage varchar(100),UnitCost money,Description varchar(1500), CategoryName varchar(100), isActive bit,weight money)
INSERT INTO @tCat(ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight)SELECT CMRC_Products.ProductID, CMRC_Products.CategoryID, CMRC_Products.SellerUserName, CMRC_Products.ModelName, CMRC_Products.Medium,CMRC_Products.ProductImage, CMRC_Products.UnitCost, CMRC_Products.Description, CMRC_Categories.CategoryName, CMRC_Products.isActive,CMRC_Products.weightFROM CMRC_Products INNER JOIN CMRC_Categories ON CMRC_Products.CategoryID = CMRC_Categories.CategoryIDWHERE (CMRC_Products.CategoryID = @categoryID) AND (CMRC_Products.isActive = 1)
SELECT ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weightFROM @tCat WHERE TID >= @startRowIndex AND TID <= @finishRowIndexGO
I am using a stored procedure to page some objectsThe procedure looks like this:
CREATE PROCEDURE sw20aut.sw20_Kon( @sid_nr INT, @sid_stl INT = 35, @kid int )AS BEGIN SET NOCOUNT ON DECLARE @rader INT, @sid_antal INT, @ubound int, @lbound int SELECT @rader = COUNT(*), @sid_antal = COUNT(*) / @sid_stl FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE kund_id = @kid AND del = '0'
IF @rader % @sid_stl != 0 SET @sid_antal = @sid_antal + 1 IF @sid_nr < 1 SET @sid_nr = 1 IF @sid_nr > @sid_antal SET @sid_nr = @sid_antal SET @ubound = @sid_stl * @sid_nr IF(@sid_antal > 0) SET @lbound = @ubound - (@sid_stl - 1) ELSE SET @lbound = 0 SELECT CurrentPage = @sid_nr, TotalPages = @sid_antal, TotalRows = @rader
DECLARE @ename VARCHAR(64), @fname VARCHAR(64), @konid VARCHAR(64) SET ROWCOUNT @lbound SELECT @ename = enamn, @fname = fnamn, @konid = kon_id FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE kund_id = @kid AND del = '0' ORDER BY enamn, fnamn, kon_id SET ROWCOUNT @sid_stl SELECT kon_id, enamn, fnamn FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE enamn + fnamn + '~' + CAST(kon_id as VARCHAR(64)) >= @ename + @fname + '~' + @konid AND (kund_id = @kid AND del = '0') ORDER BY enamn, fnamn, kon_id SELECT startid = @konid SET ROWCOUNT 0END
The big problem is that i need to display objet with the same name. In my book the best identifier is the PK and therefor i have sorted as above by ordering after LastName, FirstName, ContactId
After som thinking ive reached the conclusion that this dont work if the idnumbers isnt of the same length. as long as they are(for example two people named John Smith, one with id = '23' and one with id = '87' it works. If there ids would have been '23' and '1203' it will not work correctly) of the same length it works fine.
What im wondering is if anyone have a good solution to this? Only thing i can think of is filling all idnumbers with zeros to equal length. Dont know how and if this will affect performance though. Anyone has a practical solution to this?
Questoin
I am using Sql Server 2000.
I have a table named Cities which has more than 2600000 records.
I have to display the records for a specific city page wise.
I don't want to compromise with performance.
Can anyone has the idea?
Waiting for your fruitful response.
Happy Day And Night For All
Muhammad Zeeshanuddin Khan
Hello To make pagination I would like to retrieve only the record from x to y ...I couldn't find how to do to in sql , I was thinking so if there is a way to do it with a sqldatasourceI make my request , put it in a sqldatasource and bind it to my datalistis there a way to "filter the sqldatasource ?" to make what I need ? Thx in advance ?
View 4 Replies View RelatedIm in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.
Oracle Example wrote:
Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;
I have a paging dilema with an ADO/ASP web based application.
Currently I am using the temp table and inserted index method of paging which works well but the pages that use this paging have a variety of filters on them and a largish subset of data available. This means that every time the page is refreshed the code is creating that temporary table, inserting all the data, selecting the subset and then dropping it.
I was looking for a more efficent way of getting paged data to the client. One of the alternatives I came across was using a server side forward only cursor and the ado getrows() method. This sounds good in princible but I don't know if I am going to get a performance hit by using a server side cursor as opposed to sending the entire recorset to the client and letting it page the results.
Would it be any better to use a stored procedure and pass the full sql statement to it. I can't actually write the sql into the stored procedure becuase it is all totally dynamic.
So I guess I have three options, temp tables, server side cursor and small amounts of data sent to the client or client side cursor and large amounts of data sent to the client.
Any ideas or recomendations?
Is this a correct statement? When commit Charge total (k) is greater than Physical Memory total (k) then the server is paging badly, correct?
thanks.
Hi,
My application runs this query using a stored proc
SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3
Now I want to implement paging for the same using Row_Number so that I can display the results in pages.
Can someone please help me write a query for the same. I tried playing with Row_Number but no luck with it.Basically I am not good with SQL and I had programatically implemented paging in asp.net by looping through all records returned by the query.
Thanks,
Ganesh
Hello, I have incorporated a paging query in my software. I got the query from here:
http://rosca.net/writing/articles/serverside_paging.asp
My web software ususlly responded in .005 - .02 seconds with about a 100 rows of data. When I put simulated data on my database I added about 2 million rows. when I did this -- every page that did not execute the paging query responded lightning fast. But the webpages that executed the paging query took over 5 seconds. I dont understand why this paging query brought my web application to its knees.
Does anyone know of a more efficient way to do paging. I have SQL server 2000. If it may be easier I can upgrade to SQL 2005. PLZ Let me know. Thanks
Hello, I have a datagrid with paging allowed, but when i click on page number 2 or 3, it still displays the records from the first page. How do I solve this?
Thank you.
hello, what are the strategies when designing tables that needspaging?in the past i used to useselect top 200 * from tablewhere id not in (select top 100 id from table)with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?or any other advice?thanks
View 4 Replies View Related
Hiii all
SQL Server 2000 or 2005 dose not support the LIMIT statement like mySQL. So plz can anyone tell me tht how to do paging in SQL Server?? Without using CLR Integration...