Query String to Specify Link to use?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query String to Specify Link to use??

Author

Topic

mastajbl
Starting Member

42 Posts

Posted - 2009-04-27 : 15:39:17

So I am trying to figure out a way I can get the correct redirect from the database based on the username. For instance if userC currently logged in they would go to userA's link. I'm pretty sure i have to setup a query string to get the information for userC and than say to use that for the redirect. I bolded the two areas that pertain to the Response.Redirect; now would I add a string to one of these areas to achieve what i am looking to do?


<%@LANGUAGE="VBSCRIPT"%>
<!--#include virtual="/DCG/Connections/catalog.asp" -->
<%
Dim RS_Login
Dim RS_Login_cmd
Dim RS_Login_numRows

Set RS_Login_cmd = Server.CreateObject ("ADODB.Command")
RS_Login_cmd.ActiveConnection = MM_catalog_STRING
RS_Login_cmd.CommandText = "SELECT Access, Link FROM login" 
RS_Login_cmd.Prepared = true

Set RS_Login = RS_Login_cmd.Execute
RS_Login_numRows = 0
%>
<%
' *** Validate request to log in to this site.
MM_LoginAction = Request.ServerVariables("URL")
If Request.QueryString <> "" Then MM_LoginAction = MM_LoginAction + "?" + Server.HTMLEncode(Request.QueryString)
MM_valUsername = CStr(Request.Form("userId"))
If MM_valUsername <> "" Then
  Dim MM_fldUserAuthorization
  Dim MM_redirectLoginSuccess
  Dim MM_redirectLoginFailed
  Dim MM_loginSQL
  Dim MM_rsUser
  Dim MM_rsUser_cmd
  
  MM_fldUserAuthorization = "Access"
  MM_redirectLoginSuccess = RS_Login.Fields.Item("Link").Value
  
  MM_redirectLoginFailed = "jblcontact.html"

  MM_loginSQL = "SELECT UserID, Password"
  If MM_fldUserAuthorization <> "" Then MM_loginSQL = MM_loginSQL & "," & MM_fldUserAuthorization
  MM_loginSQL = MM_loginSQL & " FROM login WHERE UserID = ? AND Password = ?"
  Set MM_rsUser_cmd = Server.CreateObject ("ADODB.Command")
  MM_rsUser_cmd.ActiveConnection = MM_catalog_STRING
  MM_rsUser_cmd.CommandText = MM_loginSQL
  MM_rsUser_cmd.Parameters.Append MM_rsUser_cmd.CreateParameter("param1", 200, 1, 50, MM_valUsername) ' adVarChar
  MM_rsUser_cmd.Parameters.Append MM_rsUser_cmd.CreateParameter("param2", 200, 1, 50, Request.Form("Password")) ' adVarChar
  MM_rsUser_cmd.Prepared = true
  Set MM_rsUser = MM_rsUser_cmd.Execute

  If Not MM_rsUser.EOF Or Not MM_rsUser.BOF Then 
    ' username and password match - this is a valid user
    Session("MM_Username") = MM_valUsername
    If (MM_fldUserAuthorization <> "") Then
      Session("MM_UserAuthorization") = CStr(MM_rsUser.Fields.Item(MM_fldUserAuthorization).Value)
    Else
      Session("MM_UserAuthorization") = ""
    End If
    if CStr(Request.QueryString("accessdenied")) <> "" And false Then
      MM_redirectLoginSuccess = Request.QueryString("accessdenied")
    End If
    MM_rsUser.Close
    Response.Redirect(MM_redirectLoginSuccess)
  End If
  MM_rsUser.Close
  Response.Redirect(MM_redirectLoginFailed)
End If
%>

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 15:47:38

RS_Login_cmd.CommandText = "SELECT Access, Link FROM login"

You aren't specifying which user you want the link for??
So MM_redirectLoginSuccess = RS_Login.Fields.Item("Link").Value
is getting the first row's value.

It should be something like
RS_Login_cmd.CommandText = "SELECT Access, Link FROM login WHERE UserID = '" & MM_valUsername& "'"

Make sure to put the variables as command parameters.

mastajbl
Starting Member

42 Posts

Posted - 2009-04-27 : 15:49:59

Wow...I knew that...its been a long day thanks a lot. Ill do that an let you know how it goes.

mastajbl
Starting Member

42 Posts

Posted - 2009-04-27 : 16:20:16

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/jblproduction/loginauth2BU.asp, line 31

I added in the cmd parameter, I think I did it right, And now I am receiving the above error message.

[CODE]
<%
Dim RS_Login
Dim RS_Login_cmd
Dim RS_Login_numRows

Set RS_Login_cmd = Server.CreateObject ("ADODB.Command")
RS_Login_cmd.ActiveConnection = MM_catalog_STRING
RS_Login_cmd.CommandText = "SELECT Access, Link FROM login WHERE UserID = '" & MM_valUsername & "'"
RS_Login_cmd.Prepared = true
RS_Login_cmd.Parameters.Append RS_Login_cmd.CreateParameter("param3", 200, 1, 50, MM_valUsername) ' adVarChar

Set RS_Login = RS_Login_cmd.Execute
RS_Login_numRows = 0
%>
[/CODE]

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 17:42:58

The problem is that you're assigning a value to the MM_valUsername variable AFTER the link recordset is opened.

Move this code to the top:
MM_valUsername = CStr(Request.Form("userId"))

