sql insert

how i make sure that a query will open new record -on the last place and not after the selected?

for example if i will use this record and pid=5
the records will set on the next record (6)and not on the last one (can be 232)
mysql="INSERT INTO demo(newid           ,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT (SELECT MAX(ID) + 1 FROM demo),starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1 FROM demo WHERE id = '" & pid & "'"

Open in new window

sasha85Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BadotzConnect With a Mentor Commented:
Try this:

mysql = _
"INSERT INTO demo " & _
"(newid, starttask, firstread, lastread, uname, tasks, ptime, " & _
"sub, fromloc, toloc, customer, hlimit, carnum, status ) " & _
"SELECT starttask, firstread, lastread, uname, tasks, ptime, " & _
"sub, fromloc, toloc, customer, hlimit, carnum, 1 AS Status " & _
"FROM demo " & _
"WHERE id = '" & pid & "'";
0
 
TextReportCommented:
In your table ID should be an Auto Increment field, you then do not specify the value for ID when you insert and the value will be automatically allocated.

Cheers, Andrew
0
 
sasha85Author Commented:
on my query i pasted the id for the new record not specified...
as i understand the:
id = '" & pid & "'"
use only for the copy?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
BadotzCommented:
You cannot use

WHERE id = '" & pid & "'"

in an INSERT query - there is no value (yet) for the ID column. That is where the AutoNumber column does its magic.
0
 
sasha85Author Commented:
ye...but when you inserting a new record and the values for that record are being copied...
like this:
mysql="INSERT INTO demo(newid           ,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT (SELECT MAX(ID) + 1 FROM demo),starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1 FROM demo WHERE id = '" & pid & "'"
0
 
BadotzCommented:
You cannot insert into the same table - what is the point?

0
 
sasha85Author Commented:
duplicating the record...
for example in row with id=5 there is 6 fields that i want to copy to a new record
0
 
sasha85Author Commented:
sorry but what was changed here?
0
 
BadotzCommented:
You can see the changes I made if you compare my example against yours.
0
 
sasha85Author Commented:
u deleted the newid value and added , 1 AS Status
0
 
BadotzCommented:
Among other things, that is correct.
0
 
TextReportCommented:
sasha85 please note that id is a number field so you do not need the ' around it.
Cheers, Andrew

PS I will raise this again it is very bad design to control the ID the way you are trying to do this. use the Auto Increment feature for the ID field.
INSERT INTO demo(newid, starttask, firstread, lastread, uname, tasks, ptime, sub, fromloc, toloc, customer, hlimit, carnum, status)
SELECT (SELECT MAX(ID) + 1 FROM Demo), starttask, firstread, lastread, uname, tasks, ptime,sub,fromloc,toloc,customer,hlimit,carnum,1
FROM demo 
WHERE id = " & pid 

Open in new window

0
 
sasha85Author Commented:
my question again:
WHERE id = " & pid
that is tha part that orders to save the record in the record where the id column have the variable pid?

cause if it is so its not good...i need that inserted last...

and i am using id auto inc...why do you thonk i don't?
0
 
TextReportConnect With a Mentor Commented:
"and i am using id auto inc...why do you thonk i don't?" You appear to be trying to insert a value into your ID field, I am assuming the NEWID field is the auto increment field, therefore why do you need to do the MAX(ID)+1?

Cheers, Andrew
mysql = _
"INSERT INTO demo(newid, starttask, firstread, lastread, uname, tasks, ptime, sub, fromloc, toloc, customer, hlimit, carnum, status)" & vbcrlf & _
"SELECT (SELECT MAX(ID) + 1 FROM Demo), starttask, firstread, lastread, uname, tasks, ptime,sub,fromloc,toloc,customer,hlimit,carnum,1" & vbcrlf & _
"FROM demo " & vbcrlf & _
"WHERE id = " & pid & vbcrlf

Open in new window

0
 
sasha85Author Commented:
god no...i don't want to change the id field...the newid field is not auto inc -it is a serial number

for example there can be 4 records with the same serial number....the sireal number of all 4 of them will be the id field number of the first record
0
 
sasha85Author Commented:
"You appear to be trying to insert a value into your ID field"
where is that line wich do this?
0
 
TextReportConnect With a Mentor Commented:
So is NEWID one of the fields you are copying from the original record? If so then the code below should be OK
Cheers, Andrew
mysql = _
"INSERT INTO demo(newid, starttask, firstread, lastread, uname, tasks, ptime, sub, fromloc, toloc, customer, hlimit, carnum, status)" & vbcrlf & _
"SELECT NewID, starttask, firstread, lastread, uname, tasks, ptime,sub,fromloc,toloc,customer,hlimit,carnum,1" & vbcrlf & _
"FROM demo " & vbcrlf & _
"WHERE id = " & pid & vbcrlf

Open in new window

0
 
TextReportConnect With a Mentor Commented:
"You appear to be trying to insert a value into your ID field"
where is that line wich do this?

You have already explained that the NEWID is NOT an Auto Increment field so this is not the case.

Cheers, Andrew
0
 
thecodistConnect With a Mentor Commented:
See if this is what you are looking for:

mysql="INSERT INTO demo(newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) "
& "SELECT a.ID + 1,b.starttask,b.firstread,b.lastread,b.uname,b.tasks,b.ptime,b.sub,b.fromloc,b.toloc,b.customer,b.hlimit,b.carnum,1 FROM demo As a, demo As b WHERE a.id = (Select Max(id) From demo) And b.id = '" & pid & "'"
0
All Courses

From novice to tech pro — start learning today.