|
 |
asp_databases thread: Tried to read the max ID from the table and then add 1 to it and update the recordset with a new number
Message #1 by "Marko Ramstedt" <marko.ramstedt@a...> on Thu, 22 Aug 2002 16:04:52
|
|
...but something is wrong with it :-(
<%
Dim sTopic
Dim sQuestion
Dim sSql
Dim Conn
Dim ConnStr
Dim sSql2
Dim Conn2
Dim ConnStr2
Dim ID
Dim sID
Set conn = Server.CreateObject("ADODB.Connection")
connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("/data/users_fin.mdb")
Conn.Open connStr
sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
sID = ID
Conn.Execute sSql
Conn.Close
Set Conn=Nothing
sID = sID + 1201
Set conn2 = Server.CreateObject("ADODB.Connection")
connStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("/data/users_fin.mdb")
Conn2.Open connStr
sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
("devUserName") & "'"
Conn2.Execute sSql2 ' Update the record
Conn2.Close ' Close the database connection ( this is always a good
practice )
Set Conn2=Nothing 'Clear the connection vaiable
%>
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Thu, 22 Aug 2002 17:41:07 +0200
|
|
> ...but something is wrong with it :-(
What makes you think there's something wrong with it :-?
-Kim
> -----Original Message-----
> From: Marko Ramstedt [mailto:marko.ramstedt@a...]
> Sent: 22. august 2002 16:05
> To: ASP Databases
> Subject: [asp_databases] Tried to read the max ID from the table and
> then add 1 to it and update the recordset with a new number
>
>
> ...but something is wrong with it :-(
>
> <%
>
> Dim sTopic
> Dim sQuestion
> Dim sSql
> Dim Conn
> Dim ConnStr
> Dim sSql2
> Dim Conn2
> Dim ConnStr2
> Dim ID
> Dim sID
>
> Set conn = Server.CreateObject("ADODB.Connection")
> connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn.Open connStr
>
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
>
> sID = ID
>
> Conn.Execute sSql
> Conn.Close
> Set Conn=Nothing
>
> sID = sID + 1201
>
> Set conn2 = Server.CreateObject("ADODB.Connection")
> connStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn2.Open connStr
>
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
> ("devUserName") & "'"
>
> Conn2.Execute sSql2 ' Update the record
> Conn2.Close ' Close the database connection ( this is always a good
> practice )
> Set Conn2=Nothing 'Clear the connection vaiable
>
> %>
Message #3 by "Jack Dunstan" <jdunstan7@h...> on Thu, 22 Aug 2002 12:40:40 -0400
|
|
Marko,
The way I understand it, you need to set a records variable to the result of
your execute command if you expect to return a result. Right now you have
sID = ID. the ID you dim'd has no relation to the ID in your "SELECT ID
..." statement. Try this:
Dim rsMax as New ADODB.recordset
'then set it to the execute command like
set rsMax = Conn.Execute sSql
'then
sID = rsMax("ID").value
There are others ways of course, but that should get it working for you.
hth
Jack
----- Original Message -----
From: "Marko Ramstedt" <marko.ramstedt@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, August 22, 2002 4:04 PM
Subject: [asp_databases] Tried to read the max ID from the table and then
add 1 to it and update the recordset with a new number
> ...but something is wrong with it :-(
>
> <%
>
> Dim sTopic
> Dim sQuestion
> Dim sSql
> Dim Conn
> Dim ConnStr
> Dim sSql2
> Dim Conn2
> Dim ConnStr2
> Dim ID
> Dim sID
>
> Set conn = Server.CreateObject("ADODB.Connection")
> connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn.Open connStr
>
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
>
> sID = ID
>
> Conn.Execute sSql
> Conn.Close
> Set Conn=Nothing
>
> sID = sID + 1201
>
> Set conn2 = Server.CreateObject("ADODB.Connection")
> connStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn2.Open connStr
>
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
> ("devUserName") & "'"
>
> Conn2.Execute sSql2 ' Update the record
> Conn2.Close ' Close the database connection ( this is always a good
> practice )
> Set Conn2=Nothing 'Clear the connection vaiable
>
> %>
>
Message #4 by "Drew, Ron" <RDrew@B...> on Thu, 22 Aug 2002 17:23:49 -0400
|
|
sSql =3D "SELECT max(ID) as themax FROM Users "
response.write rs("themax")
of course u need to define ur rs
> -----Original Message-----
> From: Marko Ramstedt [SMTP:marko.ramstedt@a...]
> Sent: Thursday, August 22, 2002 12:05 PM
> To: ASP Databases
> Subject: [asp_databases] Tried to read the max ID from the table and
then add 1 to it and update the recordset with a new number
>
> ...but something is wrong with it :-(
>
> <%
>
> Dim sTopic
> Dim sQuestion
> Dim sSql
> Dim Conn
> Dim ConnStr
> Dim sSql2
> Dim Conn2
> Dim ConnStr2
> Dim ID
> Dim sID
>
> Set conn =3D Server.CreateObject("ADODB.Connection")
> connStr =3D "DRIVER=3D{Microsoft Access Driver (*.mdb)}; DBQ=3D" &
> Server.MapPath("/data/users_fin.mdb")
> Conn.Open connStr
>
> sSql =3D "SELECT ID FROM Users WHERE ID=3D(SELECT max(ID) FROM Users)"
>
> sID =3D ID
>
> Conn.Execute sSql
> Conn.Close
> Set Conn=3DNothing
>
> sID =3D sID + 1201
>
> Set conn2 =3D Server.CreateObject("ADODB.Connection")
> connStr2 =3D "DRIVER=3D{Microsoft Access Driver (*.mdb)}; DBQ=3D" &
> Server.MapPath("/data/users_fin.mdb")
> Conn2.Open connStr
>
> sSql2=3D"Update Users Set id =3D " & sID & " WHERE UserName =3D '" &
session
> ("devUserName") & "'"
>
> Conn2.Execute sSql2 ' Update the record
> Conn2.Close ' Close the database connection ( this is always a good
> practice )
> Set Conn2=3DNothing 'Clear the connection vaiable
>
> %>
Message #5 by "Kim Iwan Hansen" <kimiwan@k...> on Fri, 23 Aug 2002 01:32:40 +0200
|
|
If it's just to increment the id in the database, and nothing else, then
it'd be eaiser to just use an update statement like this:
set conn = server.createobject("adodb.connection")
conn.open "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & _
Server.MapPath("/data/users_fin.mdb")
conn.execute("UPDATE Users SET ID = ID+1 WHERE ID = MAX(ID)")
conn.close
set conn = nothing
Remember that the ID field has to be updateable (so not gonna work if it's
an autonumber field)!
-Kim
> -----Original Message-----
> From: Drew, Ron [mailto:RDrew@B...]
> Sent: 22. august 2002 23:24
> To: ASP Databases
> Subject: [asp_databases] RE: Tried to read the max ID from the table and
> then add 1 to it and update the recordset with a new number
>
>
> sSql = "SELECT max(ID) as themax FROM Users "
> response.write rs("themax")
>
> of course u need to define ur rs
>
>
> > -----Original Message-----
> > From: Marko Ramstedt [SMTP:marko.ramstedt@a...]
> > Sent: Thursday, August 22, 2002 12:05 PM
> > To: ASP Databases
> > Subject: [asp_databases] Tried to read the max ID from the
> table and then add 1 to it and update the recordset with a new number
> >
> > ...but something is wrong with it :-(
> >
> > <%
> >
> > Dim sTopic
> > Dim sQuestion
> > Dim sSql
> > Dim Conn
> > Dim ConnStr
> > Dim sSql2
> > Dim Conn2
> > Dim ConnStr2
> > Dim ID
> > Dim sID
> >
> > Set conn = Server.CreateObject("ADODB.Connection")
> > connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> > Server.MapPath("/data/users_fin.mdb")
> > Conn.Open connStr
> >
> > sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
> >
> > sID = ID
> >
> > Conn.Execute sSql
> > Conn.Close
> > Set Conn=Nothing
> >
> > sID = sID + 1201
> >
> > Set conn2 = Server.CreateObject("ADODB.Connection")
> > connStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> > Server.MapPath("/data/users_fin.mdb")
> > Conn2.Open connStr
> >
> > sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
> > ("devUserName") & "'"
> >
> > Conn2.Execute sSql2 ' Update the record
> > Conn2.Close ' Close the database connection ( this is always a good
> > practice )
> > Set Conn2=Nothing 'Clear the connection vaiable
> >
> > %>
>
Message #6 by "Chetan Kelkar" <chetan@c...> on Fri, 23 Aug 2002 08:36:18 +0530
|
|
i can put in my penny worth of brains in this if u cud provide the error
that is been thrown !
the code looks ok !
chetan
----- Original Message -----
From: "Marko Ramstedt" <marko.ramstedt@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, August 22, 2002 4:04 PM
Subject: [asp_databases] Tried to read the max ID from the table and then
add 1 to it and update the recordset with a new number
> ...but something is wrong with it :-(
>
> <%
>
> Dim sTopic
> Dim sQuestion
> Dim sSql
> Dim Conn
> Dim ConnStr
> Dim sSql2
> Dim Conn2
> Dim ConnStr2
> Dim ID
> Dim sID
>
> Set conn = Server.CreateObject("ADODB.Connection")
> connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn.Open connStr
>
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
>
> sID = ID
>
> Conn.Execute sSql
> Conn.Close
> Set Conn=Nothing
>
> sID = sID + 1201
>
> Set conn2 = Server.CreateObject("ADODB.Connection")
> connStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn2.Open connStr
>
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
> ("devUserName") & "'"
>
> Conn2.Execute sSql2 ' Update the record
> Conn2.Close ' Close the database connection ( this is always a good
> practice )
> Set Conn2=Nothing 'Clear the connection vaiable
>
> %>
>
Message #7 by shawnc@u... on Sat, 24 Aug 2002 00:41:07
|
|
The code isn't ok, but just do it all in one step.
sSql="Update Users Set id = (Select MAX(id)+1201 FROM Users) WHERE
UserName = '" & session("devUserName") & "'"
conn.execute sSql
Shawn
> i can put in my penny worth of brains in this if u cud provide the error
that is been thrown !
the code looks ok !
chetan
----- Original Message -----
From: "Marko Ramstedt" <marko.ramstedt@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, August 22, 2002 4:04 PM
Subject: [asp_databases] Tried to read the max ID from the table and then
add 1 to it and update the recordset with a new number
> ...but something is wrong with it :-(
>
> <%
>
> Dim sTopic
> Dim sQuestion
> Dim sSql
> Dim Conn
> Dim ConnStr
> Dim sSql2
> Dim Conn2
> Dim ConnStr2
> Dim ID
> Dim sID
>
> Set conn = Server.CreateObject("ADODB.Connection")
> connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn.Open connStr
>
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
>
> sID = ID
>
> Conn.Execute sSql
> Conn.Close
> Set Conn=Nothing
>
> sID = sID + 1201
>
> Set conn2 = Server.CreateObject("ADODB.Connection")
> connStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("/data/users_fin.mdb")
> Conn2.Open connStr
>
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
> ("devUserName") & "'"
>
> Conn2.Execute sSql2 ' Update the record
> Conn2.Close ' Close the database connection ( this is always a good
> practice )
> Set Conn2=Nothing 'Clear the connection vaiable
>
> %>
>
Message #8 by "the Office of Brent Allen VanderMeide" <ccbbttmm@a...> on Fri, 23 Aug 2002 22:52:20 -0600
|
|
Is there a certain reason you want to set the ID. Is it so you can get
the ID Key after inserting a new user? If so, you are all going at this
the wrong way.
However; please explain why you are trying to change the ID of a record.
It defeats the purpose of databases. Well, at least the purpose of data
integrity.
If you are simply trying to figure out what the ID of a newly created
record is then simply ask and I'll send you the solution.
Message #9 by "Marko Ramstedt" <marko.ramstedt@a...> on Sun, 25 Aug 2002 15:42:19
|
|
Got it to work already. The whole idea with this code was that every new
user will get a userID numbered 5000+. Then later when he/she becomes
and VIP user, it drops to 2000+. In this way when initiating search of
all users and sort them by userID, the smaller numbers come listed first.
:-)
> Is there a certain reason you want to set the ID. Is it so you can get
the ID Key after inserting a new user? If so, you are all going at this
the wrong way.
However; please explain why you are trying to change the ID of a record.
It defeats the purpose of databases. Well, at least the purpose of data
integrity.
If you are simply trying to figure out what the ID of a newly created
record is then simply ask and I'll send you the solution.
|
|
|
View Cart
