none
(Urgent) How can i solve this error RRS feed

  • Question

  • 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

    Thursday, October 25, 2007 7:09 PM

Answers

  •  

    Caddre,

     

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

     

    Regards

    Karen

    Friday, October 26, 2007 2:28 PM

All replies

  • 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

     

    Thursday, October 25, 2007 8:39 PM
  • 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

    Thursday, October 25, 2007 8:49 PM
  • 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.

     

    Thursday, October 25, 2007 9:48 PM
  •  

    Caddre,

     

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

     

    Regards

    Karen

    Friday, October 26, 2007 2:28 PM