Solved

Insert Run Time Error '2113'

Posted on 2012-03-24
5
677 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now