Solved

ERROR: 2147217913

Posted on 2004-04-07
3
393 Views
Last Modified: 2013-12-25
I get the error datatype mismatch in criteria expression with my Insert query, which is as follows:

SQLInsert = "insert into Participation([Term Code], [Course Code], [Player ID])"
                 SQLInsert = SQLInsert & " values('" & frm_input.MsData.TextMatrix(frm_input.MsData.Row, 8) & "', '" & frm_input.MsData.TextMatrix(frm_input.MsData.Row, 9) & "', '" & frm_input.MsData.TextMatrix(frm_input.MsData.Row, 10) & "')"
                 objConn.Execute SQLInsert

in the immediate window, this is what ?SQLInsert gives;

insert into Participation([Term Code], [Course Code], [Player ID]) values('1910', 'AU2004', 'CU1358')

Which works fine in MsAccess, which is my back-end.

I have checked datatype in acess and its all good, also the exact query works on a different program for the same databse.

Please help!!!!
0
Comment
Question by:claracruz
  • 2
3 Comments
 
LVL 8

Expert Comment

by:bramsquad
ID: 10778748
are you sure your SQL query is correct?   this would be the format i would use


INSERT INTO Participation
SELECT
FROM YourTable
WHERE (((YourTable.[Term Code])='1910') AND ((YourTable.[Course Code])='AU2004') AND ((YourTable.[Player ID])='CU1358'));

~b

0
 
LVL 8

Expert Comment

by:bramsquad
ID: 10778759
nevermind what i just put, i read that q incorrect.......brb
0
 
LVL 18

Accepted Solution

by:
Sethi earned 500 total points
ID: 10780205
Type mismatch smpley points to the fact that you are inserting a string in a numeric column. Please check whether any of the columns is a numeric data type. Assuming that [Term Code] is a numeric one then this is how the query should look:
insert into Participation([Term Code], [Course Code], [Player ID]) values(1910, 'AU2004', 'CU1358')

You have to remove the single quotes from your SQL statement:

SQLInsert = "insert into Participation([Term Code], [Course Code], [Player ID])"
                 SQLInsert = SQLInsert & " values(" & frm_input.MsData.TextMatrix(frm_input.MsData.Row, 8) & ", '" & frm_input.MsData.TextMatrix(frm_input.MsData.Row, 9) & "', '" & frm_input.MsData.TextMatrix(frm_input.MsData.Row, 10) & "')"
                 objConn.Execute SQLInsert


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
A macro to Count the number of rows across all worksheets 3 126
VBA: Select SQL query based on a config Sheet v2 11 49
TT Auto Dashboard 13 95
RUNRMTCMD from AS/400 12 69
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question