SQL Select using variables

Visual FoxPro General

Index ‹ Visual FoxPro

- Previous
  - 1
    - deleting VFP external table using sql DTS i did this but!!! '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim cn1 As New OleDbConnection("Provider=VFPOLEDB.1;Data Source=x:\output\ace\;") 'line 6 cn1.Open() Dim cmd2 As New OleDbCommand("EXECSCRIPT([USE AceStore EXCLUSIVE] + CHR(13) + CHR(10) + [zap])", cn1) cmd2.ExecuteNonQuery() Main = DTSTaskExecResult_Success End Function Error description: expected end of statement line 6 did i make something wrong Tag: SQL Select using variables
  - 2
    - FoxPro Views over VFP Oledb with VB? Hi Kanaida, What did you do in SQL that worked What have you tried against VFP data via OLE DB You can select data from a Fox view the same as you would work with data from any other Fox table. Tag: SQL Select using variables
  - 3
    - SELECT-SQL vs. SET RELATION Hi Ed, This is very long explanation I'll answer on the few points starting with the last and easiest: SET RELATION would be an equavalent of the LEFT JOIN, in my opinion, since you may have parent records without children. Now, in the exists (select .. ) statement the field that follows the select doesn't really matter, you can use any field or * For inner join condition you need to use the existing indexes for speed, though I would not recommend to use alltrim() function in them. So, say, your index is upper(LastName + FirstName) in both tables, then the following JOIN condition from Table1 T1 INNER JOIN Table2 T2 on upper(t1.LastName + t1.FirstName) = upper(t2.LastName + t2.FirstName) would use an existing index tag to optimize the join. Also you can always check sys(3054) function result to see the optimization details. These are some quick pointers. I may be wrong in some of my statements, so better test before believing them as is Tag: SQL Select using variables
  - 4
    - How to improve dot matrix printer speed from vfp application. I guess it is your coding issue. I had the similar problem with VFP9 printing (to laser printer.) You may: 1. check how fast it gathers data and displays on the screen. Report form yourReportFile preview (Maybe you do not have a report file, using or @....) 2. Send a text file from the command window of both VFP9 and Foxpro 2.5 to see the difference. Tag: SQL Select using variables
  - 5
    - Copying a numeric value with decimals from a cursor into excel without rounding up .Value = ... is not a good style to fill in excel values and would be slow but it would work with small amount of transfers. Second with numeric values you shouln't pass them as strings but numbers. Third, assuming passing as strings was a good way, your conversion expression is the one making the roundation. See str() in help. str(123.655) str(123.655,10,2) str(123.655,10,5) Tag: SQL Select using variables
  - 6
    - Delete File The command you need is SET SAFETY OFF You do not need macro substitution here (e.g. &lcFile2). You can do it directly. Do not do: DELETE "c:\client\*.*" burt ERASE "c:\client\*.*" DELETE is used to delete records in a DBF table. Tag: SQL Select using variables
  - 7
    - Hiding parts of a form It's as simple as setting Visible = .t. or .f. You could place your controls in a container and just play with container's visibility (or even bind it to checkbox's value). Tag: SQL Select using variables
  - 8
    - VFP to SQL2005 OLE DB "errors" In SS2005 the following doesn't work set null on you can set ansi_nulls, or ansi_null_dflt_on, or ansi_null_dflt_off but not just null. Or am I missing something here Is there some other way to do it The datetime thing, while anoying, is fixable (can also do a case statement). My problem is that numeric/decimal fields that are empty in VFP are coming in with a default of 0. If it is empty, then it should be null. A value of zero implies that someone entered it, while empty/null implies "no answer". And "no answer" should never be implied to mean zero. As for default behaviours, I've done nothing to change the VFPOLEDB driver's behaviour so far. What I'm seeing is, to me at least, the default behaviour. And that is that I'm getting manufactured data where it shouldn't exist. Thanks again, Ed Tag: SQL Select using variables
  - 9
    - Dynamic object creation Thanks a lot for ur reply. My problem is solved. Now I've another problem, whenever I use the build button in project manager to build the executable file for my project then for the first time there is no problem but when I run that executable file and then rebuild the executable then there is an error message saying "<my temp folder path>\<filename>.fxp is not closed, please close the file and rebuild the project.". But when I repeate the whole process again then it successfuly builds the exe. Why is it so Regards, Tag: SQL Select using variables
  - 10
    - Can a VFP application be run from a Sharepoint Page Thanks for the reply. To clarify, if I have a VFP 9 exe residing on a folder, can the Sharepoint page run the exe and pass a parameter that will say which report to run. I am assuming then that the report would be run specifying HTML output. Would that open in the Sharepoint page At what point does the VFP app end I haven't done much yet in VFP 9 (mostly we have VFP 6 apps) and haven't worked with the Report Listener function at all. I know we could re-write these reports to provide the data via a web service and then develop the actual report in something that works well from with Sharepoint, but the big hitch there is duplicating a substantial security function that is contained inside the foxpro app and determines what data can be seen by a particular user. Thanks for any help. Greg Tag: SQL Select using variables
  - 11
    - Virtual keyboard problems Hi, Thanks for your answer. I've tried those number but it doesn't work. Can you help me Tag: SQL Select using variables
  - 12
    - What is The diffrence Between Odbc Provider In Vfox And Vb.net Hi Pooya, When you write code to access data you need to use the ODBC driver or OLE DB data provider that matches the data. Since you're working with Fox DBFs then you should use the FoxPro ODBC driver, or preferably the OLE DB data provider, even when you're accessing the data via VB.NET. Can you please post your connection string and the code you're using, and also the exact text of the error message(s) that you get Tag: SQL Select using variables
  - 13
    - Empty Picture Box Sure. You just need to amend the code which moves the record pointer (or directly changes the Picture property of the Image control) such that the Visible property of the Image control is set to False when the relevant field is empty, i.e. THISFORM.myImageControl.Visible = NOT EMPTY (mytable.myfield) Tag: SQL Select using variables
  - 14
    - how to direct the output of the report form object type 5 to a specific location. Yes this worked! Thank You! Is there someway to turn the message off that ask if you want to paste the link to the clipboard Tag: SQL Select using variables
  - 15
    - Record No. in Not Deleted Records Be aware that the number of non-deleted records is not the same as the record number (row number) of that last record that's not deleted. Naomi's solution tells you how many there are. If you want to know the actual record number (though why you'd need that, I can't imagine), you need to do something like: SELECT MAX(RECNO()) AS nRecNo; FROM YourTable ; WHERE NOT DELETED() ; INTO CURSOR LastRec Then LastRec.nRecNo contains the record number of the last non-deleted record. Be aware, also, that the code above only works for looking at a single table. If you add any other tables to the query, it will break. Tamar Tag: SQL Select using variables
  - 16
    - Control Excel Application #DEFINE xlMinimized -4140 #DEFINE xlMaximized -4137 loExcel.WindowState = xlMinimized loExcel.WindowState = xlMaximized loExcel.Caption = 'My New caption' loExcel.Width = loExcel.Height = Tag: SQL Select using variables
- Next
  - 1
    - CREATE TABLE, but where is it saved on the drive?! If you don't have a table, when why this is your connection string Tag: SQL Select using variables
  - 2
    - MasFoxPro (MoreFoxPro) campaign This is a message to inform you of the MasFoxPro (MoreFoxPro) campaign and encourage you to support it. We suppose your already know Microsoft announced that there would be no Visual Foxpro version 10. You can find it at: http://blogs.msdn.com/yag/archive/2007/03/13/message-to-the-vfp-community.aspx and: http://msdn2.microsoft.com/en-us/vfoxpro/bb308952.aspx Both announcements lack a convincing explanation or logical reason why Microsoft decided not to release a new version of Visual FoxPro. This announcement has been a major blow to the FoxPro community. This is especially true for the medium and small application developers (the vast majority of developers in Visual FoxPro), that have many years of Visual FoxPro code. It is not practical or cost effective to attempt to move these applications to Microsoft's recommended development environment (.NET). The community must rethink its future development platform. This campaign was created as an idea that originated in the Spanish FoxPro Microsoft newsgroups. Mas stands for more in Spanish. PortalFox and the MVPs that manage PortalFox, as a part of the community, decided to offer a way (hosting and support) to let the community to express itself and promote this campaign. March, 22th of 2007, a Wiki called MasFoxPro was created at: http://www.masfoxpro.com Remember that this campaign does not only focus on the collecting signatures. There are more actions (even more important than the signatures), More information regarding this campaign can be found at: http://www.masfoxpro.com/Masfoxpro We encourage you, your user group or web page support this campaign. Long term persistence could possibly give us an opportunity for some kind of success. Anyhow this your opportunity to FoxPro and the FoxPro Community. It is a worldwide campaign, not limited to any specific user groups. If your users group or web page supports this campaign, you can add your URL and data here: http://www.masfoxpro.com/Sponsors If you want to link us (please do it), The official link is:: http://www.masfoxpro.com the logo located at: http://www.masfoxpro.com/images/logo.gif Also we ask you to pass this email to other users, user groups, developers, press, blogs, etc. Is quite relevant that this campaign have world wide participation. Thanks for your attention. PD: PortalFox is the main Spanish talking developers portal in Visual FoxPro. With over than 28,000 registered users worldwide, is affectionately known as "El Portal del Pueblo" (People's Portal). More info regarding PortalFox is available at: http://www.masfoxpro.com/PortalFox http://www.portalfox.com For more info regarding the MasFoxPro campaign go to his website ( http://www.masfoxpro.com ) or contact with managers@masfoxpro.com Tag: SQL Select using variables
  - 3
    - Server Name? You may use: http://www.news2news.com/vfp/ group=49&function=254 to retrieve server name. Tag: SQL Select using variables
  - 4
    - REPLACE command help wanted Dave M. wrote: This looks line a CDX problem to me , maybe network security on it. Delete it and recreate. Check network security . All operations are local. No network is involved. What makes you think it is a network security issue If I do DELETE and INSERT a new record it will mess my "natural order" of records. It is very inconvenient in many respects. I am wondering if someone could try my codes on their computers. It can be done from VB or C#, perhaps J# whatever is available. If they work for someone it will give me a totally new perspective. Tag: SQL Select using variables
  - 5
    - Top level windows are not activated If you are using a main program who is doing a main form (with READ EVENTS), at form you may check properties: WindowsType = Modal ShowWindow = As Top-Level form Autocenter = .T. (for test only) Tag: SQL Select using variables
  - 6
    - CONFIG in top-level application Hi Naomi, Thanks for the answer. 1) I think it is in the right place. I have a main directory in the root of the disc called C:\FOODAPP I have created this specifically for this application as i find it a pain to navigate around in the VisualStudio/vpf directory structure. The application builder seems to create a series of subdirectories called PROGS, REPORTS, LIBS, etc. In the root directory C:\FOODAPP is a file called config.fpw which was created by the app builder and this is the one i am editing. I then build.. an application called FOODAPP which creates a file in the directory which i can run inside Foxpro or I can build an executable which replaces the app with an exe and i can run that. I think i might have a path problem, as I am forever having to use 'set default to' and 'set path to' to get everything in the right place. But anyway, this is where the config is, but i don't know of the builder is including it when it is generating the .app or the .exe I know when i used to use fpw 2.6 that you had to add a call to the config on the desktop shortcut - and they still refer to that on the MSDN files but i can't see where you might do that with the application being built for you. 2) The user thing - i was just wondering if there was a data base created which stored the user names and passwords and security levels or something - or is just so that you can code for particular users. When i set up my user name it said something about being a 'basic user' whihc imples there are different security levels which can have different access rights and can be coded into the app. Thanks again. Tag: SQL Select using variables
  - 7
    - VFP9 application in same network with email server & VOIPs Hi dni. Thanks for the reply. I am using optimistic table buffering, multilocks off and optimize is on. Yes, tables are indexed. I am not sure if the network is using low bandwidth or if it's one of the factors as well Actually it works fine just before the VOIP was implemented. Regards Daniel Tag: SQL Select using variables
  - 8
    - Visual Foxpro Grid is hiding something from me :( Yo thanks man. that was really helpfull... and it ddnt decrease the performance of my form since when im creating a dummy cursor for the recordsource , i select .F. . thanks a lot Tag: SQL Select using variables
  - 9
    - How to get the dbf table's Field Name and Field Type and Field Size Check out the AFIELDS function, e.g. LOCAL ARRAY laStructure [1] lnNoOfFields = AFIELDS (laStructure, mytable) FOR lnField = 1 TO lnNoOfFields lcFieldName = laStructure [lnField, 1] lcFieldType = laStructure [lnField, 2] lnWidth = laStructure [lnField, 3] lnDecimals = laStructure [lnField, 4] ENDFOR Tag: SQL Select using variables
  - 10
    - VFP9 application in same network with email server & VOIPs Hi dni. Thanks for the reply. I am using optimistic table buffering, multilocks off and optimize is on. Yes, tables are indexed. I am not sure if the network is using low bandwidth or if it's one of the factors as well Actually it works fine just before the VOIP was implemented. Regards Daniel Tag: SQL Select using variables
  - 11
    - Accessing Web Service using Visual FoxPro 8 I much experience in FoxPro (not any really), but I have been given a task to to try to access a Web Service. I can define the service to access with the Task Pane easily enough but what I am having trouble doing is retrieving the data into a meaningful type when calling the method. For e.g. LOCAL ResultSet LOCAL loGMWService AS "XML Web Service" * LOCAL loGMWService AS "MSSOAP.SoapClient30" * Do not remove or alter following line. It is used to support IntelliSense for your XML Web service. *__VFPWSDef__: loGMWService = http://192.168.101.197/gmwservice/GMWService.asmx WSDL , GMWService , GMWServiceSoap LOCAL loException, lcErrorMsg, loWSHandler WITH THISFORM TRY loWSHandler = NEWOBJECT("WSHandler",IIF(VERSION(2)=0,"",HOME()+"FFC\")+"_ws3client.vcx") loGMWService = loWSHandler.SetupClient("http://192.168.101.197/gmwservice/GMWService.asmx WSDL", "GMWService", "GMWServiceSoap") * Call your XML Web service here. ex: leResult = loGMWService.SomeMethod() ResultSet = loGMWService.GetNextRecords(.TxtGroupID.Value, .TxtOfficeID.Value, 10) CATCH TO loException lcErrorMsg="Error: "+TRANSFORM(loException.Errorno)+" - "+loException.Message DO CASE CASE VARTYPE(loGMWService)#"O" * Handle SOAP error connecting to web service CASE !EMPTY(loGMWService.FaultCode) * Handle SOAP error calling method lcErrorMsg=lcErrorMsg+CHR(13)+loGMWService.Detail OTHERWISE * Handle other error ENDCASE * Use for debugging purposes MESSAGEBOX(lcErrorMsg) FINALLY ENDTRY ENDWITH The data returned into ResultSet of type 'ArrayofCommand', what options are available to how should I define ResultSet and be able to iterate through the resulting array which may not be of a set size Thanks for the help Tag: SQL Select using variables
  - 12
    - Get column name in SQL query Alex, Don't use evaluate here, use macro, since macro will perform faster in this context. I've just responded on a different forum with this test: CREATE CURSOR curTest (cField C( 10 )) FOR i = 1 TO 100000 INSERT INTO curTest VALUES ( 'AAAAAAA' ) next lcField = 'cField' lcNewField = 'NewField' lnSec = SECONDS () SELECT evaluate (m.lcField) as (m.lcNewField) FROM curTest INTO CURSOR ( 'curTest1' ) nofilter = MESSAGEBOX ( SECONDS () - m.lnSec) lnSec = SECONDS () SELECT &lcField as (m.lcNewField) FROM curTest INTO CURSOR ( 'curTest2' ) nofilter = MESSAGEBOX ( SECONDS () - m.lnSec) UPDATE. Apologize for using the word 'wrong' in the heat of the discussion. Tag: SQL Select using variables
  - 13
    - Delete any file/subfolder from a directory You may try: d= GETDIR () &&select folder d1=d+"*.*" ERASE &d1 &&delete all files RD &d &&remove folder Tag: SQL Select using variables
  - 14
    - RLOCK on a DBF file on a Windows Vista Shaded Folder Hi, Yes, DBF File is open in shared mode and multilock is on Tag: SQL Select using variables
  - 15
    - fox table question The easiest way is to check the size of the field (0 if it does not exist). Open the table EXCLUSIVE If Fsize("mycolumn") = 0 Alter Table mytable add column mycolumn C(10) EndIf Conversely, if you want to delete it. If Fsize("mycolumn") > 0 Alter Table mytable drop column mycolumn EndIf Tag: SQL Select using variables
  - 16
    - sam error 1529 I get this error when running sam and it wont go away please help me. here is what i get from beginning to end error (1526) error creating cursoradapter: connectivity error:[microsoft][odbc sql server driver] [dbnetlib] connection write(send)()). veiw tmppat not created. application sam version 3.0.765 program:rscursoradapter.doquery line 700 time 09/26/2007/ 10.09 am sql odbc connection is invalid how can I fix this Tag: SQL Select using variables

cdaviduik

I would like to create a SQL SELECT statement in VFP9 but instead of specifying the table explicitly I would like it to be read from a variable. For example:

TABLE_NAME = "table_to_select"

SELECT * FROM TABLE_NAME;
INTO CURSOR tbl_cur

and when I execute the above I would actually like this to happen:

SELECT * FROM table_to_select
INTO CURSOR tbl_cur

Any ideas on how to do this I have not been able to find a solution with VFP9 help or within the forums.
Thank you.

Re: SQL Select using variables

Markish

Hi,

It can be done through use of macros...

Code Snippet

Table_name='table_to_select'

select * from &table_name into cursor tbl_cur

Best regards,

Markish

Re: SQL Select using variables

hangover

or using a name expression, e.g.

Code Snippet

SELECT * FROM (TABLE_NAME) ;

INTO CURSOR tbl_cur

Dom

Re: SQL Select using variables

Naomi Nosonovsky

Not OR Smile Using the name expression in this context is the right answer.

You don't want to use macros when unnecessary.

Re: SQL Select using variables

Tamar E. Granor

And name expressions work in some cases where macros don't, specifically when there are spaces in the path.

Tamar

Re: SQL Select using variables

cdaviduik

Thank you very much for your help!