Interbase DataTable

[benjamin]

Hi,

I'm trying to connect an interbase database using updateable datatables (connected to a datagrid).

Currently i have to access it using their dll's

Dim conInput As New BdpConnection
Dim cmdInput As BdpCommand
Dim rdrInput As BdpDataReader

Then i attach the datareader into the datatable and return (from a function taking an sql query).

Then allocate the datasource to the datagrid.

This works fine - displays no problem in the grid.

However, I want to be able to update the datatable back to the interbase database.

Normally i would have a dataset with a datatable (for sql connections), but am finding this difficult via pure code.

Am wondering what is the best way to do this. Should i keep looking to declare a table adapter (somehow?) or pass the altered datatable back to some function to manually go through each record and update?

Any help appreciated.

 

[MattP]

Interbase connection string samples - ConnectionStrings.com

I would start looking here for Interbase providers.

 

[jmcilhinney]

See the example here and just substitute your types for the SqlClient types.

 

[benjamin]

Hi,

Thanks for the replies.

Possibly i didn't word my question correctly.

I have the connection strings and have the data being retrieved as i want.

And can manually run update statements to the interbase server; but was seeking to learn how to do a clean datatable update - and if this would work using the connection strings at all.

As I currently have a datareader allocated to a datatable (which i then plug into the datagrid) was wanting to know how i could get it to update cleanly.

 

[jmcilhinney]

No, you don't have a DataReader allocated to a DataTable. What you have is a DataTable populating itself by reading the schema and data from a DataReader. If you have followed the link I provided and READ what it contains then will know that what you are doing there is exactly what I did in the example entitled "Retrieving multiple records for display that will not be updated". Is that what you want to do? No, by your own admission it is not. Have you read the titles on the other examples? Does "Retrieving multiple records for display and editing, then saving the changes" sound more like what you want to do? I think you should have a closer look at that example.

 

[cjard]

As I currently have a datareader allocated to a datatable (which i then plug into the datagrid) was wanting to know how i could get it to update cleanly.

Er, DataReaders arent for DataTables, DataAdapters are. A DA uses a DR under the hood to read data and places it into a DT or sends changes back. Naturally, when you set the .SelectComamnd of a DA it becomes able to download data from a database. When you likewise set the .UpdateCommand, .InsertCommand and/or .DeleteComamnd it gains the ability to insert, update or delete records from DT to database

If you have an Odbc driver for interbase there shouldnt be any reason why you cannot use the dataset wizards. If youre using some .NET interbase drivers directly there is no reason why you cannot create a table of the same schema in SQLserver, use the wizards to make a tableadapter for that table, then take all the code the designer generated and modify it (in a new class, not the original one otherwise your edits will be lost) so that it works with interbase (change SQLs so that parameters are named/declared correctly etc)

 

[benjamin]

Hi Guys,

Firsty just wanted to thank you for your input - very helpful indeed.

Yes I think i've gone askew a little, i was using a datareader and doing a datatable.load of the datareader then setting this to the datasource of the datagridview. Is displaying fine, but the updating was my problem (other than manually using a cyclical thing through the changed records and updating individually) - which i know is painful.

So, i've had a good read through the 'Retrieving multiple records for display and editing, then saving the changes' and yes looks good and after a few substitutions of the sqltypes as suggested returns the data i'm after to the datagridview.

My problem now, which is obviously my ignorance is linking the update commands to the fields in the datagridview.


The error received on the Me.adapter.Update(Me.table) is:

Token unknown - line 1, char 32
@

Which to me is the parameter. Which i am actually adding.. but suspect i havn't linked these to the datagridview fields correctly? Or that possibly interbase cannot handle these? (i've found it quite painful to use at times with sql styled queries).

Am wondering if anyone has a decent example of this being linked up correctly.

I've done this via the wizard for sql, and updates are not a problem - so.. wondering if there is supporting code for the datagrid control linked with the provided examples? Or am happy to post the code i am testing this with.

any help appreciated.

 

[MattP]

I believe interbase supports both named and positional parameters in queries. Try using a : to designate your named parameter rather than an @.

Positional:

INSERT INTO Location (Country, City) VALUES (?, ?)

Named:

INSERT INTO Location (Country, City) VALUES (:Country, :City)

 

[benjamin]

Yes looks like thats it.. although i keep getting messages telling me that the columns don't actually exist!!! And i know they do.

aaaaah

 

[cjard]

your column-to-parameter mapping is probably broken. it is not enough to call your parameters the same names as your datatable columns, you need to set the .SourceColumnMapping of the parameter to the name of the column

I get the same thing when I work with oracle; it doesnt prepopulate the column names into this property. Fortunately, I can do it in the designer so there is less messing about..

 

[benjamin]

Thanks all, jmcilhinney and mattp, only solution has been to use the ?'s rather than named @parameter

Much appreciated

 

[cjard]

sounds like youre using an odbc or oledb driver.. ?

Can you get the designer to do anything for you?

 

[cjard]

I did some research and it seems to depend on the driver youre using. The one from IBProvider.com Interbase and Firebird Developer's manual for Visual Studio Net. Part 1. Introduction to ADO .Net. indicates use of ? or :namedParameter is acceptable. Borland Data Provider Borland Data Provider (BDP) for the Microsoft.NET Framework (2004) indicated only unnamed etc..

Refer to your dicumantation for whether your chosen driver supports named parameters