Deleting records returned by datareader

I have a function that opens a connection to an SQL database, issues aSELECT command, and reads the records with an OleDbDataReader. As therecords are read, any that match certain criteria are deleted with aDELETE command. Simplified example code is shown below:
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed toSQL Server, I get a database timeout error when attempting to do theDELETE. I suspect the reason is that the connection the reader has openhas the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property"Mode=Share Deny None". I wonder if there is some similar way to tellSQL Server to allow editing of records that are open for reading withan OleDbDataReader.
Any help would be appreciated.

Hi,

If you are using SQL Server, use SqlClient class.

The SqlClient classes use the native SQL Server drivers to access a database, while the OleDb classes use the generic OLE-DB interface

HTH

|||Thanks for the tip, HTH, but the application was originally written touse MS Access databases using the OleDb classes, and then modificationswere made to upgrade to SQL Server. While using the native SQL driversmay be more efficient, I'm not looking to re-write the application atthe moment - I just want to resolve the issue at hand.
Can anyone tell me how to set up an SQL Server connection so thatopening a connection for read by an OleDbDataReader doesn't lock therecords? I know that I could read all the records into a datagrid, andthen delete the required rows, or that I could add the records thatneed to be deleted to an arraylist for later deletion. But this seemsmuch more complicated than it should be.
All I really need to do is open a datareader, read through the recordsone at a time, and delete a few of the records that match certaincriteria. This was easily done with MS Access, but with SQL Server(using the coonnection string described in my original post) I get atimeout error when I try to delete a record. I am presuming that atimeout occurs because the record I am trying to delete is locked bythe datareader.
Someone surely has a simple answer to this problem.

|||Go to Enterprise Manager click on tools then Query Analyzer and click on tools again then Options then click on Connections there are a few options see if one could help. But I would also run the SQL statement timing out in the Query Analyzer and click on show plan before execution to see if a minor change in the statement will make a difference and use the Profiler to create a trace on statement start and statement close events. I would also run a search for Query Governor in the BOL(books online) see what adjustments you need. Hope this helps.

deleting hyphens in a phone field

I am trying to delete hyphens and spaces from a field containing phone
numbers. I know that the "Replace" Function should allow me to do this but
I
don't know how. Every phone number is different, so I don't have a 1st valu
e
to put into the arguments. I am very new to SQL Server and this is driving
me nuts.I don't understand what you mean by '1st value'. You can nest REPLACE
functions to remove both spaces and hyphens in one pass:
UPDATE dbo.MyTable
SET Phone = REPLACE(REPLACE(Phone, '-',''), ' ', '')
Hope this helps.
Dan Guzman
SQL Server MVP
"John H." <John H.@.discussions.microsoft.com> wrote in message
news:03E99E25-D30F-4C4E-839D-F25015B38FA2@.microsoft.com...
>I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this
> but I
> don't know how. Every phone number is different, so I don't have a 1st
> value
> to put into the arguments. I am very new to SQL Server and this is
> driving
> me nuts.|||John H. a écrit :
> I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this bu
t I
> don't know how. Every phone number is different, so I don't have a 1st va
lue
> to put into the arguments. I am very new to SQL Server and this is drivin
g
> me nuts.
This function :
/ ****************************************
***********************************
*/
-- delete all undesirable chars
/ ****************************************
***********************************
*/
-- exemple : FN_RESTRICT('_ Paris...?', 'abcdefghijklmnopqrstuvwxyz')
=>'aris'
CREATE FUNCTION F_RESTRICT (@.IN VARCHAR (8000),
@.CHARSOK VARCHAR(256))
RETURNS VARCHAR (8000)
AS
BEGIN
-- effets de bord
IF @.IN IS NULL
RETURN NULL
IF @.CHARSOK IS NULL
RETURN NULL
IF LEN(@.IN) = 0
RETURN @.IN
-- initialisation
DECLARE @.I INTEGER
DECLARE @.OUT VARCHAR(8000)
SET @.OUT = ''
-- lecture caractère par caractère
SET @.I =1
WHILE @.I <= LEN(@.IN)
BEGIN
IF PATINDEX('%' + SUBSTRING(@.IN, @.I, 1)+ '%', @.CHARSOK) > 0
SET @.OUT = @.OUT + SUBSTRING(@.IN, @.I, 1)
SET @.I = @.I + 1
END
RETURN @.OUT
END
GO
Use it in a trigger :
UPDATE MyTable
SET PHONE_NUMBER = F_RESTRICT (PHONE_NUMBER, '0123456789')
You will have a column with only figures in it.
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************sql

Deleting Databases in SMO for C#

Hey all,

I was trying to write a C# function that would delete everything in the old database before doing anything new. The following is the code I tried to use for the deleting part. I tried "Killdatabase" but it doesn't seem to be working. Anyway, is there a way to reference the database in the server and send commands to it? The compiler gives me a error message saying that srv.Databases("Name") is wrong because I am using a property as a method.

p.s. Someone posted a similar question the other day and they solved it in visual basic by introducing a local variable. It didn't work for me on C#.

Many Thanks

Server srv = new Server(con);

srv.Databases("Name").Drop();

Database d = new Database(srv, "Name");

d.Create();

d.ExecuteNonQuery(command);

Ooops, I am sorry. I should have used [] instead of (). But I did have to use a local variable.

Thanks anyway.

Delete problem

Dear all,

I have an asp.net webform which will provide delete function. If there are foreign key constraint and the user click the delete button, i would like the user to get response (Eg You must delete other data first......or something like this)

1. Any good idea?

2. One way i search from this form is like this, it raise error in db side(stored proc)

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR (''VersionA.)
RETURN
END

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR (''VersionB.)
RETURN
END

How can i get the raiseerror and identiy the veriosn of error in asp.net page?

Thanks in advance!!!!

Return a value to indicate that child records exists. Raising an error is not good idea due to performance.

|||This should already be taken care of from the sql side as long as youhave enforce constraits on for deleting and crud operations. Allyou have to do is catch the SQL errors and handle them from your aspxwhich isnt hard. Search handling sql errors from the codebehind. All you would have to do is handle the event and showjavascript to the user that you need to delete things.