Update using SQL

[LaPluma]

Hello

I am still experiencing an updating problem witha poll/voting program I have.

I am updating (or trying to) using recordset values, but it has been suggested that I try using SQL statements.

As far as I know, the following is valid for updating with SQL:

UPDATE table_name SET column_name = new_value
WHERE column_name = some_value

In my case, this translates to:

sql = "UPDATE vote SET novote = novote + 1 WHERE novote = ;

'vote' is the name of one (of 3) table, and novote is a field in the 'vote' table.

What I am concerned about is the some_value which comes after the = sign.

Basically, the program should register (through the update statement) the fact that a visitor to the poll has voted (once). When the visitor clicks on another asp page, that page should display the fact that his vote has been recorded. However, the poll showings record 'no submissions', in other words, it does not record the vote.

What, therefore, do I need to write in some_value? Is it +1?

Thanks for any advice.

 

[JuanitaC]

What is the structure of your table? Is there just one column with the value in it, or are there many columns and many records?

The reason I am asking is that if your table only contains one column and one record, then there is no need to have a WHERE statement at all.

If, however, you have many records in your table, then you need to set your WHERE statement to whatever uniquely identifies that row.

For instance, if I have a table with many employees in it and I want to update one person's name, I would write:
UPDATE tblEmployee
SET FirstName = 'This Value'
WHERE EmployeeID = 12

I hope this makes some sense. If not, post back with more details about your table structure, etc.

 

[LaPluma]

Hello Juanita

Many thanks for your message and help.

I think I know what you mean when you refer to updating a record when you know what that record is. In your case, the ID of the employee concerned is no 12.

In my case, what needs updating is the amount of votes recorded (for a particular option) and, in turn, this would update the total amount of vote submissions. In other words, if I vote that the next Olympics should be held in South Africa, the vote for South Africa would increase by one, and the total amount of people who have voted in the poll would also increase by one.

Having said all that, the voting has got nothing to do with South Africa - it's simply an example to illustrate the point.

I suppose the theory behind it is similar to your own, except that where you are referring to a number of Employees (or, rather, their IDs), I am concerned with the number of votes.

There is one database (MS Access2000), with three tables, but the table I am concerned about here is the table called 'vote'. This has 4 fields: no (number); ID; Answer; novote;

The ASP page itself (that is, text written on the ASP page) actually asks the question which voters vote on, but the answers are stored in the 'Answer' field (and visible on the ASP page).

The ID field refers to the answers for a particular poll. For example, all the answers for one poll have ID number 1; all the answers for another poll have ID number 2; This is because the same voting program can have many different polls on it (if you see what I mean).

The site is here:

http://www11.brinkster.com/stevehigham/db/poll/poll.asp

I hope you are able to help!

Best wishes

LaPluma

 

[JuanitaC]

I guess because you don't have a unique identifier for each row, you will have to use two of the rows to identify the row you want to update. If I understand correctly, this something like this might work:

UPDATE vote SET novote = novote + 1
WHERE ID = 1 AND Answer = 'South Africa'

Give your sql statement the ID of the poll you are on and the answer that was selected.

Does that help?