Solved

Insert Run Time Error '2113'

Posted on 2012-03-24
5
685 Views
Last Modified: 2012-03-24
All,

I have just received an error on an SQL insert into table statement.

The problem field "AnalysisName" is defined as text with 50 characters.

This is what I tried to insert into the table (without the quotes):

"TEST 7 ‘0’ SOHSOO IN STATES NOT RANGED"

The error code I received is -2147217900 / Run Time Error '2113' The value you entered isn't valid for this field.

I have enclosed the SQL statement to assist in helping me sort this out. As follows:

strSQL = "INSERT INTO MT_ANALYSIS_MASTER (AnalysisName, AnalysisType, CreationDTS, CreationUserID, " & _
    "LastUpdateDTS, LastUpdateUserID, AnalysisStatusID, ContainerCategoryId, InventoryFlag) " & _
    "VALUES('" & uAnalysisName & "'," & uAnalysisType & ",'" & vCreationDTS & "','" & uUserID & "'," & _
    " '" & vLastUpdateDTS & "','" & uUserID & "'," & vAnalysisStatusID & "," & uContainerCategoryID & "," & uInventoryInclusion & ")"
    Conn.Execute strSQL, dbFailOnError

CF
0
Comment
Question by:creativefusion
  • 3
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37760225
You may need to double your single quotes:

T = "TEST 7 ‘0’ SOHSOO IN STATES NOT RANGED"
T = Replace(Replace(T, Chr(145), Chr(145) & Chr(145)), Chr(146), Chr(146) & Chr(146))

will return:
TEST 7 ‘‘0’’ SOHSOO IN STATES NOT RANGED

/gustav
0
 

Author Comment

by:creativefusion
ID: 37760329
Hi thanks for the suggestion.

The variable "Analysis Name" which caused the issue is parsed through a function that is called from a user form.

Can you please assist in incorporating your solution into the SQL Insert Statement?

I.e. VALUES('" & uAnalysisName & "',  Chr(145),

Or better still, is there a way I can put validation in the form text box control to stop the user from typing in these characters? Only allow Text and Numbers, no characters at all?

CF
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 37760370
You could test this before going to filter the user input:

strSQL = "INSERT INTO MT_ANALYSIS_MASTER (AnalysisName, AnalysisType, CreationDTS, CreationUserID, " & _
    "LastUpdateDTS, LastUpdateUserID, AnalysisStatusID, ContainerCategoryId, InventoryFlag) " & _
    "VALUES('" & Replace(Replace(uAnalysisName, Chr(145), Chr(145) & Chr(145)), Chr(146), Chr(146) & Chr(146)) & "'," & uAnalysisType & ",'" & vCreationDTS & "','" & uUserID & "'," & _
    " '" & vLastUpdateDTS & "','" & uUserID & "'," & vAnalysisStatusID & "," & uContainerCategoryID & "," & uInventoryInclusion & ")"

Open in new window


I'm not sure you will have to replace both sorts of the single quotes. An experiment will show.

/gustav
0
 

Author Closing Comment

by:creativefusion
ID: 37760395
Excellent.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37760396
Thanks!

/gustav
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

832 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