Updating a table...

[BigDaddy]

Ok...I need to update a table. In the table, there are 4 fields. One field is the unique identifier, so I cannot duplicate it. Before I do my update, I loop through the recordset to make sure it does not exist:

Do while not oRS.EOF
IF oRS("CODE") = new_code then
dupe=TRUE
end if
oRS.movenext
loop

Then, I check the value of "dupe" by:

IF dupe = TRUE then response.write "dupe found....blah blah blah"

This works great if I'm just adding a new record, but if I want to edit a file, and not change the field that is the unique identifier, it loops through, finds the record I'm changing and , noticin that it matches the one I'm inputting, it throws an error. Any ideas how to get by that?

Thanks.

[raf]

can you clarify some more.

i usually have an AutoNumber field as the primary, so the database itself assigns a value to that field when inserting a new record. When you update records, the primary key remains unchanged. So it can never be duplicated. Its even never altered.

i don't quite see the problem

(but maybe i'm a bit stupid)

[BigDaddy]

I usually use an auto-number myself, but in this instance, the DBA who designed the table (I didn't design it) didn't assign an auto-number. He made the unique identifier one of the fields that I'm supposed to be able to edit. My problem is that, how do I check to make sure the field doesn't already exist--except for the one I'm editing, which will be changed to the new value?

I'm thinking what might need to be done is to delete the record and re-insert it with a new value in the field.

[raf]

it doesn't sound like a good idea to me (changing the value of a unique identifier), but hey ...

what you could do is update it like this
sql = update tablename set variable=Max(variable)+1

So it picks the heighest number and the heighes+1 is always unique. (it's also quite usufull, because you can then use that number to select the last updated records) Of coarse this only works when you update only one record and only for number-variables !

Other then that, i don't know. Selecting and then building an array and inserting isn't completely save if it's a multi-user environnement.

[BigDaddy]

I appreciate the suggestions. If it was me who designed it, the table would have had a unique counter by which to reference each record. The problem is, that the unique identifier is a letter, so I cannot just add 1 to it, I need to set it to whatever the user requests.

What I think I might end up doing is just deleting the record, then checking to see if any exist, and re-inserting from there.

[miranda]

Try using 2 SQL statements to do it. the 1st one looks for the new identifier and the 2nd one actually updates the record

for example say it is a username of chuck and you want to update it to charles


Set objRs = objConn.Execute ("SELECT * FROM foobar WHERE username = 'charles'")
If objRs.EOF Then
objConn.Execute ("UPDATE foobar SET username = 'charles' WHERE username = 'chuck'")
Else
Response.Write "Sorry that username is already taken."
End If

[aCcodeMonkey]

Another was to prevent the duplicatication is to Use the "In" statement in your update query.
Example:

sSQL="If Not Exists (Select PrimaryKeyField From myTable Where PrimaryKeyField = '" & sPValue & "')
BEGIN
INSERT INTO myTable (PrimaryKeyField, Field2, Field3)
Values('" & sPValue &"',2,3)
END"

This query will only fire the insert of the there is no matching PrimaryKey Field values

If you are in SQL and were using an Identy field you can add

SELECT @@IDENTITY AS 'Identity' to the end ot the inset to return the new record id.

INSERT INTO mydatble (Field1,Field2,Field3)
VALUES ('New Value',12,125)
SELECT @@IDENTITY AS 'Identity'

Example:

Dim sConn.oConn,oCmd
Dim sSQL
Dim iID

sConn = Application("myConnectionString") ' SQL OLEDB Data Connection String
Ser oConn = server.CreateObject("ADODB.CONNECTION")

sSQL = "INSERT INTO Categories (CategoryName, Description)" & _
" VALUES ('Guitars', 'Here you can find just the" & _
" guitar you were looking for');" & _
"SELECT @@IDENTITY As 'Identity'"


Set oCmd = server.CreateObject(ADODB.COMMAND")

oCmd.Commandtext = sSQL
oCmd.Connection =oConn

oConn.Open
iID = oCmd.ExecuteScalar()

Response.Write("The ID of the new record is: " & iID(0))

[raf]

i suppose you could also use a subquery. (think it's the best sollution, certainly in run time)

something like

sql = "UPDATE tablename SET variablename = thevalue WHERE thevalue NOT IN (SELECT variablename FROM tablename)"

sql=replace(sql,"thevalue",request.form("value"))

So tou basically skip the array processing in ASP and leave it over to the MDBM.

edit:
of coarse you need to include an extra condition in your statement to select the record that needs to be updated.
something like


sql = "UPDATE tablename SET variablename = thevalue WHERE thevalue NOT IN (SELECT variablename FROM tablename) AND variablename=theoldvalue"

sql=replace(sql,"thevalue",request.form("newvalue"))
sql=replace(sql,"theoldvalue",request.form("originalvalue"))

[BigDaddy]

Thanks, guys. I will try the suggestions given.

[whammy]

Lemme know what happens, BigDaddy. :) I'm interested in how Oracle handles databases as opposed to SQL Server, especially with your comments about the primary key. :D

What IS the primary key? If it's an email address,as an example, you could set another "bit" field (for instance) to 0 if it was no longer a valid email address, and then insert the new record - and then you could search by "email = whatever and bitfield = 1".

Sometimes it is a VERY bad idea to delete a record by primary key, because there could be other tables associated with it that will then fail. :)

Of course, I know nothing about Oracle, so if I'm totally off base here, please let me know! :D