Fill Command

[SBTBILL]

In VB there is a command called fill which moves a record set into a grid or other container. Anybody know of something similar in VFP.

 

[TamarGranor]

Can you describe a little better what you want the command to do? What are you starting with? What do you want to end up with?

Tamar

 

[SBTBILL]

I'm talking about a command that would take an entire ADO data set and move it to a dbf. Or take an entire dbf and insert it into an SQL table in one command. IE. read contents of rs.table1 into dbf temp or insert into sqltable1 dbf temp. I can do both these easily a record at a time but I was just wondering if anyone knew of a batch method that could be used from within VFP code.

 

[Mike Lewis]

In your first post, you said you wanted to move the data to a "grid or other container".

Now you are saying you want to move it a DBF or a "SQL table".

You'll get a better answer if you are clearer in your own mind about what you are asking.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SBTBILL]

VB has a command to fill a grid with a table. You'd use something like grid1.fill.

I'm basically asking about both these things.

 

[Olaf Doschke]

Why the impedance mismatch with ADO and a record set at all? What is your backend you query with ADO?

There is infact Rs2dbf.prg here:

http://support.microsoft.com/?scid=kb;en-us;192692&x=16&y=15

But it also works recordwise. There's a much easier way in using the Cursoradapter with an ADO connection to generate a foxpro cursor you can use with vfp controls as usual.

Bye, Olaf.

 

[TamarGranor]

A cursoradapter can do this for you with the CursorFill method. You do have to set it up first, though.

Tamar

 

[JRB-Bldr]

I agree with Mike above in that what you are asking to do is not clear.

"moves a record set into a grid"
You can obviously use the SELECT SQL command to put a set of records into a cursor to use in a Grid.

SELECT *;
   FROM MyVFPTable;
   WHERE <whatever criteria>
   INTO CURSOR GridData

"Or take an entire dbf and insert it into an SQL table in one command."

If the SQL Server table was opened in an Updateable Remote View and the fields of the 2 data tables were the same name and type then you can use the APPEND command.

USE MyVFPTable IN 0

OPEN DATABASE SqlSrvr
USE MySQLTable
SELECT MySQLTable
APPEND FROM DBF('MyVFPTable')
=TABLEUPDATE()
CLOSE DATABASES

Once you have clarified what you want to do, it will be easier for us to offer advice which may indeed work for you.

Good Luck,
JRB-Bldr

 

[SBTBILL]

looked at what OlafDoschke suggested.

Currently what I do is create a temporary table.

Then I get the record set I want

select fielda, fieldb, fieldc from table1 outer join table2 on common field where criteria

rs=connection(grab)
if not (rs.bof() and rs.eof())
rs.movefirst
do while not rs.eof()
select a_tmpf1
append blank
replace dbfield1 with nvl(rs.fields("fielda").value,"")
replace dbfield2 with nvl(rs.fields("fieldb").value,"")
replace dbfield3 with nvl(rs.fields("fieldc").value,"")
rs.movenext
enddo
endif
rs.close
connection.close

What I'm wondering is, is there an easier or faster way?

Now most of the time I'm not just grabbing data. Once I've got it I sort it, merge it with non-sql tables, and analysis it so my temp table often has more fields then I'm loading from SQL.

The only difference I can see with Olaf's is that it is generic. Am I missing something?

 

[Mike Lewis]

SBTBill,

Am I missing something?

I don't know if you are missing something, but I certainly am.

I've just re-read your question, and the answers so far, and I still don't get it. Where does the "grid or other container" come into it?

And, by the way, what exactly do you mean by a "SQL table". How does that differ from any other table?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[SBTBILL]

Right now I use DBF's either SBT 6.0 which are free tables or temporary tables. Then we have an app that use an MSSQL back end. Most of what I do is merging the two. I.e. I move information back and forth, present data in reports and the like. I normally work in VFP 9, but lately have done a bit in ASP.NET using VB. Also write some MSSQL stored procedures and view. I put a lot of the reports into grids.

In VB I do the following

