specifying row/record in an update ssql statement

[HEIDI]

I am new at database programming and I am using vb6 and trying to update a record in an oracle db, but I cannot seem to find out how to set the where clause to be equal to the record. The update statement updates all the records to the one that is supposed to be the last record. I loop through two control arrays to get my values and then execute the ssql. Please help??? Here is my loop:


For ncounter = 0 To 9
nMonth = Int(txtMonth(ncounter))
nDay = Int(txtDay(ncounter))
ssql = "UPDATE tgirdba.holiday SET month= ' " & nMonth & " ' , " & "day= ' " & nDay & " ' " & "WHERE ?????????"
cnInjury.Execute (ssql)
ssql = ""
Next ncounter

Thanks in advance!!
Heidi

[Johnny101]

You will have to have the "key" to the table. It could be the ID field (if there is one), or maybe a combination of fields, like LastName = 'lName' and Firstname ='fName'. If the table was setup by anyone with a fair amount of db experience, there should be something like this. Maybe an autonumber field somewhere. If you are updating employee info - the the social security number could be the key. if the table doesn't have an actual key, but each row is unique, the combination of month and day (unique to each year) might do.

If you have the old values and the above applies you could use this:

Dim sql as string

sql = "UPDATE YourTable set Month = 'NewValue', Day = 'NewValue' "
sql = sql & "WHERE Month = 'OldValue' And Day = 'OldValue'"




This is a tricky way of doing updates, but I hope it gives you some ideas for getting the correct row(s) for your updates.

Hope this helps,
john

John Pirkey
MCSD
www.ShallowWaterSystems.com

[HEIDI]

Thank you very much for your post. I did end up setting an autonumber key so I would update a specific row.

Thanks again
Heidi