1,105,290 Community Members

Error: Type Mismatch - access + vb 6.0

Member Avatar
JerieLsky
Junior Poster in Training
63 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I'm having this error: type mismatch.. i dont actually get/understand the error.. i think the error is in the WHERE clause [tblUserAccount].[UserID].. the field [UserID] is set to a data type Autonumber, and the roleUserID is set as integer..

Global rolesDbs as new adodb.connection
Global rolesRs as new adodb.recordset

 rolesDbs.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\library.mdb;MODE=ReadWrite"
        rolesDbs.CursorLocation = adUseClient
        Set rolesRs.ActiveConnection = rolesDbs
        rolesRs.LockType = adLockOptimistic
        rolesRs.CursorType = adOpenDynamic
        rolesRs.Source = "SELECT [tblRoles].[UserID], [tblRoles].[Role] " & _
                         "FROM [tblUserAccount] INNER JOIN [tblRoles] ON ([tblUserAccount].[UserID] = [tblRoles].[UserID]) " & _
                         "WHERE [tblUserAccount].[UserID] = " + roleUserID + ""
        rolesRs.Open , rolesDbs, , , adCmdText

help me out here please.. thanks

Member Avatar
vb5prgrmr
Posting Virtuoso
1,670 posts since Mar 2009
Reputation Points: 143 [?]
Q&As Helped to Solve: 299 [?]
Skill Endorsements: 3 [?]
 
0
 

You cannot "add" strings together (+), you can however concatinate strings together (&). As for everything else, it looks fine at first glance.


Good Luck

Member Avatar
JerieLsky
Junior Poster in Training
63 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

well if i tried changing the (+) sign using the (&) sign the error now changes to: Data type mismatch in criteria expression. So how's that? I'm not actually concatinating it, im comparing it using the WHERE clause.

Member Avatar
ProfessorPC
Posting Whiz in Training
272 posts since Dec 2007
Reputation Points: 19 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 0 [?]
 
0
 

try this

"WHERE [tblUserAccount].[UserID] = '" & roleUserID & "'"

single quotes around roleuserid

Member Avatar
JerieLsky
Junior Poster in Training
63 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

try this

"WHERE [tblUserAccount].[UserID] = '" & roleUserID & "'"

single quotes around roleuserid

"WHERE [tblMembership].[UserID] ='" & Trim(roleUserID) & "'"

in this code if i place a single quotes the error is "Data type mismatch in criteria expression." but if i remove the single quotes, what will happen is that i am already concatinating the string together, which causes an error..

Member Avatar
ProfessorPC
Posting Whiz in Training
272 posts since Dec 2007
Reputation Points: 19 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 0 [?]
 
0
 

if you are pulling data from one table only why use the join?

"SELECT UserID, Role FROM tblRoles where UserID = '" & roleUserID & "'"
Member Avatar
JerieLsky
Junior Poster in Training
63 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I ain't actually getting data from only one table, i need some datas also from the other table that's why i applied the INNER JOIN, i just don't know why it shows an error with the WHERE clause.. please, need back up :D ..

Member Avatar
ProfessorPC
Posting Whiz in Training
272 posts since Dec 2007
Reputation Points: 19 [?]
Q&As Helped to Solve: 29 [?]
Skill Endorsements: 0 [?]
 
0
 

in your query you are only pulling from one table. i guess its throwing me off. what are the fields from the other table you are using? can you test your query in your db's query analyser?

Member Avatar
JerieLsky
Junior Poster in Training
63 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

in your query you are only pulling from one table. i guess its throwing me off. what are the fields from the other table you are using? can you test your query in your db's query analyser?

Ok, i tried this Query from only one table which is Table Membership:

"SELECT * FROM [tblMembership] WHERE [tblMembership].[UserID] = '"+ rolesUserID + "'"

if i remove the WHERE clause, i get no errors, but if place that WHERE clause in the Query it pops up the error: Type Mismatch. In the table Membership, i set the data type of UserID to Number, and the variable rolesUserID to Integer.
The rolesUserID is actually a variable that i have set to make a comparison to [tblMembership].[UserID].

rolesUserID = usersRs.Fields("UserID").Value

i dont know what's causing the problem.

Member Avatar
vb5prgrmr
Posting Virtuoso
1,670 posts since Mar 2009
Reputation Points: 143 [?]
Q&As Helped to Solve: 299 [?]
Skill Endorsements: 3 [?]
 
0
 

Seems like user id is a numeric field. Remove the single ticks (') from around your variable.

Good Luck

Member Avatar
JerieLsky
Junior Poster in Training
63 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Seems like user id is a numeric field. Remove the single ticks (') from around your variable.

Good Luck

Yes, UserID stores a numeric value.. The problem if i remove the (') single quotes, what would happen is that it makes a concatination which makes the SQL statement wrong or produces a SQL error..

"SELECT * FROM [tblMembership] WHERE [tblMembership].[UserID] = " + roleUserID + ""

- this produces a concatination.

Member Avatar
vb5prgrmr
Posting Virtuoso
1,670 posts since Mar 2009
Reputation Points: 143 [?]
Q&As Helped to Solve: 299 [?]
Skill Endorsements: 3 [?]
 
0
 

Because once again you are trying to add strings together (+), you are supposed to concatinate them (&)

"SELECT * FROM tblMembership WHERE UserID = " & roleUserID

Good Luck

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article