Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Update record error (data type mismatch)

I'm trying to update a record. but i get the following error: Data type mismatch in criteria expression. error: -2147217913
I have spent 4 hrs and i can't find the solution. I'm going crazy. Is it because i'm using an array?  the debuging points to the .open

the Course_Registration_ID field in access is my Primary Key and is autonumber.
the intCourseID , i have changed to long and it doesn't work either.

Dim intCourseID as integer

intCourseID = txtCourseID(index).Text

strSQL = "SELECT * FROM Course_Registration "
         strSQL = strSQL & "WHERE Course_Registration.Course_Registration_ID = '" & _
                           intCourseID & "'"

With recCertificate
            .ActiveConnection = cnConn
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .Source = strSQL
            .Open
            .MoveFirst
            .Find "Course_Registration_ID = '" & intCourseID & "'"
            .Fields("Certificate") = txtCertificate(index).Text
            .Fields("Certificate_Date") = Date
            .Update
            .Close
        End With
        Set recCertificate = Nothing

do you know what i am doing wrong?? thank you so much.
0
mapy
Asked:
mapy
2 Solutions
 
vinnyd79Commented:
Does it help if you change strSQL to?

strSQL = "SELECT * FROM Course_Registration WHERE Course_Registration_ID = '" & _
                           intCourseID & "'"
0
 
leonstrykerCommented:
Is Course_Registration_ID a numeric field?  Then your statement should be:

strSQL = "SELECT * FROM Course_Registration "
strSQL = strSQL & "WHERE Course_Registration.Course_Registration_ID = " & intCourseID

If that does not work, put a break point the line after the strSQL statement and copy you sql string from the immediate window into you database and try to run the query, see if that works.

Leon
0
 
fantasy1001Commented:
rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly
intCourseID = txtCourseID(index).Text

strSQL = "SELECT * FROM Course_Registration WHERE Course_Registration_ID=" & intCourseID

With recCertificate
            .ActiveConnection = cnConn
            .CursorLocation = adUseClient
            .Source = strSQL
            .Open strSQL, , adOpenKeyset, adLockOptimistic
            .MoveFirst
            .Find "Course_Registration_ID = '" & intCourseID & "'"
            .Fields("Certificate") = txtCertificate(index).Text
            .Fields("Certificate_Date") = Date
            .Update
            .Close
End With
Set recCertificate = Nothing

regards,
~ fantasy ~
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Arthur_WoodCommented:
since you have declared :

Dim intCourseID as integer

intCourseID = txtCourseID(index).Text

strSQL = "SELECT * FROM Course_Registration "
         strSQL = strSQL & "WHERE Course_Registration.Course_Registration_ID = '" & _
                           intCourseID & "'"

I assume that the field Course_Registration.Course_Registration_ID is a NUMBER.  If that is correct then leonstryker is 100% correct.  get rid of the '....' around the intCourseID varaible in the SQL.  the '...' indicate that the value is a STRING of thext, not a NUMBER, and that will cause the Type Mismatch error.

AW
0
 
mapyAuthor Commented:
Thank you guys.   Strings don't need quotations, hey!  & "'"
0
 
leonstrykerCommented:
mapy,

No, strings do need quotes.  Numbers do not.

Leon
0
 
fantasy1001Commented:
Thanks for that.

String do need quotes. And Numbers do not need that.

~ fantasy ~
0
 
leonstrykerCommented:
deja vu  

all other again
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now