>>>>I am having a problem with a select from SQL Server. My code is as follows:
>>>>

>>>>*lcPermId = alltrim(companyinfo.PermID) && removed for testing
>>>>lcPermID = '1178072'
>>>>lnProgramYear = companyinfo.yearproduced
>>>>lcRawTable = addbs(fileloc) + "surveys\RawData" + transform(lnProgramYear) + '.dbf'
>>>>
>>>>lcConnectionString = "DRIVER=SQL Server;SERVER=PAG-SQLSERVER;" + ;
>>>>		"UID=DonF;APP=Microsoft Visual FoxPro;" + ;
>>>>		"WSID=TPD284;DATABASE=TRPSurvey;Trusted_Connection=Yes"
>>>>lnHandle = sqlstringconnect(m.lcConnectionString)
>>>>lcSQL = "select * " + ;
>>>>	"from TRPSurvey" + "..exported " + ;
>>>>	"where lcPermID = alltrim(PermID)"
>>>>
>>>>sqlexec(m.lnHandle,lcSQL,"crsImport")
>>>>sqldisconnect(m.lnHandle)
>>>>

>>>>
>>>>The problem is that the select does not return any records when the WHERE clause is used even though I know there are records that meet the criteria. If I drop the WHERE clause it works fine and returns all of the records. Does SQL Server not support WHERE?
>>>>
>>>>- Don
>>>
>>>Don, SQL Server has no such function like ALLTRIM(). Better make PermId to be varchar not CHAR. Also SQL Server doesn't know the value of lcPermID. YTou must pass that value as parameter or direclty use its value:
>>>
>>>

>>>** 1. use lcPermID as parameter
>>>lcSQL = "select * " + ;
>>>	"from TRPSurvey" + "..exported " + ;
>>>	"where PermId = ?m.lcPermID"
>>>
>>>
>>>** 2. Direct use of lcPermID
>>>lcSQL = "select * " + ;
>>>	"from TRPSurvey" + "..exported " + ;
>>>	"where PermId = '"+m.lcPermID+[']
>>>
>>>*** SQLEXEC()
>>>sqlexec(m.lnHandle,lcSQL,"crsImport")
>>>

>>>
>>>Also ALWAYS check what SQLEXEC returns. That way you will have information WHY that statement is not run:
>>>

>>>IF sqlexec(m.lnHandle,lcSQL,"crsImport") < 0
>>>   AERROR(laError)
>>>   MessageBox(laError[1,2])
>>>ENDIF
>>>

>>
>>
>>OK Here's my solution. Thanks to all of you who contributed.
>>Borislav - I don't see the need for your form of ?m.lcPermID since lcPermID is a local variable and the select is called only once. Perhaps I am missing something.
>>

>>lcSQL = "select * " + ;
>>	"from TRPSurvey" + "..exported " + ;
>>	"where ltrim(rtrim(PermID)) = " + lcPermID
>>
>>IF sqlexec(m.lnHandle,lcSQL,"crsImport") < 0
>>   AERROR(laError)
>>   MessageBox(laError[1,2])
>>ENDIF
>>

>
>
>
>Don,
>SQL Server doesn't know the value of your variables, no matter if they are PUBLIC, PRIVATE or LOCAL. So you need to find a way to pass them to Backend. SPT means exactly what s its name PASS-THROUGH. VFP Get the STRING ans pass that string w/o doing anything with it. The only exception is when you add a PARAMETERS in that string. The parameters are added with question mark in-front of them.
>
>With your edited version you ALSO will have problems IF PermID is some char type (char, varchar or nvarchar), because SQL Server will expect the STRING after the equal sign, what you pass to it is:
>

>lcPermID = [123456]
>lcSQL = "select * " + ;
>	"from TRPSurvey" + "..exported " + ;
>	"where ltrim(rtrim(PermID)) = " + lcPermID
><pre>
>
>The SQL server will get this command:
><pre>
>select * from TRPSurvey..exported where ltrim(rtrim(PermID)) = 123456
>

>
>Somtimes this will works, because SQL Server is smart enough to do implicit conversion, but sometimes you may have a trouble with the code that run for days, months or even years. So better give to SQL Server WHAT it expect:
>

>lcPermID = [123456]
>TEXT TO lcSQL NOSHOW TEXTMERGE
>     select * from TRPSurvey..exported
>	    where ltrim(rtrim(PermID)) = '<<lcPermID>>'
>ENTEXT
><pre>
>
>Did you see the difference?
>
>The SQL server will get this command now:
><pre>
>select * from TRPSurvey..exported where ltrim(rtrim(PermID)) = '123456'
>

>
>An did you don't want to bother with conversion pass lcPermID as parameter:
>

>lcPermID = [123456]
>TEXT TO lcSQL NOSHOW TEXTMERGE
>     select * from TRPSurvey..exported
>	    where ltrim(rtrim(PermID)) = ?m.lcPermID
>ENTEXT
>

OK, I see your point. Since I am not real comfortable yet with the ?variable or <> formats, do they have an advantage over Greta's option 1?