An Added Space

[enak]

I have never had this happen to me before but I am trying to insert a string into a SQL statement. However, when I do there is a space that is inserted at the front of the string that I am trying to insert. See below:

"select * from tblDefendants " & _
"where Defendant ='" & Trim(strDef) & "' "

strDef is a string that comes from an ASP.
If I look at the string in the ASP there is no space at the front of the string contained in the page.

Here is the resulting SQL statement:

select * from tblDefendants where Defendant =' AMF INCORPORATED' and caseid = 167

How can I get rid of this extra space?

I am doing all of this in a DLL.

TIA
Nate

 

[JohnYingling]

Shot in the dark. Add a space between the equal and the apostrophe in Defendant =' i.e. Defendant = '

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[CajunCenturion]

Another shot in the dark

Could it be that the leading space is not a space, but some other non-printable character not removed by the Trim function. You might try something like the following to remove such non-printable characters.

strDef = Trim(strDef)
Do While (Asc(strDef) < 33)
strDef = Mid(strDef, 2)
Loop
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[JohnYingling]

Also, to avoid an &quot;SQL Injection Atack&quot; change
Trim(strDef)
to
Replace(Trim(strDef),&quot;'&quot;, &quot;''&quot ' Double up apostrophes (single quote)
' Equivalent to Replace(Trim(strDef),Chr(39), Chr(39) & Chr(39))

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[enak]

Thanks for your responses. However, I have tried both of your suggestions but I still get the space when I insert the string into the SQL statement.

 

[object57]

Another suggestion -
Investigate the string you insert: To check the first character include this line just before Select statement and look in debugger window for the ascii code. It may be something else, not space(33) that prevents Trim to do what you expect.

Debug.Print Asc(strDef)

Hope that helps.

 

[enak]

I tried that and I get 65. I am running these tests by putting a VB frontend on the class. I make a call to the function from the frontend.

When I examine the contents of the variable and step through the code everything is fine. But if I try to run it from an ASP by compiling it into a DLL I get the added space.