Solved

Insert Run Time Error '2113'

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 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

15 Experts available now in Live!

Get 1:1 Help Now