Problem inserting a autonumber value from a recordset to another table as text

[Dave Posh]

The problem I'm having is I'm trying to insert a number value (UserID)
obtained from the first SQL1 query from a table called User. In the
Access database the column UserID is a autonumber. I'm taking that
value and storing it as a variable in ASP using vbscript.

Set RSlookup = conn.execute(SQL1)
UserID = RSlookup("UserID")

This works because when I test it using a response.write UserID it
displays the value I want.

However, when I run SQL2 which tries to insert the Use rid variable
into another table called Connections as text all of the data inserts
except the Userid.

When it test the query by changing the variable to Userid = 1234 the
insert statemnet works inserting all the data.

I believe my problem lies with the UserID = RSlookup("UserID"), the
UserID is not being stored as text or a number.


Note: Im using Access 2003 database is in 2000 format


************************************************** ***************


SQL1 = "SELECT * FROM User WHERE Name='"&Name&"'"

SQL2 = "INSERT INTO Connections (UserID, InternalIP, ExternalIP,
TimeofConnection, DateOfConnection) Values("_
& "'" & UserID & "', "_
& "'" & local & "', "_
& "'" & external & "', "_
& "'" & timeofuser & "', "_
& "'" & dateofuser& "')"

'Queries the table Users and then stores UserID from a recordset into
a variable called UserID

conn.open connstring
Set RSlookup = conn.execute(SQL1)
UserID = RSlookup("UserID")
conn.close

'Inserts Info including the UserID from UserID variable obtain by SQL1
conn.open connstring
conn.execute(SQL2)
conn.close

[Bob Barrows [MVP]]

Dave Posh wrote:

>
> ************************************************** ***************
>
>
> SQL1 = "SELECT * FROM User WHERE Name='"&Name&"'"
>
> SQL2 = "INSERT INTO Connections (UserID, InternalIP, ExternalIP,
> TimeofConnection, DateOfConnection) Values("_
> & "'" & UserID & "', "_

At this point, UserID HAS NO VALUE

:-)

Move this code to after the line of code where you give UserID a value.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron [SQL Server MVP]]

> SQL1 = "SELECT * FROM User WHERE Name='"&Name&"'"

>
> SQL2 = "INSERT INTO Connections (UserID, InternalIP, ExternalIP,
> TimeofConnection, DateOfConnection) Values("_
> & "'" & UserID & "', "_
> & "'" & local & "', "_
> & "'" & external & "', "_
> & "'" & timeofuser & "', "_
> & "'" & dateofuser& "')"

Where are all of these values populated??? Do they come from the User
table, some other part of the ASP script, thin air?

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Dave Posh]

Here is my full code.

<%
dim conn
dim connstring

Set conn = Server.CreateObject("ADODB.Connection")
connstring = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=C:\databasefile\rcontrol.mdb"
%>

<%
Function WriteToDB(IP)
dim SQL1
dim SQL2
dim SQL3
dim RSlookup
dim UserID
dim local
dim external
dim Name
dim Branch
dim ext


dim dateofuser
dim timeofuser

local = IP
Name = Request.form("nametxt")
Branch = Request.form("branchtxt")
ext = Request.form("exttxt")



external = Request.ServerVariables("REMOTE_ADDR")

dateofuser = date()
timeofuser = time()


SQL1 = "SELECT * FROM User WHERE Name='"&Name&"'"

