• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 716
  • Last Modified:

Insert Run Time Error '2113'

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
creativefusion
Asked:
creativefusion
  • 3
  • 2
1 Solution
 
Gustav BrockCIOCommented:
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
 
creativefusionAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
creativefusionAuthor Commented:
Excellent.
0
 
Gustav BrockCIOCommented:
Thanks!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now