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

 

[amorous]

Sure...but i didnt quiet understand you...

What kind of file is this and how is it structured...how does the data look inside this file...

-VJ

 

[edgarv]

ok the data is a field like the FECHNB field, it lives in the db2 not in my access database with user names passwords links and userid.
What I want to do is for example if I were to add a input box to my login and I enter a number lets say 2345 it would take the number and put it in my sql statement like it did with the Where FECHNB " &presentuserid& ""

so could I add
Where FECHNB " &presentuserid& "" and DHIVNB BETWEEN "myfield"" or something like that but it hast to grab it straight form the log in box

 

[amorous]

Sure you can do that...go ahead and give a shot and let me know if you get any errors...

i did not quite understand what you meant by

DHIVNB BETWEEN "myfield"" ??

i think you want myfield to come from the login file...

so if this is the case...first we have to read the log in file to get the string myfield

Anyways...first give it a shot your way and let me know the errors...


-VJ

 

[edgarv]

it did not work. any suggestions?

 

[amorous]

Show me your code and the error you got?

-VJ

 

[edgarv]

login.asp

<%=FormatDateTime(Now(),vbLongDate)%> </B>


<%
BackgroundColor="#AFD1F8"
BorderColor="#000080"


Content = "" 'Clear the Content string
QStr = Request.QueryString("login") 'Save the login querystring to QStr


Title = "Login"

if QStr="passfailed" then
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Wrong password</P><A href=Javascript:history.go(-1)>Back</A></td></tr>"
:history.go(-1)>Back</A><BR><BR><A HREF=login.asp>Cancel registration</A></td></tr>"
elseif QStr="namefailed" then
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Invalid username</P><A HREF=login.asp?login=createnew>Click here to create an acount</A><BR><BR><A HREF=Javascript:history.go(-1)>Back</A></td></tr>"
'elseif QStr="createnamefailed" then
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Invalid username</P><A HREF=Javascript:history.go(-1)>Back</A><BR><BR><A HREF=login.asp>Cancel registration</A></td></tr>"
'elseif QStr="creatednew" then
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Your account has been created</P><A HREF=login.asp>Login</A></td></tr>"
'elseif QStr="createnew" then
'Content = Content & "<form name=frmCreate method=POST action=create.asp>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Username: <input type=text name=txtUsername></td></tr>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Password: <input type=password name=txtPassword></td></tr>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Full name: <input type=text name=txtFullname></td></tr>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><input type=submit name=cmdSubmit value=Register></td></tr>"
'Content = Content & "</form>"
else
Content = Content & "<form name=frmMain method=POST action=verify.asp>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Username: <input type=text name=txtUsername></td></tr>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Password: <input type=password name=txtPassword></td></tr>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Date Range: <input type=text name=txtrange></td></tr>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><input type=submit name=cmdSubmit value=Login></td></tr>"
Content = Content & "</form>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><A HREF=login.asp?login=createnew>Click here to create an acount</A></td></tr>"
end if
Response.Cookies("ValidUser") = Validated
%>

<!-- Build the page with the table --><head>
<title>Shipped Orders Login</title>


<div align="center">
<p><img src="/images/dav1.jpg" width="376" height="178"> </head> <body link="<% Response.Write(BorderColor) %>" vlink="<% Response.Write(BorderColor) %>" alink="<% Response.Write(BorderColor) %>" text="<% Response.Write(BorderColor) %>">
</p>
<p><strong><font size="5">Shipped Orders Login</font></strong></p>
<p><br>
</p>
</div>
<div align="center">

<table border="2" cellspacing="5" bgcolor="<% Response.Write(BackgroundColor) %>" bordercolor="<% Response.Write(BorderColor) %>"width="250px">


<%
Response.Write("<tr><td valign=top align=center><b>" & Title & "</b></td></tr>")
Response.Write(Content) ' Paste the contents in the table
%>

</table>

<p>&nbsp;</p>
<p>&nbsp;</p>
<p>
<object classid="clsid27CDB6E-AE6D-11cf-96B8-444553540000" codebase="

http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0"

width="740" height="90">
<param name="movie" value="rpt.swf">
<param name="quality" value="high">
<embed src="rpt.swf" quality="high" pluginspage="

http://www.macromedia.com/go/getflashplayer"

type="application/x-shockwave-flash" width="740" height="90"></embed></object>
</p>
</div>

</body>


variables.asp


<%
Username = Request.Form("txtUsername")
Password = Request.Form("txtPassword")
daterange= Request.Form("txtrange")
'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 range, presentuserid
range=rs("range")
presentuserid=rs("userid")






strKeyField="FECHNB"
strKeyField2=""
strKeyField3=""
strTableName="[WEBQRY].[SHPORD]"
PageSize=20
session("pagesize") = PageSize
strLeftWrapper="["
strRightWrapper="]"
gstrOrderBy=""
bKeyFieldEditable1=False
bKeyFieldEditable2=False
bKeyFieldEditable3=False


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

'Where FECHNB='" &presentuserid& "'"


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



vDebug = false


