Please help.

I wanted to show records (computer details) for a specific user automatically when he logs in. The user accounts (i.e. username and password) are found on tbl_users of the database while computer details (i.e. Monitor, CPU, Keyboard, etc.) are found on tbl_Accountdtls.

I have 3 asp pages,

1.login.asp

<input name="user" type="text" id="user" value="guest">
<input name="password" type="password" id="password" value="guest">

2.process.asp

<%
StrUser = Request.Form("user")
%>

<%
'connection to sql server
...
SQLStmt.CommandText = "SELECT * FROM tbl_Users WHERE tbl_Users.username='" & StrUser & "'"
...
'rest of the scripts to redirect to main.asp or admin.asp for users
'and admin respectively, if username and password is correct. Or
'Response.Redirect("login.asp?Error=Incorrect username or password")
'and Response.Redirect("login.asp?Error=Admin session was time out")
'no problem here
%>

3.main.asp

<%
'the problem lies here
...
SQLStmt.CommandText = "SELECT * FROM tbl_Accountdtls, tbl_Users WHERE tbl_Accountdtls.ID=tbl_Users.ID"

'Im using the field ID to make a relationship between tbl_Accountdtls
'and tbl_Users to link the logged user to his specific computer
'recordset in the tbl_Accountdtls table.
'this is where i think i got it all wrong, i may have to use session but don't know how to go about it.
...
%>

Monitor<%=RS("AccMonitor")%>



Please help. Thank you all :)

I've modified my scripts to the ff:

--process.asp
'added the ff
Session("userID") = RS("ID")
Session("userName") = RS("username")


--main.asp
'added the ff
strUserName = Session("userName")
userID = Session("userID")

SQLStmt.CommandText = "SELECT tbl_Accountdtls.AccMonitor, tbl_Accountdtls.AccKeyboard, tbl_Accountdtls.AccMouse, tbl_Accountdtls.AccCPU, tbl_Accountdtls.AccCDRom, tbl_Accountdtls.AccModem, tbl_Accountdtls.AccPrograms, tbl_Accountdtls.AccPrinter, tbl_Accountdtls.AccOthers FROM tbl_Accountdtls, tbl_Users WHERE tbl_Users.ID =" & userID

'but still displays the first recordset in the table
'i may be getting close already, something is just wrong in the sql
'statement

Monitor<%=RS("AccMonitor")%>

Please help :)

Hi,

How are the user & accounts tables related - do you have a userid field or similar in the accounts table?

You need to create a join between the tables when querying e.g.
HTH,

Chris

Greetings from me. I have just joined this forum

This is what you have written
**************************************************
SQLStmt.CommandText = "SELECT tbl_Accountdtls.AccMonitor, tbl_Accountdtls.AccKeyboard, tbl_Accountdtls.AccMouse, tbl_Accountdtls.AccCPU, tbl_Accountdtls.AccCDRom, tbl_Accountdtls.AccModem, tbl_Accountdtls.AccPrograms, tbl_Accountdtls.AccPrinter, tbl_Accountdtls.AccOthers FROM tbl_Accountdtls, tbl_Users WHERE tbl_Users.ID =" & userID
************************************************** ****

Just Make a correction as follows
[ i assume that id in tdl_accounts and userid in other table are the joining fields]
************************************************** *************
SQLStmt.CommandText = "SELECT tbl_Accountdtls.AccMonitor, tbl_Accountdtls.AccKeyboard, tbl_Accountdtls.AccMouse, tbl_Accountdtls.AccCPU, tbl_Accountdtls.AccCDRom, tbl_Accountdtls.AccModem, tbl_Accountdtls.AccPrograms, tbl_Accountdtls.AccPrinter, tbl_Accountdtls.AccOthers FROM tbl_Accountdtls WHERE tbl_Accountdtls.ID =" & session("userID")
************************************************** **************
That's it. Simple.

Anubhav Kumar

Hi mikersantiago, I don't see any necessity to use tbl_Users table in the above query,as you weren't listing any of the columns from tbl_Users table. So why should that be used in FROM and WHERE clauses? Is there a column that depicts the USERID within tbl_Accountdtls? If so rephrase your last part of the query as give below. That should help you out of this.

Cheers!

_________________________
- Vijay G
Strive for Perfection