getting data from DBF into SQL Express

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Burtamus - 19 May 2006 21:34 GMT

I'm using VB.NET 2005 and SQL Express. I have WinApp set up and shows my
clients with no problem. I receive a DBF file from a supplier and would like
to suck that into SQL Express. Since SQL Express can't import the data
directly, I'm working a coding it.

I can connect to the DBF file using the VFP OLE DB data connector. The
question is how do I insert it into SQL. I'm not a strong database
programmer (I'm not really even a weak database programmer, it is my first
attempt)

I'm using this SQL statement
INSERT INTO SQLDataTable SELECT * FROM " & dbfDatabaseName & ";"

but it just comes back and says the file SQLDataTable.DBF does not exist.

Any assistance would be helpful.

Signature

TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================

Reply to this Message

Cindy Winegarden - 20 May 2006 23:01 GMT

Hi Burt,

Since the connection you're using to work with the DBFs is to a VFP DBC or
free table directory then the Insert Into clause of your SQL statement is
trying to insert data into a DBF. You can't connect to both SQL Server and
VFP at the same time (i.e. within one SQL statement.

Since you're trying to "suck data" into a SQL Express database I'd create a
linked server in the SQL Express DB. You can use:

EXEC master.dbo.sp_addlinkedserver
@server = N'YourLinkedServerNameHere',
@srvproduct=N'Your description here',
@provider=N'VFPOLEDB',
@datasrc=N'"C:\YourDirectoryHere\"',
@provstr=N'VFPOLEDB.1'

Then, to import the data make a connection to the SQL Server and issue
commands (either in a SQL code window or via a VB program) like:

Insert Into SQLDataTable
Select * From YourLinkedServerNameHere...YourDBF

Please note the three dots to represent the four-part naming convention for
the linked server table.

Signature

Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com

> I'm using VB.NET 2005 and SQL Express. I have WinApp set up and shows my
> clients with no problem. I receive a DBF file from a supplier and would
[quoted text clipped - 12 lines]
>
> Any assistance would be helpful.

Reply to this Message

Burtamus - 26 May 2006 22:21 GMT

Thanks Cindy.

OK, I ran the EXEC in SQL Server Management Studio and it came back
successfully and I can see my linked server under Server Objects/Linked
Servers (oddly enough)

When I debug my program and import the data, it comes back with "Incorrect
syntax near '.'."

I understand that parameterized queries are better ;-) so I did my best.
Where am I going wrong?

Here's the code:
Using cn = New
SqlConnection(Global.MyProgram.My.MySettings.Default.MyConnectionString)

Dim cm As SqlCommand = cn.CreateCommand
cm.CommandType = CommandType.Text

cm.CommandText = "Insert Into Previews Select * From @Server...@Table;"

cm.Parameters.AddWithValue("@Server", "D610_62RGC91")

cm.Parameters.AddWithValue("@Table", strShortDatabaseName)

cn.open()

cm.ExecuteNonQuery()

'Dim dr As DataReader = cm.ExecuteReader

cn.close()

End Using

Signature

TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================

> Hi Burt,
>
[quoted text clipped - 38 lines]
>>
>> Any assistance would be helpful.

Reply to this Message

Burtamus - 26 May 2006 22:42 GMT

Sorry, I hit a key before I was finished editing the entry. I try to keep my
edits nice and clean. No reason to quote a quote, etc.

BTW Cindy, I tried to go to your web page to get some other pointers of
FoxPro. It came up empty.

Signature

TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================

Reply to this Message

Cindy Winegarden - 26 May 2006 23:15 GMT

We've had some power outages lately and I need to check that the machine
it's on (lives behind my desk at home) is up and running. Thanks.

Signature

Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com

> BTW Cindy, I tried to go to your web page to get some other pointers of
> FoxPro. It came up empty.

Reply to this Message

Cindy Winegarden - 30 May 2006 00:37 GMT

Hi Burt,

Instead of piecing together a command string with some parts, the table
names, as variables your code makes SQL Server think you're passing tables
as paramters. Does code like this work for you?

Dim cn1 As New SQLConnection( _
"Data Source=(local);Initial Catalog=Test;Integrated
Security=True")
cn1.Open()
Dim cmd1 As New SqlCommand( _
"Insert Into Customers Select * From
VFP_Northwind...Customers " + _
"Where CustomerID Like 'A%'", cn1)
cmd1.ExecuteNonQuery()

Dim targetTable As String = "Customers"
Dim sourceTable As String = "VFP_Northwind...Customers"
Dim criteria As String = "'B%'"

Dim cmd2 As New SqlCommand( _
"Insert Into " + targetTable + " Select * From " + _
sourceTable + " Where CustomerID Like " + criteria, cn1)
cmd2.ExecuteNonQuery()

Signature

Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com

> Dim cm As SqlCommand = cn.CreateCommand
> cm.CommandType = CommandType.Text
[quoted text clipped - 4 lines]
>
> cm.Parameters.AddWithValue("@Table", strShortDatabaseName)

Reply to this Message

William (Bill) Vaughn - 31 May 2006 18:31 GMT

Use ADO.NET 2.0 BCP. It's fast and easy to use.

Signature

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Reply to this Message

getting data from DBF into SQL Express

Free Magazines