grabline = "Select Distributors.dbo.distributors.dist_id, "
grabline = grabline & " distributors.dbo.distributors.firstname, "
grabline = grabline & " distributors.dbo.distributors.lastname, "
grabline = grabline & " distributors.dbo.distributors.address1, "
grabline = grabline & " distributors.dbo.distributors.address2, "
grabline = grabline & " distributors.dbo.distributors.city, "
grabline = grabline & " distributors.dbo.distributors.state, "
grabline = grabline & " distributors.dbo.distributors.zip, "
grabline = grabline & " distributors.dbo.distributors.phone, "
grabline = grabline & " distributors.dbo.distributors.country "
grabline = grabline & " from distributors.dbo.distributors "
grabline = grabline & " where distributors.dbo.distributors.dist_id = " & mdist_id
GRAB.Open()
Dim grab2 As New SqlCommand(grabline, GRAB)
'
Dim G1 As New SqlDataAdapter(grab2)
Dim G2 As New DataSet
'
G1.Fill(G2, "THEdist")

My question which probably wasn't phrased well was is there something similar to G1.Fill(G2,"Thedist") in VFP? I don't have a problem moving data into VFP but just wondering if there is a faster way then what I use.

 

[TamarGranor]

As I said yesterday, you can do this with a CursorAdapter.

Tamar

 

[SBTBILL]

Ok Tamara I'll look into that. Can you tell me any good places to get more info on a CursorAdapter?

 

[cbasoz]

I think you meant IDataAdapter.Fill() method. The closest thing in VFP is a CursorAdapter's CursorFill() method. However you can also use use, select-SQL, RemoteView, SPT ... in VFP and you don't need to create an ADO recordset in between. But if you want to, then easiest way is to create a cursoradapter using ADO datasourcetype. If you type:

dx[spacebar][enter]

in command window, dataexplorer pops up and you can create a connection there, drag & drop a table on to a code window. VFP would write all the code for you. Here is a short video that I have recorded some time ago:

http://www.screencast.com/t/MzZlOWFmZTc

Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Cursoradapter has quite a good builder you can use, SBTBILL.
Despite this Bernard Bout has some tutorials on Cursoradapter, just google that. Last not least Cetin also has shown how to use ADO recordsources with Cursoradapter, which might or might not be a necessity in your case.

You say you work with ADO, the question still is why would you work on ADO recordsets, if it's SQL Server you query using Cursoradapter or SPT can be done using ODBC and te SQL Server Native Driver.

Bye, Olaf.

 

[cbasoz]

Olaf,
Maybe that is because VFP is problematic with SPT when it is SQL server 2005 and later. ADO on the other hand works right.

Cetin Basoz
MS Foxpro MVP, MCP

 

[SBTBILL]

Never heard of the cursoradapter before this thread.

 

[Mike Lewis]

VFP is problematic with SPT when it is SQL server 2005 and later

Cetin,

You've got me worried. I'm currently working on project that uses SPT with SQL Server 2005. Can you say what these problems are? I haven't come across any yet, but it's early days.

Thanks.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

1. cursoradapter exists since VFP8, so it exists in VFP since January 31, 2003 (release date of VFP8) and you could get hands on it in the last quarter of 2002.

2. I've seen a few problems with some drivers on SQL Server 2005, eg varchar(MAX) fields come in as C(0). But that's problems related to the driver, not an SPT problem. The same goes for cursoradapter or remote views, so the problem is in the driver or how foxpro uses it. Nevertheless I have reimplemented a DBC oriented vfp9 app with SQL2005. It's a flagship app of our biggest customer and works like a charm with SPT and cursoradapter based on ODBC.

Bye, Olaf.

 

[cbasoz]

Olaf,
Wrong, the problems are not related to the SQL server drivers. Those SQL server drivers work just as they should with other tools/languages (say VB, Jscript ...). It is VFP that cannot handle new datatypes of MSSQL when used with an ODBC driver.

Mike,
The problem is with new datatypes. If you don't use those new types then you wouldn't see the problems (you may live without those types but why you should when they are available). If you use new datatypes and new drivers that are aware of those types (ie: "SQL Server Native Client 10.0" - SQL 2008 driver) then you get "" with some types instead of data content. With binary types either too you get 0 length data. If you use old driver (SQL 2000's "SQL Server") then you get those as memo and image but this type you lose other types like date, vice versa. In summary, I think the days with VFP + SQL server ODBC is ended.


Cetin Basoz
MS Foxpro MVP, MCP