locked
how can i connect to paradox database file(.db) using ado.net RRS feed

  • Question

  • Hi ,

    I am developing a windws application. the back end is sql server 2000 and (another application storing clients info in paradox format)paradox(.db).it also using bde ver 5.1.

    1)What is the best way to connect to .db files(odbc/oledb/....)?how?

    2)is it better to convert all .db file to sql and then connect?(30 users are using the application that storing the info in .db format)

    3)will i be able to update paradox files using ado.net?

    4)can i connect paradox via sql server 2000 then use that table in my application?(the way we connect sql server 2000 via ms access)

     

    thanks

    Friday, July 28, 2006 11:00 PM

Answers

  • www.connectionstrings.com

    ODBC 
     5.X:

    • Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=c:\pathToDb\;Dbq=c:\pathToDb\;CollatingSequence=ASCII"

    •  7.X:
      "Provider=MSDASQL.1;Persist Security Info=False;Mode=Read;Extended Properties='DSN=Paradox;DBQ=C:\myDb;DefaultDir=C:\myDb;DriverId=538;FIL=Paradox 7.X;MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\myDb"
  •  OleDbConnection (.NET)

Saturday, July 29, 2006 12:30 AM

All replies

  • www.connectionstrings.com

    ODBC 
     5.X:

    • Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=c:\pathToDb\;Dbq=c:\pathToDb\;CollatingSequence=ASCII"

    •  7.X:
      "Provider=MSDASQL.1;Persist Security Info=False;Mode=Read;Extended Properties='DSN=Paradox;DBQ=C:\myDb;DefaultDir=C:\myDb;DriverId=538;FIL=Paradox 7.X;MaxBufferSize=2048;PageTimeout=600;';Initial Catalog=C:\myDb"
  •  OleDbConnection (.NET)

