SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This problem is so simple it's driving me nuts!!!

    I have a form to update a field... 2 pages... real easy...

    Form page code:
    <form method ="post" action="cpeupdated.asp">
    <font color="white">Type in the Courseno to change:
    <input type=text name=courseNo><br><br>
    cityinput type=text name=city><br>
    <input type=submit value="Go">
    </font></form>


    Update Page code:

    <%
    city=request.form("city")
    courseNo=request.form("courseNo")

    dbase="../fpdb/cpe.mdb"
    myconnection="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
    myconnection=myconnection & server.mappath(dbase)& ";"

    Set Conn=server.CreateObject("adodb.Connection")
    Conn.open myconnection
    SQL = "UPDATE courses SET city='"& city & "' where courseNo='"& courseNo & "'"
    Set rsRecords=conn.execute(SQL)
    response.redirect("cpeupdate.asp")
    %>

    Can Anyone help????????????

    Thanks!

  2. #2
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    Panhandle of Florida (White Sand/Brown Skin)
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im pretty new to ASP... but i think i might know the problem your having...

    You didnt post the error message but i assume its an SQL message?

    And if so then I think the problem is in the SQL statement:

    I assume that you have the field set to "number" and if so... then you cant put the single quotes around it (i believe).
    Because I was having the same exact problem yesterday and it drove me nuts for 30 minutes!

    Finally I had tried EVERYTHING i knew and then i see those quotes and figured... what the heck... I took em out and it worked...

    so try changing that code to this:
    Code:
    SQL = "UPDATE courses SET city='" & city & "' where courseNo=" & courseNo
    Hope that Helps!!
    Last edited by -SeFu-; May 18, 2001 at 14:07.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still not working... .

    You can see it at http://www.tscpa.com/cpe/CPEupdate_.asp.

    The message I now get is:

    Type mismatch

    The message I was getting was :

    Data type mismatch in criteria expression.

    both in line 12: Set rsRecords=conn.execute(SQL)

    Thanks for trying...

  4. #4
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dim all variables. This usually solves half the problem. Also, add this line to the top of your page:
    Code:
    <%Option Explicit%>
    This will solve most of your problems for the future with coding and misspelling. It's a must for any ASP document, in my opinion.

    Also, try not to use conn.Execute. I would recommend using a recordset to get the records. Like this:
    Code:
    <%dim RS1
    set RS1 = Server.CreateObject("ADODB.Recordset")
    SQL1="SELECT Blah blah blah, etc."
    RS1.Open SQL1, conn, 1, 1%>
    At this point, RS1 would contain all the records you selected. To reference a record, you simply say:
    Code:
    <%=RS1("FieldName")%>
    Hope this helps.

    Any more questions? Just post back here.

    'Till next time.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    #1) Make sure there are values in the two variables. that can cause problems.

    #2) Try changing the execute statement to: Conn.Execute (strSql). I've never seen a set variable= for an update

    If CourseNo is an integer, then you don't want quotes around the field. If it's alphanumeric, then you do....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wooohooo! Success....


    SeFu was right in the first case...just a lille left-over morsel I forgot to delete when implemenitng his solution.

    I also dim'ed all variables and add the Option explicit... I will try to do so forever....

    Thanks all!

  7. #7
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup, thats very important! Make that the first time you script. I usually make comments too, to help me remind myself if I'll side-tracked.
    Free Science Homework Help
    http://www.physicsforums.com

  8. #8
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    Panhandle of Florida (White Sand/Brown Skin)
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOOHOOO
    I cant wipe this smile off my face!!!
    my first successful ASP help!!!

    I can now consider myself a novice ASPer! AOOWEEE


    Id like to thank all the little people... without which I would never have reached this level...

  9. #9
    SitePoint Enthusiast pinkstar's Avatar
    Join Date
    May 2001
    Location
    in a gallaxy far, far away
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    to quote or not to quote

    Quotes give me this:

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

    [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

    /onlinepay2/query3.asp, line 25

    No quotes give me this:

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

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Field1 = & strAcct1 & AND Field2 = & strAcct2 &'.

    /onlinepay2/query3.asp, line 25

    Both Field1 and Field2 are integers that come from a form.

    Help?

    Toni

    Originally posted by -SeFu-
    Im pretty new to ASP... but i think i might know the problem your having...

    You didnt post the error message but i assume its an SQL message?

    And if so then I think the problem is in the SQL statement:

    I assume that you have the field set to "number" and if so... then you cant put the single quotes around it (i believe).
    Because I was having the same exact problem yesterday and it drove me nuts for 30 minutes!

    Finally I had tried EVERYTHING i knew and then i see those quotes and figured... what the heck... I took em out and it worked...

    so try changing that code to this:
    Code:
    SQL = "UPDATE courses SET city='" & city & "' where courseNo=" & courseNo
    Hope that Helps!!
    PS: All I need to do is select the table data for display on a page.
    Last edited by pinkstar; May 23, 2001 at 18:23.
    pb.

  10. #10
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm assuming you have a where clause?

    For all your numbers, to make sure they're numbers, use the CInt() function on them.

    numbers have no quotes, text has single quotes. You've already been told that here, I believe.

    I would just suggest the CInt() function and see where that leads you. here's how to use it:
    Code:
    dim var1
    'a number in string format
    var1 = "2345"
    'change the string to a number
    var1 = CInt(var1)
    Hope this helps!

    'Till next time......
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  11. #11
    SitePoint Enthusiast pinkstar's Avatar
    Join Date
    May 2001
    Location
    in a gallaxy far, far away
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Goober, thanks for the suggestion. It didn't work. I got the same error messages.

    Also, for me, var1 has to equal Request.Form("something from a form"), or even better: something from an authentication appliction.

    My login/account info is in three parts:

    1. Authenticate username/password.
    2. Authentication generates account number(s) to be used in SQL string.
    3. SQL string grabs account info for relevant account number(s).

    Oh yeah, I do have WHERE in my SQL string.

    Originally posted by goober
    I'm assuming you have a where clause?

    For all your numbers, to make sure they're numbers, use the CInt() function on them.

    numbers have no quotes, text has single quotes. You've already been told that here, I believe.

    I would just suggest the CInt() function and see where that leads you. here's how to use it:
    Code:
    dim var1
    'a number in string format
    var1 = "2345"
    'change the string to a number
    var1 = CInt(var1)
    Hope this helps!

    'Till next time......
    Last edited by pinkstar; May 23, 2001 at 18:48.
    pb.

  12. #12
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, a few things I've seen before I address the actual problem:

    1. Use Option Explicit and always dim your variables. It prevents spelling errors and is a must for any serious ASP developer.

    2. Whenever you redirect, Always add this line of code to the top of the page:
    Code:
    <%Response.Buffer = True%>
    Okay, now on to your problem. I believe the conn.execute is the problem. Try doing it this way:
    Code:
    <%Option Explicit
    dim city, courseNo, dbase, myConnection, Conn, SQL, rsRecords
    city=request.form("city") 
    courseNo=request.form("courseNo") 
    
    dbase="../fpdb/cpe.mdb" 
    myconnection="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" 
    myconnection=myconnection & server.mappath(dbase) & ";" 
    
    Set Conn=server.CreateObject("ADODB.Connection") 
    Set RSRecords = Server.CreateObject("ADODB.Recordset")
    Conn.open myconnection 
    SQL = "Select * FROM  courses WHERE courseNo='"& courseNo & "'"
    RSRecords.Open SQL, myconnection, adOpenKeyset, adLockOptimistic
    RSRecords.Fields("city") = city
    RSRecords.Update
    response.redirect("cpeupdate.asp") 
    %>
    Let me know how it works out.

    'Till next time!
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  13. #13
    SitePoint Enthusiast pinkstar's Avatar
    Join Date
    May 2001
    Location
    in a gallaxy far, far away
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    This works!

    I put something together that works for me! It's not exactly for the same kind of project, but it might help, especially if you have ASP 2.0 like me:

    http://www.sitepointforums.com/showt...threadid=23933 (scroll down)
    pb.


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
  •