Also, youre parameters are wrong. Even though you added a parameter to the command, you did not define it properly in the SQL string. It should be:
RS_Login_cmd.CommandText = "SELECT Access, Link FROM login WHERE UserID = @param3"

mastajbl
Starting Member

42 Posts

Posted - 2009-04-28 : 08:49:27

Ok I moved MM_valUsername = CStr(Request.Form("userId")) to the top and updated the SQL string and am now receiving this error. Could this be due to my connection string? I haven't had a problem with any other functions as far as searching and displaying records using it, but I did find that some people mention this error is due to not running the connection with microsoft Jet??

****Edit
I changed my connection string to use jet and still received teh same error message.
****

Microsoft VBScript compilation error '800a03ea'

Syntax error

/jblproduction/loginauth2BU.asp, line 15

response.Write(Set RS_Login = RS_Login_cmd.Execute)
---------------^

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

/jblproduction/loginauth2BU.asp, line 15

[CODE]
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_catalog_STRING
MM_catalog_STRING = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/DCG/Db/JBL_DB.mdb")

%>
[/CODE]

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 09:23:18

The error has to do with the sql string. Post the SQL string that the line response.Write(Set RS_Login = RS_Login_cmd.Execute) is using.

mastajbl
Starting Member

42 Posts

Posted - 2009-04-28 : 09:25:49

<%
Dim RS_Login
Dim RS_Login_cmd
Dim RS_Login_numRows

Set RS_Login_cmd = Server.CreateObject ("ADODB.Command")
RS_Login_cmd.ActiveConnection = MM_catalog_STRING
RS_Login_cmd.CommandText = RS_Login_cmd.CommandText = "SELECT Access, Link FROM login WHERE UserID = @param3"
RS_Login_cmd.Prepared = true
RS_Login_cmd.Parameters.Append RS_Login_cmd.CreateParameter("param3", 200, 1, 50, MM_valUsername) ' adVarChar

Set RS_Login = RS_Login_cmd.Execute
RS_Login_numRows = 0
%>

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:27:52

response.Write("Set RS_Login = RS_Login_cmd.Execute")

E 12°55'05.63"
N 56°04'39.26"

mastajbl
Starting Member

42 Posts

Posted - 2009-04-28 : 09:33:14

Peso,
This was the message it sent back to me after adding in the quotes.

Set RS_Login = RS_Login_cmd.Execute
Microsoft VBScript runtime error '800a01a8'

Object required: ''

/jblproduction/loginauth2BU.asp, line 32

This is my line 32.

MM_redirectLoginSuccess = RS_Login.Fields.Item("Link").Value

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 09:36:10

There are two errors in that code.

Microsoft VBScript compilation error '800a03ea'
You can't do the following:
response.Write(Set RS_Login = RS_Login_cmd.Execute)

RS_Login_cmd.Execute returns a recordset, not a scalar value so you simply cannot write it. You have to iterate through the recordset to get the "link" required. For an example:

Set RS_Login = RS_Login_cmd.Execute
Do While Not RS_Login.EOF
MM_redirectLoginSuccess = RS_Login("Link")
RS_Login.MoveNext
Loop
Set RS_Login = Nothing
Response.write MM_redirectLoginSuccess

The second error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Post the code where this error is occurring (prefably the SQL string).

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 09:38:01

quote:
Originally posted by Peso

response.Write("Set RS_Login = RS_Login_cmd.Execute")

E 12°55'05.63"
N 56°04'39.26"

??????????????
That will not work. His not writing a string. His trying to write a recordset.

mastajbl
Starting Member

42 Posts

Posted - 2009-04-28 : 09:53:30

wait that line of code...i'm not trying to write at all, it was a suggestion from a co-worker to see what it gives me back. What it needs to do is work so when a user logs in they are redirected to the page that is specific for there username in the link column. What was initially happening was that the link being used was only the first field in the link column because the SQL SELECT Access, Link FROM login did specify the WHERE userID = MM_ValUsername.

What would happen if I said userID =(Request.Form("userID" & 'username')) <-- Don't think i typed that right as it isnt working for me now.

mastajbl
Starting Member

42 Posts

Posted - 2009-04-28 : 09:58:02

and line 15 whitefang is this code.

Set RS_Login = RS_Login_cmd.Execute

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 10:03:12

The code is really messy and use very poor techniques.
Anyways, do the following and it should fix your problem:

'Move this line to the top
MM_valUsername = CStr(Request.Form("userId"))

'Add the if condition to check for empty usernames passed in
If MM_valUsername <> "" Then

Set RS_Login_cmd = Server.CreateObject ("ADODB.Command")
RS_Login_cmd.ActiveConnection = MM_catalog_STRING
RS_Login_cmd.CommandText = "SELECT Access, Link FROM login WHERE UserID = @param3"
RS_Login_cmd.Parameters.Append RS_Login_cmd.CreateParameter("param3", 200, 1, 50, MM_valUsername) ' adVarChar

RS_Login_cmd.Prepared = true

Set RS_Login = RS_Login_cmd.Execute
RS_Login_numRows = 0

Do While Not RS_Login.EOF
'Get the link for this username
MM_redirectLoginSuccess = RS_Login("Link")
RS_Login.MoveNext
Loop
Set RS_Login = Nothing

End If

'Get rid of this line
MM_redirectLoginSuccess = RS_Login.Fields.Item("Link").Value

mastajbl
Starting Member

42 Posts

Posted - 2009-04-28 : 10:32:37

Thanks whitefang for all your help on this. I had to change @param3 to ? and everything worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources