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.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Help With Expression Error

    Can someone tell me why I keep getting this error and what I can do to correct it?

    The Original code in question...

    <%

    OpenDB con, "Admin"

    Set objRec = Server.CreateObject("ADODB.Recordset")
    SQL = "SELECT * FROM tblUser ORDER BY fldAuto ASC"
    objRec.open SQL, Con, 3, 3

    amount = 0

    While Not objRec.EOF

    user = objRec("fldUserName")
    mail = objRec("fldEmail")
    pass = objRec("fldPassword")
    when = objRec("fldWhen")
    gnum = objRec("fldGroupNumber")
    title = objRec("fldTitle")
    fname = objRec("fldFirstName")
    lname = objRec("fldLastName")
    org = objRec("fldOrg")
    phone = objRec("fldPhone")

    Randomize
    code = user & (9776929856 * CInt((RND * 32000) + 100)) & Left(pass,1) & Right(pass,1)

    Set objRec3 = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM tblAuthor Where tblAuthor.Username = '" & objRec("fldUserName") & "'"
    objRec3.open strSQL, Con, 3, 3

    If objRec3.EOF Then

    sSQL = "INSERT INTO tblAuthor (Username,User_code,Password,Author_email) VALUES ('" & objRec("fldUserName") & "','" & code & "','" & objRec("fldPassword") & "','" & objRec("fldEmail") & "')"
    con.Execute(sSQL)

    amount = amount+1

    End If
    objRec.MoveNext
    Wend
    Alert(" All Done " & amount & " Users were added to the DBase.")

    %>

    That Works Just Fine, But when I change ONLY the sSQL (and only the sSQL is different from above) to look like this...

    sSQL = "INSERT INTO tblAuthor (Username,User_code,Password,fldGroupNumber,Author_email,fldTitle,fldFirstName,fldLastName,fldRank,f ldOrg,fldPhone,Join_date) VALUES ('" & objRec("fldUserName") & "','" & code & "','" & objRec("fldPassword") & "','" & objRec("fldGroupNumber") & "','" & objRec("fldEmail") & "','" & objRec("fldTitle") & "','" & objRec("fldFirstName") & "','" & objRec("fldLastName") & "','" & objRec("fldOrg") & "','" & objRec("fldPhone") & "','" & objRec("fldWhen") & "')"

    I get this error every time I try it...

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
    [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
    /sitefiles/transfer.asp, line 45

    This is driving me nuts.
    Thanks in advance for any help I may get.
    Larry

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Posts
    344
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Data type mismatch is when you try to compare to types of variable that aren't compatible (Like comparing a string of characters to an Integer, a Boolean value (true or false) to a String, etc.)

    On Line 45 you get an error... what's on Line 45??

    Check and make sure all your data types being compared are of the same type (This could be a problem with the way your fields are set up in the database.)

    You could also be getting this error when trying to insert a certain type of variable into a table field that is a different type - like inserting a True value into a field that is supposed to be an Integer.

    Let me know if this helps.

    ~Quack
    Last edited by QuackHead; 06-24-2002 at 03:10 PM.

  • #3
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually. No, it didn't help. As I said in my previous the first initial explanation of the problem I have the Error is referencing to the sSQL = "blah blah" statement. The first statement I showed works but the second one (which has no true/false data or such, it's all text or memo) gets the error.

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Posts
    344
    Thanks
    0
    Thanked 0 Times in 0 Posts
    K, well here's something I noticed - if you copied that SQL statement straight from the ASP page - you have a few formatting errors..

    When you want to include the next line in your statement the underscore (_) should be on the top line...

    VariableName = "blah blah blah blah blah " _
    & "blah blah blah"

    Also, you don't have a coma separating a few of your fields in that statement...
    • Between Author and Email
    • Between f and ldOrg


    Change that around, see if it works...

  • #5
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunately when I did copy the statement the forum displayed it the way you currently see it. In actuality it is indeed all one line and all the commas are in place. here it is again...

    Working Original,
    Code:
    sSQL = "INSERT INTO tblAuthor (Username,User_code,Password,Author_email) VALUES ('" & objRec("fldUserName") & "','" & code & "','" & objRec("fldPassword") & "','" & objRec("fldEmail") & "')"
    con.Execute(sSQL)
    NonWorking Changed but almost Identical,
    Code:
       sSQL = "INSERT INTO tblAuthor (Username,User_code,Password,fldGroupNumber,Author_email,fldTitle,fldFirstName,fldLastName,fldRank,fldOrg,fldPhone,Join_date) VALUES ('" & objRec("fldUserName") & "','" & code & "','" & objRec("fldPassword") & "','" & objRec("fldGroupNumber") & "','" & objRec("fldEmail") & "','" & objRec("fldTitle") & "','" & objRec("fldFirstName") & "','" & objRec("fldLastName") & "','" & objRec("fldRank") & "','" & objRec("fldOrg") & "','" & objRec("fldPhone") & "','" & objRec("fldWhen")  & "')"
       con.Execute(sSQL)

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Posts
    344
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmmm
    Sorry, yeah I did read it wrong...

    not sure why this wouldn't be working...

    hopefully someone else can give us a hand (It may be a back-end database problem though)

    ~Quack

  • #7
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah I was afraid of that

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are all the fields 'strings'?

    Try taking out the '' around :
    '" & code & "'

  • #9
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, But That Didn't work Either.

    I think what I'm going to try is just add one of them at a time until I get the error and then I'll know exactly which expression or data field it should be.

    Unless someone else happns to know what the prob is right off...
    Whammy, Dave?

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What's line 45?

    Also, usually the best way to troubleshoot this problem yourself is to comment out the part that executes the query, and instead, write it to the page:

    Response.Write(sSQL): Response.End

    Before you execute sSQL

    That will enable you to get a good look at the syntax that is actually trying to execute.

    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #11
    New Coder
    Join Date
    Jun 2002
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured it out. It was the Join_date in the sSQL String. They were indeed completely differently defined from one table to the next so I just omitted that portion and it works great.

    Thanks for all the help

    Larry

  • #12
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You know you can convert one data type to another, right?

    Check these out:

    Asc()
    CBool()
    CByte()
    CCur()
    CDate()
    CDbl()
    CInt()
    CLng()
    CSng()
    CStr()

    Although in my experience, I've found I mostly use CStr() and CInt() to convert from Strings to Integers, and vice versa. However you never know when the other stuff will come in handy!

    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

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