SQLCeResultSet + Filter
May 17, 2007Is there anyway to filter a existing SQLCEReusltSet?
ADVERTISEMENT
SqlCeResultSet
Sep 13, 2007
How can i get the current row index of SqlCeResultSet specially after calling Seek method?
Paging On SqlCeResultSet
Aug 17, 2007Hi
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
Insert Identity Value With SqlCeResultSet
Aug 20, 2007Hi
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?
SQLCeResultset, Connections And Performance
Sep 25, 2007I 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!
Update Database Using Sqlceresultset
Aug 21, 2007Hi,
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
SqlCeResultSet Performance Vrs OLEDB
May 23, 2007Hi 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
How To Update An SDF Database With A SqlCEResultSet
May 4, 2008I 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?
SqlCeResultSet - Problem After Deleting First Record
Oct 4, 2006
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.
SQL Mobile - SQLCEResultSet - Fast Retreival
May 23, 2007Hi,
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
SqlCeResultSet - Problem After Deleting First Record
Oct 3, 2006Hi,
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.
Update TextBox Binding To SQLCeResultSet
Jan 31, 2008Hello,
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
Automatic Select Filter (something Like Global Table Filter)
Apr 15, 2008Hello,
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
Obtain Sqlceresultset Table Schema With GetSchemaTable
May 4, 2006I 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
SqlCeResultSet Looses Updatable Option If Query Has 2 Params
Feb 26, 2008i 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.
And/or Filter Field Not Enabled In The Group Filter Tab
Jan 26, 2006Howdy,
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 dont 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)
Unspecified Error When Inserting/updating Record Using SqlCeResultSet On Sql Compact
Nov 14, 2007Hi,
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
BindingSource ListChanged Does Not Fire For New Records In SqlCeResultSet With Sensitive Option
Nov 6, 2007I 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.
Filter Help
Feb 3, 2007Hello I am working in an advertising company and I have to find all records who have advertised 7 days back but if one person has advertised more than once means for different products, his name should come only once. How can I filter this. I am totally new to this language I can write simple query but donât know how to filter it.
Name IndividualID Product Date
A 1 x 2007-01-27.23.59.59.777
A 1 y 2007-01-28.00.00.00.000
b 2 xx 2007-01-28.00.00.00.000
A 1 z 2007-01-28.00.00.00.000
b 2 yy 2007-01-28.00.00.00.000
A 1 p 2007-01-28.00.00.00.000
c 3 m 2007-01-28.00.00.00.000
d 1 n 2007-01-28.00.00.00.000
A 1 s 2007-01-28.00.00.00.000
Filter On ' In Name
May 17, 2007Hi,
I have a supllier named K O'Neill and i want to filter on his name in my supplier table . Is this possible ?
When I try Vend Name = 'K O%Neill' it does not work.
Can anyone help?
Thanks.
Filter
Aug 21, 2006I need a query that returns every n record from the database and the last record.
For every n-th is easy, but I need a
WHERE Index % n = 0
but I need a last one too.
Any Help.
Filter && *
May 24, 2007Hi I am wandering if it is possible to achieve the following:
I am using Reporting Services and Reporting services I got a report with a text parameter.
I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...
I am doing the following:
Filter([DIM Product].[Product].ALLMEMBERS,
,IIF(INSTR(@Product, "*") > 0
,[DIM Product].[Product].currentmember.name = Replace(@Product, "%", "")
,INSTR([DIM Product].[Product].currentmember.name, @Product)
)
)
The problem ist that the function Replace does not work!
Does someone know how to do it?
Using IN As Filter
May 22, 2007What is the syntax for using IN as a filter? in SQL it would be IN( 'A', 'B', 'C') but this does not work in Reporting services.
View 4 Replies View RelatedFormatting A Filter
Jun 9, 2008In a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
How To Filter Sqldatasource
Jun 8, 2006Hi,
I am new in framework 2 and I can't find a way to filter the sqldatasource.
I have an sqldatasource control that retrive data from data base-"Select * from myTable"
I set the fiterExpression property-sqlDataSource1.FilterExpression="ID='" + strID + "' " ;
I don't know how to continue from here.If I bound the sqlDataSource1 to a control like gridView it works good and I see the filter oparation. but I want to get the result set in the code and loop threw it like I did with ver 1.1 with sqldataReader:
While sqlDatareader1.Read { myCode ... }
How can I do it with sqlDataSource ?
Thanks,
David
JOIN Filter
Aug 24, 1999I have two tables A and B. I want to insert data into tables C and D based on join between A and B (A.column = B.column). What is the best way to accomplish this type of task?
IE-------> If column = xyz then insert into table C
IF column = abc then insert into table D
ELSE do_not_insert
Please help.
Regards
Ziggy
Profiler Filter
Sep 15, 2004I have been running traces in SQL Profiler and have not been able to get the filtering to work correctly. I am interested in capturing only statements that use CPU and have setup the filter for CPU > 0. It still returns all of the rows that have Null. Is there any way of filtering out the rows that have nunll for CPU?
Thanks,
Ken Nicholson
How Do I Add A Filter To This Query?
May 3, 2006I'm an MDX newbie.. with some help I was able to create a query that returns accurate results. All I need to do now is filter for Completed Work value of greater than 0.
I have tried to use the filter wizard in VS2005, but I have never gotten the desired results.
Platform Info:
SQL Server 2005 Standard
VS2005
---------Start Statement----------------------------------------------------------
WITH
MEMBER [Measures].[Completed Work by WI on dt1] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].[Date].&[2006-04-23T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work by WI on dt2] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].[Date].&[2006-04-29T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work] AS
[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]
SELECT
NON empty
{
[Measures].[Completed Work]
}
ON COLUMNS,
NON EMPTY
{
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title])
}
ON ROWS
FROM
[Team System]
----------End Statement---------------------------------------------------
Filter Date
Jun 9, 2004I got a field called Date#, datatype = datetime, default value = getdate().
So after i insert a record, the Date# will auto record the system date and the time. And my MS SQL Server show: 6/9/2004 12:24:35 PM
However i am not able to filter my record by date. The statement i tried are:
SELECT * from mytable WHERE Date# = '6/9/2004 12:24:35 PM'
SELECT * from mytable WHERE Date# = "6/9/2004 12:24:35 PM"
SELECT * from mytable WHERE Date# = 6/9/2004 12:24:35 PM
SELECT * from mytable WHERE Date# = '6/9/2004'
Pls help me,
Thanks a million
Filter A Measure With MDX
Dec 27, 2005HI all friends
I have a Fact table like this
DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws
I just want make the next query with MDX
SELECT sales
FROM myFactTable
WHERE salesws > 0
Note that salesws is a measure not a dimmension
How it would be in MDX? I need a lot of help
Regards!
Time Filter
Feb 6, 2006Hi,
I need to filter my table to return data between 18:30 and 7:00
I am not sure how to do the half hour and span over midnight.
I think this might work for 18:00 and 7:00.
SELECT *
FROM MyTable
WHERE datePart(hour, Timestamp) Between 18 AND 24 Or datePart(hour, Timestamp) Between 00 AND 7
I would appreciate any help!
Thanks
Problem With Like Filter.
Mar 15, 2008I have a table with a varchar in it.
Inside the var char it contains data like:
Variable.[32] + Variable.[432] * (Variable.[333] / Const.[21])
What I am trying to do is search for all records that are using variable 333
I was trying:
SELECT * FROM my_table WHERE data_field LIKE '%Variable.[333]%'
However, that returning nothing.
I am going to guess that the [ ] need to be escape or something, but I don't know if thats really the issue.
Doing:
SELECT * FROM my_table WHERE data_field LIKE '%Variable%'
Works.
Thanks in advance,
Rich
Where Clause Filter
Apr 14, 2008I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:
Select
DisplayColumns...
From
InterestingTables
Where
(Filter1) --W1
AND --W2
NOT --W3
(Filter2) --W4
Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?).
Now, say SetF1ExF2 is the result set of the sql script above.
Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3.
Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2
I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.
TIA.