UPDATE

[remcoploeg]

I want to update a record. Like this

UPDATE table SET City = Rotterdam WHERE object_id = 5
This is working. But now I want to be Rotterdam a Textfield. How Can I implement that in my sql statement.
UPDATE table SET City = Text4.Text WHERE object_id = 5. But this is not working. Please help me.

Remco Ploeg
[email protected]

[Sephozzy]

Had the same problem once, I forgot the ' before and after the textfield.

BAD example:
sqlString = "UPDATE table SET City = " & txt.Text " where object_id = 5"

GOOD example:
sqlString = "UPDATE table SET City = '" & txt.Text "' where object_id = 5"

The ' aren't optional because it's a character constant.
Hope this helps

[Sephozzy]

ok I forgot the & after the txt.Text should be:
sqlString = "UPDATE table SET City = '" & txt.Text & "' where object_id = 5"

[remcoploeg]

my object_id is also variable. So I do I put that in the statement??

Remco Ploeg
[email protected]

[Sephozzy]

Yes, you can insert all variables like that, just remember to include the ' for text-variables. So assumming that NumVar is some numeric variable(for example an integer) and not text you could create something like:

sqlString = "UPDATE table SET City = '" & txt.Text & "' where object_id = " & NumVar

[remcoploeg]

sorry but your statement is not working. It gives a error: Compile error, Expected end of statement.
What goes wrong?

Remco Ploeg
[email protected]

[remcoploeg]

It is not working, He doesn't give any error more but he doesn't update the table, When I put the statement below in a query, it's updating the table, but via VB6 it is not working. What can be the problem. This is the statement in VB:

Dim sSQL as String
sSQL = "UPDATE klantenbestand SET City = 'Test' WHERE CompanyID = 1"
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = sSQL

This function runs when I hit a button.What can be the problem

Remco Ploeg
[email protected]

[remcoploeg]

it's working now, but now I want to be two field updated. Can I put that in one statement. Or do I need two update statements?

Remco Ploeg
[email protected]

[Sephozzy]

In my app I always update 1 field at a time. That's because I update them when the user moves the focus from one textbox to somewhere else. I then check if (s)he changed anything in the text box and if so I just execute the update statement. I also have a checkbox to turn off this kind of autoupdate, if the checkbox is not checked I ask the user if (s)he wants to update the field, if yes I trigger the update if no I put the old value back in the textbox. Just to tell you that I think it's safer to update one at a time.
I also prefer it because in my app it gives me alot more flexibility. If I need the code to add fields somewhere else in the program I can just call the individual updates whitout having to write a new update which can handle 3,4,5...columns at once.

[remcoploeg]

Ok, maybe that is the best. I think it is also nice when the computer breaks down. It saved already a lot of things. I have 30 items.
Thanks alot for your help.

Remco Ploeg
[email protected]

[Cakkie]

It depends, if you need to update two (or more) values that affect records with the same condition met, you can do it in one statement. if for the different values a different condition must be met, you will need to do it using multiple statements.

UPDATE table set City = 'Amsterdam', Description = 'VBCodelibrary Amsterdam weekend: 16/11/2001' where object_id = 5



the above statement will update City and description of all records where object_id is 5

UPDATE table set City = 'Amsterdam' where object_id = 5
UPDATE table set Description = 'VBCodelibrary Amsterdam weekend: 16/11/2001' where object_id = 6



The above two statements cannot be combined.



Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook