How to Update records when no duplicates allowed

[Ray]

Hello.
I'm trying to write records to a database, if they are not there I add them new and if they are there I update them. I'm looking for a sensible approach to this in ADO.NET.
Previoulsy I was using adodb code like this where "filename" can't be a duplicate...
(Of course typically these code blocks would be put into loops and the hard coded values would be variables)
Try
cmd.CommandText = "INSERT INTO simple_list (filename, owner)VALUES ('mydoc.txt','Ted')"
Catch
cmd.CommandText = "UPDATE simple_list SET simple_list.owner = 'Ted' WHERE simple_list.filename='mydoc.txt"
End Try

Can anyone tell me how to get similar results in ADO.NET? Currently I'm on this path which works if the record isn't already there...
Dim cs As String = "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='../database/list.mdb'"
Dim cNet As New System.Data.OleDb.OleDbConnection(cs)
Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM simple_list", cNet)
Dim ds As New DataSet
Dim dr As DataRow

Try

Dim cmdbldr As New System.Data.OleDb.OleDbCommandBuilder(da)
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds, "simple_list")`

dr = ds.Tables("simple_list").NewRow()
dr("filename") = 'mydoc.txt'
dr(UserName) = 'Ted'
ds.Tables("simple_list").Rows.Add(dr)
da.Update(ds, "simple_list")

da.Update(ds, "simple_list")

Catch ex As Exception

End Try

 

[Mark Ashton]

If I understand this, have a set of inserted rows in the DataTable and if
the InsertCommand fails you want to again with an UpdateCommand.

Wouldn't just be easier to write you sql statement something like
command.CommandText =
"IF EXIST ( SELECT * FROM simple_list where
simple_list.filename='mydoc.txt') "+
"BEGIN "+
"UPDATE simple_list SET simple_list.owner = 'Ted' WHERE
simple_list.filename='mydoc.txt "+
"END "+
"ELSE "+
"BEGIN "+
"INSERT INTO simple_list (filename, owner)VALUES
('mydoc.txt','Ted') "+
"END";

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
Hello.
I'm trying to write records to a database, if they are not there I add them
new and if they are there I update them. I'm looking for a sensible
approach to this in ADO.NET.
Previoulsy I was using adodb code like this where "filename" can't be a
duplicate...
(Of course typically these code blocks would be put into loops and the hard
coded values would be variables)
Try
cmd.CommandText = "INSERT INTO simple_list (filename, owner)VALUES
('mydoc.txt','Ted')"
Catch
cmd.CommandText = "UPDATE simple_list SET simple_list.owner = 'Ted'
WHERE simple_list.filename='mydoc.txt"
End Try

Can anyone tell me how to get similar results in ADO.NET? Currently I'm on
this path which works if the record isn't already there...
Dim cs As String = "Provider='Microsoft.Jet.OLEDB.4.0';Data
Source='../database/list.mdb'"
Dim cNet As New System.Data.OleDb.OleDbConnection(cs)
Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
simple_list", cNet)
Dim ds As New DataSet
Dim dr As DataRow
Try

Dim cmdbldr As New System.Data.OleDb.OleDbCommandBuilder(da)
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds, "simple_list")`

dr = ds.Tables("simple_list").NewRow()
dr("filename") = 'mydoc.txt'
dr(UserName) = 'Ted'
ds.Tables("simple_list").Rows.Add(dr)
da.Update(ds, "simple_list")
da.Update(ds, "simple_list")

Catch ex As Exception
End Try

 

[Cor Ligthert [MVP]]

Ray,

A not loved method by everyone active in this newsgroup. I like it for
simple solutions.

http://msdn.microsoft.com/library/d...temdataoledboledbcommandbuilderclasstopic.asp

I hope this helps,

Cor

 

[Ray]

Hi Cor.
I'm pretty sure this is the approach I've used all ready if you take another
look.
I would expected that if the Insert failed it would have tried the Update.

Ray.

 

[Ray]

Thanks Mark for the time but I can't make this work. I don't know enough
about ADO.NET. Also I am trying to work on a JET database.

 

[Bob Lehmann]

How about....

Note: this would be a stored procedure, but you convert to dynamic SQL for
Access.
IF EXISTS (SELECT filename from simple_list where filename=@new_filename)
BEGIN
INSERT simple_list (filename, owner)VALUES (@new_filename, @owner)"
END
ELSE
UPDATE simple_list SET simple_list.owner = @owner WHERE
simple_list.filename=@new_filename


Bob Lehmann

Hello.
I'm trying to write records to a database, if they are not there I add them
new and if they are there I update them. I'm looking for a sensible
approach to this in ADO.NET.
Previoulsy I was using adodb code like this where "filename" can't be a
duplicate...
(Of course typically these code blocks would be put into loops and the hard
coded values would be variables)
Try
cmd.CommandText = "INSERT INTO simple_list (filename, owner)VALUES
('mydoc.txt','Ted')"
Catch
cmd.CommandText = "UPDATE simple_list SET simple_list.owner = 'Ted'
WHERE simple_list.filename='mydoc.txt"
End Try

Can anyone tell me how to get similar results in ADO.NET? Currently I'm on
this path which works if the record isn't already there...
Dim cs As String = "Provider='Microsoft.Jet.OLEDB.4.0';Data
Source='../database/list.mdb'"
Dim cNet As New System.Data.OleDb.OleDbConnection(cs)
Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
simple_list", cNet)
Dim ds As New DataSet
Dim dr As DataRow

Try

Dim cmdbldr As New System.Data.OleDb.OleDbCommandBuilder(da)
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds, "simple_list")`

dr = ds.Tables("simple_list").NewRow()
dr("filename") = 'mydoc.txt'
dr(UserName) = 'Ted'
ds.Tables("simple_list").Rows.Add(dr)
da.Update(ds, "simple_list")

da.Update(ds, "simple_list")

Catch ex As Exception

End Try

 

[Ray]

Thanks Bob. I guess the point of this exercise is to do it using the
ADO.NET dataset and related objects. It's not that I can't do it a stored
proc or with adodb, I just want to understand .NET better.

 

[Bob Lehmann]

The point, although unstated, that I wanted to make, is that exceptions are
expensive, and should be used to trap low-level errors. What you're doing is
not exceptional.

Test, first, if the record record exists, and if so update it. Otherwise
insert it. As far as I know, there is nothing in ado.net that prevents you
from doing this.

Bob Lehmann

 

[Ray]

Good point Bob, we could nearly get you a job as an Irish barman, oh but
that's "pint" not "point".

Ok so I would kick myself in my ass now if I had a rubber leg coz all I had
to do for starters was change

Dim cmd As New ADODB.Command
To..
Dim cmd As New System.Data.OleDb.OleDbCommand

And...
cmd.Execute To cmd.ExecuteNonQuery

I am rid of Adodb at this stage as far as my current code goes but the good
point about not using exceptions to manage the program flow has yet to be
covered.
So I have a new problem now of not knowing how to get an "IF - THEN - ELSE"
clause into a SQL string like the one that matches the Stored Proc listed
below.

Ray.