|
 |
access_asp thread: Fetching the max ID number, adding 1 to it and updating the record set, but how?
Message #1 by "Marko Ramstedt" <marko.ramstedt@a...> on Thu, 22 Aug 2002 15:54:08
|
|
Hi,
Got this code, but it doesn't read the max number from the database.
The value of ID seems to be 0 all the time. What could be wrong?
<%
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 "Rob Parkhouse" <rparkhouse@o...> on Fri, 23 Aug 2002 01:03:57
|
|
> Hi,
> Got this code, but it doesn't read the max number from the database.
T> he value of ID seems to be 0 all the time. What could be wrong?
> <%
> Dim sTopic
D> im sQuestion
D> im sSql
D> im Conn
D> im ConnStr
D> im sSql2
D> im Conn2
D> im ConnStr2
D> im ID
D> im sID
> Set conn = Server.CreateObject("ADODB.Connection")
c> onnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("/data/users_fin.mdb")
C> onn.Open connStr
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
> sID = ID
> Conn.Execute sSql
C> onn.Close
S> et Conn=Nothing
> sID = sID + 1201
> Set conn2 = Server.CreateObject("ADODB.Connection")
c> onnStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("/data/users_fin.mdb")
C> onn2.Open connStr
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
(> "devUserName") & "'"
> Conn2.Execute sSql2 ' Update the record
C> onn2.Close ' Close the database connection ( this is always a good
p> ractice )
S> et Conn2=Nothing 'Clear the connection vaiable
> %>
Hi Marko,
you are trying to assign the value of the maximum ID to variable sID
before you execute the SQL. The SQL you have should work but could be a
bit simpler. Try something like:
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
sSql = "SELECT Max(ID) AS MaxID FROM Users"
objRS.Open sSql ,Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
sID = objRS!MaxID
Conn.Close
Set Conn=Nothing
Set objRS = Nothing
sID = sID + 1201
then the rest of your code
Best regards
Message #3 by Karri Peterson <KPeterson@C...> on Fri, 23 Aug 2002 08:17:40 -0500
|
|
try "SELECT MAX(ID) From Users"
-----Original Message-----
From: Rob Parkhouse [mailto:rparkhouse@o...]
Sent: Thursday, August 22, 2002 8:04 PM
To: Access ASP
Subject: [access_asp] Re: Fetching the max ID number, adding 1 to it and
updating the record set, but how?
> Hi,
> Got this code, but it doesn't read the max number from the database.
T> he value of ID seems to be 0 all the time. What could be wrong?
> <%
> Dim sTopic
D> im sQuestion
D> im sSql
D> im Conn
D> im ConnStr
D> im sSql2
D> im Conn2
D> im ConnStr2
D> im ID
D> im sID
> Set conn = Server.CreateObject("ADODB.Connection")
c> onnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("/data/users_fin.mdb")
C> onn.Open connStr
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
> sID = ID
> Conn.Execute sSql
C> onn.Close
S> et Conn=Nothing
> sID = sID + 1201
> Set conn2 = Server.CreateObject("ADODB.Connection")
c> onnStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("/data/users_fin.mdb")
C> onn2.Open connStr
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
(> "devUserName") & "'"
> Conn2.Execute sSql2 ' Update the record
C> onn2.Close ' Close the database connection ( this is always a good
p> ractice )
S> et Conn2=Nothing 'Clear the connection vaiable
> %>
Hi Marko,
you are trying to assign the value of the maximum ID to variable sID
before you execute the SQL. The SQL you have should work but could be a
bit simpler. Try something like:
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
sSql = "SELECT Max(ID) AS MaxID FROM Users"
objRS.Open sSql ,Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
sID = objRS!MaxID
Conn.Close
Set Conn=Nothing
Set objRS = Nothing
sID = sID + 1201
then the rest of your code
Best regards
Message #4 by "Shawn Clabough" <shawnc@u...> on Sat, 24 Aug 2002 00:35:33
|
|
Why not do it in one step?
sSql="Update Users Set id = (Select MAX(ID) + 1201 From Users) WHERE
UserName = '" & session("devUserName") & "'"
Shawn
> Hi,
> Got this code, but it doesn't read the max number from the database.
T> he value of ID seems to be 0 all the time. What could be wrong?
> <%
> Dim sTopic
D> im sQuestion
D> im sSql
D> im Conn
D> im ConnStr
D> im sSql2
D> im Conn2
D> im ConnStr2
D> im ID
D> im sID
> Set conn = Server.CreateObject("ADODB.Connection")
c> onnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("/data/users_fin.mdb")
C> onn.Open connStr
> sSql = "SELECT ID FROM Users WHERE ID=(SELECT max(ID) FROM Users)"
> sID = ID
> Conn.Execute sSql
C> onn.Close
S> et Conn=Nothing
> sID = sID + 1201
> Set conn2 = Server.CreateObject("ADODB.Connection")
c> onnStr2 = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
S> erver.MapPath("/data/users_fin.mdb")
C> onn2.Open connStr
> sSql2="Update Users Set id = " & sID & " WHERE UserName = '" & session
(> "devUserName") & "'"
> Conn2.Execute sSql2 ' Update the record
C> onn2.Close ' Close the database connection ( this is always a good
p> ractice )
S> et Conn2=Nothing 'Clear the connection vaiable
> %>
|
|
|
View Cart