Saturday, July 29, 2006 12:30 AM
  •  vijay1 wrote:

    1)What is the best way to connect to .db files(odbc/oledb/....)?how?

     

    Use .Net Provider as noted above

     vijay1 wrote:
    2)is it better to convert all .db file to sql and then connect?(30 users are using the application that storing the info in .db format) 
    That's a business decision...personally I would recommend SQL Servers for all of your data needs (2005 preferably)

     vijay1 wrote:
    3)will i be able to update paradox files using ado.net? 
    Yes

     vijay1 wrote:
    4)can i connect paradox via sql server 2000 then use that table in my application?(the way we connect sql server 2000 via ms access) 
     If business requirements dictate using the paradox files then  I would connect independently otherwise put the data in SQLSvr and  just use SQL Server

    Saturday, July 29, 2006 12:38 AM
  • Thanks for the info. I tried using dsn or passing the provider info. I am able to retrieve data to gridview from some of the .db files .however I am unable to connect most of the .db files.  I am getting odbc error or some strange error at filling the dataset.  When I link those .db files via ms access, I am able to get data to datagridview. Also I am unable to link paradox tables via sql server 2000(link server option).

    thanks

    vijay1  

    Sunday, July 30, 2006 12:09 AM

  • You may want to specify the error message text that is being generated.
    Monday, July 31, 2006 1:55 PM
  • odbc is connection is looking for the .net driver. I could not find the odbc driver on my machine. so tried oledb connection. it worked. however i see only the  column name no data. My label box control shows that there are 27000 record count. I even tried a small data file

     

    Wednesday, August 2, 2006 2:51 PM
  • odbc is connection is looking for the .net driver. I could not find the odbc driver on my machine. so tried oledb connection. it worked. however i see only the  column name no data. My label box control shows that there are 27000 record count. I even tried a small data file

    1)what is dbq in odbc?

    Dim strconn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data;Extended Properties=Paradox 5.x;"

    'Dim conn As New OdbcConnection("DSN=paradox connection;DefaultDir=C:\data;" & "Dbq=C:\data;")

    'Dim strconn As String = "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox .5X;DefaultDir=C:\data;Dbq=C:\data;CollatingSequence=ASCII"

    Dim str As String

    str = "select * from email"

    Dim oledbconn As New OleDbConnection(strconn1)

    oledbconn.Open()

    Dim oledbcadp As New OleDbDataAdapter(str, oledbconn)

    Dim dt As New DataTable("email")

    oledbcadp.Fill(dt)

    Me.DataGridView1.DataSource = dt

    'Me.DataGridView1.DataMember = dt.Tables(0).TableName

    Label1.Text = CStr(Me.DataGridView1.RowCount)

    Wednesday, August 2, 2006 2:55 PM
  • First I tried this query "SELECT Count(*) as Total FROM Table" and it return the number, 7810.
    Then I used the code below and it returned the number 7811;

    private OleDbDataAdapter ConnectParadox(string pPath, string pQuery)
    {
    string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pPath+";Extended Properties=Paradox 5.x;";
    try
    {
    OleDbConnection oleConnection = new OleDbConnection(strConnection);
    oleConnection.Open();
    OleDbDataAdapter oleAdapter = new OleDbDataAdapter(pQuery, oleConnection);
    return oleAdapter;
    }
    catch (OleDbException e)
    {
    MessageBox.Show(e.Message);
    return null;
    }
    }

    private void Form1_Load(object sender, EventArgs e)
    {
    OleDbDataAdapter OleAdapt = ConnectParadox(@"c:\Database", "SELECT * FROM Table");
    DataTable oleTable = new DataTable("tablename");
    OleAdapt.Fill(oleTable);
    dataGridView1.DataSource = oleTable;
    this.Text = dataGridView1.RowCount.ToString(); //this returns 7811, but Count thing returns 7810
    }

    this code is so similar to yours. And I also able to see the rows and the data.

     

    Wednesday, February 7, 2007 9:49 AM
  • thanks for the info. i was able to solve the problem few months ago,here is my code:

    Private Sub cmdOldedb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOledb.Click

    Dim timer As New Stopwatch

    timer.Start()

    Dim oledbParadoxconn As New OleDbConnection

    Try

    Dim Sqltext As String = Me.txtSQL.Text

    Dim oledbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\data;Extended Properties=Paradox 5.x;"

    oledbParadoxconn.ConnectionString = oledbconnstr

    oledbParadoxconn.Open()

    Dim myDataAdapter As New OleDbDataAdapter(Sqltext, oledbParadoxconn)

    Dim dtgetdata As New DataSet

    myDataAdapter.Fill(dtgetdata, "table1")

    Me.DataGridView1.DataSource = dtgetdata

    Me.DataGridView1.DataMember = "table1"

    Me.TextBox1.Text = CStr(Me.DataGridView1.RowCount)

    Catch excep As Exception

    MessageBox.Show(" Error " & excep.ToString & "Message " & excep.Message & "Source " & excep.Source & "Stack " & excep.StackTrace)

    Finally

    If oledbParadoxconn.State = ConnectionState.Open Then

    oledbParadoxconn.Close()

    End If

    End Try

    Dim requestTime As System.TimeSpan = timer.Elapsed

    Me.RichTextBox1.Text = requestTime.ToString

    timer.Stop()

    End Sub

    Friday, February 9, 2007 6:36 PM
  • Hi!

    I'm allso trying to connect to Paradox database v.5.X

    Anyway... i want to synchronize 2 tables (Paradox and Access97). I know how to work with typed dataset, but i'm new to Untyped...

    I have no clue where to start, how to do it...
    I need some sample codes or tutorials or something... i'm a quick learner Wink
    Monday, June 9, 2008 7:02 PM
  • Hi,

    use the above code to select the data from paradox db and display on a datagridview. to insert or update use the following code.

     

    Private Sub cmdUpdate_Insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate_Insert.Click

     

     

    Dim oledbParadoxconn As New OleDbConnection

    Try

     

    'this textbox pass the sql statement(insert/update/select)

    'example:"select * from sometable"

     

    Dim Sqltext As String = Me.txtSQL.Text

    'path for database folder for paradox

    Dim oledbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\cp pro test data\Data;Extended Properties=Paradox 5.x;"

     

    oledbParadoxconn.ConnectionString = oledbconnstr

    oledbParadoxconn.Open()

     

    Dim cmdOledb As OleDbCommand = oledbParadoxconn.CreateCommand()

    cmdOledb.CommandType = CommandType.Text

    cmdOledb.CommandText = Sqltext

     

    Dim count As Integer = cmdOledb.ExecuteNonQuery()

    cmdOledb = Nothing

     

    MessageBox.Show("record updated : " & count.ToString)

     

    Catch OledbExp As OleDbException

    '

    If OledbExp.ErrorCode = -2147467259 Then

    MessageBox.Show(CStr(OledbExp.ErrorCode))

    End If

     

    Catch excep As Exception

    MessageBox.Show(" Error " & excep.Source & "Message " & excep.Message & "Source " & excep.Source & "Stack " & excep.StackTrace)

    Finally

     

    If oledbParadoxconn.State = ConnectionState.Open Then

    oledbParadoxconn.Close()

    End If

     

    End Try

     

    End Sub

     

    -----------------------

     

    Tuesday, June 10, 2008 12:33 AM
  • I'm not sure if i really need to display data from paradox to DataGridView...

     

    What i'm trying to achive is to run update query so i can synhronize data between two tables (paradox and access). Basicly i don't even need to create win app, i can run it from cmd, so console app would be enough. But let's forget about that Smile

     

    If i'm right, i need to create connection (let's say it name is conn1) to paradox 5.X and a connection (let's say its name is conn2) to access 97.

    Then i need to write a query and at the end delete some cells in paradox.

     

    I know how to do that trough Server explorer and Data sources. But i can't connect to paradox trough Server Explorer.

     

    i need some tuttorials, samples, sample code or smth.

    Tuesday, June 10, 2008 4:04 PM
  • Hi,

     

    for testing purpose, create a windows application in vb.net. add a command button(cmdoldedb), and a datagridview1

    .

    1)First check the paradox connection(assuming that paradox server is running on a server-->drive XSmile

    2)copy and paste the code below to check if you connect to paradox db

     

    Also you can goto odbc tool and create a dsn to paradox database or via access

     

    useful links:

    http://www.connectionstrings.com/?carrier=paradox

     

    http://dn.codegear.com/article/31014

     

    http://cm.megasolutions.net/blogs/ado_net/archive/2006/09/13/Making-the-Connection_3A00_-Accessing-Your-Database-using-ADO.NET.aspx

     

    http://www.functionx.com/vcnet/databases/paradox.htm

     

    godd luck

     

     

     

     

    Private Sub cmdOldedb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOledb.Click

     

    Dim oledbParadoxconn As New OleDbConnection

    Try

    Dim Sqltext As String = "Select * from sometable"

     

    'X:\data is the path where all the  paradox files resides(.db,..................)

    Dim oledbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\data;Extended Properties=Paradox 5.x;"

     

    oledbParadoxconn.ConnectionString = oledbconnstr

    oledbParadoxconn.Open()

    Dim myDataAdapter As New OleDbDataAdapter(Sqltext, oledbParadoxconn)

     

    Dim dtgetdata As New DataSet

    myDataAdapter.Fill(dtgetdata, "table1")

    Me.DataGridView1.DataSource = dtgetdata

    Me.DataGridView1.DataMember = "table1"

     

    Catch excep As Exception

    MessageBox.Show(" Error " & excep.ToString & "Message " & excep.Message & "Source " & excep.Source )

    Finally

    If oledbParadoxconn.State = ConnectionState.Open Then

    oledbParadoxconn.Close()

    End If

    End Try

     

     

    End Sub

    Friday, June 13, 2008 1:08 PM
  • hi!

    i tried a new aproach

    i linked table from paradox and table from access in access2007

    then:

    Code Snippet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim CRMconnection As New ADODB.Connection
             CRMconnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\baze\Synchro.accdb;Jet OLEDB:Database Password=prodajapass1;")
            CRMconnection.Execute("UPDATE Catania INNER JOIN CRM ON Catania.St_stranke = CRM.[Zunanji ID] SET Catania.www = CRM.URL")
              CRMconnection.Close()
        End Sub


        i get error DataType mismatch

    St_stranke is AutoNumber Long Intiger and Zunanji ID is Text 20

    i know how to do it in access

     
    Code Snippet

    UPDATE Catania INNER JOIN CRM ON Catania.St_stranke = val(CRM.[Zunanji ID]) SET Catania.www = CRM.URL


    how do i do it in VB?
    Friday, June 13, 2008 1:16 PM
  • you have to cast the datatype

     

    http://www.startvbdotnet.com/language/conversion.aspx

     

    Friday, June 13, 2008 1:42 PM
  • i don't get it

    Isn't there any way to convert the datatype in query, like in access?
    Friday, June 13, 2008 2:21 PM