pass a variable 1

[stpmtp]

Hello, I ran into a little problem.
I have a login page where the user enters his/her user name and his/her password. up to this point everything works well. Then there are two text boxes where you can enter a date for example 010106 to 013106 and here is where I run into the problem I would like to take what the user types in the text boxes and insert it in my SQL statement.
Would I have to do do this. my date fields are called dt1 and dt2

dim dt1, dt2
dt1= Request.Form("txtdt1")
dt2= Request.Form("txtdt2")

select username,   userid,   date  From Users  Where date BETWEEN "&dt1&" and  "&dt2&" "

But this does not seem to work

 

[DotNetGnat]

what database are you using...try this:

select username,   userid,   date  From Users  Where date BETWEEN '"&dt1&"' and  '"&dt2&"' "

for access db

select username,   userid,   date  From Users  Where date BETWEEN #"&dt1&"# and  #"&dt2&"# "

-DNG

 

[stpmtp]

it is an access DB but I don't need to store the value of the date I only want to pass it to the sql statement

 

[DotNetGnat]

for the sql query to run you should have the correct syntax that access db accepts...

-DNG

 

[stpmtp]

the thing is that if I hard code the date in the asp page then it works

here is my login page

<%
	'Green colorset
	'BackgroundColor="#C9DDB3"
	'BorderColor="#006600"
	
	'Blue colorset
	BackgroundColor="#AFD1F8"
	BorderColor="#000080"
	
	'Purple colorset
	'BackgroundColor="#FDC8F2"
	'BorderColor="#800080"
	
	Content = ""							'Clear the Content string
	QStr = Request.QueryString("login")		'Save the login querystring to QStr

	'if ucase(left(QStr,6))="CREATE" then 
		'Title = "Register"
	'else
		Title = "Login"
	'end if
	
	'The code below saves the contents the table must have in the variable Content
	'The content depends on what's in the QueryString
		
	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>"
	'elseif QStr="createpassfailed" then		
		'Content = Content & "<tr><td valign=top bordercolor="& BackgroundColor &" align=center><P>Wrong password</P><A href=Javascript: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=txtdt1>and<input type=text name=txtdt2></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>  

<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>


  </div>
		
</body>

and here is the verify page

<% 'Copyright © 2004 Edgar Villanueva
	'Save the entered username and password
	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 ("dbforrma.mdb")
	set rs = server.CreateObject ("ADODB.Recordset")		
	'Open record with entered username
	rs.Open "SELECT * FROM users where username='"& Username &"'", conn, 1 
	 Response.Cookies("ValidUser") = Validated
	'If there is no record with the entered username, close connection
	'and go back to login with QueryString
	If rs.recordcount = 0 then
		rs.close
		conn.close
		set rs=nothing
		set conn=nothing
		Response.Redirect("login.asp?login=namefailed")
	end if
	
	'If entered password is right, close connection and open mainpage
	if rs("password") = Password then
		Session("Username") = Username
		Response.Redirect(RS("link"))

	'If entered password is wrong, close connection 
	'and return to login with QueryString
	else
		rs.Close
		conn.Close
		set rs=nothing
		set conn=nothing
		Response.Redirect("login.asp?login=passfailed")
	end if

then this redirects me to the page with my sql statement

gstrSQL = "select username,   userid,   date  From Users  Where date BETWEEN #"&dt1&"# and  #"&dt2&"# "

the # and the ' did not work

 

[theniteowl]

Where are you passing in the date values?
Your form has txtdt1 and txtdt2 fields but you do not request them in your verify page.
In your sql statement page you use dt1 and dt2 rather than txtdt1, txtdt2 but there is no shown method for passing the data to this page to use.

Unless the data is persisted somehow in code you have not shown then there is no way dt1 and dt2 have a value when your SQL statement runs.

And as always, you should do a response.write gstrSQL after you set the gstrSQL value so you can see if the query is formatted the way you expect it to be. That would show you if there were missing values or problems with syntax.




Stamp out, eliminate and abolish redundancy!

 

[stpmtp]

ok, so how can I pass the value of dt1 and dt2 directrly into my sql page?

 

[DotNetGnat]

what you have to do is...on your verify page...

session("mydate1")=request.form("txtdt1")
session("mydate2")=request.form("txtdt2")

then on the sql query page...do this:

gstrSQL = "select username, userid, date From Users Where date BETWEEN #"&session("mydate1")&"# and #"&session("mydate1")&"# "

-DNG

 

[stpmtp]

thank you so very much DotNetGnat YOU ARE THE KING!!!!!
I am not worthy!!!!

 

[DotNetGnat]

no problem...glad to be of help

-DNG

 

[stpmtp]

DotNetGnat

do you do anykind of freelance work?

 

[DotNetGnat]

did you look at my profile...if not click on my id.

-DNG