Const FORMAT_NONE = ""
Const FORMAT_DATE_SHORT = "Short Date"
Const FORMAT_DATE_LONG = "Long Date"
Const FORMAT_DATE_TIME = "Time"
Const FORMAT_CURRENCY = "Currency"
Const FORMAT_HYPERLINK = "Hypelink"
Const FORMAT_EMAILHYPERLINK = "Email Hyperlink"
Const FORMAT_FILE_IMAGE = "File-based image"
Const FORMAT_DATABASE_IMAGE = "Database image"
Const FORMAT_LOOKUP_WIZARD = "Lookup wizard"

Const EDIT_FORMAT_NONE = ""
Const EDIT_FORMAT_TEXT_FIELD = "Text field"
Const EDIT_FORMAT_TEXT_AREA = "Text area"
Const EDIT_FORMAT_PASSWORD = "Password"
Const EDIT_FORMAT_DATE = "Date"
Const EDIT_FORMAT_RADIO = "Radio button"
Const EDIT_FORMAT_CHECKBOX = "Checkbox"
Const EDIT_FORMAT_DATABASE_IMAGE = "Database image"
Const EDIT_FORMAT_LOOKUP_WIZARD = "Lookup wizard"
Const EDIT_FORMAT_HIDDEN = "Hidden field"
Const EDIT_FORMAT_READONLY = "Readonly"

Const EDIT_DATE_SIMPLE = 0
Const EDIT_DATE_SIMPLE_DDMMYYYY_DP = 1
Const EDIT_DATE_SIMPLE_MMDDYYYY_DP = 2
Const EDIT_DATE_DDMMYYYY = 3
Const EDIT_DATE_DDMMYYYY_DP = 4
Const EDIT_DATE_MMDDYYYY = 5
Const EDIT_DATE_MMDDYYYY_DP = 6

Const LOGIN_HARDCODED = 0
Const LOGIN_TABLE = 1

Const ADVSECURITY_ALL = 0
Const ADVSECURITY_VIEW_OWN = 1
Const ADVSECURITY_EDIT_OWN = 2
Const ADVSECURITY_NONE = 3

Const ACCESS_LEVEL_ADMIN = "Admin"
Const ACCESS_LEVEL_USER = "User"
Const ACCESS_LEVEL_GUEST = "Guest"

cAdvSecurityMethod = 0
cLoginMethod = 0
cUserName = ""
cPassword = ""
cLoginTable = ""
cUserNameField = ""
cPasswordField = ""
cAdminUserID = ""
cNumberOfChars = 0
%>


so I want to get the input data in the Date Range field in the login.asp to go into the sql statement in the variables.asp

the reason is because it is easier to get the date this way I tried it with this sql statement and it works
select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where DHIVNB BETWEEN 1040701 AND 1040710 AND FECHNB = " &presentuserid& ""

the problem is that the date in here is hardcoded and I need it so the user can type it in.
that is why I was adding it to the login page. but if there is a better way to do it please let me know

 

[amorous]

OK

1. you have

daterange= Request.Form("txtrange")

How does the user enter the daterange in the form textbox??

2. Why dont you create two input boxes on the form for dates...one for StartDate and one for EndDate

Once you do that then you can write something like this
Where DHIVNB BETWEEN "&StartDate&" AND "&EndDate&"

3.
But if suppose if you dont want to create or if you cannot create two input fields for two dates then

i would like to what the user enters in the textbox you provided...

for example there can be many ways one can enter the daterange...
from01/01/2004to01/01/2005
02/02/2004-04/04/2004
and so on...
then obviously the below code wont work...

Where DHIVNB BETWEEN "&range&"

-VJ

 

[edgarv]

I get what you are saying but I need the user to type the date in the login.
I think I am confusing you with all this stuff I apologize for that. I am completly new to ASP and I got thrown into this.
So Please bear with me.

let's forget about the daterange for a second.

can I get the data from an input field and insert it on another asp page? if so how would I do that?

 

[amorous]

can I get the data from an input field and insert it on another asp page? if so how would I do that?

YES YOU CAN...

But how is the data entered in the input field...

Ok lets say for example...

I have two files...

Login.htm and ProcessLogin.Asp

on Login.htm page

we have a input textbox where the user can enter the date/name/or something else

then on the ProcessLogin.Asp page
i can get the value of the input field from Login.htm page like this...

dateenteredbyuser=Request.form("date")

-VJ

 

[edgarv]

ok I did this
Dim range, presentuserid

presentuserid=rs("userid")
range= Request.Form("txtrange")




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



it could not get the "txtrange" data typed by the user

 

[amorous]

if you did this:
range= Request.Form("txtrange")

how can you get "txtrange" ??

you will get "range"

because that the variable in which you are storing your value

-VJ

 

[edgarv]

should I do this then?

Dim range, presentuserid

presentuserid=rs("userid")
range= Request.Form("txtrange")




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


it does not give me the asp error, it goes to the page but it does not display it here is what it says
ASP error happened

