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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent.
Thanks!
/gustav
/gustav
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