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.
View 7 Replies
ADVERTISEMENT
Oct 3, 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.
View 1 Replies
View Related
Nov 14, 2007
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
View 3 Replies
View Related
Oct 9, 2007
I have three tables
1. membership table(aspnet_membership table)
2. User Contacts table
3. Address table
These three table have relation ship with one another through a UserId field.
How will you set up cascaded delete on these tables, Like if I delete a user in the membership table I want the related records in the other tables to be deleted as well. Cascaded delete is it something done through code, or is it definde when the tables are created.
Please advice.
View 1 Replies
View Related
Jun 23, 2005
Hey Guys,I have Performance Monitor running and storing the network usage to my MsSQL database, and this is done a few times a minute. I have a page that then shows show much of my bandwidth is being used. As you can gather, the database quickly starts filling up with hundrreds of records so I could do with a script that delete these records. I cant simply delete all records because that would cause my webpage to fail so I need a way to delete all records apart from the latest one. Wondering if anyone would know how I could do this?
View 3 Replies
View Related
Nov 19, 2004
hi to all,
How to delete duplicate record in the recordset?
Thanks...
View 3 Replies
View Related
Feb 27, 2006
Hi SQL gurus,
I have a form that depending on the outcome, will either add, update or delete a record.
1) If there IS NO record of "this" and "that" and Request("x") <> "" it will add a new record.
2) If there IS a record for "this" and "that" and Request("x") <> "" it will update column 1,2,3 or 4.
3) If there is a record for "this" and "that" and Request("x") = "" it will delete the record.
My problem is if there is a value in request("1") it works fine, but if there is no value in ("1") and there is a value in 2, 3, or 4 it will delete the record.
If request("1") <> "" OR request("2") <> "" OR request("3") <> "" OR request("4") <> "" Then
sSQL = "SELECT * FROM some_column WHERE this = '" & request("this) & "' and that = '" & request("that) & "'"
oRS.Open sSQL,oConn,adOpenKeySet,adLockOptimistic
If oRS.EOF Then
oRS.Addnew
oRS.Fields("this") = request("this")
ors.Fields("that") = request("that")
Else
ors.Movefirst
End If
oRS.fields("1") = request("1")
oRS.fields("2") = request("2")
oRS.fields("3") = request("3")
oRS.fields("4") = request("4")
oRS.Update
oRS.Close
Else
sSQL = "Delete from some_table Where this = '" & '" & request("this) & "' & "' AND that = '" & '" & request("that) & "' & "'"
objCmd.ActiveConnection = oConn
objCmd.CommandType = adCmdText
objCmd.CommandText = sSQL
objCmd.Execute
End if
Hope I explained that okay.
View 1 Replies
View Related
Jul 20, 2005
Hi All,I am using Microsoft SQL Enterprise Manager version 8.0 and havecreated a view from a combination of 4 different tables. I would liketo be able to go into sql and open the view and select a row anddelete that row however this seem impossible right now. I am not sureif it's possible to delete a row from a view?? Or could it be thatthese tables are all interconnected and in order to delete a recordthat is joined to one or more of the tables it has to be deleted atthe top level of the join heirarchy etc etc. (do you understand what imean?) Can this be done??Thanks in advance,Erin
View 4 Replies
View Related
Nov 25, 2006
hai dears i want to delete a row from the table on the basis of key sent in beusers obj
"connection myConn = new connection();" this is ma own class that establishes the connetion which works fine in other caseseg insert
'' it shows no error or exception ' but do not delete record
so plz tell me how to do this...
or whats wrong in this
public void delete(BEusers obj)
{
connection myConn = new connection();
oCommand = new SqlCommand("sp_delete_tbl_users_by_userid",myConn.sqlCon);
oCommand.CommandType = CommandType.StoredProcedure;
try {
myConn.OpenConnection();
sp = oCommand.Parameters.Add("@User_Id", SqlDbType.VarChar);
sp.Size = 20;
sp.Value = obj.user_id;
oCommand.ExecuteNonQuery();
}
catch (Exception oException)
{ throw oException; }
finally { myConn.CloseConnection(); }
}
View 3 Replies
View Related
Aug 26, 2005
Hey Guys,I have been trying to work out how I would delete a record that was created more then 10 minutes ago.I can use this to delete records older then a day.DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1Just need something now that will do it for just 10 minutes.Cheers.
View 1 Replies
View Related
Mar 26, 2008
Hi ,
i am using sql server 2005.
i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them
my problem is table structure have idendtity column which is unique.
that is m table structure is something like
recid citycode hospcode doctorcode otherdesp
1 0001 hp001 d0001 ...
2 0002 hp002 d0002 ...
3 0001 hp001 d0001 ...
4 0002 hp002 d0002 ...
please suggest
thank you
View 2 Replies
View Related
Jun 12, 2008
I am using the tables created by the aspnet_regsql.exe tool for security. Basically, I need to ensure that an account named Administrator is never deleted. I also have a role named administrator, and I need to make sure that Administrator is never removed from the administrator role.Can I create a trigger to ensure that the Administrator is never deleted and that the Administrator is never removed from the Administrator role? I know it will probably be two separate triggers: one on the aspnet_users table and one on the aspnet_usersinroles table.Thanks a lot for the help!
View 1 Replies
View Related
Oct 9, 2007
I can not get this stored procedure to delete my records...
I have a
contact table
RecordID
FirstName
LastName
etc
and a Address table
RecordID
Street
Zip
Town
Country
And a Relation table
RecordID
ContactID
AddressID
CreateDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bc_Contact_Delete]
@ContactID int
AS
--SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete all Adresses
DELETE FROM [Address]
WHERE
RecordId in (SELECT ca.AdressId from [ContactAddress] ca
where
ca.ContactID = @ContactID)
-- Delete all Relations
DELETE FROM [ContactAdress]
WHERE ContactID = @ContactID
--- Delete Kontakt
DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
RETURN
My Errormessage is
The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.
Can someone please post me an advice?
View 5 Replies
View Related
Mar 11, 1999
I can't delete records for a SQL Server table when I attached the table to Microsoft Access 97. I time out when I try to delete the record. However I can query the table.
I use the standard ODBC setup, do I need to do anything else beside using the standard Access link.
Thanks in advance
View 1 Replies
View Related
Jun 18, 2007
Hi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
View 2 Replies
View Related
Sep 13, 2007
How can i get the current row index of SqlCeResultSet specially after calling Seek method?
View 3 Replies
View Related
May 17, 2007
Is there anyway to filter a existing SQLCEReusltSet?
View 1 Replies
View Related
Aug 17, 2007
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
View 4 Replies
View Related
Aug 20, 2007
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?
View 4 Replies
View Related
Sep 25, 2007
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!
View 4 Replies
View Related
Aug 21, 2007
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
View 1 Replies
View Related
May 23, 2007
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
View 4 Replies
View Related
May 4, 2008
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?
View 1 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Jul 18, 2014
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
View 9 Replies
View Related
May 23, 2007
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
View 3 Replies
View Related
Jan 31, 2008
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
View 1 Replies
View Related
May 4, 2006
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
View 1 Replies
View Related
Feb 26, 2008
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.
View 1 Replies
View Related
Nov 6, 2007
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.
View 3 Replies
View Related
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 6, 2007
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
View 5 Replies
View Related
