UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mysql Insert Keeps Appending An "n" To Every String., 5.7    
 
   
MCL1981
post Apr 20 2016, 08:04 AM
Post#1



Posts: 220
Joined: 1-November 11



I have a table with a field holing text strings. I've tried this with both MEDIUM TEXT and VARCHAR(65000). VBA executes and SQL insert that puts a string into this field. Every time it inserts the string, an n is appended to the end. Every single insert into ends with this stupid n. I can debug.print the string just before it goes into the SQL Insert statement, and there is no n.
Go to the top of the page
 
Marsupilami72
post Apr 20 2016, 09:56 AM
Post#2



Posts: 429
Joined: 17-April 12
From: Germany


Well...you should at least show us your code...
Go to the top of the page
 
MCL1981
post Apr 20 2016, 10:21 AM
Post#3



Posts: 220
Joined: 1-November 11



It is an SQL Insert Into... Values statement. The values are coming from arguments and variables in the VBA function. The one in question is the first table field called ProjectJournalText, which gets the string stored in the argument called JournalText.

CODE
strSQL = "Insert into Tbl_ProjectJournal (ProjectJournalText, ProjectJournalDateTime, PeopleID, ProjectID, MgtYN, CustomerYN, coYN, CustExecYN, InternalYN, SupYN ) Values ('" & JournalText & "', '" & Now & "', " & CUID & ", " & PID & ", " & Nz(MgtYN, 0) & ", " & Nz(CustYN, 0) & ", " & Nz(coYN, 0) & ", " & Nz(CustExecYN, 0) & ", " & Nz(InternalYN, 0) & ", " & Nz(SupYN, 0) & ");"

CurrentDb.Execute strSQL, dbFailOnError
Go to the top of the page
 
nuclear_nick
post Apr 20 2016, 10:59 AM
Post#4



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


What happens if you do this...
CODE
<your code>
Debug.Print strSQL
Stop


That way you can see what happens to the SQL statement you are trying to execute.

What is 'JournalText', and where does it come from?
Go to the top of the page
 
MCL1981
post Apr 20 2016, 11:44 AM
Post#5



Posts: 220
Joined: 1-November 11



I did that to make sure it wasn't something funky in the code. It is the string as it should be, surrounded by single quotes without the mystery n.

JournalText is an string type argument when calling the function. It contains the text from a text box on an Access form.
Go to the top of the page
 
River59
post Apr 20 2016, 12:10 PM
Post#6



Posts: 1,344
Joined: 7-April 10
From: Detroit, MI


What happens if you use a left function and don't pass the last character in JournalText?

Values (Left('" & ProjectJournalText '" & , Len('" & ProjectJournalText '" & )-1)

Just curious to see if this eliminates the 'n' or the last needed character.
Go to the top of the page
 
MCL1981
post Apr 21 2016, 10:40 AM
Post#7



Posts: 220
Joined: 1-November 11



It removes the last valid character, and still appends an n
Go to the top of the page
 
River59
post Apr 21 2016, 10:52 AM
Post#8



Posts: 1,344
Joined: 7-April 10
From: Detroit, MI


One of those mysteries that may never be solved. I only spend so much time banging my head on things then I yell uncle and come up with a workaround.

Have you tried an update query after you pass the value to the field that strips the 'n' out? If so, does it only strip out that pesky little 'n'?

sqlStr = "UPDATE [Auditor Master] SET [Auditor Master].[Auditor Name] = Left(Len([Auditor Master]![Auditor Name]),Len([Auditor Master]![Auditor Name])-1):"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 03:42 PM