ASP and SQL

Discussion in 'ASP General' started by Athmaus, Jun 2, 2005.

  1. Athmaus

    Athmaus Guest

    I have this login script for a certain portion of our website for a while,
    and have around ~7500 users in a database that the script accesses.

    I added two new columns in this table, the reason for this is because i
    found a new trick for adding more security for the section of the site that
    this script is protecting. Problem is that now that I have added thee two new
    colums, the script does not write in any information in these two columns.

    I have copied the script and made a test table and everythign works, and the
    infromation is added in those two new columns.

    Does anyone know what might be preventing the sciprt from writing in these
    tables? Or can you not add columns to a SQL table once it is in use (and that
    would make no sense to me if it was like that)

    I wish i could provide more information other than posting up the script, as
    i get no errors at all.

    Any help would be greatly appreciated. Thanks!
     
    Athmaus, Jun 2, 2005
    #1
    1. Advertising

  2. Athmaus wrote:
    > I have this login script for a certain portion of our website for a
    > while, and have around ~7500 users in a database that the script
    > accesses.
    >
    > I added two new columns in this table, the reason for this is because
    > i found a new trick for adding more security for the section of the
    > site that this script is protecting. Problem is that now that I have
    > added thee two new colums, the script does not write in any
    > information in these two columns.
    >
    > I have copied the script and made a test table and everythign works,
    > and the infromation is added in those two new columns.
    >
    >
    > I wish i could provide more information other than posting up the
    > script, as i get no errors at all.
    >
    > Any help would be greatly appreciated. Thanks!


    At least post the portion of the script that is supposed to write the
    information to the database. (we do not need to see any html - we only need
    to see the vbscript code that performs the data insertion)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 2, 2005
    #2
    1. Advertising

  3. Athmaus

    Athmaus Guest

    Here is the code, it works on a fresh database that i setup, but it is not
    working on the already established database wehre i put 2 new columns in




    If Session("login") = FALSE Then
    Response.Redirect "http://www.yahoo.com"
    Else

    Dim myconn, verify, blnLoggedIn, user, pass, site, logged, objRS, exceeded
    Set verify = Server.CreateObject("ADODB.Connection")
    verify.open = "connection string"
    Set myconn = Server.CreateObject("ADODB.Connection")
    myconn.open = "connection string"

    'Response.Write(Session("username"))
    'Response.Write(Session("password"))

    user = CStr(Session("username"))
    pass = CStr(Session("password"))

    exceeded = 5

    Set objRS = myconn.execute("SELECT id, download, totaldl FROM regfreeup
    WHERE username='" & user & "' AND pass='" & pass & "';")

    If objRS.EOF Then '''NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
    blnLoggedIn = False
    Response.Redirect "http://www.yahoo.com"

    Else
    If objRS("download") >= exceeded Then 'LOGGED IN AN ABNORMAL TIME
    blnLoggedIn = false
    Response.Redirect "http://www.google.com"

    Else '''EVERYTHING PASSED PROCEEDE WITH DOWNLOAD
    blnLoggedIn = True
    verify.execute("UPDATE regfreeup set download = (download + 1) , totaldl
    = (totaldl + 1) WHERE username='" & user & "' AND pass='" & pass & "';")

    Response.Redirect "http://www.ps2.ign.com"

    objRS.Close
    Set objRS= Nothing
    myconn.Close
    Set myconn= Nothing
    verify.Close
    Set verify= Nothing

    End If
    End If
    End If

    "Bob Barrows [MVP]" wrote:

    > Athmaus wrote:
    > > I have this login script for a certain portion of our website for a
    > > while, and have around ~7500 users in a database that the script
    > > accesses.
    > >
    > > I added two new columns in this table, the reason for this is because
    > > i found a new trick for adding more security for the section of the
    > > site that this script is protecting. Problem is that now that I have
    > > added thee two new colums, the script does not write in any
    > > information in these two columns.
    > >
    > > I have copied the script and made a test table and everythign works,
    > > and the infromation is added in those two new columns.
    > >
    > >
    > > I wish i could provide more information other than posting up the
    > > script, as i get no errors at all.
    > >
    > > Any help would be greatly appreciated. Thanks!

    >
    > At least post the portion of the script that is supposed to write the
    > information to the database. (we do not need to see any html - we only need
    > to see the vbscript code that performs the data insertion)
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >
     
    Athmaus, Jun 2, 2005
    #3
  4. Athmaus wrote:
    > Here is the code, it works on a fresh database that i setup, but it
    > is not working on the already established database wehre i put 2 new
    > columns in
    >
    >
    >
    >
    > If Session("login") = FALSE Then
    > Response.Redirect "http://www.yahoo.com"
    > Else
    >
    > Dim myconn, verify, blnLoggedIn, user, pass, site, logged, objRS,
    > exceeded Set verify = Server.CreateObject("ADODB.Connection")
    > verify.open = "connection string"


    Hopefully you are using a sqloledb connection string ...
    http://www.aspfaq.com/show.asp?id=2126

    > Set myconn = Server.CreateObject("ADODB.Connection")
    > myconn.open = "connection string"


    Why two connection objects? Are these separate database servers? If not,
    only one connection is needed. Don't be wasteful of your network and server
    rewources.

    >
    > 'Response.Write(Session("username"))
    > 'Response.Write(Session("password"))
    >
    > user = CStr(Session("username"))
    > pass = CStr(Session("password"))
    >
    > exceeded = 5
    >
    > Set objRS = myconn.execute("SELECT id, download, totaldl FROM
    > regfreeup WHERE username='" & user & "' AND pass='" & pass & "';")
    >
    > If objRS.EOF Then '''NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
    > blnLoggedIn = False


    Bad technique here. Always close and destroy your ADO objects when finished
    with them. The lines of code appearing after a redirect will NOT get
    executed.

    > Response.Redirect "http://www.yahoo.com"
    >
    > Else
    > If objRS("download") >= exceeded Then 'LOGGED IN AN ABNORMAL TIME
    > blnLoggedIn = false
    > Response.Redirect "http://www.google.com"
    >
    > Else '''EVERYTHING PASSED PROCEEDE WITH DOWNLOAD
    > blnLoggedIn = True
    > verify.execute("UPDATE regfreeup set download = (download + 1) ,
    > totaldl = (totaldl + 1) WHERE username='" & user & "' AND pass='" &
    > pass & "';")
    >
    > Response.Redirect "http://www.ps2.ign.com"
    >
    > objRS.Close
    > Set objRS= Nothing
    > myconn.Close
    > Set myconn= Nothing
    > verify.Close
    > Set verify= Nothing
    >
    > End If
    > End If
    > End If


    My recommendations:
    1. to facilitate debugging, comment out the redirects
    2. Insert some response.write statements so you can follow the execution of
    the code.
    3. When using dynamic sql, assign your sql statements to variables so they
    can be written to response for debugging
    4. Use indenting
    5. Use parameters
    6. Use stored procedures to minimize the trips to the database

    Here is how I would rewrite this code:

    I would first create a stored procedure on your server, like this:

    CREATE PROCEDURE VerifyUser (
    @user varchar(50),
    @pass varchar(50),
    @limit int) AS
    IF NOT EXISTS (SELECT * FROM regfreeup WHERE
    username= @user AND pass = @pass)
    RETURN 1
    DECLARE @downloads int
    SET @downloads = (SELECT download FROM regfreeup
    WHERE username= @user AND pass = @pass)
    IF @downloads > @limit
    RETURN 2
    UPDATE regfreeup set download = (download + 1) ,
    totaldl= (totaldl + 1)
    WHERE username= @user AND pass = @pass
    IF @@ERROR =0
    RETURN 0
    ELSE
    RETURN 3


    Then, in ASP, I would use a Command object as follows

    <%
    Dim myconn, retVal, user, pass, site, logged, exceeded
    dim sURL

    If Session("login") = FALSE Then
    sURL = "http://www.yahoo.com"
    Response.Write "Not Logged In. <BR>"
    Else
    Set myconn = CreateObject("ADODB.Connection")
    myconn.open = "connection string"
    user = CStr(Session("username"))
    pass = CStr(Session("password"))
    exceeded = 5

    set cmd=createobject("adodb.command")
    arParms = array(user,pass)
    cmd.commandtext="VerifyUser"
    cmd.ActiveConnection = myconn
    set params = cmd.Parameters
    params.append cmd.CreateParameter("RETURN_VALUE", _
    3,4)
    params.append cmd.CreateParameter("@user", _
    200,1,50,user)
    params.append cmd.CreateParameter("@pass", _
    200,1,50,pass)
    params.append cmd.CreateParameter("@limit", _
    3,1,,exceeded)
    cmd.Execute ,,129
    retVal = params(0).value
    select case retVal
    case 0
    sURL="http://www.ps2.ign.com"
    Response.Write "No problems. <BR>"
    case 1
    sURL = "http://www.yahoo.com"
    Response.Write "No problems. <BR>"
    case 2
    sURL = "http://www.google.com"
    Response.Write "Improper login. <BR>"
    case 3
    sURL = "http://www.microsoft.com"
    Response.Write "The update failed. <BR>"
    end select
    set params=nothing
    set cmd=nothing
    myconn.close: set myconn=nothing
    End If
    Response.Write "Redirecting to " &
    Server.htmlencode(sURL)
    'Response.Redirect sURL
    %>


    When finished debugging, comment out the response.writes and uncomment the
    redirect.

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Jun 2, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ecoolone
    Replies:
    0
    Views:
    764
    ecoolone
    Jan 3, 2008
  2. Ed Garcia
    Replies:
    4
    Views:
    190
    Guinness Mann
    Aug 7, 2003
  3. Belinda
    Replies:
    4
    Views:
    365
    Bob Barrows [MVP]
    Jun 11, 2004
  4. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    206
    Jeff Cochran
    Sep 22, 2004
  5. Replies:
    10
    Views:
    278
    Bob Barrows [MVP]
    Jan 6, 2005
Loading...

Share This Page