Technical information
Error number -2147217900
Error description [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token was not valid. Valid tokens: ( + - ? : DAY RRN CASE CAST CHAR.
URL /shptest/SHPORD_list.asp
SQL query select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where DHIVNB BETWEEN and FECHNB =20

it does not see the "&range&" or "&txtrange&"

 

[amorous]

show me your code for the FORM where you have the field txtrange (where you can enter the date)

-VJ

 

[edgarv]

it is in the login.asp


<%=FormatDateTime(Now(),vbLongDate)%> </B>


<%
BackgroundColor="#AFD1F8"
BorderColor="#000080"


Content = "" 'Clear the Content string
QStr = Request.QueryString("login") 'Save the login querystring to QStr


Title = "Login"

if QStr="passfailed" then
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Wrong password</P><A href=Javascript:history.go(-1)>Back</A></td></tr>"
:history.go(-1)>Back</A><BR><BR><A HREF=login.asp>Cancel registration</A></td></tr>"
elseif QStr="namefailed" then
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Invalid username</P><A HREF=login.asp?login=createnew>Click here to create an acount</A><BR><BR><A HREF=Javascript:history.go(-1)>Back</A></td></tr>"
'elseif QStr="createnamefailed" then
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Invalid username</P><A HREF=Javascript:history.go(-1)>Back</A><BR><BR><A HREF=login.asp>Cancel registration</A></td></tr>"
'elseif QStr="creatednew" then
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Your account has been created</P><A HREF=login.asp>Login</A></td></tr>"
'elseif QStr="createnew" then
'Content = Content & "<form name=frmCreate method=POST action=create.asp>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Username: <input type=text name=txtUsername></td></tr>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Password: <input type=password name=txtPassword></td></tr>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Full name: <input type=text name=txtFullname></td></tr>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><input type=submit name=cmdSubmit value=Register></td></tr>"
'Content = Content & "</form>"




else
Content = Content & "<form name=frmMain method=POST action=verify.asp>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Username: <input type=text name=txtUsername></td></tr>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center>Password: <input type=password name=txtPassword></td></tr>"
Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Date Range: <input type=text name=txtrange></td></tr>"






Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><input type=submit name=cmdSubmit value=Login></td></tr>"
Content = Content & "</form>"
'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><A HREF=login.asp?login=createnew>Click here to create an acount</A></td></tr>"
end if
Response.Cookies("ValidUser") = Validated
%>

<!-- Build the page with the table --><head>
<title>Shipped Orders Login</title>


<div align="center">
<p><img src="/images/dav1.jpg" width="376" height="178"> </head> <body link="<% Response.Write(BorderColor) %>" vlink="<% Response.Write(BorderColor) %>" alink="<% Response.Write(BorderColor) %>" text="<% Response.Write(BorderColor) %>">
</p>
<p><strong><font size="5">Shipped Orders Login</font></strong></p>
<p><br>
</p>
</div>
<div align="center">

<table border="2" cellspacing="5" bgcolor="<% Response.Write(BackgroundColor) %>" bordercolor="<% Response.Write(BorderColor) %>"width="250px">


<%
Response.Write("<tr><td valign=top align=center><b>" & Title & "</b></td></tr>")
Response.Write(Content) ' Paste the contents in the table
%>

</table>

<p>&nbsp;</p>
<p>&nbsp;</p>
<p>
<object classid="clsid27CDB6E-AE6D-11cf-96B8-444553540000" codebase="

http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0"

width="740" height="90">
<param name="movie" value="rpt.swf">
<param name="quality" value="high">
<embed src="rpt.swf" quality="high" pluginspage="

http://www.macromedia.com/go/getflashplayer"

type="application/x-shockwave-flash" width="740" height="90"></embed></object>
</p>
</div>

</body>

 

[amorous]

Ok got it ---here is it:

Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><br>Date Range: <input type=text name=txtrange></td></tr>"

this is in the form on Login.asp page and you are posting it to "verify.asp" page...

Content = Content & "<form name=frmMain method=POST action=verify.asp>"

but your query code is in the page variables.asp

then how can you expect Request.Form("txtRange") to work on variables.asp page when you are actually posting it to verify.asp...

BUT if you want you can do this....

On verify.asp page

do something like this:

session("presentrange")=Request.Form("txtrange")

and on variables.asp page then you can do...

Where Where DHIVNB BETWEEN "session("presentrange")

let me know if you have any more questions...

-VJ

 

[edgarv]

I got this

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
variables.asp, line 42, column 213

thisis my line 42
gstrSQL = "select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where FECHNB " &presentuserid& " and DHIVNB BETWEEN "session("range")

 

[amorous]

it should be
gstrSQL = "select FECHNB, FECVNB, FECBTX, FEAAC3, FEAAC9, FEALDT, FEKNNB, DDAIDT, DDAITX, DDARQT, DDAAGP, EXT_PRICE From WEBQRY.SHPORD Where FECHNB =" &presentuserid& " and DHIVNB BETWEEN "&session("range")&" "

-VJ

 

[edgarv]

I have only one thing to say

YOU ARE THE KING!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

it works like a charm

Thank you so very very much

 

[amorous]

You are welcome...i am glad that you got it working in the end...


-VJ