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. :confused:
Thanks in advance for any help I may get.
Larry

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

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.

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...

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,

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,

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("fldRank") & "','" & objRec("fldOrg") & "','" & objRec("fldPhone") & "','" & objRec("fldWhen") & "')"
con.Execute(sSQL)


:confused:

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

Yeah I was afraid of that

Are all the fields 'strings'?

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

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?;)

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.

:D

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:D

Larry :thumbsup:

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!

:)