Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Quote Originally Posted by SteveH View Post
    Yes, I can see a pattern - it's repeated apart from those fields which have some kind of numeric attribute such as telephone number or date.
    That is *NOT* the reason we use Replace( text, "'","''" ).

    We do that to
    (a) Prevent SQL injection. I'm not going to discuss that here. You can google for it. Find dozens if not hundreds of references.

    (b) Allow for apostrophes in the text fields.
    If you did *NOT* do that, then when you tried to do an INSERT such as:
    Code:
    INSERT INTO table (field) VALUES('I don't like cheese.')
    You would get an error. Because the database would see that query as
    Code:
    INSERT INTO table (field) VALUES('I don'      t like cheese.')
    And it would have no idea at all what to do with t like cheese.'

    The rule in Access and SQL Server for EMBEDDING apostrophes in database fields is the same as is the rule in VBScript for embedding quote marks: Use two in a row to signify one embedded mark.

    So you *WANT* the query to look like
    Code:
    INSERT INTO table (field) VALUES('I don''t like cheese.')
    Because then the text IN THE DATABASE will indeed be I don't like cheese.

    You can read more here:
    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=137
    and here
    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=27
    (WOW! I can't believe I wrote that THIRTEEN YEARS ago next week! Double wow.)

    By the by, as old as those ASPFAQs are, they are still almost all applicable. You could do worse than to read through all of them.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  2. Users who have thanked Old Pedant for this post:

    SteveH (09-16-2013)

  3. #17
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Yes, I have often thought about that with names such as O'Brien, that kind of thing. I have saved those pages, OP, and will go through them carefully.

    Unfortunately, the server still doesn't like the script as I am getting a 'Data type mismatch' error. I have checked the fields in the database and checked that each line of code corresponds to the attributes (text, numeric, etc) of that field.

    Are you one of the 4 guys from Rolla? I used to post on that board sometimes - the ASP Message Board, isn't it? Bill W if I remember correctly.

    Thanks again for the links.

  4. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Yes, I'm Bill. Show me your updated code.

    Have you debugged by trying the query directly in Access? Sometimes Access will give you a better message than the JET engine will.

    Also, show me which line you are getting the error from, just in case.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  5. #19
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    You get about Bill, don't you?! As long as you enjoy what you do. Nice to meet you again.

    I have taken another look at the database and zero-length strings are allowed for all (relevant) fields and no field is required to be completed. This is the (stripped) code I have for the table displayed on the Web page here:

    http://www.bayingwolf.com/display_recordsTEST1.asp followed by the script (same page, all one file) to add records:
    Code:
    					
    <% 
    
     Dim Connection, Recordset
     Dim sSQL, sConnString
     Dim fullName, wolfID, telNo, address, email, description, received, action, dispatched
    
    sSQL="SELECT TOP 10 ID, fullName, wolfID, telNo, address, email, description, received, action, dispatched FROM tblWolf"
    
     sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & "E:\client.mdb"
    
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
     connection.Open sConnString
    
     recordset.Open sSQL,connection
    
    response.write "<table class='myTable'>"
    %>
    
    <%
    
     If Recordset.EOF Then
     Response.Write "<tr><td>No records returned.</td></tr>"
     Else
    
    Do While Not recordset.EOF
    
    Response.Write "<tr><td>" & recordset("ID") & "</td>"
    Response.Write "<td>" & recordset("fullName") & "</td>"
    Response.Write "<td>" & recordset("wolfID") & "</td>"
    Response.Write "<td>" & recordset("telNo") & "</td>"
    Response.Write "<td>" & recordset("address") & "</td>"
    Response.Write "<td>" & recordset("email") & "</td>"
    Response.Write "<td>" & recordset("description") & "</td>"
    Response.Write "<td>" & recordset("received") & "</td>"
    Response.Write "<td>" & recordset("action") & "</td>"
    Response.Write "<td>" & recordset("dispatched") & "</td></tr>" 
    
     Recordset.MoveNext
     Loop
    
     response.write "</table>"
     End If
    %>
    
    <%
    Dim conn, rs, my_fullName, my_wolfID, my_telNo, my_address, my_email, my_description, my_received, my_action, my_dispatched
    
    my_fullName = Replace( Request.Form("fullName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_address = Replace( Request.Form("address"), "'", "''" )
    my_email = Replace( Request.Form("email"), "'", "''" )
    my_description = Replace( Request.Form("description"), "'", "''" )
    my_received = CDATE( Request.Form("received") )
    my_action = Replace( Request.Form("action"), "'", "''" )
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    
    
    sSQL="INSERT INTO tblWolf " &_
     "(fullName, wolfID, telNo, address, email, description, received, [action], dispatched)" &_
     " VALUES ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "#, '" & my_action & "', '" & my_dispatched & "')"
    
    connection.Execute sSQL
    
    Response.write "<div align='center'><br>The record has been added.</div>"
    
     connection.Close
     
     Set connection = Nothing
    %>
    Add records form goes here

    The error is showing as being on this line: connection.Execute sSQL:

    Microsoft JET Database Engine error '80040e07'

    Data type mismatch in criteria expression.

    /display_recordsTEST1.asp, line 171
    Many thanks again.

  6. #20
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    That's fine, but WHICH is line 171???

    Note: "in criteria expression" *usually* means the error is in part of the WHERE clause, but I see no WHERE clause in any of the code you posted there. ANd there certainly aren't 171 lines in that code you posted, either.

    Also, I used that page to add a dummy record and got NO ERROR at all.

    Have we not talked about DEBUG DEBUG DEBUG??

    For EXAMPLE ONLY:
    Code:
    sSQL="INSERT INTO tblWolf " &_
     "(fullName, wolfID, telNo, address, email, description, received, [action], dispatched)" &_
     " VALUES ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "#, '" & my_action & "', '" & my_dispatched & "')"
    
    Response.Write "<hr/>DEBUG SQL:<br/>" & sSQL & "<hr/>" & vbNewLine
    
    connection.Execute sSQL
    So now, when you do get the error, at least you will know the contents of the query that caused the error.

    An alternative way to do it:
    Code:
    sSQL="INSERT INTO tblWolf " &_
     "(fullName, wolfID, telNo, address, email, description, received, [action], dispatched)" &_
     " VALUES ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "#, '" & my_action & "', '" & my_dispatched & "')"
    
    On Error Resume Next
        connection.Execute sSQL
        If Err.Number > 0 Then
            Response.Write "<hr/>ERROR from query: " & Err.Description & "<br/>SQL:<br/>" & sSQL & "<hr/>" 
            Response.End
        End If
    On Error GoTo 0
    
    Now you will only get the debug info if you do have a failure.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  7. #21
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Line 171 is this:

    connection.Execute sSQL

    If I do this:

    Response.Write "<hr/>DEBUG SQL:<br/>" & sSQL & "<hr/>" & vbNewLine

    exactly where you have done and upload the page I get the error that you see here:

    http://www.bayingwolf.com/display_recordsTEST1.asp

    That was an imaginary record that I tried to add a couple of hours ago.

  8. #22
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    OK, I think I know the problem. I had:

    Code:
    sSQL="SELECT TOP 10 ID, fullName, wolfID, telNo, address, email, description, received, action, dispatched FROM tblWolf"
    and got errors.

    I have now changed it to 20, and saw your record added.

    Do you think that was it?

  9. #23
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Nope, that TOP 20 vs. TOP 10 would have NOTHING WHATSOEVER to do with the problems you are having.

    I have tried various types of purposeful errors in the <form> fields, and I can get errors that I can explain, but I can *NOT* get the error on the conn.Execute sSQL line that you say you get.

    One more time: Put in some debug or I can't possibly help you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  10. #24
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    By the by, one major problem you have is that you are doing the INSERT sql operation *after* you display the existing records. So the new record you added will *NEVER* show up until you come back to the page.

    You truly need to reverse those two operations. Do the INSERT at the very top of the page and then do the SELECT *after* that.

    And if you would do "...ORDER BY ID DESC" in your SELECT, you would see the most recently added records first, including the one you just added if you put the INSERT first.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  11. #25
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    And, finally, you are doing no VALIDATION whatsoever of the Request.Form data.

    If you are going to be the only person to ever use that, I guess that's okay. But if you expect other people to use it, you need to rewrite this *A LOT* to do some form field validation.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  12. #26
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I have done the On Error Resume Next

    The conn.Execute sSQL error has disappeared. I can add records via the form. And if I refresh the page, I can see the record in the table.

  13. #27
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    Well, the ON ERROR stuff didn't FIX the problem. All it was there for was to DETECT the problem.

    So you must have done something else that corrected the actual problem.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  14. #28
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I will try this tomorrow:

    Code:
    sSQL="SELECT TOP 20 ID, fullName, wolfID, telNo, address, email, description, received, action, dispatched FROM tblWolf ORDER BY ID DESC"
    and investigate 'validation'.

    I'll also rearrange INSERT INTO and SELECT.

    I owe you a massive thanks for all your great advice, as usual, and your patience.

    Thanks again - enjoy the rest of your day.

    Steve


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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