SQL2 = "INSERT INTO Connections (UserID, InternalIP, ExternalIP,
TimeofConnection, DateOfConnection) Values("_
& "'" &UserID& "', "_
& "'" & local & "', "_
& "'" & external & "', "_
& "'" & timeofuser & "', "_
& "'" & dateofuser& "')"

SQL3 = "INSERT INTO User (Name, Branch, Phone, DateofUser, TimeOfUser)
Values("_
& "'" & Name & "', "_
& "'" & Branch & "', "_
& "'" & ext & "', "_
& "'" & dateofuser & "', "_
& "'" & timeofuser & "')"

conn.open connstring

Set RSlookup = conn.execute(SQL1)

if RSlookup.eof then
conn.execute(SQL3)
conn.close

conn.open
Set RSlookup = conn.execute(SQL1)
UserID = RSlookup("UserID")
conn.close

conn.open connstring
response.write userid
conn.execute(SQL2)

conn.close

else

UserID = RSlookup("UserID")
conn.close
conn.open connstring
response.write userid
conn.execute(SQL2)
conn.close

end if



dim fs, f
set fs = Server.CreateObject("Scripting.FileSystemObject")
set f = fs.CreateTextFile("c:\website\batch\"&Name&".bat", true)
f.WriteLine("c:\progra~1\radmin\radmin.exe /connect:"&local&"
/Through:"&external )

f.Close
set f=nothing
set fs=nothing

response.write "<a href=""batch/"&Name&".bat"">"&Name&"</a></p>"


end function

[Bob Barrows [MVP]]

What's the problem? I didn't make myself clear enough?

Look, you can't create SQL2 until AFTER you open the recordset on SQL1 and
assign a value to UserID. Is that clearer? Like this:

SQL1 = " ... "
Set RSlookup = conn.execute(SQL1)
UserID = RSlookup("UserID")

SQL2 = " ... "
etc.

Bob Barrows

Dave Posh wrote:

> Here is my full code.
>
> <%
> dim conn
> dim connstring
>
> Set conn = Server.CreateObject("ADODB.Connection")
> connstring = "DRIVER={Microsoft Access Driver
> (*.mdb)};DBQ=C:\databasefile\rcontrol.mdb"
> %>
>
> <%
> Function WriteToDB(IP)
> dim SQL1
> dim SQL2
> dim SQL3
> dim RSlookup
> dim UserID
> dim local
> dim external
> dim Name
> dim Branch
> dim ext
>
>
> dim dateofuser
> dim timeofuser
>
> local = IP
> Name = Request.form("nametxt")
> Branch = Request.form("branchtxt")
> ext = Request.form("exttxt")
>
>
>
> external = Request.ServerVariables("REMOTE_ADDR")
>
> dateofuser = date()
> timeofuser = time()
>
>
> SQL1 = "SELECT * FROM User WHERE Name='"&Name&"'"
>
> SQL2 = "INSERT INTO Connections (UserID, InternalIP, ExternalIP,
> TimeofConnection, DateOfConnection) Values("_
> & "'" &UserID& "', "_
> & "'" & local & "', "_
> & "'" & external & "', "_
> & "'" & timeofuser & "', "_
> & "'" & dateofuser& "')"
>
> SQL3 = "INSERT INTO User (Name, Branch, Phone, DateofUser, TimeOfUser)
> Values("_
> & "'" & Name & "', "_
> & "'" & Branch & "', "_
> & "'" & ext & "', "_
> & "'" & dateofuser & "', "_
> & "'" & timeofuser & "')"
>
> conn.open connstring
>
> Set RSlookup = conn.execute(SQL1)
>
> if RSlookup.eof then
> conn.execute(SQL3)
> conn.close
>
> conn.open
> Set RSlookup = conn.execute(SQL1)
> UserID = RSlookup("UserID")
> conn.close
>
> conn.open connstring
> response.write userid
> conn.execute(SQL2)
>
> conn.close
>
> else
>
> UserID = RSlookup("UserID")
> conn.close
> conn.open connstring
> response.write userid
> conn.execute(SQL2)
> conn.close
>
> end if
>
>
>
> dim fs, f
> set fs = Server.CreateObject("Scripting.FileSystemObject")
> set f = fs.CreateTextFile("c:\website\batch\"&Name&".bat", true)
> f.WriteLine("c:\progra~1\radmin\radmin.exe /connect:"&local&"
> /Through:"&external )
>
> f.Close
> set f=nothing
> set fs=nothing
>
> response.write "<a href=""batch/"&Name&".bat"">"&Name&"</a></p>"
>
>
> end function

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Dave Posh]

Thanks Bob, works great!