Database SQL question

[MPrimal]

I am using a database file to save some data for a program I am writing. I can create the database and save the data no problem, but if the user edits an entry that is stored I cannot get it to overwrite that entry within the database file.

I get an error saying the line already exists. I assume there is an SQL command that will alow you to overwrite existing data (I need to overwrite a complete row and not just one column on a row). At the moment I have to close the database file, physically delete it and re-create it which seems very long winded.

Can someone tell me if there is a command that will alow me to do this and if so what it is.

Thanks

Andy

[Fangbeast]

I am using a database file to save some data for a program I am writing. I can create the database and save the data no problem, but if the user edits an entry that is stored I cannot get it to overwrite that entry within the database file.

I get an error saying the line already exists. I assume there is an SQL command that will alow you to overwrite existing data (I need to overwrite a complete row and not just one column on a row). At the moment I have to close the database file, physically delete it and re-create it which seems very long winded.

Can someone tell me if there is a command that will alow me to do this and if so what it is.

Thanks

Andy

From LarsG and my tutorial. Note, you cannot just replace an entire line and there are good reasons for that. But you can directly replace any column on its' own or multiple changed columns so there is no need to replace an entire record.

Query.s = "Update MyTableName Set ProductColumn = 'Door Hinge', InfoColumn = 'Brass door hinge' Where RecordId = '55';"

We are updating the "ProductColumn" field outright with the value "Door Hinge" and updating the "InfoColumn" field outright with the value "Brass door hinge'" where the "RecordId" field is equal to the value of "55".


We could also update a field based on its' previous value.


Query.s = "Update MyTableName Set ProductColumn = 'Door Hinge' WHERE ProductColumn = 'Door Knob'"

Change all occurrences of Door Hinge in the ProductColumn field to Door Knob.

And so on.

As I said before, you can update a single column or multiple columns without having to worry about replacing a field.


Does this help? Or do you need more info?

[MPrimal]

Query.s = "Update MyTableName Set ProductColumn = 'Door Hinge', InfoColumn = 'Brass door hinge' Where RecordId = '55';"

We are updating the "ProductColumn" field outright with the value "Door Hinge" and updating the "InfoColumn" field outright with the value "Brass door hinge'" where the "RecordId" field is equal to the value of "55".

As I said before, you can update a single column or multiple columns without having to worry about replacing a field.

I think I follow that. let me give an example to see if I understand correctly.

If I had columns called "Info1", "Info2", "Info3" and I wanted to change the values for those colums on line 45 to read "Entry Data1", "Entry Data2", "Entry Data3" I would do something like this:

Query.s = "Update MyTableName Set Info1 = 'Entry Data1', Info2= 'Entry Data2", Info3="Entry Data3' Where RecordId = '45';"

Is that correct or have I missed something?

Thanks I really appriecate this.

Andy

[Straker]

That should work providing you have a key in the table named "RecordId", otherwise it would fail. All of your tables should have a unique key column which stores a value that is different for every row and never a null or empty value in that column.

[Fangbeast]

That query is correct. Straker's comment is correct also and in that case, you would need to have another unique identifier to find the correct record with such as:

Query.s = "Update MyTableName Set Info1 = 'Entry Data1', Info2= 'Entry Data2", Info3="Entry Data3' Where MyOtherUniqueColumMighBe = 'whatevervalueyouarelookingfor';"

Also, if you want to update a range of records where the collumn MIGHT be SIMILAR to a particular value , then do this:

Query.s = "UPDATE MyTableName SET Info1 = 'Entry Data1', Info2= 'Entry Data2", Info3="Entry Data3' WHERE ColumnToLookFor LIKE '%whatiamlookingfor';"

LIKE means 'sounds like'.

% at the start of the value means 'ends with this value,
% at the end of the value means 'starts with this value,
% on either side of the value means 'can contain this anywhere in the value.

[MPrimal]

Thanks. That is a HUGE help! I can see how to do what I need now. It is certainly a lot better than what I was doing that's for sure!

Thanks again both of you.

Andy

[Fangbeast]

That should work providing you have a key in the table named "RecordId", otherwise it would fail. All of your tables should have a unique key column which stores a value that is different for every row and never a null or empty value in that column.

When you say 'unique key column', the idea here is that he could be using any unique field (if known) in a database table to do the update (and still have an exact match), not just the record id. This was merely an idea to get him started, using a field that most of us would normally employ as accepted normal practice.

I've always used the record field for exact matches and 'LIKE' for generic updates.

[Straker]

True. Or any combination of columns that uniquely identify the row.

Anyway, I didn't know if you guys were speaking of a specific database as my answer was intended to be of a generic nature.

Just trying to start the new ones off on the right foot, so to speak.

[Fangbeast]

But what if they are left footed?

[Straker]