Question Use ODBC DSN's to Access / Manipulate Data

[cathalo]

Hey Community,

I have done some vb.net courses in school, however none with database or getting into inheritance etc. I specialized in System Admin.

I now have the need to create an application that extracts data from our ERP software. The software uses CISAM files and we have Transoft ODBC connections available to the data.

I'm lost on how to access this data. I'm using MS VB 2008 Express edition. I recently downloaded and installed the references found here (How to use the ODBC .NET Managed Provider in Visual Basic .NET and connection strings) on how to make an ODBC connection.

I have created a button that does the following successfully, which displays the first record in a row - I terminate the app to stop it.

 Dim conn As OdbcConnection
        Dim connectionString As String
        Dim comm As OdbcCommand
        Dim dr As OdbcDataReader
        Dim sqlStr As String

        connectionString = "dsn=Encore50A;uid=;pwd=;"
        conn = New OdbcConnection(connectionString)
        sqlStr = "Select * from INVPRICE"

        conn.Open()
        comm = New OdbcCommand(sqlStr, conn)
        dr = comm.ExecuteReader()

        While (dr.Read())
            MsgBox(dr.GetValue(0).ToString())
        End While


        conn.Close()
        dr.Close()
        comm.Dispose()
        conn.Dispose()
    End Sub

There are few things I guess I need:

a) A good resource with examples so I can understand how this works
b) Showi me how to display the data in a DataGrid - with/without the ability to modify.
c) Figure out how (via programming) to copy tables from this odbc connection to a local MDB file.

Feel free to lead me to water, I can attempt to drink and come back with more questions

 

[cathalo]

Good to see the message has been read, no replies.

I've progressed a little - I have found some code (however I don't completely understand it) that creates an Access DB.

Public Function CreateAccessDatabase( _
    ByVal DatabaseFullPath As String) As Boolean
        Dim bAns As Boolean
        Dim cat As New ADOX.Catalog()
        Try

            'Make sure the folder
            'provided in the path exists. If file name w/o path 
            'is  specified,  the database will be created in your
            'application folder.

            Dim sCreateString As String
            sCreateString = _
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               DatabaseFullPath
            cat.Create(sCreateString)

            bAns = True

        Catch Excep As System.Runtime.InteropServices.COMException
            bAns = False
            'do whatever else you need to do here, log, 
            'msgbox etc.
        Finally
            cat = Nothing
        End Try
        Return bAns
    End Function

I have also found a few sample "CopyTable" examples - but receive various errors from these.

I will keep digging.

 

[cathalo]

I receive this message when attempting the code listed below.
Any ideas on this one?

ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function

Dim cn As New OdbcConnection("dsn=Encore50A;uid=;pwd=;")
        Dim cmd As OdbcCommand
        Dim adp As OdbcDataAdapter
        Dim ds As New DataSet

        cn.Open()
        cmd = New OdbcCommand("select * from INVPRICE", cn)
        adp = New OdbcDataAdapter(cmd)
        adp.Fill(ds, "StockCode")

        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "StockCode"
        'MsgBox(cn.State.ToString)
        cn.Close()

 

[jin29_neci]

try to configure your database file at ODBC Data Source Administrator that can be found at control panel>Aministrative Tools>Data Sources....

 

[cjard]

a) A good resource with examples so I can understand how this works

Not much to understand at this stage; youre opening a database conenction and reading from a database
If you want a tabular representation of your data, you'd be better using a DataAdapter and a DataTable. the adapters Fills the table

b) Showi me how to display the data in a DataGrid - with/without the ability to modify.

Modify depends on the driver ability to write; we don't know if that exists, but get cracking on with a relevant commandbuilder (OdbcCommandBuilder) to amke the Insert/Update/Delete Queries for you, or write your own, and use the DataAdapter Update() method to save changes

Showing the data in a DataGridView (not DataGrid; old component) is a matter of setting the grid's DataSource to be the filled DataTable

c) Figure out how (via programming) to copy tables from this odbc connection to a local MDB file.

You'd be better off reading the DW2 link in my signature, section Creating a Simple Data App, to give you a rought overwiew of datasets, datatables, etc

As for copying the tables intoa local db, you may find it better to have the jet driver do that directly

see connectionstrings.com if it has an entry for inline attachment to another database.. Jet supports some syntax something like

SELECT * INTO new_table FROM [dsn=Encore50A;uid=;pwd=;]

i.e. the jet (access) driver will connect the dsn, retrieve the data and copy it locally. Hopefully; there may be no need for your app to proxy it

 

[SysVic]

cathalo,

I take it you are trying to connect to Syspro using ODBC's.

Send me a message when you get this if you are still interestered.

Cheers

 

[cathalo]

Yes,

Not having success. Seems to be multiple methods - however can't get any of them to work.

 

[ChrisKirk]

Also using ODBC's to access Syspro CISAM database. Having some limited success but could really do with some help from someone who's been there before me!

I can programmatically read data with SQL SELECT/WHERE statements without any problem. If I use an UPDATE statement called with an execute.nonquery(), the data is updated BUT I then get the same 'IM001 Driver does not support this function' as described above by cathalo, and the Visual Studio 2008 VB app fails.

I can use a horrible workaround of enclosing the execute.nonquery() in a Try, Catch, End Try block and ignoring the exception, however this is not an acceptable solution.

Additionally I get the same error if I attempt to visually create a uSQL ODBC data source in a Visual Studio project. I get as far as the point where I can choose which database objects to include in the project and then the system fails with the IM001 message.

The UPDATE statement works before I get the error; the data is most definitely updated. This implies to me that the problem is a driver/Visual Studio combination issue and that maybe VS is not correctly interpreting the SQLSUCCESS info supplied back from the driver after updating data, would you agree? I think the problem of visually creating a datasource is probably exactly the same cause. Has anyone had any success in integrating a combination of Syspro CISAM, uSQL and Visual Studio 2008 and avoiding this problem, or is this simply not a match made in heaven?

Thanks.