SELECT staffid, firstname, lastname, locationid, userlevelid, login, password FROM staff WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#' The error message comes back and says: SQL SELECT staffid, firstname, lastname, locationid, userlevel FROM staff WHERE login = 'dkemens' AND password = 'password' DATASOURCE wcyswe1_wcys05 - So it's passing everything over, and I just checked the value names and table again, there's no typos there. :confused; [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => dkemens [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 6 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Here's incl/logincheck.cfm: SELECT staffid, firstname, lastname, locationid, userlevelid FROM staff WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#' UPDATE staff SET lastlogin='#DateFormat(Now(),"mm-dd-yyyy")#,#TimeFormat(Now(), "h:mmtt")#' WHERE staffid=#SESSION.auth.staffid# It's all pretty straight forward and I *can* query the database manually from excel and have verified it's connection in the cf admin [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => dkemens [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 19 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Query not working - Macromedia ColdFusion

Query not working - Macromedia ColdFusion

My providor is running cf6.1. I have a query that should work, but doesn't. It's pretty standard and i am horribly confused. Essentially, it states: SELECT staffid, firstname, lastname, locationid, userlevelid FROM staff WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#' I've verified all of the tables, fields, and the odbc link. It's telling me this, though, upon running the query: "Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. " Any ideas?...

  1. #1

    Default Query not working

    My providor is running cf6.1. I have a query that should work, but doesn't.
    It's pretty standard and i am horribly confused. Essentially, it states:

    SELECT staffid, firstname, lastname, locationid, userlevelid
    FROM staff
    WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#'

    I've verified all of the tables, fields, and the odbc link.

    It's telling me this, though, upon running the query:

    "Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver] Too few parameters. Expected 1. "

    Any ideas?


    dkemens Guest

  2. #2

    Default Query not working

    My providor is running cf6.1. I have a query that should work, but doesn't.
    It's pretty standard and i am horribly confused. Essentially, it states:

    SELECT staffid, firstname, lastname, locationid, userlevelid
    FROM staff
    WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#'

    I've verified all of the tables, fields, and the odbc link.

    It's telling me this, though, upon running the query:

    "Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver] Too few parameters. Expected 1. "

    Any ideas?


    dkemens Guest

  3. #3

    Default Re: Query not working

    I think, and I could be wrong, but if you're comparing "login =
    #form.userlogin# AND password = #form.userpassword#" I think you have to add
    that to your SELECT statement - like so:

    SELECT staffid, firstname, lastname, locationid, userlevelid, login, password
    FROM staff
    WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#'

    Give that a shot and see what happens.

    HTH

    Defuse Guest

  4. #4

    Default Re: Query not working

    Hm, thanks for the tip -but unfortunately; no change. This should be so simple,
    but I'm completely dumbfounded - not so much as I'm sure I will be when I find
    the problem, as I'm sure it's just as simple as this should be.

    dkemens Guest

  5. #5

    Default Re: Query not working

    It is a basic query. Microsoft says this about that error: This error occurs
    only with Microsoft Access when one of the column names specified in a select
    statement does not exist in the table being queried

    Of course, you stated that you already checked all of the fields and what not.
    Is that query you posted exactly like the one you have?

    Defuse Guest

  6. #6

    Default Re: Query not working

    Yep, here's the exact code

    <!--- Make sure we've got a username and password --->
    <cfparam name="FORM.userlogin" type="string">
    <cfparam name="FORM.userpassword" type="string">

    <!--- check against db --->
    <cfquery name="getuser" datasource="#datasource#">
    SELECT staffid, firstname, lastname, locationid, userlevelid, login,
    password
    FROM staff
    WHERE login = '#FORM.userlogin#' AND password = '#FORM.userpassword#'
    </cfquery>

    The error message comes back and says:

    SQL SELECT staffid, firstname, lastname, locationid, userlevel FROM staff
    WHERE login = 'dkemens' AND password = 'password'
    DATASOURCE wcyswe1_wcys05

    - So it's passing everything over, and I just checked the value names and
    table again, there's no typos there.
    :confused;

    dkemens Guest

  7. #7

    Default Re: Query not working

    Have you checked your error logs? They usually provide more info than the
    browser output. Other than that, I don't see anything wrong with your SQL.
    Although, I am a bit concerned about your <cfparam> tags. They're named
    "Form.userlogin" but what's their default value?

    Defuse Guest

  8. #8

    Default Re: Query not working

    First of all, you do not need to include login or password columns in your
    SELECT statement if you don't plan on using them in your output, even though
    the columns exist in your WHERE clause. Also, what happens if you replace all
    of the column names in the SELECT with the * wildcard instead? This would
    eliminate a bogus selected column name causing the problem.

    Phil

    paross1 Guest

  9. #9

    Default Re: Query not working

    try escaping some reserved words:
    WHERE [login] = '#FORM.userlogin#' AND [password] = '#FORM.userpassword#'

    HTH
    --
    Tim Carley
    [url]www.recfusion.com[/url]
    [email]infoNOSPAMINGrecfusion.com[/email]
    Mountain Lover Guest

  10. #10

    Default Re: Query not working

    Oye!

    Replaced the selected fields with a wildcard and it brings the same thing back
    as though I hadn't made the change - the logs say only this:

    "Error","jrpp-181","04/08/05","12:54:02","WCYSSTAFF","Error Executing Database
    Query.[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
    Microsoft Access Driver] Too few parameters. Expected 1. The specific sequence
    of files included or processed is: D:\hshome\wcysweb\staff.wcys.com\index.cfm "

    dkemens Guest

  11. #11

    Default Re: Query not working

    What are the data types for the "login" and "password" fields?
    Defuse Guest

  12. #12

    Default Re: Query not working

    login and password are of type 'text' and I tried escaping the login and
    password with no change. - In fact, i can change login or password in the query
    to anything, like toilet and paper, and it doesn't make a difference. I'm a
    lamp guy usually (php/mysql) and don't know much about windows -- so this may
    be completely rediculous -

    Since the select and where fields are fine, and the from is asking for the
    right table, and the username and password from the form are being passed over
    just as they're typed -- wouldn't this imply that it can't even connect to the
    database? Could it possible be permissions? Admin, system, and my ftp account
    all have full control...

    This would imply

    dkemens Guest

  13. #13

    Default Re: Query not working

    If you couldn't could connect to the database, it would have told you in the Admin section of CF when you were creating the datasource.
    Defuse Guest

  14. #14

    Default Re: Query not working

    Good point. I just double checked and verefied the connection to the database in the cf admin. Ugh this is preposterous. By the way; thanks for your help, everybody.
    dkemens Guest

  15. #15

    Default Re: Query not working

    First, ensure that your datasource is pointing where you think it is. Then,
    take the exact query, copy and paste it into the Access Query tool, and run it
    directly. Once you get it running in the Access query tool, then copy and paste
    it back into your CF code, replacing #FORM.login# and #FORM.password# as
    appropriate.

    Some other things you can check are the case of the table name and column
    names. You can simplify things for testing and replace your select list with
    the wildcard. You could try removing your WHERE clause, thus supposedly getting
    back everything in the table (if this doesn't work, then your datasource is
    wrong or the table name is wrong).

    Kronin555 Guest

  16. #16

    Default Re: Query not working

    I checked a very similar query that I run which has never failed. The only
    difference was the word FORM is in lower case. That may make a difference. My
    code: <CFquery name = 'getfac' datasource = 'wumentor'> select
    facidno,facpass,facfname,faclname from faclist where facidno =
    '#form.facidno#' and facpass = '#form.facpass#' </CFquery> Hopes this helps.
    I am running CF 6.1 with MySQL for the database.

    Dr.Data Guest

  17. #17

    Default Re: Query not working

    I've checked all of this. I wish it was that simple, Kronin. I agree that in
    any other situation, it would be... but there's some other variable here. I
    noticed something intriguing just now. It says the error is on line 15 - which
    is where the 'where' clause was. So I took the where clause out - therefore the
    </cfquery> tag was on line 15 and guess what - error was still on line 15, - so
    then I put 20 blank lines betwee </cfquery> and the following text and then the
    error was on [now] blank: line 15. This seems kind of odd to me... I could
    assume it's just windows living up to its name, but I would assume macromedia
    to be better than that.

    dkemens Guest

  18. #18

    Default Re: Query not working

    Do you have any corresponding code in the Application.cfm template? Anything that has to do with the login process?
    Defuse Guest

  19. #19

    Default Re: Query not working

    Here's application.cfm::

    <!--- Set application wide variables --->
    <cfset datasource = "wcyswe1_wcys05">
    <cfset companyname = "Warehouse Creek Yacht Sales, Inc.">
    <cfset erroremail = "maryfriend.ly.net">
    <cfset approot = "http://staff.wcys.com/">

    <cfapplication name="WCYSSTAFF"
    applicationtimeout="#CreateTimeSpan(0,2,0,0)#"
    sessionmanagement="yes"
    clientmanagement="yes">

    <!--- Check if user is logged in --->
    <cfif NOT IsDefined("SESSION.Auth.IsLoggedIn")>
    <!--- If user is submitting their info., let's validate --->
    <cfif IsDefined("FORM.userlogin")>
    <cfinclude template="incl/logincheck.cfm">
    </cfif>

    <cfinclude template="incl/loginform.cfm">
    <cfabort>
    </cfif>


    Here's incl/logincheck.cfm:

    <!--- Make sure we've got a username and password --->
    <cfparam name="FORM.userlogin" type="string">
    <cfparam name="FORM.userpassword" type="string">

    <!--- check against db -->
    <CFquery name="getuser" datasource="#datasource#">
    SELECT staffid, firstname, lastname, locationid, userlevelid FROM staff WHERE
    login = '#FORM.userlogin#' AND password = '#FORM.userpassword#'
    </CFquery>


    <!--- If record found, set status and remember some details --->
    <cfif getuser.recordcount EQ 1>
    <cfset SESSION.auth = structnew()>
    <cfset SESSION.auth.isloggedin = "yes">
    <cfset SESSION.auth.staffid = getuser.staffid>
    <cfset SESSION.auth.firstname = getuser.firstname>
    <cfset SESSION.auth.lastname = getuser.lastname>
    <cfset SESSION.auth.userlevelid = getuser.userlevelid>
    <cfset SESSION.auth.locationid = getuser.locationid>

    <!--- update the db to reflect this login date --->
    <cflock name="wcysstaffloginupdate" type="exclusive" timeout="10">
    <cfquery datasource="#datasource#">
    UPDATE staff
    SET lastlogin='#DateFormat(Now(),"mm-dd-yyyy")#,#TimeFormat(Now(),
    "h:mmtt")#'
    WHERE staffid=#SESSION.auth.staffid#
    </cfquery>
    </cflock>

    <!--- now lets send the user back to where they were --->
    <cflocation url="#CGI.SCRIPT_NAME#">
    </cfif>


    It's all pretty straight forward and I *can* query the database manually from
    excel and have verified it's connection in the cf admin


    dkemens Guest

  20. #20

    Default Re: Query not working

    I wonder if those param tags are interferring in any way. I dont' know if CF
    has an issue with naming a param tag with a form variable prepended. Try this:

    <cfparam name="user" default="#form.userlogin#">
    <cfparam name="pwd" default="#form.userpassword#">

    Just trying to come up with some solutions.

    Defuse Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Acces Query not working
    By rubeel in forum Coldfusion - Getting Started
    Replies: 7
    Last Post: September 9th, 11:59 PM
  2. PHP Update query not working
    By sharky92663 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 6th, 02:06 AM
  3. Help me I have been working all day trying to get thisUpdate Query to work
    By murpg in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 28th, 03:52 AM
  4. sum in query not working. Help please.....
    By Joe Science in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 14th, 05:53 PM
  5. recordset query not working
    By Don Pacino in forum Dreamweaver AppDev
    Replies: 5
    Last Post: April 2nd, 09:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •