Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2006
    Posts
    72

    Unanswered: Type mismatch: 'CDBL' error

    Hello

    On my page here: Displaying all records from a database table I am getting the following error:

    Microsoft VBScript runtime error '800a000d'

    Type mismatch: 'CDBL'

    /addRecords.asp, line 112
    Line 112 is this:
    Code:
    my_wolfID = CDBL( Request.Form("wolfID") )
    The error message occurs when I deliberately leave all fields blank on my online 'Add Records' form and press 'submit'. This is a MS Access db and wolf ID has a 'Number' data type.

    The code I have is:

    Code:
    Dim conn, rs, my_fullName, my_wolfID, my_telNo, my_address, my_email, my_description, my_received, my_action, my_dispatched
    ' set varaible values from request.form
    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"), "'", "''" )
    
    'build insert statement
    sSQL="INSERT INTO tblWolf " &_
     "(fullName, wolfID, telNo, address, email, description, received, [action], dispatched)" &_
     " VALUES ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & my_address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "#, '" & my_action & "', '" & my_dispatched & "')"
    I have tried debugging using:
    Code:
    Response.Write "<hr/>DEBUG SQL:<br/>" & sSQL & "<hr/>" & vbNewLine
    but I don't get any results (please see screenshot).

    What might be the best way to remove that error, please?

    Thanks!
    Attached Thumbnails Attached Thumbnails DB_screenshot.jpg  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is the value of Request.Form("wolfID") ?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    I don't see any value at all on screen, and if I insert this:
    Code:
    Response.Write Request("wolfID")
    , nothing is returned - just that 'Type mismatch: 'CDBL' error message.

    wolfID is a customer ID with a numeric (only) value in my small MS Access database.

    Cheers

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you comment out the wolf ID line, do you get the same error message elsewhere?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2006
    Posts
    72
    Hello George

    I have commented out:
    Code:
    'my_wolfID = CDBL( Request.Form("wolfID") )
    When I now go to the addRecords.asp page, neither the Webpage table with the records displayed nor the form is displayed, but I do get the following error:

    Microsoft JET Database Engine error '80040e14'

    Syntax error in INSERT INTO statement.

    /addRecords.asp, line 127
    Line 127 is this:
    Code:
    connection.Execute sSQL
    That can mean just about anything, can't it?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dunno much about crystal reports... but doesnt cdbl convert a value into type double. With A column called wolfid id expect its datatype to be integer, but it may be legit, however using cdbl on a telephone number is very unlikely to be legit. If it is as per your column deaign then id argue the design is suspect.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Humm... can you post the HTML that makes up the </form> ?
    I reckon that wolfID doesn't exist. And you can't convert nothing in to a double I reckons...
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2006
    Posts
    72
    Yes, here is a screenshot from the database itself (just for your information), and here is the form code:

    Code:
    <form action="addRecords.asp" method="post">
    
    <h3>Add records</h3>
    
    <p class='font'>Complete each form field to add records to the MS Access database.</p>
    
    <p class='font'>Full Name:</p>
    <p><input type="text" name="fullName" id="fullName" /></p>
    
    <p class='font'>Wolf ID:</p>
    <p><input type="text" name="wolfID" id="wolfID" /></p>
    
    <p class='font'>Tel No:</p>
    <p><input type="text" name="telNo" id="telNo" /></p>
    
    <p class='font'>Address:</p>
    <p><input type="text" name="address" id="address" /></p>
    
    <p class='font'>Email:</p>
    <p><input type="text" name="email" id="email" /></p>
    
    <p class='font'>Description:</p>
    <p><input type="text" name="description" id="description" /></p>
    
    <p class='font'>Date received:</p>
    <p><input type="text" name="received" id="received" /></p>
    
    <p class='font'>Action:</p>
    <p><input type="text" name="action" id="action" /></p>
    
    <p class='font'>Date dispatched:</p>
    <p><input type="text"  name="dispatched" id="dispatched" /></p>
     
    <p><input type="submit" class='input' value="Add data" /></p>
    
    </form>
    Thanks again.
    Attached Thumbnails Attached Thumbnails Design_View.jpg  

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so there are some fundamentals out of whack here:-
    1 using the wrong datatype, a phione number is not a true number, by forcing it to be numeric you cause formatting issues. there is no need for it to be a number as you will never do mathmatical operations (eg add, subtract sum average etc...)

    2) you have an ID column yet you also have a wolfid. I'm suspicious that you have a non-normalised data design there. looks like you are conflating two or more entities into one. its hard top say as we don't know the details however I'd expect you to have persons (who have an address, phonenumber and so on), wolves (animals you re observing and reports (where someone saw something and some action was taken ON A SPECIFIC DATE)

    3 you are using the cdbl function to coerce a string value from a web page which will then be inserted into a column which is of type integer. use CINT instead

    4 you are doing no validation, range checking or otherwise ensuring values are reasonable and sane. NEVER EVER trust anything a user provides, especially on a web service. you don't knwo if you have a genuine user making mistakes or a scumbag tryng to breach your system. read up on SQL injection attacks.

    5) you say this error occurs when
    The error message occurs when I deliberately leave all fields blank on my online 'Add Records' form and press 'submit'. This is a MS Access db and wolf ID has a 'Number' data type.
    . well the 'smarter develoepr route is to recognise you have invalid data and not attempt to write the data to your db. there is no point trying to weed out the crap once its in your db, stop it getting there in the first place
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim my_wolfID
    my_wolfID = -937
    
    If Request.Form("wolfID") > "" Then
        If IsNumeric(Request.Form("wolfID")) Then
            my_wolfID = CInt(Request.Form("wolfID"))
        End If
    End If
    
    Response.Write my_wolfID
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2006
    Posts
    72
    I will try to summarise a couple of things. If I use CDBL I cannot see the records in my table at all and therefore I cannot see the 'Add Records' link which takes me to the 'Add Records' form. All I get is the following Type mismatch: 'CDBL' error:

    Microsoft VBScript runtime error '800a000d'

    Type mismatch: 'CDBL'

    /addRecords_TEST.asp, line 110
    On the other hand, if I replace CDBL with CINT I do see the records in my table and can click on the link to bring up the 'Add Records' form. If I then leave all fields blank and press enter, I get a similar error:

    Microsoft VBScript runtime error '800a000d'

    Type mismatch: 'CINT'

    /addRecords_TEST.asp, line 110
    If I now copy and paste

    Code:
    Dim my_wolfID
    my_wolfID = -937
    
    If Request.Form("wolfID") > "" Then
        If IsNumeric(Request.Form("wolfID")) Then
            my_wolfID = CInt(Request.Form("wolfID"))
        End If
    End If
    
    Response.Write my_wolfID
    I get the following error:

    Microsoft VBScript compilation error '800a0411'

    Name redefined

    /addRecords_TEST.asp, line 121
    Dim conn, rs, my_fullName, my_wolfID, my_telNo, my_address, my_email, my_description, my_received, my_action, my_dispatched
    --------------------------------^
    I am not sure what that means - it seems to be something to do with duplication according to what I have read on various sites this afternoon.

    I understand where healdem is coming from (thank you for your post), though if phone number is not a numeric value I am not sure what it is.

    wolfID is simply a customerID, but if you feel that as I already have an ID column that wolfID is superflous then I can remove it - originally, I intended it to be the customer's password when tracking the state of their order while email would be their username, but that's a different story!

    If it seems reasonable to use my MS Access ID column instead of wolfID, I am happy to remove wolfID altogether.

    Thanks again for your time.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You have already declared (Dim) the variable "my_wolfID".

    Comment out the first line of the snippet I posted and retry.
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    phone numbers shoudl be text/string/char
    us formats
    (999) 999-9999
    UK formats
    0999 999-9999
    099999 999999
    French
    99 99 99 99 99

    international can be prefixed with 00 (0 is long distance) or a +
    all of which can be handled with numbers, except punctuation such as the us standard (999), or using hyphens or spaces to separate numbers. as said before telephone numbers are best represented by text/string as thats what they effectively are NOT numbers.

    As said before you should not be attempting to enter blank rows into your table(s). stop crap getting into your db, rahter than try to fix a problem afterwards. so test your values received from users are sane (sensible) and range check & validate everything

    use an surrogate key (an autogenerated id) when there is no better id column(s) available. in this case you have 'wolfid'... so on the face of it wolfid and id are both surrogate keys. use one or t'other but not both

    if wolfid is really a customer id then call it that ffs

    also asa said before there is no ppoint using cdbl if you wnat to convert a text/string value into an integer value. CDBL attempts to coerce such values as double preciiosn numbers NOT integers

    unless you trust your users then always, always validate dataa
    so check the value is numeric BEFORE attempting to convert it to numeric. use the isnumeric function to check. there's a whole suite of such checks that allow you to validate your data before presenting it to the db.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2006
    Posts
    72
    With this:

    Code:
    'Dim my_wolfID
    my_wolfID = -937
    
    If Request.Form("wolfID") > "" Then
        If IsNumeric(Request.Form("wolfID")) Then
            my_wolfID = CInt(Request.Form("wolfID"))
        End If
    End If
    
    Response.Write my_wolfID
    and this:

    Code:
    my_wolfID = CINT( Request.Form("wolfID") )
    I get the following error when I leave all form fields blank and press enter:

    -937
    Microsoft VBScript runtime error '800a000d'

    Type mismatch: 'CInt'

    /addRecords_TEST.asp, line 126
    Line 126 is this:
    Code:
    my_wolfID = CINT( Request.Form("wolfID") )
    It really doesn't like that column, does it!?

  15. #15
    Join Date
    Feb 2006
    Posts
    72
    Thanks for that healdem.

    I didn't know that.

    I'll change it from numeric to text.

    Yes, I am beginning to think you are right: either an autogenerated ID or the wolfID. The wolfID seems to be the source of some problems! And, as you wrote yesterday, I had better read up on SQL injection attacks.

    Thanks again.

Posting Permissions

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