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
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    I get an error message:

    Microsoft VBScript runtime error '800a01a8'

    Object required: ''

    /display_records2.asp, line 30
    on line 30, Old Pedant (line 30 = connection.Execute sSQL), but I suspect the error lies elsewhere.

    It may be that the 'order' of the code is incorrect.

    Steve

  2. #17
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Either you haven't opened the connection yet or you have already closed it.

    With the exception of loops and subroutine calls, code executes from top to bottom.

    There's no magic.

    If you need to do connection.Exeucte then someplace BEFORE that line you need to create and open a connection *AND* you can't close the connection until AFTER that point. Simple steps. A, B, C, D, etc.
    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.

  3. #18
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello Old Pedant

    Do you mean like this:

    Code:
    sSQL="INSERT INTO myTable (field names here) VALUES (etc)"
    
    sSQL="SELECT TOP 30 ID, firstName, other fields go here FROM myTable"
    
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    'Open the connection to the database
     connection.Open sConnString
    
    'Open the recordset object, execute the SQL statement
     recordset.Open sSQL,connection
    
    connection.Execute sSQL
    
    Close connection, etc
    Also, can I have these two together:

    Code:
    sSQL="INSERT INTO myTable (field names here) VALUES (etc)"
    
    sSQL="SELECT TOP 30 ID, firstName, other fields go here FROM myTable"
    Thanks again for your time.

    Steve

  4. #19
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    No, no, no, no.

    Read what I wrote, again:
    With the exception of loops and subroutine calls, code executes from top to bottom.

    So in your code, you first do this:
    Code:
    sSQL="INSERT INTO myTable (field names here) VALUES (etc)"
    And now the variable sSQL indeed contains the string INSERT INTO myTable (field names here) VALUES (etc)

    And then you do this:
    Code:
    sSQL="SELECT TOP 30 ID, firstName, other fields go here FROM myTable"
    And NOW that *SAME VARIABLE* sSQL is OVERWRITTEN and NOW contains the string SELECT TOP 30 ID, firstName, other fields go here FROM myTable

    YOU THREW AWAY WHAT THE STRING FIRST CONTAINED.

    ***********

    And then, further in the code, you do this:
    Code:
    recordset.Open sSQL,connection
    
    connection.Execute sSQL
    Those two statements DO THE SAME THING.

    They both execute the *SAME* string, the *ONLY* one contained in the ONE AND ONLY sql string you have, the variable sSQL.

    ***********

    YOU MUST THINK LINEAR! Point A to point B to point C, etc. The way the computer executes your instructions. Linear. (Unless, of course, you have loops or subroutines involved. Which you do NOT have here.)
    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. #20
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    As a general rule:

    At the top of your page, open your connection to the database.

    Leave it open throughout the page.

    At the bottom of the page, close the connection. (And, with ASP code, if you forget to do the close it doesn't matter. ASP will close it for you. There's a miniscule performance penalty--a microsecond or two--if you let ASP do it for 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.

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

    SteveH (07-12-2013)

  7. #21
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello Old Pedant

    I feel as if I have done everything you have suggested (thank you). The error I get is this:

    Microsoft JET Database Engine error '80040e07'

    Data type mismatch in criteria expression.

    /display_records2.asp, line 48
    Line 48 = recordset.Open sSQL,connection

    Line 51 = 'connection.Execute sSQL
    I get the same error if I do this:

    'recordset.Open sSQL,connection

    connection.Execute sSQL
    This is my code now (after trying to think in a linear, and not dialectical, manner):

    Code:
    <% 
    
     Dim Connection, Recordset, sSQL, sConnString, my_firstName, etc
     
    my_firstName = Replace( Request.Form("firstName"), "'", "''" )
    
    Etc
    
    
    sSQL="INSERT INTO myTable (firstName, etc) VALUES ('" & my_firstName & "', etc)"
    
    
    'sSQL="SELECT TOP 30 ID, firstName, etc FROM myTable"
    
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
     connection.Open sConnString
    
     recordset.Open sSQL,connection
    
    'connection.Execute sSQL
    
    
    'HTML table to house and format results
     
    %>
    
    Table stuff
    
    Close HTML table
    
    Close connection %>
    You take care.

    Steve

  8. #22
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    You need to show me the *ACTUAL* query you are using, not showing me etc..

    That error message means that you are trying to match the value of a numeric database field with a non-numeric test value, or vice versa, or or or...

    For example:
    Code:
    SELECT * FROM tablename WHERE amountOfMoney = 'frammis'
    Presumable the field named amountOfMoney is a numeric field. In which case the database engine is expecting you to compare it to a number. It can't compare it to the string 'frammis' and so you get the "mismatch" message.

    Oh, and the "in criteria expression" usually means it's an error in your WHERE clause.

    I can't help you further unless you show the ACTUAL query.
    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.

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

    SteveH (07-12-2013)

  10. #23
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello Old Pedant

    Thanks for pointing that out.

    I am attaching a screenshot of the field values from the MS Access database.

    The fields are all text except for 'wolfID' and 'telNo' which are numbers, and 'received' and 'dispatched' which are date/time values.

    The code is as follows:

    Code:
    <%@ Language="VBScript" %>
     <% Option Explicit %>
    
    <% 
    
     Dim Connection, Recordset, sSQL, sConnString, my_firstName, my_lastName, my_wolfID, my_telNo, my_stAddress, my_city, my_postCode, my_items, my_received, my_dispatched
     
    my_firstName = Replace( Request.Form("firstName"), "'", "''" )
    my_lastName = Replace( Request.Form("lastName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_stAddress = Replace( Request.Form("stAddress"), "'", "''" )
    my_city = Replace( Request.Form("city"), "'", "''" )
    my_postCode = Replace( Request.Form("postCode"), "'", "''" )
    my_items = Replace( Request.Form("items"), "'", "''" )
    my_received = Replace( Request.Form("received"), "'", "''" )
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    
    
    sSQL="INSERT INTO tblWolf (firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched) VALUES ('" & my_firstName & "', '" & my_lastName & "'," & my_wolfID & ", " & my_telNo & ", '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "', '" & my_items & "', '" & my_received & "', '" & my_dispatched & "')"
    
    
    'sSQL="SELECT TOP 30 ID, firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched FROM tblWolf"
    
    
    
     Set connection = Server.CreateObject("ADODB.Connection")
     Set recordset = Server.CreateObject("ADODB.Recordset")
    
    
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & Server.MapPath("myDatabase.mdb") 
    
    
     connection.Open sConnString
    
     recordset.Open sSQL,connection
    
    
    'connection.Execute sSQL
    
    
    'HTML table to house and format results
    
    
     response.write "<table width='100%' border='1'>"
    %>
    
    <%
    Response.Write statements here and then:
    
    Recordset.Close
     Connection.Close
     Set Recordset = Nothing
     Set Connection = Nothing
     %>
    Thanks for looking at it.

    Steve
    Attached Thumbnails Attached Thumbnails Error: Object required-mdb.jpg  

  11. #24
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Didn't need the table display.

    Reason is easy: With Access and JET OLEDB, date (and datetime and time) values must be enclosed in #...#. *NOT* in '...'.

    Yes, this is different than every other database I know of. Peculiar to just Access/JET.

    So:
    Code:
    sSQL="INSERT INTO tblWolf " _
         & "(firstName, lastName, wolfID, telNo, stAddress, city, postCode, items, received, dispatched)" _
         & "  VALUES ('" & my_firstName & "', '" & my_lastName & "'," & my_wolfID & ", " _
         & my_telNo & ", '" & my_stAddress & "', '" & my_city & "', '" & my_postCode & "'," _
         & " '" & my_items & "', #" & my_received & "#, #" & my_dispatched & "#)"
    Do yourself a favor and make your code readable by breaking up long lines as I just did.
    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. #25
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    I would also suggest replacing this code:
    Code:
    my_received = Replace( Request.Form("received"), "'", "''" )
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    with simply this, for better data type validation:
    Code:
    my_received = CDATE( Request.Form("received") )
    my_dispatched = CDATE( Request.Form("dispatched") )
    A date should never have an apostrophe in it, anyway. So checking (via CDATE) that it actually is a date makes more sense. (Similar to what you did with CDBL for wolfid and phone.)
    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.

  13. #26
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello Old Pedant

    Many thanks again for your corrections. I could not possibly have known that about Access/Jet.

    I have incorporated your code, thank you, and uploaded it to the server.

    I am getting another error (nothing to do with your code), but I will try to sort it out over the weekend. If I can't, I will ask you again, if that's OK?

    I feel as if I have taken up enough of your time already.

    Many thanks for all your help and advice - I must owe you a year's supply of your favourite drink!

    Enjoy your weekend.

    Steve

  14. #27
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Quote Originally Posted by SteveH View Post
    I must owe you a year's supply of your favourite drink!
    Luckily for you, that's iced tea. <grin/>
    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.

  15. #28
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    In the meantime, *do* download that Scripting Help file, if you haven't already. It will save you *HOURS* of grief. I will bet that, over the past 10 years, it has saved me maybe 100 hours of putzing around.
    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.


 
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
  •