asp sql problem 2

[edgarv]

Hello,

I was wondering if it is possible to add an rs to an sql statement here is what i mean

gstrSQL = "select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where FECHNB=rs("Username")"

because I could do

gstrSQL = "select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where FECHNB = bob"
but I would have to create a bunch of pages for each user.

how would I go about doing this?


thanks

Edgar V

 

[newbie404]

'You could split it into two actions...

gstrSQL = "select <whatever is going to get you the username>"
conn.execute(gstrSQL)
myFECHNB=rs("Username")

' then use the returned value in another query

gstrSQL = "select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where FECHNB='" & myFECHNB & "'

 

[edgarv]

but the problem is that I am getting that I want to get the user name from the login.asp
for example if user bob enters his name and password it would only give bobs page

 

[amorous]

Then i guess you should use a session variable...something like this:

Where FECHNB='" & Session("Username")& "'"


-VJ

 

[newbie404]

I don't get the problem but one easy way of achieving this would be to have bobs page stored in the database in the record that has his username and passowrd combination. That way bob enteres his username and password. Say the username is "bobjones", password is "bobspassword" and page is "bobshomepage".
Then in your dB have a table PASSWORDS with
username, password, and url fields. For bobs record the data would be as above. So when he clicks the submit page you would run

pwrd=request.form("password")
user=request.form("username")

SQLstmt="SELECT url FROM PASSWORDS WHERE password='" & pwrd & "' AND username='" & user "'"
SET rs = conn.execute(SQLstmt)
IF NOT rs.EOF THEN
server.redirect(rs("url"))
Else
response.write ("Incorrect login")
End If

 

[edgarv]

Thank you so very much Amorous it worked like a charm.
newbie404 your input was also outstanding.

Once again thanks you guys.

 

[edgarv]

one more question
could I use another field instead of the user name?here is what I mean
right now what i have is
username password link
bob bob123 default.asp (here is where my sql statement lives)

could I do

username password idnumber link
bob bob123 2545 default.asp

and sql statement Where FECHNB='" & Session("idnumber")& "'"
???????

 

[amorous]

YES you can do BUT you need another change too
I mean
and sql statement Where XXXXX='" & Session("idnumber")& "'"

because your previous field FECHNB refers to usernames and i guess there should be another field XXXXX that refers to the idnumbers...so you have to use that field...

if you use FECHNB='"&Session("idnumber")&"'...you will get no results....

-VJ

 

[edgarv]

the thing is that FECHNB is the idnumber, but they are simple like 10,20,etc.
I could do so they logi in user id 10 and password letmein
but I wanted to give it an name

 

[amorous]

You can use either the idnumber or name...but be sure to use the correct relationship in your query...I mean match a number to a number or a name to a name...

-VJ

 

[edgarv]

I am still confused about this, but could I do an if statement in the asp page
or how could I tell the login page that when bob types bob and his password it should get bob's id and put it at the end of the sql statement?
Where FECHNB='" & Session("Username")& "'"
or something like this??

 

[edgarv]

could I do this at the end of my sql statement

Where FECHNB='" if (Request.Form("name")) {
Response.Write ("This is a test")"'"

 

[amorous]

I am not sure what you actually want to do...can you please elaborate more on this...

-VJ

 

[edgarv]

ok
I have a login page that redirects the user depending on user name and password
username password link
bob bob123 /commissions.asp

in the commissions.asp page there is a sql statement
Where FECHNB=10" (10would be bobs id) and that is how i get the page. What I was trying to avoid doing is building about 100 commission pages whith different sql statements
Where FECHNB=20"
Where FECHNB in (30,40)", etc.
so you gave me Where FECHNB='" & Session("Username")& "'"
and it works but I would have to type 10 instead of bob to get the correct sql statement, because there is nothing relating the number 10 and bob in my database.
so my question was could I add a userid to my access database where I have my username password link? so it lookis like
username userid password link?

and then add something at the end of my sql statement?
like
Where FECHNB='" if (Request.Form("name")) {
Response.Write ("This is a test")"'"
or something like that.
is there another way to go about doing this or would I have to create a commissions.asp page for each user?

 

[amorous]

Ok if i got you write...

Previously you had something like this:

username password link
bob bob123 /commissions.asp
john john123 /johnspage.asp

and you wrote

Where FECHNB='" & Session("Username")& "'"

so who ever is logged in his username will be fed to the query and he will be directed to the appropriate link...

Now scenario too...

you want to change your table to...

username userid password link
bob 10 bob123 /commissions.asp
john 20 john123 /johnspage.asp

ok now...

you want to change the query to something like below

Where FECHNB='" & Session("userid")& "'"

NOW THE THING here is you can only do the above query if you first get the userid of the corresponding person who logged in...

so what you have to do is...

you need one more query...

"SELECT userid FROM yourtable Where Where FECHNB='" & Session("Username")& "'"

what we are doing here is getting the userid of the user who is logged in for the particular session...then


So your code shud look similar to something like this...

Set newrs= Server.CreateObject("ADODB.RecordSet")
newrs.Open "SELECT userid FROM yourtable Where Where FECHNB='" & Session("Username")& "'", conn

Dim presentuserid

presentuserid=newrs("userid")

'Then your next query comes where in you do this in your where clause

Where FECHNB='" &presentuserid& "'"


hope something is clear to you now...

if you need any other suggestions or if my approach was wrong...let me know...

-VJ

 

[edgarv]

Almost right
the usernames, passwords and link are in an access database

the commisions.asp links to a DB2 database that is why the sql statement
I tried the code you wrote but it gives me an error

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/commissions.asp,
line 4 and line 4 is
Dim presentuserid

 

[amorous]

Please post your complete code....

-VJ

 

[edgarv]

I got it I tried this and it worked






Username = Request.Form("txtUsername")
Password = Request.Form("txtPassword")

'Build connection with database
set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("shipped.mdb")
set rs = server.CreateObject ("ADODB.Recordset")

'rs.Open "SELECT userid FROM yourtable Where Where FECHNB='" & Session("Username")& "'", conn
rs.Open "SELECT userid FROM userlist where username='" & Session("Username")& "'", conn, 1
Dim presentuserid

presentuserid=rs("userid")




then on the sql statement

gstrSQL = "select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where FECHNB " &presentuserid& ""


the way i set up the userid was


username userid password link

bob =10 bob123 /commissions.asp
sam in (30,40) sam234 /commissions.asp , etc.


thank you so very much for all your help, with out it I would still be lost.

 

[amorous]

edgarv,
You are welcome. I am glad that you got it working...

-VJ

 

[edgarv]

could I bother you again amorous?
ok we have all that stuff worked out, if I wanted my sql statement to return with whatever I input in the log in file how would I go about it. Here is what I mean

log in file has
___________
username| | if I wanted to get the user name
|___________| straight from the login with out
using my database. how would I go
about doing this. Coul it be done?
___________
password | |
|___________|