using UPDATE to increment an value

[Naive Amoeba]

Hi, I'm new to these forums so.. hello :)I'm using an UPDATE statement to make a value in an access database increment by one. I've tried this code:

SQL = "UPDATE tbl SET my_db_value = my_db_value + 1 WHERE sql_variable = '" & variable & "'"

But it comes up with an "Object required" error. So I could use an SQL select statement, put that in a variable, and then use that in an SQL INSERT statement, but that's really messy.Any ideas?Thanks a lot,Matt

[logz]

theres a couple of ways of doing it. The most easiest of which is creating a field which uses the AutoIncrement function. When you use the CREATE TABLE function, add this line:CREATE TABLE your_table(id autoincrement)Then, when you go to insert some data into the table, the ID field will automatically add 1 to the current value.However, if you delete a record in the middle of the table, you will be left with this id array:12356As you can see, the 4 is missing, and its carried straight on to five.You can overcome this by using the second method. You need to create the field ID as a varchar or whatever table. NOT AutoincrementThis one is slightly longer. First of all, create your ADO connection and use the SELECT SQL statement, but use the function COUNT(*). Note ive created the variable "add_count".

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "YOUR DATABASE ROUTE"set rs = Server.CreateObject("ADODB.recordset")rs.Open "SELECT COUNT(*) FROM your_table", conndo until rs.EOF    for each x in rs.Fields     dim add_count     add_count=x.value    next    rs.MoveNextlooprs.closeconn.close%>

What ive done, is counted all the records in the "your_table" table. ive then assigned this value to a variable. You will need this variable later.Next, you need to create a new variable which will add 1 to the total number of records.... to do this write this:

<%   dim iadd_count   iadd_count=add_count+1%>

Now, you can write your update or insert program as normal:

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "DATABASE PATH"sql="INSERT INTO your_table (id)"sql=sql & " VALUES "sql=sql & "('" & iadd_count & "')"on error resume nextconn.Execute sql,recaffectedif err<>0 then  Response.Write("Error")else   Response.Write("Record Added")end ifconn.close%>

Now, you've included the "iadd_count" variable in the insert program.If you need any help, just ask!Try the first method first though!

[allan_bañares]

Hi, i'm Allan:i'm a new member that need some guide on how to create some of these: in SQL we Have6 type of command;can you give me some example per each commands likehow to --CREATE,INSERT,ADD,DELETE,UPDATE AND SORRY IF FORGOT THE OTHER ONE I THINK YOU KNOW THE MISSING ONE!!!!CAN YOU GIVE ME EXAMPLE ON SAID COMMANDS?????PLS SEND IN THIS E-MAIL ADDRESS[scorpio_linkin@yahoo.com] i hope you give me some of example!!!!i'm a student need some information about on SQL!!!Thanks a lot!!!!!!!!Allan;

[allan_bañares]

ALLAN,I REMEMBER THE ANOTHER ONE IT IS "SELECT"!!!ALLAN;