Karenros

Hi i am trying to transfer the contents of a dbf file to sql server 2005 and this is my error message

'C:\Documents and Settings\Karen\My Documents\Visual Studio 2005\WebSites\ASC1\pdf\client\AS.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

This is my code

Code Block

strAS = Path.GetFileName(FlAS.PostedFile.FileName)

location = Server.MapPath("pdf/Client/" & strAS)

If (FlAS.PostedFile.FileName.ToLower.EndsWith(".dbf")) Then

FlAS.PostedFile.SaveAs(location)

Try

'Connection string to a dbase file

Dim dbfConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=True;Extended Properties=dBase IV", location)

'create connection to the DBF file

Using connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(dbfConnectionString)

Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select from AS.dbf", connection)

connection.Open()

'Create a dbDatareader to the dbf file

Using dr As Data.Common.DbDataReader = command.ExecuteReader()

Dim sqlConnectionString As String = System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION)

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))

Dim query As String = "Truncate table ASDBF"

myConnection.Open()

Dim cmd As New SqlCommand(query, myConnection)

cmd.CommandType = CommandType.Text

cmd.ExecuteScalar()

myConnection.Close()

'bulk copy of sql server

Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)

BulkCopy.DestinationTableName = "ASDBF"

BulkCopy.WriteToServer(dr)

End Using

End Using

connection.Close()

End Using

Catch ex As Exception

Throw ex

End Try

Any help will be appreciated.

Regards

Karen



Re: .NET Framework Data Access and Storage (Urgent) How can i solve this error

Caddre

I don't see how you intend to move a Dbase database to SQL Server 2005 through ADO.NET, it is not practical. The reason is we move data by data types Dbase data types cannot be compared to SQL Server so you need to clean your data. If you are using SQL Server Express try the link below for the trial version of SQL Server 2005 install all components and use integration services to move your database to SQL Server. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx






Re: .NET Framework Data Access and Storage (Urgent) How can i solve this error

Karenros

Cadre,

Thanks for your answer... but i am getting an error at connection.open() and i think its something to do with the Connection string...

Can u please help me..

Regards

Karen





Re: .NET Framework Data Access and Storage (Urgent) How can i solve this error

Caddre

Connection Open is not relevant you cannot move Dbase database to SQL Server in ADO.NET, while you are at it read the docs for SQLBulk copy and you will know that SSIS is needed.






Re: .NET Framework Data Access and Storage (Urgent) How can i solve this error

Karenros

Caddre,

I got it to work by giving an Visual fox pro driver instead of a Microsoft Jet oledb driver.

Regards

Karen